two different constraint for one column

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)

two different constraint for one column

Author

Topic

miclare
Starting Member

5 Posts

Posted - 2006-06-14 : 17:33:47

in northwind database(orders table), i want to return shipvia values for different date intervals in one query.

for example i want to return shipvia values for interval 01.08.1996 - 16.08.1996 and also for interval 16.09.1996 - 20.09.1996 in one query.

this seems very easy at first but i think it's a lot tricky, i cldnt manage to do that.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-15 : 03:32:37

Read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

miclare
Starting Member

5 Posts

Posted - 2006-06-16 : 13:54:15

i read about cross-tab reports, but this query gives error.

SELECT SUM(CASE (SCANN.DATE BETWEEN @date1 AND @date2) WHEN 1 THEN SCANN.BMENG END) AS QSold,
SUM(CASE SCANN.DATE BETWEEN DATEADD(YEAR, - 1, @date1) AND DATEADD(YEAR, - 1, @date2) WHEN 1 THEN SCANN.BMENG END) AS QSoldLastYear
FROM...
WHERE...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-16 : 14:45:53

quote:
Originally posted by miclare

i read about cross-tab reports, but this query gives error.

SELECT SUM(CASE (SCANN.DATE BETWEEN @date1 AND @date2) WHEN 1 THEN SCANN.BMENG END) AS QSold,
SUM(CASE SCANN.DATE BETWEEN DATEADD(YEAR, - 1, @date1) AND DATEADD(YEAR, - 1, @date2) WHEN 1 THEN SCANN.BMENG END) AS QSoldLastYear
FROM...
WHERE...

The format of your case statement is invalid.
case
when condition1
then value1
when condition2
then value2
else value3
end

CODO ERGO SUM

miclare
Starting Member

5 Posts

Posted - 2006-06-16 : 16:20:05

thanks Michael Valentine, no syntax error when written as stored procedure, but microsoft reporting services gives an error "access violation or syntax error". any comment?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-16 : 16:26:57

If you have a stored procedure that is working, why not use that in Reporting Services?

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources