Run out dates

Question

Run out dates

dtoews 7666

Valued Member

Points: 74
More actions
September 13, 2016 at 8:39 am

#330117

I have a large(440k rows) pivoted csv report generated by a third party with fields Item, Location, and then forward projecting dates with quantities by week in the format
Item
,Location
,[w2016 09 12]
,[w2016 09 19]
,[w2016 09 26]
,[w2016 10 03]
,[w2016 10 10]
... etc
so the data looks like;
ItemLocationw2016 09 12w2016 09 19w2016 09 26w2016 10 03w2016 10 10w2016 10 17w2016 10 24
162386-00CHB263203143832300
162386-00GRR44444444444444
162386-00MAR0000000
162387-00CHB83838383838383
162387-00GRR221220000
162387-00MAR0000000
162388-00CHB49443934292419
162388-00GRR3020100000
162388-00MAR0000000
162389-00CHB90807060504030
What I need to do is find the latest date where the qty goes <= 0 for each item/location.
What I have so far:
SELECT Item,[Location],[Weeks],min([Week])
FROM(
select Item,[Location]
,[w2016 09 12] ,[w2016 09 19] ,[w2016 09 26] ,[w2016 10 03] ,[w2016 10 10] ,[w2016 10 17] ,[w2016 10 24] ,[w2016 10 31] ,[w2016 11 07] ,[w2016 11 14] ,[w2016 11 21] ,[w2016 11 28] ,[w2016 12 05] ,[w2016 12 12] ,[w2016 12 19] ,[w2016 12 26] ,[w2017 01 02] ,[w2017 01 09] ,[w2017 01 16] ,[w2017 01 23] ,[w2017 01 30] ,[w2017 02 06] ,[w2017 02 13] ,[w2017 02 20] ,[w2017 02 27] ,[w2017 03 06] ,[w2017 03 13] ,[w2017 03 20] ,[w2017 03 27] ,[w2017 04 03] ,[w2017 04 10] ,[w2017 04 17] ,[w2017 04 24] ,[w2017 05 01] ,[w2017 05 08] ,[w2017 05 15] ,[w2017 05 22] ,[w2017 05 29] ,[w2017 06 05] ,[w2017 06 12] ,[w2017 06 19] ,[w2017 06 26] ,[w2017 07 03]
,[w2017 07 10] ,[w2017 07 17] ,[w2017 07 24] ,[w2017 07 31] ,[w2017 08 07] ,[w2017 08 14] ,[w2017 08 21] ,[w2017 08 28] ,[w2017 09 04] ,[w2017 09 11] ,[w2017 09 18] ,[w2017 09 25] ,[w2017 10 02] ,[w2017 10 09] ,[w2017 10 16] ,[w2017 10 23] ,[w2017 10 30] ,[w2017 11 06] ,[w2017 11 13] ,[w2017 11 20] ,[w2017 11 27] ,[w2017 12 04] ,[w2017 12 11] ,[w2017 12 18] ,[w2017 12 25] ,[w2018 01 01] ,[w2018 01 08] ,[w2018 01 15] ,[w2018 01 22] ,[w2018 01 29] ,[w2018 02 05] ,[w2018 02 12] ,[w2018 02 19]
FROM ProjectedActualAvailable)as PAA
UNPIVOT
(
[WEEK] for [Weeks] IN (
[w2016 09 12] ,[w2016 09 19] ,[w2016 09 26] ,[w2016 10 03] ,[w2016 10 10] ,[w2016 10 17] ,[w2016 10 24] ,[w2016 10 31] ,[w2016 11 07] ,[w2016 11 14] ,[w2016 11 21] ,[w2016 11 28] ,[w2016 12 05] ,[w2016 12 12] ,[w2016 12 19] ,[w2016 12 26] ,[w2017 01 02] ,[w2017 01 09] ,[w2017 01 16] ,[w2017 01 23] ,[w2017 01 30] ,[w2017 02 06] ,[w2017 02 13] ,[w2017 02 20] ,[w2017 02 27] ,[w2017 03 06] ,[w2017 03 13] ,[w2017 03 20] ,[w2017 03 27] ,[w2017 04 03] ,[w2017 04 10] ,[w2017 04 17] ,[w2017 04 24] ,[w2017 05 01] ,[w2017 05 08] ,[w2017 05 15] ,[w2017 05 22] ,[w2017 05 29] ,[w2017 06 05] ,[w2017 06 12] ,[w2017 06 19] ,[w2017 06 26] ,[w2017 07 03]
,[w2017 07 10] ,[w2017 07 17] ,[w2017 07 24] ,[w2017 07 31] ,[w2017 08 07] ,[w2017 08 14] ,[w2017 08 21] ,[w2017 08 28] ,[w2017 09 04] ,[w2017 09 11] ,[w2017 09 18] ,[w2017 09 25] ,[w2017 10 02] ,[w2017 10 09] ,[w2017 10 16] ,[w2017 10 23] ,[w2017 10 30] ,[w2017 11 06] ,[w2017 11 13] ,[w2017 11 20] ,[w2017 11 27] ,[w2017 12 04] ,[w2017 12 11] ,[w2017 12 18] ,[w2017 12 25] ,[w2018 01 01] ,[w2018 01 08] ,[w2018 01 15] ,[w2018 01 22] ,[w2018 01 29] ,[w2018 02 05] ,[w2018 02 12] ,[w2018 02 19]
)
)
as up
group by Item,[Location]
order by Item,[Location],[Weeks]

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

by any chance do you have access to the raw(unpivoted data)...me thinks it would be easier to provide solution

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Alan Burstein SSC Guru Points: 61152 More actions · Answer 2

I think that this is what you are looking for:

-- Sample data

IF OBJECT_ID('tempdb..#YourData') IS NOT NULL DROP TABLE #YourData;

SELECT *

INTO #YourData

FROM

(VALUES

('162386-00','CHB',263,203,143,83,23,0,0),('162386-00','GRR',44,44,44,44,44,44,44),

('162386-00','MAR',0,0,0,0,0,0,0),('162387-00','CHB',83,83,83,83,83,83,83),

('162387-00','GRR',22,12,2,0,0,0,0),('162387-00','MAR',0,0,0,0,0,0,0),

('162388-00','CHB',49,44,39,34,29,24,19),('162388-00','GRR',30,20,10,0,0,0,0),

('162388-00','MAR',0,0,0,0,0,0,0),('162389-00','CHB',90,80,70,60,50,40,30)

) AS t

(

Item, Location, [w2016 09 12], [w2016 09 19], [w2016 09 26],

[w2016 10 03], [w2016 10 10], [w2016 10 17], [w2016 10 24]

);

--SELECT * FROM #YourData

-- Solution

SELECT

Item,

Location,

Weeks = MIN

(

CASE

WHEN [w2016 09 12] <= 0 THEN 'w2016 09 12'

WHEN [w2016 09 19] <= 0 THEN 'w2016 09 19'

WHEN [w2016 09 26] <= 0 THEN 'w2016 09 26'

WHEN [w2016 10 03] <= 0 THEN 'w2016 10 03'

WHEN [w2016 10 10] <= 0 THEN 'w2016 10 10'

WHEN [w2016 10 17] <= 0 THEN 'w2016 10 17'

WHEN [w2016 10 24] <= 0 THEN 'w2016 10 24'

END

)

FROM #YourData

GROUP BY Item, Location

ORDER BY item, Location, Weeks; -- not required, included for presentation only

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

drew.allen SSC Guru Points: 77014 More actions · Answer 3

You need to look at pairs of weeks to determine if the first in the pair is greater than zero and the second is zero or less. Since you are on SQL 2008, you would need to do a self-join if you unpivoted the data, and that would require at least two scans of the database. (Using SQL 2012 or higher, you could use LEAD/LAG.) Because of that, you probably want to use a CASE expression.

SELECT CASE

WHEN [w2018 02 19] <= 0 AND [w2018 02 12] > 0 THEN 'w20180219' END

WHEN [w2018 02 12] <= 0 AND [w2018 02 05] > 0 THEN 'w20180212' END

.

END

FROM ProjectActualAvailable

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

dtoews 7666 Valued Member Points: 74 More actions · Answer 4

Alan, thanks! That worked out great. Guess I was looking too hard at it, missed the simple answer.

drew.allen SSC Guru Points: 77014 More actions · Answer 5

Do you have any cases where items are restocked/resupplied? If so, then Alan's solution won't work.