November 29, 2016 at 12:47 pm
Hello,
I have a table A like below
Acctnum StartDate
AAA 07312016
BBB 06302016
And I am looking to create below output table based on table A.
The todate field should have dates starting from startdate(Table A) till last month end date(10312016).
Acctnumfromdate toDate
AAA 07312016 08312016
AAA 07312016 09302016
AAA 07312016 10312016
BBB 06302016 07312016
BBB 06302016 08312016
BBB 06302016 09302016
BBB 06302016 10312016
What approach should i take to achieve this?
Thank you..
abi
November 29, 2016 at 1:09 pm
I hope that you're not using strings or integers to store dates.
To achieve this, I'd recommend an inline table-valued function based on a tally (or numbers) table[/url]. With that, the rest is easy.
/*
This function creates ranges incrementing months based on 2 dates.
*/
CREATE FUNCTION [dbo].[ExpandDates](
@fromdate date = '20160731',
@toDate date = '20161031'
) RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP(DATEDIFF(MM, @fromdate, @toDate) ) --Limit the number of rows created
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT @fromdate fromdate,
EOMONTH(@fromdate,n) toDate,
n
FROM cteTally t;
GO
--Create sample data
DECLARE @TableA Table(
Acctnum char(3),
StartDate datetime);
INSERT INTO @TableA
VALUES
( 'AAA', '20160731'),
( 'BBB', '20160630');
--Apply solution
SELECT A.Acctnum,
x.fromdate,
x.toDate
FROM @TableA A
CROSS APPLY dbo.ExpandDates(StartDate, '20161031') x
ORDER BY A.Acctnum, x.n;
GO
--Clean my test database
DROP FUNCTION [dbo].[ExpandDates];
November 29, 2016 at 2:22 pm
Numbers Table #FTW!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 29, 2016 at 2:35 pm
Thanks Luis for guiding me ...
i made some changes like i am on SQL 2008 and Eomonth function is not available to me so i used date functions to create my last month end date.
Also after running the below set of statements i am not getting what i wanted.
Alter FUNCTION [Custom].[fExpandDates](
@fromdate date = '20160731',
@toDate date = '20161130'
) RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP(DATEDIFF(MM, @fromdate, @toDate) ) --Limit the number of rows created
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT @fromdate fromdate,
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@toDate),0)) toDate, --EOmonth not available so used this
n
FROM cteTally t;
GO
--Create sample data
DECLARE @TableA Table(
Acctnum char(3),
StartDate datetime);
INSERT INTO @TableA
VALUES
( 'AAA', '20160731'),
( 'BBB', '20160630');
--Apply solution
SELECT A.Acctnum,
x.fromdate,
x.toDate
FROM @TableA A
CROSS APPLY [Custom].[fExpandDates](StartDate,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) ) x --EOmonth not available so used this
ORDER BY A.Acctnum, x.n;
GO
--output
AcctnumfromdatetoDate
AAA2016-07-312016-10-31 23:59:59.000
AAA2016-07-312016-10-31 23:59:59.000
AAA2016-07-312016-10-31 23:59:59.000
BBB2016-06-302016-10-31 23:59:59.000
BBB2016-06-302016-10-31 23:59:59.000
BBB2016-06-302016-10-31 23:59:59.000
BBB2016-06-302016-10-31 23:59:59.000
Thanks,
Abi
November 29, 2016 at 2:35 pm
TheSQLGuru (11/29/2016)
Numbers Table #FTW!!
For people that are not native English speakers, it would be easier to understand numbers table instead of tally table.
November 29, 2016 at 5:46 pm
Are your two dates ALWAYS both the end of their respective months? Becomes quite trivial if so.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 29, 2016 at 6:03 pm
This is probably overly cumbersome, but I am sick and tired - literally. 😀
Note that if you can guarantee both dates are already end of their respective months, I do think there is a trivial solution.
ALTER FUNCTION dbo.[fExpandDates](
@fromdate date = '20160731',
@toDate date = '20161130'
) RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP(DATEDIFF(MM, @fromdate, @toDate) ) --Limit the number of rows created
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT @fromdate as fromdate,
DATEADD(d, -1, DATEADD(mm, n+1, DATEADD(mm, DATEDIFF(mm,0,@fromdate),0))) AS ToDate, --EOmonth not available so used this
n
FROM cteTally t;
GO
select * from dbo.fExpandDates('20160630', '20161031')
select * from dbo.fExpandDates('20160731', '20161031')
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 30, 2016 at 9:53 am
Thanks Kevin ... this worked for me .. i have a function in place to reformat the start date and enddate to a month end date... So i should be all set.....
November 30, 2016 at 10:12 am
abhisheka.dalal (11/30/2016)
Thanks Kevin ... this worked for me .. i have a function in place to reformat the start date and enddate to a month end date... So i should be all set.....
The error you made in the code you posted gets me thinking. Do you understand how the code works?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply