SQL Server 2008 Order Date and Product Differences
This is my first post, so if I was wrong about my post and in English, I apologize :)
I am already doing some research on google and unfortunately I still cannot solve this problem.
This is my problem, show the total orders by date for each product and I want to differentiate the products
Here's my example request
SELECT Orders.OrderDate
,SUM(OrderDetails.OrderDetailQuantity) AS totalOrdered
,Products.ProductId
FROM Orders
INNER JOIN OrderDetails ON Orders.OrderId = OrderDetails.OrderId
INNER JOIN Products ON OrderDetails.ProductId = Products.ProductId
GROUP BY Orders.OrderId
,Orders.OrderDate
,Products.ProductId
HAVING (CONVERT(VARCHAR, Orders.OrderDate, 23) BETWEEN @from AND @to)
Now I want to distinguish the product depending on OrderDate, I know the big problem here is DATE, but I don't know how to distinguish the date from this request.
Please help me. Thank.
PS: If you want to solve this in a linq expression it would be much appreciated :)
source to share
Sample data and desired results will help. When you say "distinctly" I am assuming that you mean the group. Note that in the WHERE clause, you do not need to throw Order.OrderDate if you make sure the timing of your @from and @to parameters is set correctly (to include each day). Its never recommended to apply the casting operation on the left side of the comparison.
SELECT --cast(Orders.OrderDate as date),
Products.ProductId
SUM(OrderDetails.OrderDetailQuantity) AS totalOrdered,
FROM Orders
INNER JOIN OrderDetails ON Orders.OrderId = OrderDetails.OrderId
INNER JOIN Products ON OrderDetails.ProductId = Products.ProductId
where Orders.OrderDate between cast(@from as date) AND cast(@to as date)
GROUP
BY --cast(Orders.OrderDate as date),
Products.ProductId
-- to illustrate:
declare @From datetime = '1/1/2000 10:30:22',
@To datetime = '1/3/2000 9:11:31'
declare @orders table (i int, dt datetime)
insert into @orders
values(1, '1/1/2000 8:00'),(2, '1/2/2000 10:00'), (3, '1/4/2000 3:00')
-- only returns i=2
select *
from @orders
where dt between @From and @To
-- returns i=1 & i=2
select *
from @orders
where dt between cast(@From as date) and cast(@To as date)
source to share