Grouping SSRS report on multiple fields

Question

I have a report we will call ReportOne, in this ReportOne I am querying the data for this report with a stored procedure. The stored procedure query returns two values which are 'TravelDate' and 'Status'.

My report has four fields, 'BeginDate', 'EndDate', 'Status', and 'Days'.

My issue is this, I need to group the report by both the 'Status' and consecutive days. Consecutive days coming from TravelDate.

'BeginDate' will be the first new date

'EndDate' will be the last consecutive date.

'Status' will be status.

'Days' will be the number of consecutive days.

Example,

TravelDate | Status

1/1/2001   | Leave
1/2/2001   | Leave
1/3/2001   | Leave
1/5/2001   | Leave
1/6/2001   | Travel

The report will then look as follows.

BeginDate | EndDate  | Status | Days

1/1/2001  | 1/3/2001 | Leave  | 3

1/5/2001  | 1/5/2001 | Leave  | 1

1/6/2001  | 1/6/2001 | Travel | 1

Answer 1

Example

Declare @YourTable Table ([TravelDate] date,[Status] varchar(50))
Insert Into @YourTable Values 
 ('1/1/2001','Leave')
,('1/2/2001','Leave')
,('1/3/2001','Leave')
,('1/5/2001','Leave')
,('1/6/2001','Travel')


Select BeginDate=min(TravelDate)
      ,EndDate  =max(TravelDate)
      ,Status   =max(Status)
      ,Days     =datediff(DAY,min(TravelDate),max(TravelDate))+1
 From (
        Select * 
              ,Grp = DateDiff(DAY,'1900-01-01',TravelDate) - row_number() over (partition by status order by TravelDate)
         From @YourTable
      ) A
 Group By Grp
 Order By BeginDate

Returns

BeginDate   EndDate     Status  Days
2001-01-01  2001-01-03  Leave   3
2001-01-05  2001-01-05  Leave   1
2001-01-06  2001-01-06  Travel  1

EDIT -- Capture from Stored Procedure -- @YourTable Structure must match the Structure of Stored Procedure

Declare @YourTable Table ([TravelDate] date,[Status] varchar(50))
Insert Into @YourTable 
Exec youStoredProcedure

    Select BeginDate=min(TravelDate)
          ,EndDate  =max(TravelDate)
          ,Status   =max(Status)
          ,Days     =datediff(DAY,min(TravelDate),max(TravelDate))+1
     From (
            Select * 
                  ,Grp = DateDiff(DAY,'1900-01-01',TravelDate) - row_number() over (partition by status order by TravelDate)
             From @YourTable
          ) A
     Group By Grp
     Order By BeginDate

EDIT - Nested Subquery

Select BeginDate=min(TravelDate)
      ,EndDate  =max(TravelDate)
      ,Status   =max(Status)
      ,Days     =datediff(DAY,min(TravelDate),max(TravelDate))+1
 From (
        Select * 
              ,Grp = DateDiff(DAY,'1900-01-01',TravelDate) - row_number() over (partition by status order by TravelDate)
         From (
                -- Your Query Here --- 
              ) A
      ) A
 Group By Grp
 Order By BeginDate

EDIT - Consumed from a TVF

Select BeginDate=min(TravelDate)
      ,EndDate  =max(TravelDate)
      ,Status   =max(Status)
      ,Days     =datediff(DAY,min(TravelDate),max(TravelDate))+1
 From (
        Select * 
              ,Grp = DateDiff(DAY,'1900-01-01',TravelDate) - row_number() over (partition by status order by TravelDate)
         From [dbo].[YourTableValedFunction](Param1,Param2) src
      ) A
 Group By Grp
 Order By BeginDate