|
Assuming Microsoft SQL Server, ROW_NUMBER is your friend. 
WITH ctePayments As
(
SELECT
OrderId,
PaymentDate,
Terms,
PrevBal,
Payment,
Balance,
ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY PaymentDate DESC) As RN
FROM
OrderPymts
)
SELECT
O.OrderId,
O.ClientId,
O.OrderTotal,
P.Terms,
P.PaymentDate,
P.PrevBal,
P.Payment,
P.Balance
FROM
Orders As O
INNER JOIN ctePayments As P
ON P.OrderId = O.OrderId And P.RN = 1
WHERE
P.Balance >= 0.01
ORDER BY
O.OrderId
; ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn[^]
Alternatively, you could use a correlated sub-query:
SELECT
O.OrderId,
O.ClientId,
O.OrderTotal,
P.Terms,
P.PaymentDate,
P.PrevBal,
P.Payment,
P.Balance
FROM
Orders As O
CROSS JOIN
(
SELECT TOP 1 Terms, PaymentDate, PrevBal, Payment, Balance
FROM OrderPymts
WHERE OrderPymts.OrderId = O.OrderId
ORDER BY PaymentDate DESC
) As P
WHERE
P.Balance >= 0.01
ORDER BY
O.OrderId
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank You Richard. I'm coming From VB.Net learning C#, and SQL, Didn't think about a cross Join.
modified 20-Jan-23 18:43pm.
|
|
|
|
|
On a different note - it is probably not a good idea to link payments to an order, or at least make it a secondary connection. A client should have an account, orders place a debit on the account and payments credit the account. You may then want to link 1 or more payments to an order or 1 or more orders to a payment.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I needed it to show the last payment made on an invoice. But thanks for your advice.
|
|
|
|
|
I have an entity table which is begin queried into an entity:
UserEntity? user = dc.Users
.Where(x => x.UserName.CompareTo(entity.UserName) == 0)
.FirstOrDefault();
Now, if I add a property to the entity that does NOT exist in the underlying table, it throws an exception:
Microsoft.Data.SqlClient.SqlException: 'Invalid column name 'FullName'.'
FullName if not a table field. How can I prevent this exception for properties that don't exist in the data?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
If I remember correctly, you add the [NotMapped] attribute to the property in your model class.
public class User
{
public string FirstName { get; set; }
public string LastName { get; set; }
[NotMapped]
public string FullName { get; set; }
}
You could easily find this yourself just by Googling for "Entity Framework ignore property".
|
|
|
|
|
Thanks.
I spent time on Google looking around, but I wasn't really sure what I was looking for.
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
How to backup multiple database to Azure blob storage using native SQL Server Backup to URL option.
I have used the Ola Backup scripts with success but will like another option.
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@BackupType = 'FULL',
@Compress='Y',
@copyonly ='Y',
@Url='https://development.blob.core.windows.net/backups',
@BlockSize=65536,
@MaxTransferSize=4194304
--script to modify for multiple database
DECLARE @name VARCHAR(100) -- database name
Declare @url as Varchar(max) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name
-- specify database backup directory e.g. 'https://development.blob.core.windows.net/backups'
SET @url = 'https://development.blob.core.windows.net/backups'
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO URL = @URL
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
-- Any suggestions that does not include having to execute the backup command for each database will be helpful
|
|
|
|
|
Myself I would first look for a commercial solution. Rolling your own requires dealing with all of the possible error scenarios and notifications that must occur when an error happens.
But other than that there are two problems not one. So there should be two solutions.
1. Do the back up
2. Move the back up to the remote location.
|
|
|
|
|
Using EF Core 6 Code First.
I'm trying to create a Notes field.
entity.Property(e => e.Notes)
.HasColumnName("Notes")
.HasColumnType("varchar");
This becomes varchar(1) in the DB. How do I create a column with varchar(max)?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
|
Your post did not answer the question
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
When I look at the posted link it certainly looks to me like it provides an answer to the question asked.
So perhaps you might want to explain how that does not answer your question.
|
|
|
|
|
I need to create a subquery that pulls detail records for the header records in the main query.
But I'd like to display values from the detail records in the form of a comma separated list, like so, "value1, value2, value3". And this list should appear as an extra column in the result set.
Is there any way to achieve this in SQL Server?
SOLUTION: Use the STRING_AGG function! Cool.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
STRING_AGG is the best approach for SQL Server 2017 or later.
If you need to support earlier versions, you'll need an alternative:
Concatenating Row Values in Transact-SQL - Simple Talk[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Well today was a crappy day, thinking I can hit and run this small web page and clean it up. I spent hours modeling this in SQL Manager finally getting it to work, and then used it my PHP code, it worked but fell apart. Maybe I have it backwards, and I should call "Project" first, and then join "commission_Summary"
The first example works, but when I join the "project" table, it cancels out my "commission_summary.startup_check_date" filter. I get that or think that my JOIN is wrong, or that my JOIN is calling up all records and overriding my date filter. I tried different types of JOINs, but with no success.
SELECT
commission_summary.project_no,
commission_summary.EC,
commission_summary.TC,
CONVERT(CHAR(10), commission_summary.startup_check_date, 120) AS StartDate,
CONVERT(CHAR(10), commission_summary.finished_check_date, 120) AS StopDate
FROM commission_summary
WHERE CAST(commission_summary.startup_check_date AS SMALLDATETIME) >= CAST('2022-01-01 00:00:00' AS SMALLDATETIME)
ORDER BY commission_summary.startup_check_date DESC
I tried adding one join at a time, and it worked or produced my desired results using just JOIN. I don't understand why this returns all the records, well I know it's JOIN related, just not sure why. My theory, is that the "Project" JOIN may have a duplicate column name. I wonder if I should of used a wildcard for testing, like SELECT * FROM. This returns all the records
SELECT
commission_summary.project_no,
commission_summary.EC,
commission_summary.TC,
CONVERT(CHAR(10), commission_summary.startup_check_date, 120) AS StartDate,
CONVERT(CHAR(10), commission_summary.finished_check_date, 120) AS StopDate,
project.Status,
project.Sales_no,
project.swan_job,
employee.Employee_ID,
employee.FName,
employee.LName,
customer.Customer_no, customer.LName,
customer.FName,
customer.City
FROM commission_summary
JOIN project on commission_summary.project_no = project.project_no
JOIN employee on commission_summary.employee_id = employee.Employee_Id
JOIN customer on customer.Customer_No = project.Customer_no
WHERE CAST(commission_summary.startup_check_date AS SMALLDATETIME) >= CAST('2022-01-01 00:00:00' AS SMALLDATETIME)
AND project.status = 'construction' OR project.status = 'finished'
ORDER BY commission_summary.startup_check_date DESC
I'm confused by how this works, because it produces my desired results, of about 12 records in 2022.
SELECT
commission_summary.project_no,
commission_summary.EC,
commission_summary.TC,
CONVERT(CHAR(10),
commission_summary.startup_check_date, 120) AS StartDate,
CONVERT(CHAR(10), commission_summary.finished_check_date, 120) AS StopDate,
project.Status,
project.Sales_no,
project.swan_job,
employee.Employee_ID,
employee.FName,
employee.LName,
customer.Customer_no,
customer.LName,
customer.FName,
customer.City
FROM commission_summary
JOIN project on commission_summary.project_no = project.project_no
JOIN employee on commission_summary.employee_id = employee.Employee_Id
JOIN customer on customer.Customer_No = project.Customer_no
WHERE (CAST(commission_summary.startup_check_date AS SMALLDATETIME) >= CAST('2022-01-01 00:00:00' AS SMALLDATETIME))
AND project.status = 'construction' OR project.status = 'finished'
AND commission_summary.employee_id = '91'
ORDER BY commission_summary.startup_check_date DESC
Any point in the right direction would be appreciated, or even a simple explanation.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
You have an OR in there without any parentheses, that might be the problem. Try this:
AND (project.status = 'construction' OR project.status = 'finished')
Both sides of the OR are using the same table column, so you could use IN instead
AND project.status IN ('construction', 'finished')
|
|
|
|
|
That seems to be it. Wow!
I didn't catch that yesterday.
Thanks
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
I always use parens. Removes the question for me an future maintenance programmers as to what I thought the precedence was.
|
|
|
|
|
That was hard to describe.
Because of some bad business logic, or dealing with say vendors that retire or go out of business after 20 years, the question of what to do with them remains.
I'm thinking it's best to ask for the vendor anyways, and join any task if it exists. If it doesn't exist, then at least give me the vendor info, and the rest can be blank. So if I can get the VendorID, CompanyName and Vendor DeleteFlag even if the JOIN can't JOIN or the JOIN request doesn't exists, that would solve some issues for me.
I don't know what this is called, and the keywords I'm using don't bring up much except for NOT EXIST. I'm just looking for a point in the right direction or no it can't be done.
SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
proj_cost.cost,
proj_cost.pref_vendor
FROM vendor
LEFT OUTER JOIN proj_cost ON proj_cost.Vend_ID = vendor.Vendor_ID
WHERE proj_cost.proj_id = '4077'
AND proj_cost.proj_stage = 'construction'
AND proj_cost.vers_id = '8'
AND proj_cost.task_Oper = '6'
AND proj_cost.vend_id = '54'
AND proj_cost.task_id = 'TK_EX_044'
If it ain't broke don't fix it
Discover my world at jkirkerx.com
modified 21-Nov-22 15:36pm.
|
|
|
|
|
But it would interesting to be able to do it.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Not sure if this is quite what you are looking for, but here goes:
SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
proj_cost.cost,
proj_cost.pref_vendor
FROM vendor
LEFT OUTER JOIN proj_cost ON proj_cost.Vend_ID = vendor.Vendor_ID
WHERE (proj_cost.proj_id = '4077'
AND proj_cost.proj_stage = 'construction'
AND proj_cost.vers_id = '8'
AND proj_cost.task_Oper = '6'
AND proj_cost.vend_id = '54'
AND proj_cost.task_id = 'TK_EX_044')
OR proj_cost.Vend_ID IS NULL
|
|
|
|
|
That works perfect actually!
I did not think of the OR clause to get a null join.
This opens up some new possibilities for me to solve some business logic in this program I'm writing, and you made me a better coder today as well.
I looked at the logic closer, and I already had the vendors in an object that I passed to this DB function, so I just looped the object and filled in the blanks if it existed and that solved my problem on the cost analysis report. But I have a huge business logic factory or class that can use this type of help when building a construction project.
Thanks for the lesson!
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
The problem is that the WHERE clause is filtering out records where the proj_cost columns are Null.
The cleanest solution is to move those filters to the join itself:
SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
proj_cost.cost,
proj_cost.pref_vendor
FROM vendor
LEFT OUTER JOIN proj_cost
ON proj_cost.Vend_ID = vendor.Vendor_ID
AND proj_cost.proj_id = '4077'
AND proj_cost.proj_stage = 'construction'
AND proj_cost.vers_id = '8'
AND proj_cost.task_Oper = '6'
AND proj_cost.vend_id = '54'
AND proj_cost.task_id = 'TK_EX_044'
Alternatively, use a sub-query or common table expression[^]:
WITH cte As
(
SELECT
Vend_ID,
cost,
pref_vendor
FROM
proj_cost
WHERE proj_id = '4077'
AND proj_stage = 'construction'
AND vers_id = '8'
AND task_Oper = '6'
AND vend_id = '54'
AND task_id = 'TK_EX_044'
)
SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
cte.cost,
cte.pref_vendor
FROM vendor
LEFT OUTER JOIN cte ON cte.Vend_ID = vendor.Vendor_ID
Both approaches work by applying the filters to the proj_cost table before the join adds back any records from the vendor table which don't have a matching record.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
That's pretty clever. my mind was no where near thinking of it like that. And it runs pretty quick as well, and gives me exactly what I want with null records on a failed join. This opens up new possibilities for me with some of the business logic.
On a side note, I'm surprised that I didn't get laughed at for wanting to craft something like this. I couldn't think of a better way to handle this without writing a bunch of extra functions that might not be fast and reliable.
Thanks Richard!
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
