How to get YTD and MTD in SQL Server

Question

Hello all. I'm trying to get YTD and MTD for two different types of rows (based off a column in the table) from a TimeEntries table. I've tried the other solutions I've found on here, but nothing has worked.

CREATE TABLE [TimeEntries] (
  Id int IDENTITY(1,1) PRIMARY KEY,
  TransactionDate date,
  BillableHours money,
  BillableFlag nvarchar(2)
)

INSERT INTO [TimeEntries] (TransactionDate, BillableHours, BillableFlag)
VALUES ('2023-05-01', 2.00, 'B'),
       ('2023-01-05', 3.00, 'N'),
       ('2023-05-05', 4.00, 'B'),
       ('2023-02-06', 5.00, 'N'),
       ('2023-05-01', 20.00, 'B'),
       ('2023-01-05', 30.00, 'N'),
       ('2023-02-05', 40.00, 'B'),
       ('2023-02-06', 50.00, 'B');

Select 
  MTD = Sum(Case When datediff(month, current_timestamp, a.TransactionDate) = 0 Then a.BillableHours Else 0 End),  
  YTD = Sum(Case When datediff(year, current_timestamp, a.TransactionDate) = 0 Then a.BillableHours Else 0 End)  
From [TimeEntries]  a  
	  where a.BillableFlag = 'b'
Group by a.TransactionDate,  a.BillableHours  


GO 

Select 
  MTD = Sum(Case When datediff(month, current_timestamp, a.TransactionDate) = 0 Then a.BillableHours Else 0 End),  
  YTD = Sum(Case When datediff(year, current_timestamp, a.TransactionDate) = 0 Then a.BillableHours Else 0 End)  
From [TimeEntries]  a  
	  where a.BillableFlag = 'n'
Group by a.TransactionDate,  a.BillableHours  


GO 

DECLARE @billableHoursWorkedMTD money
DECLARE @billableHoursWorkedYTD money
DECLARE @nonbillableHoursWorkedMTD money
DECLARE @nonbillableHoursWorkedYTD money

SET @billableHoursWorkedMTD = (SELECT sum(BillableHours) FROM [TimeEntries] where BillableFlag = 'B'  AND TransactionDate BETWEEN dateadd(month, datediff(month, 0, current_timestamp), 0) AND GETDATE())
SET @billableHoursWorkedYTD = (SELECT sum(BillableHours) FROM [TimeEntries] where BillableFlag = 'B' AND TransactionDate BETWEEN dateadd(year, datediff(year, 0, current_timestamp), 0) AND GETDATE())
SET @nonbillableHoursWorkedMTD = (SELECT sum(BillableHours) FROM [TimeEntries] where BillableFlag = 'N'  AND TransactionDate BETWEEN dateadd(month, datediff(month, 0, current_timestamp), 0) AND GETDATE())
SET @nonbillableHoursWorkedYTD = (SELECT sum(BillableHours) FROM [TimeEntries] where BillableFlag = 'N' AND TransactionDate BETWEEN dateadd(year, datediff(year, 0, current_timestamp), 0) AND GETDATE())

Select @billableHoursWorkedMTD as 'MTD', @billableHoursWorkedYTD as 'YTD', @nonbillableHoursWorkedMTD as 'MTD', @nonbillableHoursWorkedYTD as 'YTD'

This results in a Billable (where billableFlag = 'B') dataset like so:

2.00 2.00 4.00 4.00 20.00 20.00 0.00 40.00 0.00 50.00

and a Non-Billable (where billableFlag = 'N') dataset like so:

0.00 3.00 0.00 5.00 0.00 30.00

I'm wanting a dataset like so:

26.00 116.00 0.00 38.00

where the first row is billableFlag = 'b' and the second row is billableFlag = 'n'.

The variables at the end are there as an example for each cell of data. Any help is appreciated.

Answer 1

Hi @Tye Peel

where the first row is billableFlag = 'b' and the second row is billableFlag = 'n'.

How about using UNION like this:

;WITH CTE AS
(
Select MTD = Sum(Case When datediff(month, current_timestamp, a.TransactionDate) = 0 Then a.BillableHours Else 0 End),  
       YTD = Sum(Case When datediff(year, current_timestamp, a.TransactionDate) = 0 Then a.BillableHours Else 0 End),
       'b' as billableFlag
From [TimeEntries]  a  
Where a.BillableFlag = 'b'
UNION
Select MTD = Sum(Case When datediff(month, current_timestamp, a.TransactionDate) = 0 Then a.BillableHours Else 0 End),  
       YTD = Sum(Case When datediff(year, current_timestamp, a.TransactionDate) = 0 Then a.BillableHours Else 0 End)  ,
	   'n' as billableFlag
From [TimeEntries]  a  
Where a.BillableFlag = 'n'
)
Select MTD,YTD
From CTE
Order by billableFlag

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Just remove the GROUP BY clauses from your queries.