Cross Join problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cross Join problem

Author

Topic

TheOski
Starting Member

5 Posts

Posted - 2007-04-26 : 12:23:54

Hey,

I run into a small problem with one of my queries:

SELECT OrderItems.ProductCode AS 'Product Code',
(SELECT Name FROM Products WHERE Products.Code = OrderItems.ProductCode) AS 'Product Name',
COUNT(OrderItems.ProductCode) FROM Orders
INNER JOIN OrderItems ON Orders.uid = OrderItems.OrderID
CROSS JOIN
(
Select StartDate, EndDate FROM tempDate
) q
WHERE
OrderItems.ProductCode LIKE 'W24%'
AND Orders.OrderNumber IS NOT NULL
AND Orders.Void = 1
AND CAST(Orders.DateOrdered AS DateTime) BETWEEN q.StartDate AND q.EndDate
GROUP BY OrderItems.ProductCode

I am trying to count the number of times
W24 is sold in the each month to get matrix like that:

Product Code Product Name Jan Feb Mar Apr May Jun July
W24-BLUE Wedge™ 24 inch 87
W24-LEOPARD Wedge™ 24 inch 28
W24-RED Wedge™ 24 inch 73
W24-SCT Wedge™ 24 inch 32
W24-PURPLE Wedge™ 24 inch 57
W24-TAN Wedge™ 24 inch 67
W24-JCT Wedge™ 24 inch 11
W24-BLACK Wedge™ 24 inch 141
W24-TIG Wedge™ 24 inch 9
W24-WTIG Wedge™ 24 inch 13

The table tempdate has:

Start Date End Date
2007-01-01 00:00:00.000 2007-01-31 00:00:00.000
2007-02-01 00:00:00.000 2007-02-28 00:00:00.000

I cant get sql to output the consecutive columns. Anyone has idea how to tackle this?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 16:34:19

Holy cow!

SELECT		oi.ProductCode AS 'Product Code',
		COUNT(*) AS Total,
		SUM(CASE WHEN MONTH(o.DateOrdered) = 1 THEN 1 ELSE 0 END) AS 'January',
		SUM(CASE WHEN MONTH(o.DateOrdered) = 2 THEN 1 ELSE 0 END) AS 'February',
		SUM(CASE WHEN MONTH(o.DateOrdered) = 3 THEN 1 ELSE 0 END) AS 'March',
		SUM(CASE WHEN MONTH(o.DateOrdered) = 4 THEN 1 ELSE 0 END) AS 'April',
		SUM(CASE WHEN MONTH(o.DateOrdered) = 5 THEN 1 ELSE 0 END) AS 'May',
		SUM(CASE WHEN MONTH(o.DateOrdered) = 6 THEN 1 ELSE 0 END) AS 'June',
		SUM(CASE WHEN MONTH(o.DateOrdered) = 7 THEN 1 ELSE 0 END) AS 'July',
		SUM(CASE WHEN MONTH(o.DateOrdered) = 8 THEN 1 ELSE 0 END) AS 'August',
		SUM(CASE WHEN MONTH(o.DateOrdered) = 9 THEN 1 ELSE 0 END) AS 'September',
		SUM(CASE WHEN MONTH(o.DateOrdered) = 10 THEN 1 ELSE 0 END) AS 'October',
		SUM(CASE WHEN MONTH(o.DateOrdered) = 11 THEN 1 ELSE 0 END) AS 'November',
		SUM(CASE WHEN MONTH(o.DateOrdered) = 12 THEN 1 ELSE 0 END) AS 'December'
FROM		OrderItems AS oi
INNER JOIN	Orders AS o ON o.uID = oi.OrderID
WHERE		o.Void = 1
		AND o.OrderNumber IS NOT NULL
		AND oi.ProductCode LIKE 'W24%'
		AND o.DateOrdered >= '20070101'
		AND o.DateOrdered < '20080101'
GROUP BY	oi.ProductCode
ORDER BY	oi.ProductCode

Peter Larsson
Helsingborg, Sweden

TheOski
Starting Member

5 Posts

Posted - 2007-04-26 : 19:54:53

Thanks a lot Peso. Amazing.
Quick question what if I wanted to run this sale query by grouping Product Codes.

Lets say have a table:

Product,Group
W24-BLUE,1
W24-RED,1
W24-PURPLE,1
W24-BLACK,1
W30-BLUE,1
W30-RED,1
W30-PURPLE,1
W30-BLACK,1
ESC-BLACK,2
ESC-BLUE,2
ESC-RED,2
..........

Thanks again,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 19:56:44

[code]
SELECT oi.ProductCode AS 'Product Code',
oi.ProductGroup AS 'Product Group',
COUNT(*) AS Total,
SUM(CASE WHEN MONTH(o.DateOrdered) = 1 THEN 1 ELSE 0 END) AS 'January',
SUM(CASE WHEN MONTH(o.DateOrdered) = 2 THEN 1 ELSE 0 END) AS 'February',
SUM(CASE WHEN MONTH(o.DateOrdered) = 3 THEN 1 ELSE 0 END) AS 'March',
SUM(CASE WHEN MONTH(o.DateOrdered) = 4 THEN 1 ELSE 0 END) AS 'April',
SUM(CASE WHEN MONTH(o.DateOrdered) = 5 THEN 1 ELSE 0 END) AS 'May',
SUM(CASE WHEN MONTH(o.DateOrdered) = 6 THEN 1 ELSE 0 END) AS 'June',
SUM(CASE WHEN MONTH(o.DateOrdered) = 7 THEN 1 ELSE 0 END) AS 'July',
SUM(CASE WHEN MONTH(o.DateOrdered) = 8 THEN 1 ELSE 0 END) AS 'August',
SUM(CASE WHEN MONTH(o.DateOrdered) = 9 THEN 1 ELSE 0 END) AS 'September',
SUM(CASE WHEN MONTH(o.DateOrdered) = 10 THEN 1 ELSE 0 END) AS 'October',
SUM(CASE WHEN MONTH(o.DateOrdered) = 11 THEN 1 ELSE 0 END) AS 'November',
SUM(CASE WHEN MONTH(o.DateOrdered) = 12 THEN 1 ELSE 0 END) AS 'December'
FROM OrderItems AS oi
INNER JOIN Orders AS o ON o.uID = oi.OrderID
WHERE o.Void = 1
AND o.OrderNumber IS NOT NULL
AND oi.ProductCode LIKE 'W24%'
AND o.DateOrdered >= '20070101'
AND o.DateOrdered < '20080101'
GROUP BY oi.ProductCode,
oi.ProductGroup
ORDER BY oi.ProductCode,
oi.ProductGroup[/code]
Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources