Need Sql Query

Transact-SQL
1

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

2 
----------------------------------------------------------------
-- 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'
----------------------------------------------------------------

image

3

Thanks , Can it will work if records are more than 2048 for same product ?

4

can you please explain that a little clearly

records more than 2048 for same product means ???

5

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