Talk About Network

Google


Register and Login
Nick
Password
Register create new account Sign up is FREE and you can post replies, new topics, bookmark posts and more!
Recover lost password


Data Bases > Microsoft SQL Server > Grouping by Day...
Latest [ Topics | Posts ] Archive Post A New Topic Post a Reply
<< Topic < Post Post 1 of 3 Topic 11180 of 11517
Post > Topic >>

Grouping by Day for a Date Range

by Shai Shefer <shai.shefer@[EMAIL PROTECTED] > Jun 25, 2008 at 10:49 AM

Hi,

I thought this would be easier but after looking all over the web I
had to post this question.  We have a workflow table that tracks when
an item goes in an out of a workflow state.  I would like to pull a
week's worth of data and group it by day.

My query to do this is:

DECLARE @[EMAIL PROTECTED]
 DATETIME;
DECLARE @[EMAIL PROTECTED]
 DATETIME;
DECLARE @[EMAIL PROTECTED]
 INT;

SELECT @[EMAIL PROTECTED]
(dd, 0, DATEDIFF(dd, 0, GETDATE()));
SELECT @[EMAIL PROTECTED]
(dd, 0, DATEDIFF(dd, 7, GETDATE()));
SET @[EMAIL PROTECTED]
 = 111;

SELECT DISTINCT
		@[EMAIL PROTECTED]
 AS STATE_NUM,
		CONVERT(CHAR(8), wf_entry.entrytime, 112) AS RE****TING_DATE,
		COUNT(wf_entry.itemnum) AS ENTERED,
		COUNT(wf_exit.itemnum) AS EXITED

FROM (SELECT DISTINCT *
	  FROM hsi.wflog AS wf
	  WHERE wf.statenum = @[EMAIL PROTECTED]
        AND wf.entrytime BETWEEN @[EMAIL PROTECTED]
 AND @[EMAIL PROTECTED]
) AS wf_entry
FULL OUTER JOIN (SELECT DISTINCT *
	  FROM hsi.wflog AS wf
	  WHERE wf.statenum = @[EMAIL PROTECTED]
          AND wf.exittime BETWEEN @[EMAIL PROTECTED]
 AND @[EMAIL PROTECTED]
) AS wf_exit
	ON wf_exit.itemnum = wf_entry.itemnum

GROUP BY CONVERT(CHAR(8), wf_entry.entrytime, 112)

ORDER BY RE****TING_DATE DESC

However, there is no way this query is right as it leaves a null row
with exiting values.  Could anyone offer any help / guidance?

Thanks
 




 3 Posts in Topic:
Grouping by Day for a Date Range
Shai Shefer <shai.shef  2008-06-25 10:49:07 
Re: Grouping by Day for a Date Range
--CELKO-- <jcelko212@[  2008-06-25 13:59:21 
Re: Grouping by Day for a Date Range
Erland Sommarskog <esq  2008-06-25 22:11:07 

Post A Reply:
  Go here to Signup

AddThis Feed Button


About - Advertising - Contact - Frequently Asked Questions - Privacy Policy - Terms of Use - Signup

Contact
tan12V112 Wed Dec 3 0:51:00 CST 2008.