Hello Team,
I have a table - Tbl_status in which three columns - Product , Lastupdate , Status
Insert two records - A, 21-07-2022 , Active
A.24-07-2022 , InActive
Output
A, 21-07-2022 , Active
A, 22-07-2022 , Active
A, 23-07-2022 , Active
A.24-07-2022 , InActive
Kinldy share the query for required output. Thanks
----------------------------------------------------------------
-- Create Tables
create table #Tbl_status(Product varchar(1), Lastupdate date, Status varchar(10))
insert into #Tbl_status select 'A','2022/07/21' , 'Active'
insert into #Tbl_status select 'A','2022/07/24' , 'InActive'
----------------------------------------------------------------
-- select tables
select 'data',* from #Tbl_status
----------------------------------------------------------------
-- Actual Query
;with tally_cte as (sELECT N=number FROM master..spt_values WHERE type = 'P') ,
cte as ( select Product,Status,dateadd(dd,N,LastUpdate) as LastUpdate from #Tbl_status a , tally_cte b )
select
*
from
cte where LastUpdate < ( select max(lastupdate) from #Tbl_status)
union all
select Product,Status,Lastupdate from #Tbl_status where Status = 'InActive'
----------------------------------------------------------------
Thanks , Can it will work if records are more than 2048 for same product ?
can you please explain that a little clearly
records more than 2048 for same product means ???
test it yourself as follows
create table #Tbl_status(Product varchar(1),
Lastupdate date,
Status varchar(10))
;with src
as
(
SELECT TOP (2048 )
'A' as product,
id = ISNULL(CAST(
ROW_NUMBER() OVER (ORDER BY (SELECT 1))
AS INT),0)
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
CROSS JOIN master.sys.all_columns ac3
)
insert into #Tbl_status
select product, dateadd(mm,id,GETDATE()),
case
when id % 2 = 0 then 'Active' else 'Inactive' end as status
from src
;WITH src
AS (SELECT TOP (2048 ) 'A' AS product,
id = Isnull(Cast(Row_number()
OVER (
ORDER BY (SELECT 1)) AS INT), 0
)
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
CROSS JOIN master.sys.all_columns ac3)
INSERT INTO #tbl_status
SELECT product,
Dateadd(mm, id, Getdate()),
CASE
WHEN id % 2 = 0 THEN 'Active'
ELSE 'Inactive'
END AS status
FROM src;
WITH tally_cte
AS (SELECT N=number
FROM master..spt_values
WHERE type = 'P'),
cte
AS (SELECT product,
status,
Dateadd(dd, n, lastupdate) AS LastUpdate
FROM #tbl_status a,
tally_cte b) SELECT *
FROM cte
WHERE lastupdate < (SELECT Max(lastupdate)
FROM #tbl_status)
UNION ALL
SELECT product,
status,
lastupdate
FROM #tbl_status
WHERE status = 'InActive'
drop table #Tbl_status