Query

Question

Post reply

Query

cooljagadeesh

SSC Eights!

Points: 959
More actions
June 29, 2016 at 4:02 pm

#323659

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

Viewing 5 posts - 1 through 4 (of 4 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

Nice solution, Chris.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

Phil Parkin (6/30/2016)
Nice solution, Chris.

Thanks Phil :blush: I learned it from you 🙂

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]