Post reply

How to make select statment get data based on partid from last date with gapes ?

  • March 15, 2022 at 11:59 pm

    Go to Answer

    #4000271

    I work on sql server 2012 i face issue i need to make select statment get Partid from last month until current month

    based on last date exist per partid

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

    so

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

    second case if partid with date on month 7 and month 10 and no date per part id on month 8 and 9 then it must display this gap

    according to last month as partid 1234 have gap

    both cases must applied for all data based on partid

    date used below on formate yyyy-mm-dd

     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

    Expected result

     

  • March 16, 2022 at 11:30 am

    #4000501

    code below give me part from my expected result

    because it give me gaps null between dates remaining to get dates until current month

    so How to do that please ?

    what i try

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

    • This reply was modified 2 years, 8 months ago by  ahmed_elbarbary.2010. Reason: make part from solution remaining get part id until current date

  • March 16, 2022 at 2:38 pm

    #4000609

    In a CTE you could select the date difference between the CreatedDate and the LEAD(CreatedDated, 1, default) where the default is the current month (+1 to deal with boundary crossing).  With the number of missing months calculated in the cte you could generate new rows using a tally function

    with lead_cte(PartId, CreatedDate, lead_num) as (
        select *, 
               datediff(month,
                        CreatedDate,
                        lead(CreatedDate, 1, dateadd(month, 1, cast(getdate() as date))) 
                             over (partition by PartId order by CreatedDate))
        from #Parts)
    select ld.*, calc.CreatedDateAll
    from lead_cte ld
         cross apply dbo.fnTally(0, ld.lead_num-1) fn
         cross apply (values (dateadd(month, fn.n, ld.CreatedDate))) calc(CreatedDateAll)
    order by ld.PartId, calc.CreatedDateAll;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • March 16, 2022 at 3:27 pm

    #4000641

    can you please give me function

    dbo.fnTally

    to check this query

  • March 16, 2022 at 4:08 pm

    #4000668

    Steve Collins wrote:

    In a CTE you could select the date difference between the CreatedDate and the LEAD(CreatedDated, 1, default) where the default is the current month (+1 to deal with boundary crossing).  With the number of missing months calculated in the cte you could generate new rows using a tally function

    with lead_cte(PartId, CreatedDate, lead_num) as (
        select *, 
               datediff(month,
                        CreatedDate,
                        lead(CreatedDate, 1, dateadd(month, 1, cast(getdate() as date))) 
                             over (partition by PartId order by CreatedDate))
        from #Parts)
    select ld.*, calc.CreatedDateAll
    from lead_cte ld
         cross apply dbo.fnTally(0, ld.lead_num-1) fn
         cross apply (values (dateadd(month, fn.n, ld.CreatedDate))) calc(CreatedDateAll)
    order by ld.PartId, calc.CreatedDateAll;

    Awesome job, Steve!

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 16, 2022 at 4:14 pm

    #4000669

    Sure, the function code is below.  It was taken from this article

    CREATE FUNCTION [dbo].[fnTally]
    /**********************************************************************************************************************
        Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
    **********************************************************************************************************************/
            (@ZeroOrOne BIT, @MaxN BIGINT)
    RETURNS TABLE WITH SCHEMABINDING AS 
     RETURN WITH
      H2(N) AS ( SELECT 1 
                   FROM (VALUES
                         (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                        ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                        )V(N))            --16^2 or 256 rows
    , H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
    , H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
                SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
                SELECT TOP(@MaxN)
                       N = ROW_NUMBER() OVER (ORDER BY N)
                  FROM H8
    ;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • March 16, 2022 at 4:32 pm

    #4000696

    Jeff Moden wrote:

    Awesome job, Steve!

    Thank you Professor Moden!  Congratulations in advance for passing the 1,000,000 point milestone.  I appreciate and learn from you all the time and I hope you get a million more.  Cheers Jeff!

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • March 16, 2022 at 5:04 pm

    #4000702

    Heh... I haven't been watching but, now that you brought it up and in honor of your great code, here's # 1 million. 😀

    It'll take the site software about 20 minutes to figure that out.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 16, 2022 at 5:31 pm

    #4000734

    Here's the screenshot!  Congratulations again Jeff Moden and THANK YOU for everything 🙂

    JeffModenOneMillion

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • March 16, 2022 at 6:02 pm

    #4000799

    Thanks for the screen shot and the very kind words.  I aim to please... I sometimes miss but I'm always aimin'. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 18, 2022 at 9:19 am

    Answer

    #4001831

    really very good support

    thanks you

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply