Not Grouping like I want - 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)

Not Grouping like I want

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-07-30 : 17:53:01

I have struggled all day with this query with the totals. This query returns the right amount finally, but in 16 records. It is grouping on Recs_Up and I want there to be just one record (with this criteria) with the TOTAL of Recs_Up and Recs_Down. Please help me get it.

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SELECT @StartDate=('07/27/2009')
SELECT @EndDate=('07/28/2009')
SELECT 		S.SYNCH_LOG_ID
		, O.OPERATOR_NAME
		, W.STATION_NAME
		, S.START_DATE_TIME
		, S.END_DATE_TIME
		, S.SYNCH_TYPE
		, (SELECT [HH:MM:SS] = 
			CASE WHEN DATEDIFF(SS,S.START_DATE_TIME, S.END_DATE_TIME)/3600<10 THEN '0' ELSE '' END 
		 + RTRIM(DATEDIFF(SS,S.START_DATE_TIME, S.END_DATE_TIME)/3600) 
		 + ':' + RIGHT('0'+RTRIM((DATEDIFF(SS,S.START_DATE_TIME, S.END_DATE_TIME) % 3600) / 60),2) 
		 + ':' + RIGHT('0'+RTRIM((DATEDIFF(SS,S.START_DATE_TIME, S.END_DATE_TIME) % 3600) % 60),2)) AS DUR
		, S.SYNC_MODE
		, COUNT(DISTINCT D.PATIENT_ID) PT_COUNT
		, COUNT(DISTINCT CASE WHEN D.RECS_UP > 0 THEN D.PATIENT_ID END) PTS_UP
		, COUNT(DISTINCT CASE WHEN D.RECS_DOWN > 0 THEN D.PATIENT_ID END) PTS_DN
		, D.RECS_UP
		, D.RECS_DOWN
		, COUNT(DISTINCT C.CONFLICT_ID) CONF
FROM         SYS_SYNCH_LOG S
INNER JOIN
(
    SELECT	PATIENT_ID
		, SYNCH_LOG_ID
		, COUNT(*) AS PT_COUNT 
		, SUM(RECORDS_UP) AS RECS_UP
		, SUM(RECORDS_DOWN) AS RECS_DOWN
    FROM 
	      	SYS_SYNCH_LOG_DETAIL
    GROUP BY 
    PATIENT_ID, SYNCH_LOG_ID
) D
ON  S.SYNCH_LOG_ID = D.SYNCH_LOG_ID
INNER JOIN  O_OPERATOR O ON S.OPERATOR_ID = O.OPERATOR_ID 
INNER JOIN  O_WORKSTATION W ON S.STATION_ID = W.STATION_ID 
INNER JOIN  SYS_SYNCH_CONFLICT C ON S.SYNCH_LOG_ID = C.SYNCH_ID
WHERE     (S.START_DATE_TIME >= @StartDate) AND (S.END_DATE_TIME < = @EndDate + 1) AND S.SYNCH_LOG_ID = 255972
GROUP BY	S.SYNCH_LOG_ID
		, O.OPERATOR_NAME
		, W.STATION_NAME
		, S.START_DATE_TIME
		, S.END_DATE_TIME
		, S.SYNCH_TYPE
		, S.SYNC_MODE
		, D.RECS_UP
		, D.RECS_DOWN

I know there are problems with the Group By statement at the end of the page, but when I remove things, I can't get it to run or I start having other problems. Thank you for any help.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources