March 24, 2017 at 5:09 am
Hi,
I want to get a summary of spend by the date field (s.LastEditedWhen) and stockID. Where am I going wrong (probably a few places).
SELECT GETDATE(s.LastEditedWhen), s.InvoiceID, s.UnitPrice, SUM(s.StockItemID) AS Product_Total, si.InvoiceID
FROM Sales.InvoiceLines s
INNER JOIN Sales.Invoices si
ON s.InvoiceID = si.InvoiceID
GROUP BY s.LastEditedWhen, s.StockItemID, s;
I think the Getdate function is wrong, but unsure how to group by when using a date. I also only want to show StockID (grouped), LastEditwhenWhen (grouped) and the spend. Probably a multitude of errors in here.
March 24, 2017 at 5:53 am
Some sample data that can be used to test the query would be helpful here.
Ideally, also include a snippet of the "real" output that you're getting and a sample of what you want the output to look like.
March 24, 2017 at 6:07 am
Getdate function returns the current system date (as Jeff will remind us), rather than the date portion of a date/time field.
If you want just the date (with no time) from a datetime field, there are a couple of options:
(1) CONVERT (or CAST) the datetime field to a datetime field
SELECT CONVERT(DATE, s.LastEditedWhen), s.InvoiceID, s.UnitPrice, SUM(s.StockItemID) AS Product_Total, si.InvoiceID
FROM Sales.InvoiceLines s
INNER JOIN Sales.Invoices si
ON s.InvoiceID = si.InvoiceID
GROUP BY CONVERT(DATE, s.LastEditedWhen), s.StockItemID, s;
(2) use datetime differences and additions (link to google for various explanations - pick the one you're most comfortable with) to strip off the time part of the field - this looks odd, but it works well:
SELECT DATEADD(DAY, DATEDIFF(DAY, '20160101', s.LastEditedWhen), '20160101'), s.InvoiceID, s.UnitPrice, SUM(s.StockItemID) AS Product_Total, si.InvoiceID
FROM Sales.InvoiceLines s
INNER JOIN Sales.Invoices si
ON s.InvoiceID = si.InvoiceID
GROUP BY DATEADD(DAY, DATEDIFF(DAY, '20160101', s.LastEditedWhen), '20160101'), s.StockItemID, s;
NB: It looks as though there are potentially some other issues with the code you've provided - the GROUP BY doesn't seem to have fields that match the non-aggregated fields in the SELECT...
Thomas Rushton
blog: https://thelonedba.wordpress.com
March 24, 2017 at 9:07 am
Thanks, sorry the code was a bit of mess where I was changing things around. Sorted now, thanks for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply