Please Help with Query - I am Almost There!!!

  • Thread starter Thread starter anniecallaway
  • Start date Start date
A

anniecallaway

Could someone please take a look and what I doing and see if you know
what is being done wrong?

a) This SQL View Query gives the Top 50 SKU numbers and shrink $
amounts for store 121:

SELECT TOP 50 PRTHD_STRSK_INV_SSTATS.STR_NBR,
PRTHD_STRSK_INV_SSTATS.SKU_NBR,
PRTHD_STRSK_INV_SSTATS.OVR_SHRT_CE_AMT
FROM PRTHD_STRSK_INV_SSTATS
WHERE (((PRTHD_STRSK_INV_SSTATS.FSCL_YR)=2008) AND
((PRTHD_STRSK_INV_SSTATS.STR_NBR)=121))
ORDER BY PRTHD_STRSK_INV_SSTATS.OVR_SHRT_CE_AMT,
PRTHD_STRSK_INV_SSTATS.STR_NBR;

b) However this query gives me the Top 50 SKU numbers and shrink $
amounts for store 121 and 105 combined:

SELECT TOP 50 PRTHD_STRSK_INV_SSTATS.STR_NBR,
PRTHD_STRSK_INV_SSTATS.SKU_NBR,
PRTHD_STRSK_INV_SSTATS.OVR_SHRT_CE_AMT
FROM PRTHD_STRSK_INV_SSTATS
WHERE (((PRTHD_STRSK_INV_SSTATS.FSCL_YR)=2008) AND
((PRTHD_STRSK_INV_SSTATS.STR_NBR)=121)) OR
(((PRTHD_STRSK_INV_SSTATS.FSCL_YR)=2008) AND
((PRTHD_STRSK_INV_SSTATS.STR_NBR)=105))
ORDER BY PRTHD_STRSK_INV_SSTATS.OVR_SHRT_CE_AMT,
PRTHD_STRSK_INV_SSTATS.STR_NBR;

I am looking for the top 50 SKU numbers and skrink $ amounts for store
121 & the top 50 SKU numbers and shrink $ amounts for store 105
(eventually looking to get the top 50 for all stores). However, for
this example I would get back 100 SKU numbers and shrink $ amounts
total.

Ideally I would like to see the Top 50 SKUs for Store 105 (Row 1-50)
and then see the Top 50 SKUs for Store 121 (Row 51-100)

Thanks,

Annie
 
An easy way is an union query --
SELECT TOP 50 PRTHD_STRSK_INV_SSTATS.STR_NBR,
PRTHD_STRSK_INV_SSTATS.SKU_NBR,
PRTHD_STRSK_INV_SSTATS.OVR_SHRT_CE_AMT
FROM PRTHD_STRSK_INV_SSTATS
WHERE (((PRTHD_STRSK_INV_SSTATS.FSCL_YR)=2008) AND
((PRTHD_STRSK_INV_SSTATS.STR_NBR)=105))
ORDER BY PRTHD_STRSK_INV_SSTATS.OVR_SHRT_CE_AMT,
PRTHD_STRSK_INV_SSTATS.STR_NBR
UNION ALL SELECT TOP 50 PRTHD_STRSK_INV_SSTATS.STR_NBR,
PRTHD_STRSK_INV_SSTATS.SKU_NBR,
PRTHD_STRSK_INV_SSTATS.OVR_SHRT_CE_AMT
FROM PRTHD_STRSK_INV_SSTATS
WHERE (((PRTHD_STRSK_INV_SSTATS.FSCL_YR)=2008) AND
((PRTHD_STRSK_INV_SSTATS.STR_NBR)=121))
ORDER BY PRTHD_STRSK_INV_SSTATS.OVR_SHRT_CE_AMT,
PRTHD_STRSK_INV_SSTATS.STR_NBR;
 
Annie:

Try this:

SELECT STR_NBR, SKU_NBR, OVR_SHRT_CE_AMT
FROM PRTHD_STRSK_INV_SSTATS
WHERE STR_NBR IN
(SELECT TOP 50 STR_NBR
FROM PRTHD_STRSK_INV_SSTATS
WHERE FSCL_YR=2008 AND STR_NBR=105)
OR STR_NBR IN
(SELECT TOP 50 STR_NBR
FROM PRTHD_STRSK_INV_SSTATS
WHERE FSCL_YR=2008 AND STR_NBR=121)
ORDER BY STR_NBR, OVR_SHRT_CE_AMT;

Ken Sheridan
Stafford, England
 
Oops! The subqueries also need to be ordered:

SELECT STR_NBR, SKU_NBR, OVR_SHRT_CE_AMT
FROM PRTHD_STRSK_INV_SSTATS
WHERE STR_NBR IN
(SELECT TOP 50 STR_NBR
FROM PRTHD_STRSK_INV_SSTATS
WHERE FSCL_YR=2008 AND STR_NBR=105
ORDER BY OVR_SHRT_CE_AMT)
OR STR_NBR IN
(SELECT TOP 50 STR_NBR
FROM PRTHD_STRSK_INV_SSTATS
WHERE FSCL_YR=2008 AND STR_NBR=121
ORDER BY OVR_SHRT_CE_AMT)
ORDER BY STR_NBR, OVR_SHRT_CE_AMT;

Ken Sheridan
Stafford, England
 
Annie:

Another approach occurred to me:

SELECT STR_NBR, SKU_NBR, OVR_SHRT_CE_AMT
FROM PRTHD_STRSK_INV_SSTATS AS PSIS1
WHERE STR_NBR IN
(SELECT TOP 50 STR_NBR
FROM PRTHD_STRSK_INV_SSTATS AS PSIS2
WHERE FSCL_YR=[Enter year:]
AND PSIS2.STR_NBR=PSIS1.STR_NBR
ORDER BY OVR_SHRT_CE_AMT)
ORDER BY STR_NBR, OVR_SHRT_CE_AMT;

This will return the top 50 for each of ALL the stores for the year
enterers at the parameter prompt when the query (or a form or report
based on it) is opened, ordered first by store number than by amount,
so this will act a general purpose query. You could use this as the
basis for another query to return specific stores, e.g.

SELECT *
FROM TheAboveQuery
WHERE STR_NBR IN(105,121);

Or you could open a form or report based on TheAboveQuery filtering
the form or report to specific stores.

Ken Sheridan
Stafford, England
 
Back
Top