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

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  June 29, 2016 at 7:09 pm

  #1887763

  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

  June 30, 2016 at 2:27 am

  #1887799

  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: 246960

  More actions

  June 30, 2016 at 5:27 am

  #1887824

  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

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  June 30, 2016 at 2:54 pm

  #1887976

  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]