CASE statement in SELECT - 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 2005 Forums

Transact-SQL (2005)

CASE statement in SELECT

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-07-30 : 10:36:26

I have this query here, where I have successfully COUNTed PatientID. However, I want to create 2 derived columns from PatientID, (PTS_DN and PTS_UP), each one that counting one only if the RECS_DOWN or RECS_UP column is greater than 0. So I added the 2 CASE statements you see plus a couple commented out that I tried. None of them work. I am pretty new at T-SQL and would greatly appreciate any help here:


DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SELECT @StartDate=('07/27/2009')
SELECT @EndDate=('07/28/2009')
SELECT     O_OPERATOR.OPERATOR_NAME
		, O_WORKSTATION.STATION_NAME
		, SYS_SYNCH_LOG.START_DATE_TIME
		, SYS_SYNCH_LOG.END_DATE_TIME
		, SYS_SYNCH_LOG.SYNC_MODE
		, SYS_SYNCH_LOG.SYNCH_TYPE
		, COUNT(DISTINCT SYS_SYNCH_LOG_DETAIL.PATIENT_ID) CASE WHEN SYS_SYNCH_LOG_DETAIL.RECORDS_UP > 0 THEN  ELSE 1 END PTS_UP 
		, COUNT(DISTINCT SYS_SYNCH_LOG_DETAIL.PATIENT_ID) CASE WHEN SYS_SYNCH_LOG_DETAIL.RECORDS_DOWN > 0 THEN 1 ELSE 0 END  PTS_DN
--		, CASE WHEN SYS_SYNCH_LOG_DETAIL.RECORDS_UP > 0 THEN COUNT(DISTINCT SYS_SYNCH_LOG_DETAIL.PATIENT_ID)  ELSE 0 END PTS_UP 
--		, CASE WHEN SYS_SYNCH_LOG_DETAIL.RECORDS_DOWN > 0 THEN COUNT(DISTINCT SYS_SYNCH_LOG_DETAIL.PATIENT_ID) ELSE 0 END  PTS_DN
		, SUM(SYS_SYNCH_LOG_DETAIL.RECORDS_UP) RECS_UP
		, SUM(SYS_SYNCH_LOG_DETAIL.RECORDS_DOWN) RECS_DOWN
		, COUNT( DISTINCT SYS_SYNCH_CONFLICT.CONFLICT_ID) CONF
FROM         SYS_SYNCH_LOG 
INNER JOIN  SYS_SYNCH_LOG_DETAIL ON SYS_SYNCH_LOG.SYNCH_LOG_ID = SYS_SYNCH_LOG_DETAIL.SYNCH_LOG_ID 
INNER JOIN  O_OPERATOR ON SYS_SYNCH_LOG.OPERATOR_ID = O_OPERATOR.OPERATOR_ID 
INNER JOIN  O_WORKSTATION ON SYS_SYNCH_LOG.STATION_ID = O_WORKSTATION.STATION_ID 
INNER JOIN  SYS_SYNCH_CONFLICT ON SYS_SYNCH_LOG.SYNCH_LOG_ID = SYS_SYNCH_CONFLICT.SYNCH_ID
WHERE     (SYS_SYNCH_LOG.START_DATE_TIME >= @StartDate) AND (SYS_SYNCH_LOG.END_DATE_TIME < = @EndDate + 1)
GROUP BY O_OPERATOR.OPERATOR_NAME
		, O_WORKSTATION.STATION_NAME
		, SYS_SYNCH_LOG.START_DATE_TIME
		, SYS_SYNCH_LOG.END_DATE_TIME
		, SYS_SYNCH_LOG.SYNC_MODE
		, SYS_SYNCH_LOG.SYNCH_TYPE

This worked when I just had the PatientID, but was totalling all of them on each line regardless of whether they had RECS_UP or RECS_DN.
I wanted to split this up as there is an application that does this for my company, but I am trying to duplicate it so I can automate it and that cannot be done with the application.
Here is some presently pulled data:
OPERATOR_NAME STATION_NAME START_DATE_TIME END_DATE_TIME SYNC_MODE SYNCH_TYPE PATIENTS RECS_UP RECS_DOWN CONF
Adams, John IHCFS343 7/28/2009 10:13:15 AM 7/28/2009 10:19:31 AM C A 15 342 4688 2 0
Smith, Joseph IHCFS210 7/27/2009 4:06:02 AM 7/27/2009 4:09:42 AM C A 17 47 1521 1 0
Smith, Joseph IHCFS210 7/27/2009 9:15:54 PM 7/27/2009 9:20:09 PM C A 17 1290 6576 6 0

Duane

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-07-30 : 11:13:39

,COUNT(DISTINCT CASE WHEN SYS_SYNCH_LOG_DETAIL.RECORDS_UP > 0 THEN SYS_SYNCH_LOG_DETAIL.PATIENT_ID END)

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-07-30 : 11:54:28

Thank you very much. This really helped me. It worked. The only thing is that the SUM expressions are double, but everything else appears correct:

, SUM(SYS_SYNCH_LOG_DETAIL.RECORDS_UP) RECS_UP
, SUM(SYS_SYNCH_LOG_DETAIL.RECORDS_DOWN) RECS_DOWN

IS it my join statements, or something?

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources