How to get monther per part id from last date to current date and include remaining date ? 1

ahmedsa2018 · Mar 15, 2022

I work on sql server 2014 i face issue i can't make select statment get Partid from last month until current month march (3) .

based on last date exist per partid

and on same time if there are any gaps between dates then fill it based on last date

so if

I found partid with last date 2022-01-08 then i will add 08-02-2022 and 08-03-2022 as partid 6070

and if partid with date on month 7 and next month 10 and no date per part id on month 8

and 9 then it must display this gaps according to last month as partid 1234 have gap .

both cases must applied for all data based on partid .

Createddate used below on formate yyyy-mm-dd .

th cases must applied for all data based on partid .

Createddate used below on formate yyyy-mm-dd .

Code:

create table Parts
 (
    
 PartId int,
 CreatedDate date
 )
 insert into Parts(PartId,CreatedDate)
 values
 (1234,'2021-07-03'),
 (1234,'2021-10-05'),
 (1234,'2021-11-05'),
 (5981,'2021-11-15'),
 (5981,'2021-12-03'),
 (6070,'2021-12-12'),
 (6070,'2022-01-08')

i need to make select statment display parts as expected result

green rows only for more clear that these parts must added with old parts exist before .

Expected result

PartId CreatedDate
1234 2021-07-03
1234 2021-08-03
1234 2021-09-03
1234 2021-10-05
1234 2021-11-05
1234 2021-12-05
1234 2022-01-05
1234 2022-02-05
1234 2022-03-05
5981 2021-11-15
5981 2021-12-03
5981 2022-01-03
5981 2022-02-03
5981 2022-03-03
6070 2021-12-12
6070 2022-01-08
6070 2022-02-08
6070 2022-03-08

what i try

Code:

with cte as (
      select partid, month(CreatedDate),
             dateadd(month, -1,
                     coalesce(lead(month(CreatedDate)) over (partition by partid order by month(CreatedDate)),
                              max(month(CreatedDate)) over ()
                             )
                    ) as end_month
      from Parts
      union all
      select partid, dateadd(month, 1, month(CreatedDate)) as monthes, end_month
      from cte
      where monthes < end_month
     )
select *
from cte
order by partid, month;

bborissov · Mar 17, 2022

Code:

DECLARE @Parts table 
 (
    
 PartId int,
 CreatedDate date
 )
 insert into @Parts(PartId,CreatedDate)
 values
 (1234,'20210703'),
 (1234,'20211005'),
 (1234,'20211105'),

 (5981,'20211115'),
 (5981,'20211203'),

 (6070,'20211212'),
 (6070,'20220108') 


 DECLARE @CurrentMonth date 
 SET @CurrentMonth= '20220331'
 ;WITH CteTest (PartId,CreatedDate)
 AS
 (
  SELECT PartId,CreatedDate
  FROM (SELECT PartId,CreatedDate, ROW_NUMBER() OVER (PARTITION BY PartId ORDER BY CreatedDate ASC) AS RC
        FROM @Parts) Test
  WHERE RC = 1
  UNION ALL
  SELECT CteTest.PartId,
         CASE WHEN EXISTS(SELECT * FROM @Parts Prt WHERE Prt.PartId = CteTest.PartId
                                                    AND YEAR(Prt.CreatedDate)  = YEAR( DATEADD(mm,1,CteTest.CreatedDate))
                                                    AND MONTH(Prt.CreatedDate) = MONTH( DATEADD(mm,1,CteTest.CreatedDate)))
              THEN (SELECT CreatedDate FROM @Parts Prt WHERE Prt.PartId = CteTest.PartId
                                                    AND YEAR(Prt.CreatedDate)  = YEAR( DATEADD(mm,1,CteTest.CreatedDate))
                                                    AND MONTH(Prt.CreatedDate) = MONTH( DATEADD(mm,1,CteTest.CreatedDate)))
         ELSE DATEADD(mm,1,CteTest.CreatedDate) END AS CreatedDate
  FROM CteTest
  WHERE DATEADD(mm,1,CteTest.CreatedDate) < @CurrentMonth
 )

 SELECT * FROM CteTest
 ORDER BY PartId, CreatedDate

NOT TESTED properly

Borislav Borissov
VFP9 SP2, SQL Server

ahmedsa2018 · Mar 18, 2022

this is good
and give me desired result

i try add new column as lead value
but it repeated value on all partid
and this is wrong

SQL:

DECLARE @Parts table 
 (
    
 PartId int,
 CreatedDate date,
 LeadValue int
 )
 insert into @Parts(PartId,CreatedDate,LeadValue)
 values
 (1234,'20210703',5),
 (1234,'20211005',90),
 (1234,'20211105',50),

 (5981,'20211115',70),
 (5981,'20211203',60),

 (6070,'20211212',20),
 (6070,'20220108',10) 


 DECLARE @CurrentMonth date 
 SET @CurrentMonth= '20220331'
 ;WITH CteTest (PartId,CreatedDate,LeadValue)
 AS
 (
  SELECT PartId,CreatedDate,LeadValue
  FROM (SELECT PartId,CreatedDate,LeadValue, ROW_NUMBER() OVER (PARTITION BY PartId ORDER BY CreatedDate ASC,LeadValue asc) AS RC
        FROM @Parts) Test
  WHERE RC = 1
  UNION ALL
  SELECT CteTest.PartId,
         CASE WHEN EXISTS(SELECT * FROM @Parts Prt WHERE Prt.PartId = CteTest.PartId
                                                    AND YEAR(Prt.CreatedDate)  = YEAR( DATEADD(mm,1,CteTest.CreatedDate))
                                                    AND MONTH(Prt.CreatedDate) = MONTH( DATEADD(mm,1,CteTest.CreatedDate)))
              THEN (SELECT CreatedDate FROM @Parts Prt WHERE Prt.PartId = CteTest.PartId
                                                    AND YEAR(Prt.CreatedDate)  = YEAR( DATEADD(mm,1,CteTest.CreatedDate))
                                                    AND MONTH(Prt.CreatedDate) = MONTH( DATEADD(mm,1,CteTest.CreatedDate)))
         ELSE DATEADD(mm,1,CteTest.CreatedDate) END AS CreatedDate,LeadValue
  FROM CteTest
  WHERE DATEADD(mm,1,CteTest.CreatedDate) < @CurrentMonth
 )

 SELECT * FROM CteTest
 ORDER BY PartId, CreatedDate

and this is my desired result as below

PartId CreatedDate LeadValue
1234 2021-07-03 5
1234 2021-08-03 5
1234 2021-09-03 5
1234 2021-10-05 90
1234 2021-11-05 50
1234 2021-12-05 50
1234 2022-01-05 50
1234 2022-02-05 50
1234 2022-03-05 50
5981 2021-11-15 70
5981 2021-12-03 60
5981 2022-01-03 60
5981 2022-02-03 60
5981 2022-03-03 60
6070 2021-12-12 20
6070 2022-01-08 10
6070 2022-02-08 10
6070 2022-03-08 10

ahmedsa19051985 · Mar 18, 2022

so can any one help me

ahmedsa19051985 · Mar 18, 2022

i need same result above but with Leadingvalue

ahmedsa19051985 · Mar 19, 2022

i use date format (year-month-day)
as example partid 1234
partid 1234 have gap on created date for 2 months between 2021-07-03 and 2021-10-05
so it will be

2021-08-03 and leading value 5
2021-09-03 and leading value 5

leading value will take first value of gap start
meaning it will get LEADING value of 2022-07-03 as 5 value

also for part 1234 we need to file gape between last date for part 1234
as 2021-11-05 and current month
so i will get remaining months until current month
then
i will add 4 months with same value of starting gap as 50

1234 2021-12-05 50
1234 2022-01-05 50
1234 2022-02-05 50
1234 2022-03-05 50

leading value will be 50 part 1234 will be 50 from 2021-12-05 to 2022-03-05
because data i will start from it is 2021-11-05 with leading value 50 and this is point of start
and I add date until 05-03-2022 because i add dates until current month based on
partid order by created date

bborissov · Mar 21, 2022

Code:

DECLARE @Parts table 
 (
    
 PartId int,
 CreatedDate date,
 LeadValue int
 )
 insert into @Parts(PartId,CreatedDate,LeadValue)
 values
 (1234,'20210703',5),
 (1234,'20211005',90),
 (1234,'20211105',50),

 (5981,'20211115',70),
 (5981,'20211203',60),

 (6070,'20211212',20),
 (6070,'20220108',10) 


 DECLARE @CurrentMonth date 
 SET @CurrentMonth= '20220331'
 ;WITH CteTest (PartId,CreatedDate,LeadValue)
 AS
 (
  SELECT PartId,CreatedDate,LeadValue
  FROM (SELECT PartId,CreatedDate,LeadValue, ROW_NUMBER() OVER (PARTITION BY PartId ORDER BY CreatedDate ASC,LeadValue asc) AS RC
        FROM @Parts) Test
  WHERE RC = 1
  UNION ALL
  SELECT CteTest.PartId
        ,CASE WHEN EXISTS(SELECT * FROM @Parts Prt WHERE Prt.PartId = CteTest.PartId
                                                    AND YEAR(Prt.CreatedDate)  = YEAR( DATEADD(mm,1,CteTest.CreatedDate))
                                                    AND MONTH(Prt.CreatedDate) = MONTH( DATEADD(mm,1,CteTest.CreatedDate)))
              THEN (SELECT CreatedDate FROM @Parts Prt WHERE Prt.PartId = CteTest.PartId
                                                    AND YEAR(Prt.CreatedDate)  = YEAR( DATEADD(mm,1,CteTest.CreatedDate))
                                                    AND MONTH(Prt.CreatedDate) = MONTH( DATEADD(mm,1,CteTest.CreatedDate)))
         ELSE DATEADD(mm,1,CteTest.CreatedDate) END AS CreatedDate
        ,CASE WHEN EXISTS(SELECT * FROM @Parts Prt WHERE Prt.PartId = CteTest.PartId
                                                     AND YEAR(Prt.CreatedDate)  = YEAR( DATEADD(mm,1,CteTest.CreatedDate))
                                                     AND MONTH(Prt.CreatedDate) = MONTH( DATEADD(mm,1,CteTest.CreatedDate)))
              THEN (SELECT LeadValue FROM @Parts Prt WHERE Prt.PartId = CteTest.PartId
                                                       AND YEAR(Prt.CreatedDate)  = YEAR( DATEADD(mm,1,CteTest.CreatedDate))
                                                       AND MONTH(Prt.CreatedDate) = MONTH( DATEADD(mm,1,CteTest.CreatedDate)))
         ELSE CteTest.LeadValue END AS LeadValue
  FROM CteTest
  WHERE DATEADD(mm,1,CteTest.CreatedDate) < @CurrentMonth
 )

 SELECT * FROM CteTest
 ORDER BY PartId, CreatedDate

Borislav Borissov
VFP9 SP2, SQL Server

JohnBayne · Sep 9, 2022

Thank you for the code

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How to get monther per part id from last date to current date and include remaining date ? 1

ahmedsa2018

Programmer

bborissov

Programmer

ahmedsa2018

Programmer

ahmedsa19051985

IS-IT--Management

ahmedsa19051985

IS-IT--Management

ahmedsa19051985

IS-IT--Management

bborissov

Programmer

JohnBayne

Technical User

Similar threads

Part and Inventory Search

Sponsor