Date Query help

• Nagaram

  SSCrazy

  Points: 2110

  More actions

  June 16, 2011 at 3:51 am

  #252021

  Hi,

  Here is my scenario

  create table #temp

  (

  ALLOTMENT_ROOM_ID int

  ,ROOM_ID int

  ,ROOM_DATE datetime

  ,LIST_ROOM_STATUS int

  )

  insert into #temp

  select 74,1,'2011-06-16 00:00:00.000',1 union all

  select 74,1,'2011-06-17 00:00:00.000',2 union all

  select 74,1,'2011-06-18 00:00:00.000',1 union all

  select 74,1,'2011-06-19 00:00:00.000',1 union all

  select 74,1,'2011-06-20 00:00:00.000',1 union all

  select 74,2,'2011-06-16 00:00:00.000',2 union all

  select 74,2,'2011-06-17 00:00:00.000',1 union all

  select 74,2,'2011-06-18 00:00:00.000',1 union all

  select 74,2,'2011-06-19 00:00:00.000',2 union all

  select 74,2,'2011-06-20 00:00:00.000',1 union all

  select 74,2,'2011-06-21 00:00:00.000',1 union all

  select 74,2,'2011-06-22 00:00:00.000',2

  Expected Output:

  ALLOTMENT_ROOM_IDROOM_ID ROOM_DATE

  74116-05-2011,18-06-2011 to 20-06-2011

  74217-06-2011 to 18-06-2011,20-06-2011 to 21-06-2011

  here LIST_ROOM_STATUS=2 means room_date is deleted

  I need to get room_dates in a single field group by ALLOTMENT_ROOM_ID,ROOM_ID

  Assume for 74 ALLOTMENT_ROOM_ID and ROOM_ID=1 if all LIST_ROOM_STATUS=1 then we can consider mindate to maxdate

  If LIST_ROOM_STATUS=2 then we exclude that date

  Thanks,

• Mark Cowne

  One Orange Chip

  Points: 26983

  More actions

  June 16, 2011 at 4:18 am

  #1339538

  WITH Grped AS (

  SELECT ALLOTMENT_ROOM_ID,

  ROOM_ID,

  ROOM_DATE,

  LIST_ROOM_STATUS,

  ROW_NUMBER() OVER(PARTITION BY ALLOTMENT_ROOM_ID,ROOM_ID ORDER BY ROOM_DATE) -

  ROW_NUMBER() OVER(PARTITION BY ALLOTMENT_ROOM_ID,ROOM_ID,LIST_ROOM_STATUS ORDER BY ROOM_DATE) AS rnDiff

  FROM #temp),

  Results AS (

  SELECT ALLOTMENT_ROOM_ID,

  ROOM_ID,

  MIN(ROOM_DATE) AS ROOM_DATE_START,

  MAX(ROOM_DATE) AS ROOM_DATE_END

  FROM Grped

  WHERE LIST_ROOM_STATUS=1

  GROUP BY ALLOTMENT_ROOM_ID,ROOM_ID,rnDiff)

  SELECT r1.ALLOTMENT_ROOM_ID,

  r1.ROOM_ID,

  STUFF((SELECT ',' + CONVERT(VARCHAR(10),r2.ROOM_DATE_START,105) +

  CASE WHEN r2.ROOM_DATE_END>r2.ROOM_DATE_START

  THEN ' to ' + CONVERT(VARCHAR(10),r2.ROOM_DATE_END,105)

  ELSE ''

  END AS "text()"

  FROM Results r2

  WHERE r2.ALLOTMENT_ROOM_ID=r1.ALLOTMENT_ROOM_ID

  AND r2.ROOM_ID=r1.ROOM_ID

  ORDER BY r2.ROOM_DATE_START

  FOR XML PATH('')

  ),1,1,'') AS ROOM_DATE

  FROM Results r1

  GROUP BY r1.ALLOTMENT_ROOM_ID,r1.ROOM_ID

  ORDER BY r1.ALLOTMENT_ROOM_ID,r1.ROOM_ID;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Nagaram

  SSCrazy

  Points: 2110

  More actions

  June 16, 2011 at 4:23 am

  #1339543

  Thanks you So much Mark,

  It really Excelent