My query finds Min and Max to determine Lag, but I need to find the lastest Shipdate date(max) and then previous shipdate to determine lag --> remove the Min(shipdate).
I would like output to show:
Custnbr ---- (Lastest-Order date) ----(Previous Order Date) ---- Lag
Any Help?
SELECT
custnbr,
AvgLag = CASE WHEN COUNT(*) > 1 THEN
CONVERT(decimal(7,2),
DATEDIFF(day, MIN(shipdate), 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
April 17, 2023 at 3:44 am
maybe I'm too tired or too lazy to figure out what you wrote, but why not just use a window and order the rows in the window by date descending, and then if you can use ROW_NUMBER() over that and then the first record has ROW_NUMBER()... = 1 and the previous has ROW_NUMBER() = 2, and you can just subtract. (Or I suppose you could use TOP(2)... and rownumber... then just use a CTE or whatever to get the two records on the same line and subtract.
Custnbr ---- (Lastest-Order date) ----(Previous Order Date) ---- Lag
(Since you didn't provide any data, I didn't test this code, but something like this should get you close)
SELECT c.Custnbr, ca.rn, ca.OrderDate
FROM Customer c
CROSS APPLY (SELECT TOP 2 OrderDate,
rn = ROW_NUMBER() OVER (PARTITION BY Custnbr ORDER BY OrderDate DESC
FROM Orders o
WHERE o.CustNbr = c.CustNbr
ORDER BY OrderDate DESC) ca
WHERE ca.rn <=2
ORDER BY OrderDate DESC) ca
WHERE ca.rn <=2
April 17, 2023 at 9:09 am
My query finds Min and Max to determine Lag, but I need to find the lastest Shipdate date(max) and then previous shipdate to determine lag --> remove the Min(shipdate).
I would like output to show:
Custnbr ---- (Lastest-Order date) ----(Previous Order Date) ---- Lag
Any Help?
SELECT
custnbr,
AvgLag = CASE WHEN COUNT(*) > 1 THEN
CONVERT(decimal(7,2),
DATEDIFF(day, MIN(shipdate), 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
You've nearly 2,000 points, yet still can't be arsed submitting your post with DDL and sample data. Why is that?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 18, 2023 at 2:51 am
This was removed by the editor as SPAM
April 18, 2023 at 3:01 am
Arrgh! That so stupid you editted your document too many times is sooo annoying. Okay here it is again.
Okay Phil Parkin criticizing a questioner is poor etiquete. Instead if you feel their question needed more information simply state that without being condescending. We can hope that in the future they will be more forth coming since doing so only helps them help us help them. Criticizing only degrades them and degrades you as that is how negative comments work.
SELECT [custnbr]
,[AvgLag] = CASE WHEN COUNT(*) > 1
THEN CONVERT( DECIMAL( 7, 2 )
,DATEDIFF( DAY
,MIN(shipdate)
,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;
Okay you are requesting the following bits of information but they are not included or describe as to how you are obtaining them
....... Lastest Order Date
....... Previous Order Date
....... Lag
Theoretically the Lastest Order Date would be MAX(Order_Date) but the Previous Order Date would need to be extracted by using the Max_OrderDate sorting DESC and taking the second value Further the Lag has to be properly defined is it the time between their very first order MIN or is it the time since their previous order which you do not have?
Again clarity in your question only speeds up the answer we can provide, the more detail the quicker the answer. Further if you make us guess then you could get the wrong answer.
April 18, 2023 at 3:15 am
I didn't have a problem with it. If you want condescending, you have to read some Joe Celko posts.
April 18, 2023 at 8:08 am
Thank you to Ken and pietlinden for your support.
Okay Phil Parkin criticizing a questioner is poor etiquete. Instead if you feel their question needed more information simply state that without being condescending. We can hope that in the future they will be more forth coming since doing so only helps them help us help them. Criticizing only degrades them and degrades you as that is how negative comments work.
Dennis Jensen
Unlike the OP, I am prepared to substantiate my post.
Anyone on this forum with more than a handful of points knows the etiquette required when posting a question asking for assistance with code:
That's five different things. How many has the OP provided in this case? Two, maybe (even that is not clear!)
Newcomers to the forum do not know the etiquette and I would never post anything like I did in response to them. Check the history of my posts if you do not believe me.
But Bruin is no newcomer. Check out some of their historical posts here and perhaps you will start to understand the reasons behind the tone of my comment.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 18, 2023 at 8:14 am
I didn't have a problem with it. If you want condescending, you have to read some Joe Celko posts.
For someone with a mathematical background, I do not think Joe Celko is too bad. My brother once attended a lecture on an area of research that interested him. At the end a statistician stood up and quietly destroyed two years of research, and the man's career, in less than fifteen mintues. As a blood sport it was brutal.
April 18, 2023 at 6:40 pm
I'm not saying that Joe isn't insanely smart. Not at all. He absolutely is. He's just ... (What's the right word??!!) .. inflexible? Kinda like an Aspie with Attitude? Perhaps he doesn't suffer fools, but his methods are pretty harsh.
April 18, 2023 at 7:11 pm
Phil Parkin please do not get me wrong, I fully understand your position but again negative comments rarely ever help, tweaking the comment to make it positively reinforce doing what ought to be done is just as easy. Further, you never know when a newbie is going to see one of your negative posts and feel that this site is not friendly to others asking question and go elsewhere. Lastly negative comments tear down not only the person the comment is aimed at but the person making the comment as that is the nature of negativism.
Umm.... The OP has form. I would say that repeatedly not providing DDL and test data is poor etiquette.
Okay so Ken McKelvey are you saying that two wrongs make a right?
Lastly what is wrong with just being nice, even if the individual asking the question is not doing all that they could to help themselves who does that hurt -- them -- not any of us. Our option is always just not to answer the question until they provide enough information to make the question answerable in our eyes. Heck Phil, you could even take that list you just provided (which I found quite enlightening being that I am a fairly new player to this forum) and simply copy/paste that without any negativity and then just post that to any question you feel is seriously lacking information it ought to have. Maybe title it something like: "Help Us Help You -- Please provide as much of the following as you can:" Thus simple and straight forward and you get your point across to the poster as well as inform newbies what they can include to Help Us Help Them. All positives and as such a win-win scenario. Just food for thought.
April 19, 2023 at 10:35 am
Dennis Jensen, your positivity is impressive and laudable. But the definition of insanity as "doing the same thing repeatedly and expecting a different outcome" (wrongly attributed to Einstein) is applicable in this case. The OP has been active in the forum for several years: time enough, in my opinion, to have taken onboard the myriad good-natured requests for more information along the way, all presented along the lines prescribed by you.
So I tried a different tactic. Maybe it will have an effect, maybe not, but it's better than the (figurative) flogging of a dead horse described above. I do not feel 'torn down' as a result, and do not expect to. If people take some sort of virtual dislike to me and my posts as a result of this tactic, I'm of an age where I truly don't care one jot.
Many of the posters here have 'how to ask for help'-type links in their signatures, which contain all of the points I suggested and more; indeed I have also had such a thing in the past. As you are a relative newcomer to the forums, I suggest you take the time to read some of them, as there is some excellent advice in there.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 19, 2023 at 1:45 pm
Your choice Phil Parkin but negativity does what negativity does whether you notice it our not. In fact, it is more like putting a frog in cool water and slowly turning up the heat until the frog calmly and unknowingly gets cooked alive. Oh and constantly being positive and expecting different results in others is not its purpose thus your insantity statement does not apply. I mean being constantly positive is for oneself and no one should feel responsible for what someone else chooses to do or not do with that positivity as that would be insanity or least a major step in its direction. One persues their own happiness and if they want to help others be happier they try to share a bit of that happiness with them, but whether those others choose to accept that or not is totally their choice.
Also I did give you two options: (1) A quick copy/paste solution that is not negative, takes very little effort, and waste very little time (2) Do not respond at all, takes even less effort, and wastes even less time - oh and also not negative
Lastly constant positivity while not always effective on others is always effective on oneself -- it keeps you above the general depression that prevades this world because so many folks focus on the negative (either in others and/or themselves) rather than the positives of their life (either in others and/or themselves). Again just food for thought.
Oh and btw I was not always this way, it was a long hard journey up that slope of negativity that nearly swallowed me completely. So again many folks simply allow themselves to unknowingly slide down its slippery slope. So I have experienced it first hand, so one can either learn from history (aka others mistakes) or be condemned to repeat it -- I believe this saying is what applies here. Finally may your life be joyfilled and happy, regardless of the circumstances you find yourself in.
April 21, 2023 at 4:35 am
I would like output to show:
Custnbr ---- (Lastest-Order date) ----(Previous Order Date) ---- Lag
Any Help?
SELECT
custnbr,
AvgLag = CASE WHEN COUNT(*) > 1 THEN
CONVERT(decimal(7,2),
DATEDIFF(day, MIN(shipdate), 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
Your query has shipdate column but you want to output latest two OrderDate with other columns? Is this your final query?
I observed that ShipDate and OrderDate are entirely different columns.....right? Something is missing here, Can you provide the DDL of your view?
=======================================================================
April 21, 2023 at 10:58 am
Sorry I was using the wrong name ... Shipdate should be used in all occurrences.
This is the backend table that drives the view.
Thanks.
CREATE TABLE [dbo].[ConHist_PY](
[InvoiceNbr] [nchar](10) NOT NULL,
[Line] [int] NOT NULL,
[sub_line_nbr] [int] NOT NULL,
[Channel] [char](6) NULL,
[ShipSite] [char](8) NULL,
[CustNbr] [char](19) NULL,
[ItemID] [char](18) NULL,
[ProdLine] [char](4) NULL,
[ShipDate] DATE NOT NULL,
[QtyShipped] [float] NULL,
[scmisc] [float] NULL,
CONSTRAINT [PK_ConHist_PY] PRIMARY KEY CLUSTERED
(
[InvoiceNbr] ASC,
[Line] ASC,
[ShipDate] ASC,
[sub_line_nbr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply