SQL Query
-
Hi guys, I hope you can help me with this query. Maybe because it is saturday morning I can't seem to work this out. I have 2 tables, Sales and SalesDetails. To simplify things say the Sales table consists of ID, and InvoiceDate, and SalesDetails consists of ID, Quantity, SaleID, ProductID. Now I want to return a list of the total daily sales for a specific month for a specific product, i.e. 01/03/2010 - 5, 02/03/2010 - 10 etc. Hope someone can help
-
Hi guys, I hope you can help me with this query. Maybe because it is saturday morning I can't seem to work this out. I have 2 tables, Sales and SalesDetails. To simplify things say the Sales table consists of ID, and InvoiceDate, and SalesDetails consists of ID, Quantity, SaleID, ProductID. Now I want to return a list of the total daily sales for a specific month for a specific product, i.e. 01/03/2010 - 5, 02/03/2010 - 10 etc. Hope someone can help
Try this
SELECT
SaleDate,
SUM(Amount)
FROM SalesDetail SD
INNER JOIN Sales S ON S.SaleID = SD.SaleID
WHERE SaleDate BETWEEN ThisDate AND ThatDate
GROUP BY SaleDateTricky bits will be if you have store time component in your SaleDate, you will need to strip the time off. You can also use the month/year component of the SaleDate instead of the between but it is messier.
Never underestimate the power of human stupidity RAH
-
Try this
SELECT
SaleDate,
SUM(Amount)
FROM SalesDetail SD
INNER JOIN Sales S ON S.SaleID = SD.SaleID
WHERE SaleDate BETWEEN ThisDate AND ThatDate
GROUP BY SaleDateTricky bits will be if you have store time component in your SaleDate, you will need to strip the time off. You can also use the month/year component of the SaleDate instead of the between but it is messier.
Never underestimate the power of human stupidity RAH
It worked. Thank you muchly. The dates are stored with no time component, so that was not an issue. The final SQL came out thus:
SELECT
s.InvDate, SUM(sd.Qty) as Amount
FROM SalesDetails sd
INNER JOIN Sales s ON s.ID = sd.SaleID
WHERE s.InvDate BETWEEN DATE(dteStart) AND DATE(dteEnd)
AND sd.ProdID = ProductID
GROUP BY s.InvDate; -
It worked. Thank you muchly. The dates are stored with no time component, so that was not an issue. The final SQL came out thus:
SELECT
s.InvDate, SUM(sd.Qty) as Amount
FROM SalesDetails sd
INNER JOIN Sales s ON s.ID = sd.SaleID
WHERE s.InvDate BETWEEN DATE(dteStart) AND DATE(dteEnd)
AND sd.ProdID = ProductID
GROUP BY s.InvDate;I'm curious, what database are you using as this does not work on SQL Server
DATE(dteStart)
Never underestimate the power of human stupidity RAH
-
I'm curious, what database are you using as this does not work on SQL Server
DATE(dteStart)
Never underestimate the power of human stupidity RAH
Hi I am using MySql, querying it from VB.Net. deStart and dteEnd are VB variables. MySql does not like VB date variables, and so it is best to convert to date in your SQL Query.