CODEWITHSUNDEEP
sqlsql-server
Vadzim Savenok
Vadzim Savenok

Reputation: 940

SQL Count data for different date ranges in separate columns

Let's say I have raw table data in the following format:

UserID | DateWorked | HoursWorked | AmountPaid
----------------------------------------------
1      | 11/01/17   |     5.00    |    20.00
1      | 11/02/17   |     3.00    |    5.00
2      | 11/15/17   |     10.00   |    50.00

In my current state, I have an SP (RawdataSP) that returns the raw data with the structure above by inputting date range (@BeginDate and @EndDate).

I wrote another SP, where I declare 2 table variables to store the results of 2 executions of RawdataSP like following:

declare @tablemonth table(...)
declare @tableytd table(...)
insert into @tablemonth execure RawDataSP @BeginDate = @BeginDate, @EndDate = @EndDate
insert into @tableytd execure RawDataSP @BeginDate = @YTDBeginDate, @EndDate = @YTDEndDate

It works fine as is, but the result is 2 separate tables.

I am trying to display counts for different date ranges in separate columns of a single table: 1 for month range (between @BeginDate and @EndDate) and one for YTD range (between Jan 1 and @BeginDate - 1day)

The final result I'm going for is like following:

1) Let's assume I run SP for November 2017

2) Resulting table will be like following format

columns... | TotalHoursMonth | TotalPaidMonth  | TotalHoursYTD  | TotalPaidYTD
           ---------------------------------------------------------------------
           |hours counts     | Amount paid     |hours counts    | Amount paid      |
           |between 11/01/17 | between 11/01/17|between 01/01/17| between 01/01/17 |
           |and 11/30/17     | and 11/30/17    |and 10/30/17    | and 10/30/17     |

I am struggling with the last part. Can I execute SP once with range between @YTDBeginDate and @EndDate (in order to obtain full date range of data) and then split it into separate columns like above?

Upvotes: 0

Views: 192

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can use conditional aggregation:

select UserID,
       sum(case when dateworked >= '2017-11-01' and dateworked < '2017-12-01' then hoursworked else 0
           end) as month_hours,
       sum(case when dateworked >= '2017-11-01' and dateworked < '2017-12-01' then AmountPaid else 0
           end) as month_amount,
       sum(case when dateworked >= '2017-01-01' and dateworked < '2018-01-01' then hoursworked else 0
           end) as ytd_hours,
       sum(case when dateworked >= '2017-01-01' and dateworked < '2018-01-01' then AmountPaid else 0
           end) as ytd_amount
from t
group by UserID;

Using variables, this would look like:

select UserID,
       sum(case when dateworked >= @BeginDate and dateworked < @EndDate then hoursworked else 0
           end) as month_hours,
       sum(case when dateworked >= @BeginDate and dateworked < @EndDate then AmountPaid else 0
           end) as month_amount,
       sum(case when year(dateworked) = year(@BeginDate) and dateworked < @EndDate then hoursworked else 0
           end) as ytd_hours,
       sum(case when year(dateworked) = year(@BeginDate) and dateworked < @EndDate then AmountPaid else 0
           end) as ytd_amount
from t
group by UserID;

If you want this overall, remove UserId and the GROUP BY.

Upvotes: 3

Related Questions