September 3, 2013 at 1:43 pm
SELECT DISTINCT TOP 40 A.INV_ITEM_ID
,SUM(A.QTY_REQUESTED) AS QTY_REQUESTED
,ISNULL((SELECT SUM(B.QTY_ONHAND) FROM PS_BU_ITEMS_INV B WHERE B.INV_ITEM_ID = A.INV_ITEM_ID GROUP BY B.INV_ITEM_ID),0) AS ONHAND
,ISNULL((SELECT SUM(C.QTY_REQUESTED) FROM PS_IN_DEMAND C WHERE C.INV_ITEM_ID = A.INV_ITEM_ID AND C.IN_FULFILL_STATE IN(20,30,40) AND C.SCHED_DTTM >= GETDATE() AND C.SCHED_DTTM <= GETDATE()+1 GROUP BY C.INV_ITEM_ID),0) AS TODAY
,ISNULL((SELECT SUM(C.QTY_REQUESTED) FROM PS_IN_DEMAND C WHERE C.INV_ITEM_ID = A.INV_ITEM_ID AND C.IN_FULFILL_STATE IN(20,30,40) AND C.SCHED_DTTM >= GETDATE()+1 AND C.SCHED_DTTM <= GETDATE()+2 GROUP BY C.INV_ITEM_ID),0) AS DAY2
,ISNULL((SELECT SUM(C.QTY_REQUESTED) FROM PS_IN_DEMAND C WHERE C.INV_ITEM_ID = A.INV_ITEM_ID AND C.IN_FULFILL_STATE IN(20,30,40) AND C.SCHED_DTTM >= GETDATE()+2 AND C.SCHED_DTTM <= GETDATE()+3 GROUP BY C.INV_ITEM_ID),0) AS DAY3
,ISNULL((SELECT SUM(C.QTY_REQUESTED) FROM PS_IN_DEMAND C WHERE C.INV_ITEM_ID = A.INV_ITEM_ID AND C.IN_FULFILL_STATE IN(20,30,40) AND C.SCHED_DTTM >= GETDATE()+3 AND C.SCHED_DTTM <= GETDATE()+4 GROUP BY C.INV_ITEM_ID),0) AS DAY4
,ISNULL((SELECT SUM(C.QTY_REQUESTED) FROM PS_IN_DEMAND C WHERE C.INV_ITEM_ID = A.INV_ITEM_ID AND C.IN_FULFILL_STATE IN(20,30,40) AND C.SCHED_DTTM >= GETDATE()+4 AND C.SCHED_DTTM <= GETDATE()+5 GROUP BY C.INV_ITEM_ID),0) AS DAY5
,ISNULL((SELECT SUM(C.QTY_REQUESTED) FROM PS_IN_DEMAND C WHERE C.INV_ITEM_ID = A.INV_ITEM_ID AND C.IN_FULFILL_STATE IN(20,30,40) AND C.SCHED_DTTM >= GETDATE()+5 AND C.SCHED_DTTM <= GETDATE()+6 GROUP BY C.INV_ITEM_ID),0) AS DAY6
,ISNULL((SELECT SUM(C.QTY_REQUESTED) FROM PS_IN_DEMAND C WHERE C.INV_ITEM_ID = A.INV_ITEM_ID AND C.IN_FULFILL_STATE IN(20,30,40) AND C.SCHED_DTTM >= GETDATE()+6 AND C.SCHED_DTTM <= GETDATE()+7 GROUP BY C.INV_ITEM_ID),0) AS DAY7
,ISNULL((SELECT SUM(C.QTY_REQUESTED) FROM PS_IN_DEMAND C WHERE C.INV_ITEM_ID = A.INV_ITEM_ID AND C.IN_FULFILL_STATE IN(20,30,40) AND C.SCHED_DTTM >= GETDATE()+7 GROUP BY C.INV_ITEM_ID),0) AS DAY8PLUS
FROM PS_IN_DEMAND A
WHERE A.IN_FULFILL_STATE IN(20,30,40)
GROUP BY A.INV_ITEM_ID
ORDER BY A.INV_ITEM_ID
September 4, 2013 at 12:20 am
Without the table definition and some sample data it is hard to analyse your query and give you better solutions. But I'll give it a try.
I noticed you use 8 times a derived table inside your SELECT that are almost the same. See if it is possible to move these derived statements to the FROM clause (using a JOIN) and to combine them to a single statement. Perhaps something like:
SELECT
...
FROM
PS_IN_DEMAND a
inner join
(SELECT
INV_ITEM_ID
, COALESCE(SUM(QTY_REQUESTED), 0) as QTY_REQUESTED
, DATEADD(dd, DATEDIFF(dd , 0, C.SCHED_DTTM), 0) as day_group
FROM PS_IN_DEMAND
WHERE IN_FULFILL_STATE IN (20, 30, 40)
GROUP BY
INV_ITEM_ID
, DATEADD(dd, DATEDIFF(dd , 0, C.SCHED_DTTM), 0)
) sub_select
on a.INV_ITEM_ID = sub_select.INV_ITEM_ID
September 4, 2013 at 7:07 am
Your date arithmetic has two flaws which could cause your query to return incorrect results.
1.They overlap. The day6 upper bound is C.SCHED_DTTM <= GETDATE()+6 and the day7 lower bound is C.SCHED_DTTM >= GETDATE()+6. Any C.SCHED_DTTM which happens to equal GETDATE() will fit into both buckets.
2.The datetime comparison doesn’t take the time portion into account. C.SCHED_DTTM falling either side of the time will fit into sequential buckets – they should probably fit into the same bucket. IBG has an excellent article here which provides a far better explanation than I can.
Have you considered using a calendar table to provide your query with the relevant date ranges? Here's a simple on-the-fly calendar table which I reckon fits the bill:
;WITH Calendar AS (
SELECT
RangeName,
RangeStart = DATEADD(dd,CASE rn WHEN 0 THEN -365 ELSE rn-1 END,Today),
RangeEnd = DATEADD(dd,CASE rn WHEN 8 THEN 365 ELSE rn END,Today)
FROM (SELECT Today = CAST(CAST(GETDATE() AS DATE) AS DATETIME)) d,
(SELECT rn = 0, RangeName = 'TodayMinus'
UNION ALL SELECT 1, 'Today'
UNION ALL SELECT 2, 'DAY2'
UNION ALL SELECT 3, 'DAY3'
UNION ALL SELECT 4, 'DAY4'
UNION ALL SELECT 5, 'DAY5'
UNION ALL SELECT 6, 'DAY6'
UNION ALL SELECT 7, 'DAY7'
UNION ALL SELECT 8, 'DAY8PLUS') e
)
SELECT * FROM Calendar
To use it, join it to your table on a date range expression:
SELECT
a.INV_ITEM_ID,
c.RangeName,
QTY_REQUESTED = SUM(a.QTY_REQUESTED)
FROM PS_IN_DEMAND a
INNER JOIN Calendar c
ON a.SCHED_DTTM >= c.RangeStart
AND a.SCHED_DTTM < c.RangeEnd
WHERE a.IN_FULFILL_STATE IN (20,30,40)
GROUP BY a.INV_ITEM_ID, c.RangeName
A crosstab query using this as a table source would probably be your tool of choice for pivoting the results. Here's another excellent article [/url]describing how to go about it.
Oh, and you have a second source table in your query...you could probably slot it in like this:
SELECT
a.INV_ITEM_ID,
c.RangeName,
QTY_REQUESTED = SUM(a.QTY_REQUESTED),
ONHAND = ISNULL(MAX(x.ONHAND),0)
FROM PS_IN_DEMAND a
INNER JOIN Calendar c
ON a.SCHED_DTTM >= c.RangeStart
AND a.SCHED_DTTM < c.RangeEnd
OUTER APPLY (
SELECT ONHAND = SUM(B.QTY_ONHAND)
FROM PS_BU_ITEMS_INV B
WHERE B.INV_ITEM_ID = a.INV_ITEM_ID
) x
WHERE a.IN_FULFILL_STATE IN (20,30,40)
GROUP BY a.INV_ITEM_ID, c.RangeName
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply