get Lag based on Max and previous ShipDate

Question

Post reply

get Lag based on Max and previous ShipDate

Emperor100

SSCrazy

Points: 2221

April 24, 2023 at 12:33 am

Answer

#4178335

Try this:

SELECT 
  A.custnbr, 
  AvgLag = CASE WHEN COUNT(*)-1 <> 0 THEN CONVERT(decimal(7,2), 
                            DATEDIFF(day, min(T2.ShipDate), min(T1.shipdate)))
                    / CONVERT(decimal(7,2), COUNT(*) - 1)
           ELSE NULL END                
FROM vw_saleshist_PY A
OUTER APPLY (SELECT TOP 1 ShipDate 
             FROM vw_saleshist_PY AS B  
             WHERE A.CustNbr=B.CustNbr
             ORDER BY B.ShipDate DESC) AS T1
OUTER APPLY (SELECT TOP 1 ShipDate 
             FROM vw_saleshist_PY AS C  
             WHERE C.ShipDate < A.ShipDate and A.CustNbr=c.CustNbr
             ORDER BY C.ShipDate DESC) AS T2   
where datepart(yyyy,A.shipdate) = '2023'
GROUP BY A.custnbr

Because your query is looking for two rows of Shipping details. The above query looks for two transactions of Shipping...

If a custnbr does not have two transactions, the outcome will be Null otherwise it will display the AvgLag.

Hope that helps.

=======================================================================

Viewing 10 posts - 16 through 25 (of 25 total)

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

frederico_fonseca SSCoach Points: 16250 More actions · Answer 1

Dennis Jensen wrote:

Hey Bruin I have to ask are you trying not to get help? I mean several individuals posted items that your initial post was lacking and you have made no response to their questions.

you hit the point - the OP does not wish for help - but rather wishes for us to do their work for free - with little input in most cases.

pietlinden SSC Guru Points: 63594 More actions · Answer 2

I'd submit a tested query, but I don't have any data to test it on... so, all I can offer is a vague generality.

If you use CROSS APPLY, you can get the top 2 records per parent. Just change the sort order to get the bottom values. Then you can use LAG() against that pair to get the values you want on the "first" or "second" record. Then subtract or whatever. (Yeah, I'm not reading your post, because I have no data.)

sorry if that's not satisfactory, but your posting is missing key information: actual data. If you want better help, maybe read Jeff Moden's article on How to Post a Question that'll get answered.

Dennis Jensen SSChasing Mays Points: 606 More actions · Answer 3

Ghads I cannot still believe -- they negate a post as spam for a single edit -- that is just totally ludicrous..... At least I can get back to the previous post in order to copy/paste and post it again. So lame

Okay frederico_fonseca I cannot say that is what the person is trying to do -- all I can do is point out what it seems like they are doing from my point of view which might reflect others points of view and perhaps help them understand that their inadequacy is affecting them getting the results they are hoping for as well as how they might increase their chances of getting what they seemed to have come here for. Again I can only make assumptions which I cannot base facts upon as they are simply opinions and not actually facts.

All I am hoping is that what has been presented will help the individual to change their approach so that they can get what they seem to have come here for -- an answer to their question to solve their problem -- but that again rest mostly on them being able present that enough information for that to occur.

Hey pietlinden how would you use a CROSS APPLY to do what you outlined as I am very curious to see what that might look like. If nothing else use the code I suggested and change it to outline how you would implement the CROSS APPLY. Thanks in advance.

Emperor100 SSCrazy Points: 2221 More actions · Answer 4

Replace Min(ShipDate) with the following LAG function to get the Previous Ship Date.

Lag(ShipDate, 1, '1/1/1900') OVER(Partition by Custnbr ORDER BY ShipDate ASC)

Your final query will be along the following lines:

SELECT 
  custnbr, 
  AvgLag = CASE WHEN COUNT(*) > 1 THEN 
                    CONVERT(decimal(7,2), 
                            DATEDIFF(day, Lag(ShipDate, 1, '1/1/1900') OVER(Partition by Custnbr ORDER BY ShipDate ASC), MAX(shipdate)))
                    / CONVERT(decimal(7,2), COUNT(*) - 1)
                ELSE null
           END
FROM vw_saleshist_PY
where datepart(yyyy,shipdate) = '2023'
GROUP BY custnbr  order by 2 desc

if there is no previous row for the custnbr, then the date 01/01/1900 will be substituted for NULLs.

=======================================================================

Dennis Jensen SSChasing Mays Points: 606 More actions · Answer 5

Okay Emperor100 so it seems that you have changed the fundamental request of the OP by assuming that their formula must have been meant to calculate the Average Lag but that is not what the OP stated they were striving for. Further if '1900-01-01' gets used even once that is going to completely through off any Average value presuming that is what they were striving for. On that aspect, the basics of that formula actually makes no sense for calculating the Average Lag in any way so how does this information possibly help the OP beyond perhpas sending them down the absolutely wrong rabbit hole? We are here to help them solve a problem not saddle them with more problems. Now grant you, I might be wrong but I can only base my stance on what was originally presented by the OP and what they stated they were looking for. Until the OP addresses the numerous missing elements, anything thrown out there is going to be like shooting at a target in utter darkness and not even knowing in which direction that target actually lies.

Dennis Jensen SSChasing Mays Points: 606 More actions · Answer 6

P.S. Emperor100 I do applaud you for the effort so please do not get me wrong on that part. Trying to help is a good thing, but understanding the problem is even more crucial, otherwise you might be hindering rather than helping.

Emperor100 SSCrazy Points: 2221 More actions · Answer 7

@dennis Jensen: I do not understand what is your problem?

Have you read the OP's original post? OP wants to remove Min(ShipDate). I answered to him as I understood his requirements. If there is something wrong, .... let him reply!!.

Don't act too smart... keep your smartness up to you.... and shut your mouth, and don't let me lose my tongue here. I do want to maintain forum etiquette.... but not always.

=======================================================================

Bruin SSCrazy Points: 2107 More actions · Answer 8

Emperor100,

Thanks for replies it appears the query you had suggested retrieves multiple custnbr's and avglag. I was hoping that the query returns

a single custnbr(based on Max Shipdate) then based onprevious shipdate calc lag between dates. If customer has never ordered before max maybe then show NULL

example:

cusnbr,shipdate

cust234,04/23/2023

cust234,04/20/2023

Output (it's been 3 days since

custnbr,Lag

Cust234,3

Thanks again!!!

Bruin SSCrazy Points: 2107 More actions · Answer 9

Emperor100 -- Many thanks that's exactly what I was looking for... sorry if my description of the problem seemed confusing.

Thanks again.