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


|