June 29, 2016 at 4:02 pm
HI Everyone,
can any one write one dynamic query based on fromdate and Todate columns data should come as mentioned below
declare @tbl table (id int,Fromdate datetime,Todate datetime)
insert into @tbl select 1,'2015-APR-02','2016-FEB-28'
select * from @tbl
output needed as mentioned below
IDFromdate Todate
12015-APR-022015-DEC-31
12016-JAN-01 2016-FEB-28
insert into @tbl select 1,'2015-APR-02','2017-Feb-28'
select * from @tbl
output needed as mentioned below
IDFromdate Todate
12015-APR-022015-DEC-31
12016-JAN-01 2016-DEC-31
12017-JAN-01 2017-FEB-28
June 29, 2016 at 7:09 pm
I am sorry, but I can't figure out what you want here...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 30, 2016 at 2:27 am
WITH _Tally AS (
SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d1 (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d2 (n)
)
SELECT
t.ID,
x.FromDate,
x.ToDate
FROM @tbl t
CROSS APPLY (SELECT YearBoundariesCrossed = DATEDIFF(YEAR,t.Fromdate,t.Todate)) w
CROSS APPLY (
SELECT TOP(1+YearBoundariesCrossed)
FromDate = CASE WHEN n = 1 THEN t.FromDate ELSE DATEADD(YEAR,DATEDIFF(YEAR,0,t.FromDate)+(n-1),0) END,
ToDate = CASE WHEN n = 1+YearBoundariesCrossed THEN t.ToDate ELSE DATEADD(YEAR,DATEDIFF(YEAR,0,t.FromDate)+(n),-1) END --
FROM _Tally
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 30, 2016 at 5:27 am
Nice solution, Chris.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 30, 2016 at 2:54 pm
Phil Parkin (6/30/2016)
Nice solution, Chris.
Thanks Phil :blush: I learned it from you ๐
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply