min and max startdate

Question

min and max startdate

SSC-Addicted

Points: 489

May 20, 2022 at 1:35 pm

Hi Expert,

I wanted to calculate max date for prodstatus =5 - min date for prodstatus=4 and max date for prodstatus =4 - min date for prodstatus=2

Create table
CREATE TABLE [dbo].[testdata](
[Prodnumber] [nchar](20) NULL,
[Prodid] [nchar](20) NULL,
[Prodstatusid] [nchar](20) NULL,
[PrdStartDate] [date] NULL,
[PrdEndDate] [date] NULL,
[prodstatus] [nchar](23) NULL
) ON [PRIMARY]
GO

Insert
insert into [dbo].[testdata]

values('Prod1000','873','7','2021-06-16', '11-05-2022', '4'),
('Prod1000','873', '18', '2022-05-12',NULL,'7'),
('Prod1000', '873', '19', '2022-05-12', NULL,'5'),
('Prod10000', '1254','4', '2022-03-28', '2022-03-30', '6'),
('Prod10000', '1254','1', '2022-03-3', NULL, '7'),
('Prod10002', '4427', '5', '2022-03-28', '2022-03-31', '4'),
('Prod10002', '4427', '8', '2022-04-01', NULL, NULL),
('Prod10002', '5603', '8', '2022-04-01', NULL, 2)




tried :
--create or alter view vStatusesChange
--as
WITH cte AS (SELECT ProdStatus,prodnumber, MIN(PrdStartDate) AS MinStartDate,
MAX(t.PrdStartDate) AS MaxStartDate

--select *
FROM dbo.testdata t WHERE t.ProdStatus between '4' and '5'
GROUP BY ProdStatus,prodnumber), cte2 AS (

SELECT

cte.MinStartDate AS MinStartDate,prodnumber, max(MaxStartDate) OVER (
ORDER BY CAST(ProdStatus as char)) AS MaxStartDate
FROM cte)

SELECT *, CASE WHEN cte2.MaxStartDate IS NULL THEN 0
ELSE DATEDIFF(DAY, MinStartDate, MaxStartDate) end AS '4_to5'
FROM cte2
GO

am expecting view format

Expected output:

This topic was modified 2 years, 3 months ago by Shree23.

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

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

Thom A SSC Guru Points: 99009 More actions · Answer 1

What about the rows which have a value of 'Prod10000'? How do you determine what value of Prodstatusid is returned; is that the MIN too? What is the logic for the columns 4/5 and 4_2? 330 isn't in your data at all.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Shree23 SSC-Addicted Points: 489 More actions · Answer 2

it would be 7 which means produnumber,min prodstartdate,prodid will remain same only maxprodstartdate and 4_5 and 4_2 value will change

see the output:

Thom A SSC Guru Points: 99009 More actions · Answer 3

it would be 7

But why would it be 7? You haven't explained the logic for 4_5 and 4_2 either.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Shree23 SSC-Addicted Points: 489 More actions · Answer 4

7 is expected because prodnumber=prod1000 comes with Minstartdate. whatsover value with minstartdate that should come and maxstartdate,4_5,4_2 will be calculated as below

here is the calculation for 4_5 and 4_2

calculate max date for prodstatus =5 - min date for prodstatus=4

and max date for prodstatus =4 - min date for prodstatus=2

Shree23 SSC-Addicted Points: 489 More actions · Answer 5

Shree23

SSC-Addicted

Points: 489

May 20, 2022 at 6:55 pm

#4036376

suggestion pls

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Ok, Shree23... I just want you to know that this is how you piss people off and they end up not wanting to help you. Your first posted on anther thread and don't check what you posted with too-small graphics and test data that doesn't match your "desired results". You get asked about that and, instead of answering the question, you open a new thread with the same test data and totally different desired results.

Shree23 wrote:

suggestion pls

My suggestion is that you stop doing that. 😉

Good luck.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions