Diff in Query for SQLServer 2K and SQLServer 2K5

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

General SQL Server Forums

New to SQL Server Programming

Diff in Query for SQLServer 2K and SQLServer 2K5

Author

Topic

jobejufranz
Starting Member

33 Posts

Posted - 2008-07-11 : 21:20:59

Hi guys.

Good day. i just want to clarify something. On my asp.net i have this dynamic query which was constructed through code. The thing is, this query is working on SQL Server 2005 but giving me error on SQL Server 2000. Any hint? TIA.


SELECT
	LEFT(MONTH(tDate),2) + '/' + LEFT(YEAR(tDate),4) AS 'Month',
	SUM(tDiscountPrice) AS 'Total'
FROM
	Transactions
WHERE
	tDate>='1/1/2008' AND tDate<'3/1/2008' AND
	tBranch='EastBranch' AND
	tVoid<>'YES'
GROUP BY 
	MONTH(tDate), 
	YEAR(tDate) 
ORDER BY 
	YEAR(tDate) DESC, 
	MONTH(tDate) DESC;

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-11 : 21:42:31

what's the error you get?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-11 : 22:45:02

2005 is more flexible in the group by & order by syntax. in 2000, the group by has to be exact of the select column


SELECT
	LEFT(MONTH(tDate),2) + '/' + LEFT(YEAR(tDate),4) AS 'Month',
	SUM(tDiscountPrice) AS 'Total'
FROM
	Transactions
WHERE
	tDate>='1/1/2008' AND tDate<'3/1/2008' AND
	tBranch='EastBranch' AND
	tVoid<>'YES'
GROUP BY 
	MONTH(tDate), 
	YEAR(tDate) 
        LEFT(MONTH(tDate),2) + '/' + LEFT(YEAR(tDate),4)
ORDER BY 
	YEAR(tDate) DESC, 
	MONTH(tDate) DESC;
       'Month'

KH
[spoiler]Time is always against us[/spoiler]

jobejufranz
Starting Member

33 Posts

Posted - 2008-07-11 : 23:44:14

quote:
Originally posted by khtan

2005 is more flexible in the group by & order by syntax. in 2000, the group by has to be exact of the select column

SELECT
LEFT(MONTH(tDate),2) + '/' + LEFT(YEAR(tDate),4) AS 'Month',
SUM(tDiscountPrice) AS 'Total'
FROM
Transactions
WHERE
tDate>='1/1/2008' AND tDate<'3/1/2008' AND
tBranch='EastBranch' AND
tVoid<>'YES'
GROUP BY
MONTH(tDate),
YEAR(tDate)
LEFT(MONTH(tDate),2) + '/' + LEFT(YEAR(tDate),4)
ORDER BY
YEAR(tDate) DESC,
MONTH(tDate) DESC;
'Month'

KH
[spoiler]Time is always against us[/spoiler]

Thanks for all the reply.

Here's the error generated when i run the query in sql 2k


Msg 8120, Level 16, State 1, Line 1
Column 'Transactions.tDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'Transactions.tDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'll try khtan's suggestion, if it will be successful, i'll use it both in sql 2k and sql 2k5.

Thanks again.

jobejufranz
Starting Member

33 Posts

Posted - 2008-07-11 : 23:57:35

I've tested query and so far it is working on both unfortunately, another problem arises on ORDER BY.

The result is below

9/2007
8/2007
7/2007
6/2008
5/2008
4/2008
3/2008
2/2008
12/2007
11/2007
10/2007
1/2008

Is there any possible solution where by i can sort the Date into the result below???

6/2008
5/2008
4/2008
3/2008
2/2008
1/2008
12/2007
11/2007
10/2007
9/2007
8/2007
7/2007

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-12 : 00:06:19

the easiest is don't format the date in t-sql and return the year / month and do the formating in your front end application

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-12 : 00:13:04

Or this way, if you absolutely cannot do the formatting in your front end application where the data is presented.

SELECT		
	LEFT(MONTH(YearMth),2) + '/' + LEFT(YEAR(YearMth),4) AS [MONTH],
	Total
FROM
(
	SELECT	DATEADD(MONTH, DATEDIFF(MONTH, 0, tDate), 0) AS YearMth,
		SUM(tDiscountPrice) AS Total
	FROM
		Transactions
	WHERE
		tDate	>= '20080101'   AND tDate < '20080301' AND
		tBranch =  'EastBranch' AND
		tVoid	<> 'YES'
	GROUP BY 
		DATEADD(MONTH, DATEDIFF(MONTH, 0, tDate), 0)
) AS t
ORDER BY 
	YearMth

KH
[spoiler]Time is always against us[/spoiler]

jobejufranz
Starting Member

33 Posts

Posted - 2008-07-12 : 00:24:42

quote:

Originally posted by khtan

Or this way, if you absolutely cannot do the formatting in your front end application where the data is presented.

SELECT		
	LEFT(MONTH(YearMth),2) + '/' + LEFT(YEAR(YearMth),4) AS [MONTH],
	Total
FROM
(
	SELECT	DATEADD(MONTH, DATEDIFF(MONTH, tDate, 0), 0) AS YearMth,
		SUM(tDiscountPrice) AS Total
	FROM
		Transactions
	WHERE
		tDate	>= '20080101'   AND tDate < '20080301' AND
		tBranch =  'EastBranch' AND
		tVoid	<> 'YES'
	GROUP BY 
		DATEADD(MONTH, DATEDIFF(MONTH, tDate, 0), 0)
) AS t
ORDER BY 
	YearMth

KH
[spoiler]Time is always against us[/spoiler]

khtan i really appreciate your reply, thanks. I tested your query but the result date is something like the one below

12/1791
1/1792

Is there something i miss or is it configuration of my sql server or server os?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-12 : 00:58:15

my bad. Got the datediff para the wrong way. Edited by post.

KH
[spoiler]Time is always against us[/spoiler]

jobejufranz
Starting Member

33 Posts

Posted - 2008-07-12 : 01:09:38

quote:
Originally posted by khtan

my bad. Got the datediff para the wrong way. Edited by post.

KH
[spoiler]Time is always against us[/spoiler]

Excellent! Exactly the thing i wanted. Thank you very much, appreciate it a lot.

Final request if your not that busy, can you explain this part


DATEADD(MONTH, DATEDIFF(MONTH, 0, tDate), 0)

Thanks again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-12 : 02:16:26

basically this "DATEADD(MONTH, DATEDIFF(MONTH, 0, tDate), 0)" will convert the tDate to 1st of the month

DATEDIFF(MONTH, 0, tDate) will give no of month diff between tDate and 0 (Date 0 is 1900-01-01) and then DATEADD that to 1900-01-01 again will give you back the 1st day of the month of tDate

KH
[spoiler]Time is always against us[/spoiler]

jobejufranz
Starting Member

33 Posts

Posted - 2008-07-13 : 21:30:21

, ok, you've converted all the date to the first day of every month and group from there. Excellent. I understand.

Thank you very much khtan. Really appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources