Hi, I have the following query stored:
SELECT dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, SUM(dbo.OrderDetails_Retail.Quantity) AS ProdQtyPerWeek, DATEPART(wk,
dbo.Orders_Retail.OrderDate) AS SalesWeek, YEAR(dbo.Orders_Retail.OrderDate) AS SalesYear
FROM dbo.OrderDetails_Retail INNER JOIN
dbo.Orders_Retail ON dbo.OrderDetails_Retail.OrderID = dbo.Orders_Retail.OrderID
WHERE (dbo.Orders_Retail.account = @.Account) AND (dbo.Orders_Retail.OrderStatus <> 'Deleted') AND (dbo.Orders_Retail.PayStatus <> 'Pending') AND
(dbo.Orders_Retail.OrderStatus <> 'Refunded') AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate, @.StartDate) <= 0) AND (DATEDIFF(d,
dbo.Orders_Retail.OrderDate, @.EndDate) >= 0)
GROUP BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.OrderDetails_Retail.ProductID,
dbo.OrderDetails_Retail.ProductName
ORDER BY dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk,
dbo.Orders_Retail.OrderDate)
Basically, it will return a load of results grouped by product for how much qty of that product was sold per week during a date range...
As my client wants to select multiple products at once to compare rather than do it in my application (I'm building something in ASP), I thought I might be able to do it on the database side.
The problem with the above is that.. lets say I select a date range that has weeks 1-4 in it.
Product 1 only sold qty's for weeks 1-2, product 2 sold for only week 3 and product 4 sold in all four weeks.
I'd get
Prod | Qty | Week
1 23 1
1 12 2
2 10 3
3 22 1
3 15 2
3 12 3
3 4 4
Although this looks fine - what I actually need is:
1 23 1
1 12 2
1 0 3
1 0 3
2 0 1
2 0 2
2 10 3
2 0 4
3 22 1
3 15 2
3 12 3
3 4 4
Does that make sense?
Any ideas on how to do this?
Yes, sure that makes sense, but therefore yopu will have to join your "data" table either with a calendar table, or with a subquery returning the weeks that are present for all products to appear in the resultset (cross joining)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||
ok the calendar tbl sounds the correct option - is something already in my db, or do i have to create it?
if so, any examples of how to do this?
|||Hi,
look here: http://www.aspfaq.com/2519
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||
ok I'm really a novice with all that..
I created it based on just pasting the example they had.. but that's not what I wanted..
I guess what I want is a calendar with the following:
Year, Week and that's it...
that one in that example had a load of extra data which I couldn't make head nor tail of....
|||
ok nearly there.. got my calendar as I want it, but now I've hit a bit of a problem with my join.
If I join my orders tbl by orderdate with the Dt column in my calendar.. then it doesn't return any retults.. here we are:
SELECT dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, SUM(dbo.OrderDetails_Retail.Quantity) AS ProdQtyPerWeek,
dbo.Calendar.W AS SalesWeek, dbo.Calendar.Y AS SalesYear
FROM dbo.Calendar LEFT OUTER JOIN
dbo.Orders_Retail ON dbo.Calendar.dt = dbo.Orders_Retail.OrderDate INNER JOIN
dbo.OrderDetails_Retail ON dbo.Orders_Retail.OrderID = dbo.OrderDetails_Retail.OrderID
WHERE (dbo.Orders_Retail.account = @.Account) AND (dbo.Orders_Retail.OrderStatus <> 'Deleted') AND (dbo.Orders_Retail.PayStatus <> 'Pending') AND
(dbo.Orders_Retail.OrderStatus <> 'Refunded') AND (DATEDIFF(dbo.Calendar.D, dbo.Calendar.dt, @.StartDate) <= 0) AND (DATEDIFF(dbo.Calendar.D,
dbo.Calendar.dt, @.EndDate) >= 0)
GROUP BY dbo.Calendar.Y, dbo.Calendar.W, dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName
ORDER BY dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, dbo.Calendar.Y, dbo.Calendar.W
Any ideas?
|||
ok I've narrowed it down to the fact that it doesn't like the join.
The one that joins the orderdate in my orders tbl with the dt field in my calendar tbl..
Even though both are datetime fields, both the same.. the only difference I can see between the two in terms of the values they have is that the order date usually has the date and then time, whereas the dt field just has the date...
therefore I was thinking the:
dbo.Calendar ON dbo.Orders_Retail.OrderDate = dbo.Calendar.dt
needs to be changed so that the orderdate vlaue removes it's time from it perhaps?
Any ideas?
James
||||||
Hi!,
I don't think I mad myself clear - I'm not getting an errors -I'm just not getting any results.
|||
anyone got any ideas?
I know exactly what the issue is....
in the calendar the date is held as 1/1/2003.. and up to a point the dates where held in the db like this also, however after a certain date, the time was also held so the date looks like 1/1/2003 00:00:00
This the results that aren't returned.
As I'm doing my JOIN on the dt held in the calendar tbl and then dt in the orders tbl.. as the times are held in the orders tbl also, this means that the join won't work as the calendar tbl will not hold the EXACT date and time held in the orders tbl..
does that make sense?
How do I fix that?
|||
Could you please post you table definitions to get a clear idea on how your tables look like. Seems like the datetime formats in the two columns are different. Schema definition would help in this case.
HTH,
~riyaz~
|||
ok I'm getting somewhere with this now.. however I have another problem.. here's the current query
SELECT dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.ProductBrand, SUM(dbo.OrderDetails_Retail.Quantity) AS Qty, DATEPART(wk,
dbo.Orders_Retail.OrderDate) AS SalesWeek, YEAR(dbo.Orders_Retail.OrderDate) AS SalesYear
FROM dbo.Orders_Retail INNER JOIN
dbo.OrderDetails_Retail ON dbo.Orders_Retail.OrderID = dbo.OrderDetails_Retail.OrderID INNER JOIN
dbo.Calendar ON CONVERT(datetime, CONVERT(Nvarchar, dbo.Orders_Retail.OrderDate, 102), 102) = CONVERT(datetime, CONVERT(Nvarchar,
dbo.Calendar.dt, 102), 102) RIGHT OUTER JOIN
dbo.Products ON dbo.OrderDetails_Retail.ProductID = dbo.Products.ProductID
WHERE (dbo.Orders_Retail.account = @.Account) AND (dbo.Orders_Retail.OrderStatus <> '
Deleted ') AND
(dbo.Orders_Retail.PayStatus <> ' Pending ') AND (dbo.Orders_Retail.OrderStatus <> ' Refunded ') AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate,
@.StartDate) <= 0) AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate, @.EndDate) >= 0)
GROUP BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.Products.ProductID, dbo.Products.ProductName,
dbo.Products.ProductBrand
ORDER BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.Products.ProductBrand, dbo.Products.ProductName
What this does is first get the products tbl, join that the orders_detail tbl, joins that the orders tbl which is finally joined to the calendar tbl.
What I'm finding is although it's working as intended, it's still not returning the product that haven't been bought..
I've narrowed this down to the following:
It I just to a Outer join on the products tbl to the order details tbl with no WHERE queries.. this returned what I want,, however as soon as I add the WHERE clause, it loses all the products and only shows the ones that have been purchased.
I would have thoughy my Outer join on the products tbl would get round this problem?
