How to write a Query to get related result

• suresh0534

  SSCommitted

  Points: 1966

  More actions

  February 7, 2014 at 4:29 pm

  #1687277

  One More Small Issue can you help...

  create table #table (id int, cid int , startdate datetime)

  insert #table

  select 1, 100, '02/23/2014'

  union select 1, 100, '06/25/2013'

  union select 1, 100, '06/04/2013'

  union select 1, 100, '06/17/2013'

  union select 2, 200, '08/9/2013'

  union select 2, 200, '08/3/2013'

  union select 4, 600, '06/11/2013'

  union select 4, 600, '06/18/2013'

  union select 4, 600, '06/17/2013'

  union select 4, 600, '09/17/2013'

  union select 4, 600, '01/20/2014'

  union select 4, 600, '01/24/2014'

  SELECT id, cid, StartDate

  ,EndDate=CASE

  WHEN DATEPART(year, StartDate) = DATEPART(year, GETDATE()) THEN NULL

  WHEN DATEPART(year, EndDate) = DATEPART(year, StartDate) AND EndDate <> StartDate THEN EndDate

  ELSE DATEADD(year, 1, DATEADD(year, DATEDIFF(year, 0, StartDate), 0))-1

  END

  FROM #table a

  OUTER APPLY

  (

  SELECT TOP 1 StartDate-1

  FROM #table b

  WHERE a.id = b.id AND a.cid = b.cid AND a.startdate < b.startdate

  ORDER BY b.startdate

  ) b(EndDate)

  ORDER BY id, cid, StartDate;

  idcidStartDateEndDate

  11002013-06-04 00:00:00.0002013-06-16 00:00:00.000

  11002013-06-17 00:00:00.0002013-06-24 00:00:00.000

  11002013-06-25 00:00:00.0002013-12-31 00:00:00.000

  11002014-02-23 00:00:00.000NULL

  22002013-08-03 00:00:00.0002013-08-08 00:00:00.000

  22002013-08-09 00:00:00.0002013-12-31 00:00:00.000

  46002013-06-11 00:00:00.0002013-06-16 00:00:00.000

  46002013-06-17 00:00:00.0002013-12-31 00:00:00.000 --This End Date should Come 2013-06-17 But it showing 2013 12-31

  46002013-06-18 00:00:00.0002013-09-16 00:00:00.000

  46002013-09-17 00:00:00.0002013-12-31 00:00:00.000

  46002014-01-20 00:00:00.000NULL

  46002014-01-24 00:00:00.000NULL

  Remaining Data Every Thing Perfect...only that one has Issue...

  Thanks,

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 7, 2014 at 4:38 pm

  #1687278

  That end date was one of the questions around the requirements that we already asked about.

  How do you know that the end date should be 12/31 or 6/17 when there is no future start date in that classification (cid 100)?

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events