Prev: Parse Suffix from Last Name Field
Next: IIF STATEMENT
From: Russell Hill on 7 May 2010 19:22
I am trying to write a query to count the number of unique SKUs that have
shipped, cumulatively for each week in a quarter. My data looks like:

Week SKU Quantity
14 200 50
14 300 100
14 300 60
14 400 80
15 200 400
15 300 200
15 400 60
15 500 20
16 400 10
16 400 800
16 600 600
16 700 20

The output that I am looking for is:

Week SKU Count
14 3
15 4
16 6

Where each sucessive week only adds the unique SKUs that shipped that week.
Any help with this is greatly appreciated.

Best Regards,

Russell
From: KARL DEWEY on 8 May 2010 00:51
Try these two queries --
Russell_Min --
SELECT Min(Russell.Week) AS MinOfWeek, Russell.SKU
FROM Russell
GROUP BY Russell.SKU
ORDER BY Min(Russell.Week);

SELECT Russell_Min.MinOfWeek, (SELECT Count([XX].SKU) FROM Russell_Min AS
[XX] WHERE [XX].MinOfWeek <= Russell_Min.MinOfWeek ) AS Qty_Added
FROM Russell_Min
GROUP BY Russell_Min.MinOfWeek;

--
Build a little, test a little.


"Russell Hill" wrote:

> I am trying to write a query to count the number of unique SKUs that have
> shipped, cumulatively for each week in a quarter. My data looks like:
>
> Week SKU Quantity
> 14 200 50
> 14 300 100
> 14 300 60
> 14 400 80
> 15 200 400
> 15 300 200
> 15 400 60
> 15 500 20
> 16 400 10
> 16 400 800
> 16 600 600
> 16 700 20
>
> The output that I am looking for is:
>
> Week SKU Count
> 14 3
> 15 4
> 16 6
>
> Where each sucessive week only adds the unique SKUs that shipped that week.
> Any help with this is greatly appreciated.
>
> Best Regards,
>
> Russell
From: Russell Hill on 10 May 2010 16:54
Karl,

Works perfectly! Thank you.

Russell

"KARL DEWEY" wrote:

> Try these two queries --
> Russell_Min --
> SELECT Min(Russell.Week) AS MinOfWeek, Russell.SKU
> FROM Russell
> GROUP BY Russell.SKU
> ORDER BY Min(Russell.Week);
>
> SELECT Russell_Min.MinOfWeek, (SELECT Count([XX].SKU) FROM Russell_Min AS
> [XX] WHERE [XX].MinOfWeek <= Russell_Min.MinOfWeek ) AS Qty_Added
> FROM Russell_Min
> GROUP BY Russell_Min.MinOfWeek;
>
> --
> Build a little, test a little.
>
>
> "Russell Hill" wrote:
>
> > I am trying to write a query to count the number of unique SKUs that have
> > shipped, cumulatively for each week in a quarter. My data looks like:
> >
> > Week SKU Quantity
> > 14 200 50
> > 14 300 100
> > 14 300 60
> > 14 400 80
> > 15 200 400
> > 15 300 200
> > 15 400 60
> > 15 500 20
> > 16 400 10
> > 16 400 800
> > 16 600 600
> > 16 700 20
> >
> > The output that I am looking for is:
> >
> > Week SKU Count
> > 14 3
> > 15 4
> > 16 6
> >
> > Where each sucessive week only adds the unique SKUs that shipped that week.
> > Any help with this is greatly appreciated.
> >
> > Best Regards,
> >
> > Russell
 | 
Pages: 1
Prev: Parse Suffix from Last Name Field
Next: IIF STATEMENT