suggestion please

suggestion please

ShambhuRai-4099  JingyangLi · 3 minutes ago

when value is same for the status then it should take min date

expected output

('2022-01-04 18:00:57','Newone','segmentone')

when the value is change for the status then max date

expected output

('2022-04-04 19:00:57','Newtwo','segmentfour')

Hi Expert,

Hi Expert,

there is condition when value is same for the status then it should take min date and if the value is change for the status then max date

create...

• This reply was modified 2 years, 7 months ago by  Shree23.
• This reply was modified 2 years, 7 months ago by  Shree23.
• This reply was modified 2 years, 7 months ago by  Shree23.

HI Expert,

This is perfect. but the data needs max(date) should be order by same day and not all calendar days

insert into test

values

('2022-01-04 18:00:57','Newone','segmentone'),('2022-01-04 19:00:57','Newone','segmenwo'),

('2022-04-04 18:00:57','Newtwo','segmenthee'),('2022-04-04 19:00:57','Newtwo','segmenfour')

expected output:

('2022-01-04 19:00:57','Newone','segmenwo'),

('2022-04-04 19:00:57','Newtwo','segmenfour')

what a solution ..is there any easiest way to do this just want to have capital word after space

it shows multiple dates for values which is incorrect

Hi Expert,

it is showing incorrect output. Am attaching the expected output again

Hi Expert,

I am so sorry

here is the expected output

i tried

SELECT TestID, Status, StartDate, case when Lead(StartDate) OVER (PARTITION BY TestID ORDER BY StartDate) =LEAD(StartDate) OVER (PARTITION BY TestID ORDER BY StartDate) then Lag(StartDate) OVER (PARTITION BY TestID ORDER...

I have repeated and loaded  the insert statement

insert into table3

values

('1','Progress', '20210203'),

('1', 'Closed', '20220403'),

('2', 'Closed', '20220703'),

('2', 'Progress', '20220503')

it is showing wrong output

if the date is repeated...

select Testid, Status ,count(testid) as count,

Startdate, lead(DATEADD(day,-1,Startdate)) over (order by testid,Startdate) from table3 group by Testid,Status,Startdate

I tried above lead function but unable to get above output

suggestion pls

Hi,

Actually id do not understand logic for this help me with clue

on the 6th April -Main Table - 1 (quantity )sold and 73 consumables from Ratio Table on 7th April-...

when i have added 2 rows and max 2 rows is giving '2021-02-19' only one record

('577', '-1', '0', '2021-02-19');

('577', '-1', '1', '2021-02-20');