TSQL: Create dynamic partition DataRange

  • Hello All

    I am facing difficulties to write a query to create partition.

    In input I have a table with these values.

    I want to create a parameter table to create partitions for a tabular model. Depending on the values ??of the Granularity column (Monthly or Yearly) I have to create partitions.

    For these inputs, I want to have these outputs.

    Could someone please help me to write this query?

     

  • Any help ?

  • Any help please?

  • There are different date calculations depending on granularity.  This seems to work

    drop table if exists #foo;
    go
    create table #foo(
    [Table] sysname,
    granularity varchar(20),
    minDate date,
    maxDate date);

    Insert into #foo values
    ('A', 'Yearly', '20200101', '20221231'),
    ('B', 'Monthly', '20200101', '20211231');

    select [Table], granularity,
    case when f.granularity='Yearly' then year(calc.dt)
    when f.granularity='Monthly' then year(calc.dt)*100+month(calc.dt)
    else null end [Partition],
    f.minDate FromDate, f.maxDate ToDate
    from #foo f
    cross apply dbo.fnTally(0, case when f.granularity='Yearly' then datediff(year, minDate, maxDate)
    when f.granularity='Monthly' then datediff(month, minDate, maxDate)
    else null end) fn
    cross apply (values (case when f.granularity='Yearly' then dateadd(year, fn.n, minDate)
    when f.granularity='Monthly' then dateadd(month, fn.n, minDate)
    else null end)) calc(dt)
    order by [Table], calc.dt;

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

  • Hello

    Thank you for your help.

    I will test your solution.

    Thank you

     

     

     

     

  • Hello

    I just tested the solution.

    But I don't have the dbo.fnTally function.

    I have an error when I execute the query.

    Could you please share the script of the dbo.fnTally function ?

     

    Regards

     

     

     

     

    • This reply was modified 1 month, 2 weeks ago by  Lidou123.
  • Yes sorry.  It should've been included.  The code is from Jeff Moden's article here on SSC. It comes in handy in many places

    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

  • OK. Thanks

     

  • Hello @steve-2 Collins

    I hope you are well.

    Thank you for the script. I just tested the code.

    I saw that there is an error about the validity dates for the partitions.

    Indeed, for the 2020 partition for example the values ??of the column [FromDate] must be equal to '2020-01-01' and [ToDate] = '2021-12-31'

    And for the monthly partition '202001' we should have had [FromDate] = '2020-01-01' and [ToDate] = '2020-12-21'

    I'll use your code and try to do a join with a Calendar table.

    What do you think ?

    Here is the result that I would like to obtain in output.

    In case you have an idea, I'm a taker.

     

  • Yes a calendar table is another workable approach.  What if the minDate and/or maxDate is other than the 1st or last day of the month?  Maybe something like this

    drop table if exists #foo;
    go
    create table #foo(
    [Table] sysname,
    granularity varchar(20),
    minDate date,
    maxDate date);

    Insert into #foo values
    ('A', 'Yearly', '20200102', '20221231'),
    ('B', 'Monthly', '20200101', '20211230');

    select [Table], granularity,
    case when f.granularity='Yearly' then year(calc.dt)
    when f.granularity='Monthly' then year(calc.dt)*100+month(calc.dt)
    else null end [Partition],
    datefromparts(year(calc.dt), month(calc.dt), case when fn.n=0
    then day(f.minDate)
    else 1 end) FromDate,
    case when year(calc.dt)=year(f.maxDate) and month(calc.dt)=month(f.maxDate)
    then f.maxDate
    else eomonth(calc.dt) end ToDate
    from #foo f
    cross apply dbo.fnTally(0, case when f.granularity='Yearly' then datediff(year, minDate, maxDate)
    when f.granularity='Monthly' then datediff(month, minDate, maxDate)
    else null end) fn
    cross apply (values (case when f.granularity='Yearly' then dateadd(year, fn.n, minDate)
    when f.granularity='Monthly' then dateadd(month, fn.n, minDate)
    else null end)) calc(dt)
    order by [Table], calc.dt;

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

  • Lidou123 wrote:

    Any help please?

    If you want faster help in the future, please see the first link in my signature line below for one of many different ways to post "Readily Consumable Data" and why.

    --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)
    Intro to Tally Tables and Functions

  • Hello @steve-2 Collins

    Thank you for your help.

    I tested the script and it works well.

    Thanks a lot

  • Let's try something a wee bit different and let's use a really nice tool written exactly for this type of thing.  The tool was written by Jonathan Roberts, is a nice, high performance iTVF called "dbo.DateRange" that runs faster than Richard Pryor with his hair on fire and, like I said, is perfect for this job.  It's available at the following URL.  Go get it and install it in the database you need to do all this "date partitioning" in.

    https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

    Once that's done, here's what we normally look for when it comes to test data... nice, readily consumable test data.  The first two rows contain the original test data and I added an extra one to show his function has Tamahagane Steel in the blade.

    --===== Create the partition control table.
    -- This is NOT a part of the solution!
    -- We're just simulating the table the OP has.
    DROP TABLE IF EXISTS #Partition;
    SELECT v.*
    INTO #Partition
    FROM (VALUES
    ('A' ,'Yearly' ,'20200101','20221231')
    ,('B' ,'Monthly','20200101','20200601')
    ,('JBM' ,'Monthly','20001201','20990630')
    )v([Table],Granularity,MinDate,MaxDate)
    ;

    Using Jonathan's DateRange function seriously simplifies what we need to do for this problem and makes it a whole lot easier if you want to add, for example, "quarters" to the temporal "partition" types.

     SELECT  p.[Table]
    ,p.Granularity
    ,[Partition] = CASE g.GType
    WHEN 'yy' THEN LEFT(f.FromDate,4)
    WHEN 'mm' THEN LEFT(f.FromDate,6)
    ELSE 'ERROR'
    END
    ,FromDate = CONVERT(CHAR(8),dr.[Value],112)
    ,ToDate = CONVERT(CHAR(8),
    DATEADD(dd,-1,
    CASE g.GType
    WHEN 'yy' THEN DATEADD(yy,1,dr.[Value])
    WHEN 'mm' THEN DATEADD(mm,1,dr.[Value])
    ELSE 'Error'
    END)
    ,112)
    FROM #Partition p
    CROSS APPLY (VALUES(CASE p.Granularity WHEN 'Yearly' THEN 'yy' WHEN 'Monthly' THEN 'mm' ELSE 'ERROR' END)) g(GType)
    CROSS APPLY dbo.DateRange(p.MinDate,p.MaxDate,g.GType,1) dr
    CROSS APPLY (VALUES(CONVERT(CHAR(8),dr.[Value],112))) f (FromDate)
    ;

    Here's the partial output from running the code above with the test data above.

    Seriously... Go get Jonathan's function and leave him a note thanking him for it.

    --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)
    Intro to Tally Tables and Functions

  • Thanks Jeff,

    Good so see the function is in use. It does make your code much shorter than other methods.

  • Hello @jeff Moden and @jonathan-2 .

    Thank you for your help.

    I will test this solution too.

Viewing 15 posts - 1 through 15 (of 16 total)

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