Complicated Counting

[thomasrawley]

I am having trouble wrapping my head around this one.
Here is the table of information I am working with.

ORDERS
Store# Order# Date
01 01 8/14
01 01 6/28
01 02 8/13
02 01 8/12
02 02 8/14
03 01 8/13
03 01 8/1
03 02 8/16

So let me explain the table first. It holds order history information (which is why you see multiple rows for the same store/order combination with different dates). I can easily filter out the statuses that I don't want and when I do that, this is the info I am left with. This represents completed orders.

The goal here is the following output.

Store# Count(Orders that completed for the first time)
01 1
02 2
03 1

I only care about orders that completed FOR THE FIRST TIME in a week boundary (so if I choose last week, I only care about orders that completed between 8/11 and 8/17 FOR THE FIRST TIME).

For example, since order 1 in store 1 completed before the week I care about, I do not want to include it.

How would I write a SQL statement to do this? I can of course have an intermediate step but would prefer to have it all in one SQL statement.

I will be querying a DB2 database if that matters.

Any help would be appreciated.

Thanks,

Thomas

 

[LKBrwnDBA]

I don't see any complication, have you tried coding anything yet? post it and let us know what problems you have.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[thomasrawley]

Here is the SQL I have so far. This of course does not work but I think conveys what I am trying to accomplish.

I am thinking I need a subquery somewhere in here but can't figure out where to do it...something that will limit the customer order numbers and then I can group and count in the outer query...


SELECT
SVC_STAT_HIST.STR_NBR,
COUNT(DISTINCT(SVC_STAT_HIST.CUST_ORD_NBR)),
MIN(SVC_STAT_HIST.STAT_EFF_TS)

FROM SVC_STAT_HIST
WHERE (
SVC_STAT_HIST.Svc_Typ_Cd='V' AND
SVC_STAT_HIST.Svc_Stat_Cd=1000 AND
SVC_STAT_HIST.Str_Nbr IN ('0121', '3027')
)
GROUP BY SVC_STAT_HIST.STR_NBR
HAVING MIN (SVC_STAT_HIST.STAT_EFF_TS) BETWEEN '2008-08-11-00.00.00.000000' AND '2008-08-18-00.00.00.000000'

 

[thomasrawley]

So I got this to work in MS Access without a problem (I changed the date formatting to make it work of course).

Now I just need to figure out how to make this work in Hyperion...which doesn't seem to be as flexible.

SELECT AL1.STR_NBR,
COUNT(AL1.CUST_ORD_NBR)
FROM
(
SELECT AL2.STR_NBR,
AL2.CUST_ORD_NBR,
MIN(AL2.STAT_EFF_TS)
FROM SVC_STAT_HIST AL2
WHERE
AL2.SVC_TYP_CD = 'V' AND
AL2.SVC_STAT_CD = 1000 AND
AL2.STR_NBR IN ('0121', '3027')
GROUP BY AL2.STR_NBR, AL2.CUST_ORD_NBR
HAVING MIN(AL2.STAT_EFF_TS) BETWEEN '2008-08-11-00.00.00.000000' AND '2008-08-18-00.00.00.000000'



) AL1 (STR_NBR, CUST_ORD_NBR, STAT_EFF_TS)
GROUP BY AL1.STR_NBR