April 21, 2023 at 10:25 pm
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. Keep in mind, they are only pointing these things out so that they might be able to help you -- aka do you a favor. Your ignoring them could be construed as being disrespectful, is that what you are trying to communicate? Note these are question meant to -- help you -- help us -- help you -- which is what this forum, from my understanding, is all about.
Again we are here, giving our time (which for many is not cheap) to do you a favor and help you solve your problem. You are not owed an answer, nor can you really do anything to deserve an answer, as such you are asking for a favor from us. Thus the best thing you you can do to help yourself get that favor is by making it as easy as possible for us to determine the true issue and give you the best answer we can come up with -- provided we feel motivated to do so. And believe me, many of us get mighty motivated when we see someone truly striving to -- help us -- help them.
Back to your incomplete information in an effort to try an unmuddy the waters and remember, getting a quality answers is what you are striving for (or at least that is my assumption), so perhaps you might want to do a bit more to -- help us -- help you -- get your --quality answer.
First you said (with the recent changes) that you are wanting get the following three pieces of data
Customer Number -- okay that seems pretty straight forward straight out of the table [CustNbr]
Last Ship Date -- that too seems pretty straight forward almost straight out of the table MAX([ShipDate]) (or see next)
Previous Ship Date -- again pretty straight forward
Just ORDER BY [ShipDate] DESC take the Top(2) [ShipDate] and the greater one is your Last Ship Date and the other is your Previous Ship Date
Lag -- As for this one based on your first post it is very unclear what this is supposed to be -- to me it would be, based on what you are retrieving, just DATEDIFF( SECOND, LastShipDate, PreviousShipDate ). You need to explain the rest of that strange math you are using to calculate Lag or more clearly define what you mean by Lag.
Again remember, the more you give us to work with the quicker and better the answer that you will get. Be vague or supply a fraction of the information and you can expect to either get no answer or a poor answer because with less information you can only expect to get even less of an answer. So again, you should seriously think about whether you truly want a quality answer and how fast you would like to get that answer. Remember You are Asking for the Favor -- So Help Us Help You Get That Favor
Hmmm I might use that as my Motto on here
April 21, 2023 at 10:49 pm
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.
April 22, 2023 at 2:16 am
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.
April 22, 2023 at 3:39 am
This was removed by the editor as SPAM
April 22, 2023 at 3:42 am
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.
April 23, 2023 at 12:54 am
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.
=======================================================================
April 23, 2023 at 7:44 am
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.
April 23, 2023 at 7:47 am
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.
April 23, 2023 at 10:33 pm
@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.
=======================================================================
April 23, 2023 at 11:22 pm
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!!!
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.
=======================================================================
April 24, 2023 at 12:44 pm
Emperor100 -- Many thanks that's exactly what I was looking for... sorry if my description of the problem seemed confusing.
Thanks again.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply