Select Case Statement - 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)

Select Case Statement

Author

Topic

MrSQL
Starting Member

2 Posts

Posted - 2005-12-07 : 13:33:27

I have two different Queries that should return the same result but dont. Query A returns no results which is correct because none of the dates match the criteria. Query B does return result when it should not, can some on please explain the difference why, my brain is slow today i think.

Query A

SELECT Disposition,DispositionCode,Count(disposition) as MONDAY
FROM vCalltransactions
WHERE CONVERT(varchar,transactiondate,101) = 
CONVERT(VARCHAR(10),
DateAdd(dd,-((DATEPART(w,GETDATE()))-2),GETDATE())
,101)
group by disposition,DispositionCode

Query B

SELECT Disposition,DispositionCode,
COUNT(CASE WHEN CONVERT(varchar,transactiondate,101) 
= CONVERT(VARCHAR,
DateAdd(dd,-((DATEPART(w,GETDATE()))-2),GETDATE())
,101) THEN 
DispositionCode ELSE 0 END) AS MONDAY
from vCalltransactions
GROUP BY Disposition,DispositionCode

X002548
Not Just a Number

15586 Posts

Posted - 2005-12-07 : 14:22:31

Please read the hint link on my sig....we need DDL, sample data and expected results...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

MrSQL
Starting Member

2 Posts

Posted - 2005-12-07 : 14:41:17

Ok, but i dont think it requires those things, it more of a syntax problem. Here is the previous query i wrote to display the days of the week using SUM, but this time i need to do a count of the similar records instead of suming the numbers.

SELECT ds.Description,stat.DispositionID,SUM(CASE WHEN [timestamp] = 
CONVERT(VARCHAR(10),DateAdd(dd,-((DATEPART(w,GETDATE()))-2),GETDATE()),102) THEN 
DISPCOUNT ELSE 0 END) AS MONDAY,SUM(CASE WHEN [timestamp] = 
CONVERT(VARCHAR(10),DateAdd(dd,-((DATEPART(w,GETDATE()))-3),GETDATE()),102) THEN 
DISPCOUNT ELSE 0 END) AS TUESDAY,SUM(CASE WHEN [timestamp] = 
CONVERT(VARCHAR(10),DateAdd(dd,-((DATEPART(w,GETDATE()))-4),GETDATE()),102) THEN 
DISPCOUNT ELSE 0 END) AS WENDSDAY,SUM(CASE WHEN [timestamp] = 
CONVERT(VARCHAR(10),DateAdd(dd,-((DATEPART(w,GETDATE()))-5),GETDATE()),102) THEN DISPCOUNT ELSE 0 END) 
AS THURSDAY,SUM(CASE WHEN [timestamp] = CONVERT(VARCHAR(10),DateAdd(dd,-((DATEPART(w,GETDATE()))-6),
GETDATE()),102) THEN DISPCOUNT ELSE 0 END) AS FRIDAY,SUM(CASE WHEN [timestamp] = CONVERT(VARCHAR(10),
DateAdd(dd,-((DATEPART(w,GETDATE()))-7),GETDATE()),102) THEN DISPCOUNT ELSE 0 END) AS SATURDAY,
SUM(CASE WHEN [timestamp] BETWEEN CONVERT(VARCHAR(10),DateAdd(dd,-((DATEPART(d,GETDATE()))-1),
GETDATE()),102) AND CONVERT(VARCHAR(10),DateAdd(d,-day(DateAdd(m,1,GETDATE())),DateAdd(m,1,GETDATE())),
102) THEN DISPCOUNT ELSE 0 END) AS MTD,SUM(CASE WHEN [timestamp] BETWEEN 
CONVERT(VARCHAR(10),DateAdd(qq,DATEDIFF(qq,0,GETDATE()),0),102) AND CONVERT(VARCHAR(10),
DateAdd(d,-day(DateAdd(q,1,GETDATE())),DateAdd(q,1,GETDATE())),102) THEN DISPCOUNT ELSE 0 END) AS QTD,
SUM(CASE WHEN [timestamp] = [timestamp] THEN DISPCOUNT ELSE 0 END) AS PTD FROM rpt_dNCStat as Stat, 
DailySales as DS WHERE VENDORID = 'VENDOR'  AND stat.DispositionID = 
ds.DispoSitionID GROUP BY ds.Description,stat.DispositionID ORDER BY stat.DispositionID ASC

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-07 : 15:30:04

One big difference I see is that you have a WHERE clause in Query A but you do not have a WHERE clause in Query B.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources