SQL 2008 to 2000 translation? - 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

Transact-SQL (2000)

SQL 2008 to 2000 translation?

Author

Topic

redherring
Starting Member

12 Posts

Posted - 2011-01-18 : 09:33:59

Hi there. I'm wondering if anyone would be able to "translate" the following into something that would work with SQL 2000?

Thanks so much in advance!

***********************************

;WITH DayCounts
AS
(
SELECT O.orderdate
,COUNT(DISTINCT O.clientid) AS ClientCount
FROM tblOrder O
WHERE EXISTS
(
SELECT *
FROM tblOrderDetail D
WHERE D.orderID = O.orderID
AND EXISTS
(
SELECT *
FROM tblInventory I
WHERE I.inventoryID = D.inventoryID
AND I.producttype = 1
)
)
GROUP BY O.orderdate
)
SELECT D.[dayofmonth]
,COALESCE(C.ClientCount, 0) AS countofproduct
FROM tblDaysofMonth D
LEFT JOIN DayCounts C
ON D.[dayofmonth] = C.orderdate

quote:

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-18 : 09:55:46

An cte is nothing more than a description of how data should be produced. Just like a derived table.

SELECT		D.[dayofmonth],
		COALESCE(C.ClientCount, 0) AS countofproduct
FROM		tblDaysofMonth D
LEFT JOIN	(
			SELECT		O.orderdate,
					COUNT(DISTINCT O.clientid) AS ClientCount
			FROM		tblOrder O
			WHERE		EXISTS	(
							SELECT	*
							FROM	tblOrderDetail D
							WHERE	D.orderID = O.orderID
								AND EXISTS	(
											SELECT	*
											FROM	tblInventory I
											WHERE	I.inventoryID = D.inventoryID
												AND I.producttype = 1
										)
						)
			GROUP BY	O.orderdate
		) C ON D.[dayofmonth] = C.orderdate

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-18 : 10:02:45

I think this will perform much better

SELECT		m.[DayOfMonth],
		COUNT(x.ClientID) AS ClientCount
FROM		dbo.tblDaysOfMonth AS m
LEFT JOIN	(
			SELECT		o.OrderDate,
					o.ClientID
			FROM		dbo.tblOrder AS o
			INNER JOIN	dbo.tblOrderDetail AS d ON d.OrderID = o.OrderID
			INNER JOIN	dbo.tblInventory AS i ON i.InventoryID = d.InventoryID
						AND i.ProductType = 1
			GROUP BY	o.OrderDate,
					o.ClientID
		) AS x ON x.OrderDate = m.[DayOfMOnth]
GROUP BY	m.[DayOfMonth]

Can you try both my suggestion and post back the timings of the two suggestions?

N 56°04'39.26"
E 12°55'05.63"

redherring
Starting Member

12 Posts

Posted - 2011-01-18 : 10:25:24

Thanks so much for your replies. Truthfully, neither of these 2 options is giving me any client counts at all... i'm just getting back the days of the month and 0's for clientcount. I'm right now making sure that the data is as i expect and that i actually should be getting some values back, but I would be shocked if that were not the case. If you happen to see anything with either of your options that might be missing, please do pass it on.

Thanks again.

redherring
Starting Member

12 Posts

Posted - 2011-01-18 : 10:27:26

...and of course it was my problem. details to follow...

redherring
Starting Member

12 Posts

Posted - 2011-01-18 : 11:13:47

... and now that it's working, I can't remember what was wrong with my initial implementation of your code that caused me problems.

And please forgive me, but since there's actually "more to it" than I posted (additional criteria... I tried to dumb it down a bit) and since I've actually gotten a version of your second example working - so exciting - I don't think I'm going to bother trying your first example for comparison purposes. Thank you very, very much. I only wish I had asked for assistance sooner.

Thanks again!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-18 : 13:43:07

Ok, so if you timecompare my second suggestion (with your alterations) and your original query, is there a difference?
And if so, how much?

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources