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!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
ahmedsa2018

ahmedsa2018

Programmer
Apr 25, 2018
67
EG
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;
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
Last date per userid 1
Replies
3
Views
153
Andrzejek
Andrzejek
leifoet
  • Locked
  • Question
How te filter by date ?
Replies
9
Views
246
dhookom
dhookom
peac3
  • Locked
  • Question
Derive staus date time by aligning start and end datetime
Replies
7
Views
373
Andrzejek
Andrzejek
Cpreston
  • Locked
  • Question
How to add a Letter to datepart
Replies
5
Views
415
Andrzejek
Andrzejek
dgillz
  • Locked
  • Question
How to convert varchar to time? 2
Replies
1
Views
616
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top