×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

thread183-1814184

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

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

(OP)
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 --> sqlserver

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 --> sqlserver

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;

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

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

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

(OP)
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

CODE --> 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

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

so can any one help me

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

i need same result above but with Leadingvalue

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

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

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

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

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

Thank you for the code :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close