Month wise report

• elsi

  Newbie

  Points: 5

  More actions

  June 16, 2010 at 12:05 am

  #387166

  Hi,

  I have table with Event and 4 phases for each event with begin and end dates.

  always the phase A ends one day prior to phase B begin, phase C, D also in same way.

  so the phase sequence is A->B->c->D.

  EventIdPhaseIdBeginDateEndDate

  1A1/14/10 12:00 AM3/4/10 12:00 AM

  1B3/5/10 12:00 AM5/28/10 12:00 AM

  1C5/29/10 12:00 AM9/3/10 12:00 AM

  1D9/4/10 12:00 AM12/31/10 12:00 AM

  2A2/10/10 12:00 AM6/16/10 12:00 AM

  2B6/17/10 12:00 AM7/22/10 12:00 AM

  2c7/23/10 12:00 AM11/17/10 12:00 AM

  2D11/18/10 12:00 AM12/31/10 12:00 AM

  3A3/11/10 12:00 AM6/13/10 12:00 AM

  3B6/14/10 12:00 AM8/12/10 12:00 AM

  3C8/13/10 12:00 AM10/27/10 12:00 AM

  3D10/28/10 12:00 AM12/31/10 12:00 AM

  4A1/1/10 12:00 AM3/10/10 12:00 AM

  4B3/11/10 12:00 AM5/19/10 12:00 AM

  4C5/20/10 12:00 AM9/12/10 12:00 AM

  4D9/13/10 12:00 AM12/31/10 12:00 AM

  And i want report as

  EventIDJanFebMarAprMayJunJulAugSepOctNovDec

  1AABBBCCCDDDD

  2AAAAAABCCCCD

  3--AAABBCCCDD

  4AABBBCCCDDDD

  Scenario for report:

  [1]. say if Phase C begin on May22^nd May month should show B, as that event is in phase B for most days of the month.

  is this possible in sql query? please help me in this report, i got stucked to get this report for much time, thanks...

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  June 16, 2010 at 3:26 am

  #1180836

  The reason you've had no replies is because you've not given us much incentive to work on it. Post your data like this: -

  DECLARE @table AS TABLE(

  eventid INT

  ,phaseid CHAR(1)

  ,begindate DATETIME

  ,enddate DATETIME)

  INSERT INTO @table

  (EventId

  ,PhaseId

  ,BeginDate

  ,EndDate)

  SELECT '1', 'A', '1/14/10 12:00 AM', '3/4/10 12:00 AM'

  UNION ALL SELECT '1', 'B', '3/5/10 12:00 AM', '5/28/10 12:00 AM'

  UNION ALL SELECT '1', 'C', '5/29/10 12:00 AM', '9/3/10 12:00 AM'

  UNION ALL SELECT '1', 'D', '9/4/10 12:00 AM', '12/31/10 12:00 AM'

  UNION ALL SELECT '2', 'A', '2/10/10 12:00 AM', '6/16/10 12:00 AM'

  UNION ALL SELECT '2', 'B', '6/17/10 12:00 AM', '7/22/10 12:00 AM'

  UNION ALL SELECT '2', 'c', '7/23/10 12:00 AM', '11/17/10 12:00 AM'

  UNION ALL SELECT '2', 'D', '11/18/10 12:00 AM', '12/31/10 12:00 AM'

  UNION ALL SELECT '3', 'A', '3/11/10 12:00 AM', '6/13/10 12:00 AM'

  UNION ALL SELECT '3', 'B', '6/14/10 12:00 AM', '8/12/10 12:00 AM'

  UNION ALL SELECT '3', 'C', '8/13/10 12:00 AM', '10/27/10 12:00 AM'

  UNION ALL SELECT '3', 'D', '10/28/10 12:00 AM', '12/31/10 12:00 AM'

  UNION ALL SELECT '4', 'A', '1/1/10 12:00 AM', '3/10/10 12:00 AM'

  UNION ALL SELECT '4', 'B', '3/11/10 12:00 AM', '5/19/10 12:00 AM'

  UNION ALL SELECT '4', 'C', '5/20/10 12:00 AM', '9/12/10 12:00 AM'

  UNION ALL SELECT '4', 'D', '9/13/10 12:00 AM', '12/31/10 12:00 AM'

  That way people are more inclined to look. As for your problem, I'll look into it later on.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---