Prev: table naming convention about this case..
Next: Creating a database from set of TSQLl files
From: Shai Shefer on 25 Jun 2008 13:49
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 @TODAY DATETIME;
DECLARE @YESTERDAY DATETIME;
DECLARE @STATE INT;

SELECT @TODAY=DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()));
SELECT @YESTERDAY=DATEADD(dd, 0, DATEDIFF(dd, 7, GETDATE()));
SET @STATE = 111;

SELECT DISTINCT
@STATE AS STATE_NUM,
CONVERT(CHAR(8), wf_entry.entrytime, 112) AS REPORTING_DATE,
COUNT(wf_entry.itemnum) AS ENTERED,
COUNT(wf_exit.itemnum) AS EXITED

FROM (SELECT DISTINCT *
FROM hsi.wflog AS wf
WHERE wf.statenum = @STATE
AND wf.entrytime BETWEEN @YESTERDAY AND @TODAY) AS wf_entry
FULL OUTER JOIN (SELECT DISTINCT *
FROM hsi.wflog AS wf
WHERE wf.statenum = @STATE
AND wf.exittime BETWEEN @YESTERDAY AND @TODAY) AS wf_exit
ON wf_exit.itemnum = wf_entry.itemnum

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

ORDER BY REPORTING_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
From: --CELKO-- on 25 Jun 2008 16:59
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Sample data is also a good idea,
along with clear specifications. It is very hard to debug code when
you do not let us see it.

If you want to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html
From: Erland Sommarskog on 25 Jun 2008 18:11
Shai Shefer (shai.shefer(a)gmail.com) writes:
> 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 @TODAY DATETIME;
> DECLARE @YESTERDAY DATETIME;
> DECLARE @STATE INT;
>
> SELECT @TODAY=DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()));
> SELECT @YESTERDAY=DATEADD(dd, 0, DATEDIFF(dd, 7, GETDATE()));
> SET @STATE = 111;
>
> SELECT DISTINCT
> @STATE AS STATE_NUM,
> CONVERT(CHAR(8), wf_entry.entrytime, 112) AS REPORTING_DATE,
> COUNT(wf_entry.itemnum) AS ENTERED,
> COUNT(wf_exit.itemnum) AS EXITED
>
> FROM (SELECT DISTINCT *
> FROM hsi.wflog AS wf
> WHERE wf.statenum = @STATE
> AND wf.entrytime BETWEEN @YESTERDAY AND @TODAY) AS wf_entry
> FULL OUTER JOIN (SELECT DISTINCT *
> FROM hsi.wflog AS wf
> WHERE wf.statenum = @STATE
> AND wf.exittime BETWEEN @YESTERDAY AND @TODAY) AS wf_exit
> ON wf_exit.itemnum = wf_entry.itemnum
>
> GROUP BY CONVERT(CHAR(8), wf_entry.entrytime, 112)
>
> ORDER BY REPORTING_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?

With table definitions, and test data, it's difficult to say what is
wrong, because I don't know what is right. But since you do a full
join I am not surprised if you get a row with NULL in
REPORTING_DATE. Shouldn't you change wf_entry.entrytime in both
occurrances to coalesce(wf_entry.entrytime, wf_exit.entrytime)?

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 | 
Pages: 1
Prev: table naming convention about this case..
Next: Creating a database from set of TSQLl files