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
-
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) -
-
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) -
Thanks Jeff,
Good so see the function is in use. It does make your code much shorter than other methods.
-
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply