How to get result from dynamic grouping

Question

Based on two inputs - date (any date from calendar) and period of date (like Year,month or week) result should aggregate data:

Based on the inputs the data in @calenderDate and @period result should be aggregated, for example; when @period is given as YEAR then it should aggregate results based on the year alone,

when @period is given as MONTH then it should aggregate results based on that year and all the month in given year,

when @period is given as WEEK then it should aggregate results based on that year and the specific month and all the days (between first day and last day of the given week) in that specific week

I have used dynamic grouping using CASE statement and managed to get desired result for the @period = YEAR, but not for other two inputs i.e. @period = MONTH and WEEK

Here is the sample DDL;

CREATE TABLE [dbo].[transferTable](
    [ID] [bigint] IDENTITY(1000,1) NOT NULL,
    [transferDateTime] [datetime] NOT NULL,
    [transferAmount] [money] NOT NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[transferTable] ON 

GO
INSERT [dbo].[transferTable] ([ID], [transferDateTime], [transferAmount]) VALUES (1000, CAST(0x0000AA2C0110897B AS DateTime), 10.0000)
GO
INSERT [dbo].[transferTable] ([ID], [transferDateTime], [transferAmount]) VALUES (1001, CAST(0x0000AA2D00F0AA50 AS DateTime), 151.0000)
GO
INSERT [dbo].[transferTable] ([ID], [transferDateTime], [transferAmount]) VALUES (1002, CAST(0x0000A8850110897B AS DateTime), 10.0000)
GO
INSERT [dbo].[transferTable] ([ID], [transferDateTime], [transferAmount]) VALUES (1003, CAST(0x0000AA0E0121043B AS DateTime), 151.0000)
GO
SET IDENTITY_INSERT [dbo].[transferTable] OFF
GO

here is my code :

DECLARE @calenderDate DATETIME2(0) = '2019-04-12'
DECLARE @period varchar(20) = 'MONTH'

DECLARE @year varchar(10) =  DATEPART(YEAR,@calenderDate) 
DECLARE @month varchar(10) =  DATEPART(MONTH,@calenderDate)
DECLARE @week varchar(10) =  DATEPART(WEEK,@calenderDate) 

select case when @period = 'YEAR' then DATEPART(YEAR,transferDateTime)
            when @period = 'MONTH' then DATEPART(MONTH,transferDateTime)
            when @period = 'WEEK' then DATEPART(WEEKDAY,transferDateTime)
 end as period, 
     COUNT (t.transferAmount) as volOfTxns,
     SUM (t.transferAmount) as ValueOfTxns
from transferTable t
where 
     ( 
      (DATEPART(YEAR,t.transferDateTime) = @year) OR 
      (DATEPART(YEAR,t.transferDateTime) = @year AND DATEPART(MONTH,t.transferDateTime) = @month)
      )

group by case when @period = 'YEAR' then DATEPART(YEAR,transferDateTime)
              when @period = 'MONTH' then DATEPART(MONTH,transferDateTime)
              when @period = 'WEEK' then DATEPART(WEEKDAY,transferDateTime)
          end

When @calenderDate = ‘2019-04-12’ and @period = ‘YEAR’ then it should show,

period  volOfTxns   ValueOfTxns
2019    3   302.00

When @calenderDate = ‘2019-04-12’ and @period = ‘MONTH then it should show,

period  volOfTxns   ValueOfTxns
4   2   161.00

Likewise when @calenderDate = ‘2019-03-12’ and @period = ‘MONTH then it should show,

period  volOfTxns   ValueOfTxns
3   1   151.00

When @calenderDate = ‘2019-04-12’ and @period = ‘WEEK then it should show,

period  volOfTxns   ValueOfTxns
11           1             10
12           1             151

Answer 1

It would be a case of modifying your where clause to only include the rows that match the period in which you are looking at. Your previous code was only filtering correctly to the year in which you were looking at.

DECLARE @calenderDate DATETIME2(0) = '2019-04-12'
DECLARE @period varchar(20) = 'MONTH'

DECLARE @year varchar(10) =  DATEPART(YEAR,@calenderDate) 
DECLARE @month varchar(10) =  DATEPART(MONTH,@calenderDate)
DECLARE @week varchar(10) =  DATEPART(WEEK,@calenderDate) 

select case when @period = 'YEAR' then DATEPART(YEAR,transferDateTime)
            when @period = 'MONTH' then DATEPART(MONTH,transferDateTime)
            when @period = 'WEEK' then DATEPART(WEEKDAY,transferDateTime)
 end as period, 
     COUNT (t.transferAmount) as volOfTxns,
     SUM (t.transferAmount) as ValueOfTxns
from transferTable t
where 

      CASE 
            WHEN @period = 'WEEK' THEN 
                CASE 
                    WHEN DATEPART(YEAR, t.transferDateTime) = @year 
                        AND DATEPART(MONTH, t.TransferDateTime) = @month
                        AND DATEPART(WEEK, t.transferDateTime) = @week
                    THEN 1
                    ELSE 0 
                END
            WHEN @period = 'MONTH' THEN
                CASE 
                    WHEN DATEPART(YEAR, t.transferDateTime) = @year 
                        AND DATEPART(MONTH, t.TransferDateTime) = @month
                    THEN 1
                    ELSE 0 
                END
            WHEN @period = 'YEAR' THEN
                CASE 
                    WHEN DATEPART(YEAR, t.transferDateTime) = @year 
                    THEN 1
                    ELSE 0 
                END
            ELSE 0
        END = 1

group by case when @period = 'YEAR' then DATEPART(YEAR,transferDateTime)
              when @period = 'MONTH' then DATEPART(MONTH,transferDateTime)
              when @period = 'WEEK' then DATEPART(WEEKDAY,transferDateTime)
          end

The above is an example and there are many shorter ways of doing this, however I've done it like the above to show the logic a bit better.

Answer 2

change your WHERE clause to below

where   (
        (@period = 'YEAR'   AND DATEPART(YEAR,t.transferDateTime) = @year)
    OR  (@period = 'MONTH'  AND DATEPART(MONTH,t.transferDateTime) = @month)
    OR  (@period = 'WEEK'   AND DATEPART(WEEK,t.transferDateTime) = @week)
    )

On the WEEK, I am not sure you wanted WEEK or WEEKDAY, as you reference both in your query. And not sure why there are 2 rows in your expected result for WEEK