Get Quarterly Data For a Year

Question

I need a way to query a SQL Table and pull back data on a quarter by quarter basis. I know that SQL Server has a DatePart() function which has a parameter of q = Quarter and I came up with this syntax, but for a table that holds roughly 50,000 rows this syntax is extremely slow in it. Is this the best way to achieve this result or is there more optimization that can occur?

Declare @startdate date = '20170101', @enddate date = '20171231'
Select
Employeename
,[Total Amount Paid] = SUM(ISNULL(Totalcheckamt,0))
FROM dbo.PaymentHistory
WHERE DatePart(q,[DatePaid]) = 1
AND [DatePaid] BETWEEN CAST(DateAdd(yy, -1, @startdate) As Date) 
                   AND CAST(DateAdd(yy, -1, @enddate) As Date)
GROUP BY Employeename
Order By Employeename ASC

EDIT
My desired returned results is like so

Employee Name -- Q1 ---   Q2 ---   Q3 ---    Q4
James           XXXXX.XX  XXXX.XX  XXXX.XX   XXXX.XX
Roger           XXXXX.XX  XXXX.XX  XXXX.XX   XXXX.XX

EDIT 2
I tried this syntax using a case statement but it gives me an error of the error below. What should I alter in order to be able to succesfully execute this statement?

Msg 130, Level 15, State 1, Line 18
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

SELECT
Employeename
,SUM(
    case 
        when Datepart(Year, [DatePaid]) = '2016' AND DATEPART(Quarter, [DatePaid]) = 1
        Then SUM(ISNULL(TotalCheckamt,0))
    end
) As Q12016 
,SUM(
    case 
        when Datepart(Year, [DatePaid]) = '2017' AND DATEPART(Quarter, [DatePaid]) = 1
        Then SUM(ISNULL(TotalCheckamt,0))
    end
) As Q12017 
,SUM(
    case 
        when Datepart(Year, [DatePaid]) = '2016' AND DATEPART(Quarter, [DatePaid]) = 2
        Then SUM(ISNULL(TotalCheckamt,0))       
        end
) As Q22016 
,SUM(
    case 
        when Datepart(Year, [DatePaid]) = '2017' AND DATEPART(Quarter, [DatePaid]) = 2
        Then SUM(ISNULL(TotalCheckamt,0))       
        end
) As Q22017 
,SUM(
    case 
        when Datepart(Year, [DatePaid]) = '2016' AND DATEPART(Quarter, [DatePaid]) = 3
        Then SUM(ISNULL(TotalCheckamt,0))       
        end
) As Q32016 
,SUM(
    case 
        when Datepart(Year, [DatePaid]) = '2017' AND DATEPART(Quarter, [DatePaid]) = 3
        Then SUM(ISNULL(TotalCheckamt,0))       
        end
) As Q32017 
,SUM(
    case 
        when Datepart(Year, [DatePaid]) = '2016' AND DATEPART(Quarter, [DatePaid]) = 4
        Then SUM(ISNULL(TotalCheckamt,0))       
        end
) As Q42016 
,SUM(
    case 
        when Datepart(Year, [DatePaid]) = '2017' AND DATEPART(Quarter, [DatePaid]) = 4
        Then SUM(ISNULL(TotalCheckamt,0))       
        end
) As Q42017 
FROM dbo.PaymentHistory
    GROUP BY Employeename
    Order By Employeename ASC

Scott Hodgin - Retired · Accepted Answer · 2017-02-19 10:58:58Z

Using your CASE expression example, here's how it could be written:

DECLARE @PaymentHistory TABLE (
    EmployeeName VARCHAR(100)
    ,DatePaid DATE
    ,TotalCheckAmt DECIMAL(11, 2)
    )
insert into @Paymenthistory values ('James','2016-01-01',100.00)
insert into @Paymenthistory values ('James','2016-02-01',100.00)
insert into @Paymenthistory values ('James','2016-03-01',100.00)
insert into @Paymenthistory values ('James','2016-04-01',100.00)
insert into @Paymenthistory values ('James','2016-05-01',100.00)
insert into @Paymenthistory values ('James','2016-06-01',100.00)
insert into @Paymenthistory values ('James','2016-07-01',100.00)

insert into @Paymenthistory values ('Roger','2016-01-01',100.00)
insert into @Paymenthistory values ('Roger','2016-02-01',100.00)
insert into @Paymenthistory values ('Roger','2016-03-01',100.00)
insert into @Paymenthistory values ('Roger','2016-04-01',100.00)
insert into @Paymenthistory values ('Roger','2016-05-01',100.00)
insert into @Paymenthistory values ('Roger','2016-06-01',100.00)
insert into @Paymenthistory values ('Roger','2016-07-01',100.00)
SELECT Employeename
    ,ISNULL(SUM(CASE 
                WHEN Datepart(Year, [DatePaid]) = '2016'
                    AND DATEPART(Quarter, [DatePaid]) = 1
                    THEN TotalCheckamt
                END), 0) AS Q12016
    ,ISNULL(SUM(CASE 
                WHEN Datepart(Year, [DatePaid]) = '2017'
                    AND DATEPART(Quarter, [DatePaid]) = 1
                    THEN TotalCheckamt
                END), 0) AS Q12017
    ,ISNULL(SUM(CASE 
                WHEN Datepart(Year, [DatePaid]) = '2016'
                    AND DATEPART(Quarter, [DatePaid]) = 2
                    THEN TotalCheckamt
                END), 0) AS Q22016
    ,ISNULL(SUM(CASE 
                WHEN Datepart(Year, [DatePaid]) = '2017'
                    AND DATEPART(Quarter, [DatePaid]) = 2
                    THEN TotalCheckamt
                END), 0) AS Q22017
    ,ISNULL(SUM(CASE 
                WHEN Datepart(Year, [DatePaid]) = '2016'
                    AND DATEPART(Quarter, [DatePaid]) = 3
                    THEN TotalCheckamt
                END), 0) AS Q32016
    ,ISNULL(SUM(CASE 
                WHEN Datepart(Year, [DatePaid]) = '2017'
                    AND DATEPART(Quarter, [DatePaid]) = 3
                    THEN TotalCheckamt
                END), 0) AS Q32017
    ,ISNULL(SUM(CASE 
                WHEN Datepart(Year, [DatePaid]) = '2016'
                    AND DATEPART(Quarter, [DatePaid]) = 4
                    THEN TotalCheckamt
                END), 0) AS Q42016
    ,ISNULL(SUM(CASE 
                WHEN Datepart(Year, [DatePaid]) = '2017'
                    AND DATEPART(Quarter, [DatePaid]) = 4
                    THEN TotalCheckamt
                END), 0) AS Q42017
FROM @PaymentHistory
GROUP BY Employeename
ORDER BY Employeename ASC

After you accepted my original answer, I noticed where other posters were advocating the use of PIVOT to achieve the same result as multiple CASE expressions. For completeness of my answer, I decided to try my hand at using PIVOT to provide additional options for you (and learn something myself in the process). I've found the information from Questions About Pivoting Data in SQL Server You Were Too Shy to Ask to be quite useful when trying to understand how to use PIVOT. You'll learn a lot about PIVOT by working through the examples (static and dynamic PIVOT) in the link - I know I did.

To summarize some of the key points of PIVOT (from the link):

You use the PIVOT operator within your query’s FROM clause to rotate and aggregate the values in a dataset. The data is pivoted based on one of the columns in the dataset. Each unique value in that column becomes its own column, which contains aggregated pivoted data.

To better understand how this all works, let’s start with the basic syntax for a query that uses the PIVOT operator:

SELECT column_list
FROM table_expression
  PIVOT
  (
    aggregate_function(aggregate_column)
    FOR pivot_column
    IN( pivot_column_values )
  ) [AS] pivot_table_alias
[ORDER BY column_list];

For the SELECT clause, you can specify an asterisk (*) or the individual columns, and for the FROM clause, you can specify a table or table expression. If you use a table expression, then you must also define a table alias. You can also include an ORDER BY clause, but that’s optional. You’ll get to see these clauses in action as we progress through the questions. For now, let’s focus on the PIVOT clause. You need to understand how this clause works to make sure your pivots work the way you want.

After you specify the PIVOT keyword, you pass in what are essentially three arguments, enclosed in parentheses. The first is the aggregate function and the name of the column to be aggregated. You can use any aggregate function except the COUNT function, when used with an asterisk, as in COUNT(*).

Next, you define the FOR subclause, which specifies the column on which the pivot will be based. It is this column whose distinct values are turned into their own columns. The FOR subclause also includes the IN operator, which is where you specify the pivot column values that will be turned into columns. The values you specify here must exist in the pivot column or they will be ignored.

I'm by no means a PIVOT expert and other posters may have better solutions (hey, I'm willing to learn a better way), but here's my take on it.

I'm including two examples of PIVOT - one static and one dynamic.
(Hopefully, by using my examples and the information in the link provided, you'll understand what I'm doing)

The first example is a static PIVOT and is similar to the CASE expression solution I originally provided (except it uses PIVOT).

Notice that my sample data now includes years 'outside' of the CASE expression. That information won't show up in the static version, but you'll see how we can solve that in the dynamic version later.

Static PIVOT

DECLARE @PaymentHistory TABLE (
    EmployeeName VARCHAR(100)
    ,DatePaid DATE
    ,TotalCheckAmt DECIMAL(11, 2)
    );
insert into @Paymenthistory values ('James','2016-01-01',100.00);
insert into @Paymenthistory values ('James','2016-02-01',100.00);
insert into @Paymenthistory values ('James','2016-03-01',100.00);
insert into @Paymenthistory values ('James','2016-04-01',100.00);
insert into @Paymenthistory values ('James','2016-05-01',100.00);
insert into @Paymenthistory values ('James','2016-06-01',100.00);
insert into @Paymenthistory values ('James','2016-07-01',100.00);
insert into @Paymenthistory values ('James','2017-01-01',100.00);
insert into @Paymenthistory values ('James','2018-10-01',900.00);

insert into @Paymenthistory values ('Roger','2016-01-01',100.00);
insert into @Paymenthistory values ('Roger','2016-02-01',100.00);
insert into @Paymenthistory values ('Roger','2016-03-01',100.00);
insert into @Paymenthistory values ('Roger','2016-04-01',100.00);
insert into @Paymenthistory values ('Roger','2016-05-01',100.00);
insert into @Paymenthistory values ('Roger','2016-06-01',100.00);
insert into @Paymenthistory values ('Roger','2016-07-01',100.00);
insert into @Paymenthistory values ('Roger','2020-10-01',900.00);
;
WITH cte_Paymenthistory
AS (
    SELECT CASE 
            WHEN Datepart(Year, [DatePaid]) = '2016'
                AND DATEPART(Quarter, [DatePaid]) = 1
                THEN 'Q12016'
            WHEN Datepart(Year, [DatePaid]) = '2016'
                AND DATEPART(Quarter, [DatePaid]) = 2
                THEN 'Q22016'
            WHEN Datepart(Year, [DatePaid]) = '2016'
                AND DATEPART(Quarter, [DatePaid]) = 3
                THEN 'Q32016'
            WHEN Datepart(Year, [DatePaid]) = '2016'
                AND DATEPART(Quarter, [DatePaid]) = 4
                THEN 'Q42016'
            WHEN Datepart(Year, [DatePaid]) = '2017'
                AND DATEPART(Quarter, [DatePaid]) = 2
                THEN 'Q12017'
            WHEN Datepart(Year, [DatePaid]) = '2017'
                AND DATEPART(Quarter, [DatePaid]) = 3
                THEN 'Q32017'
            WHEN Datepart(Year, [DatePaid]) = '2017'
                AND DATEPART(Quarter, [DatePaid]) = 4
                THEN 'Q42017'
            END AS ColumnLabel
        ,EmployeeName
        ,TotalCheckAmt
    FROM @PaymentHistory
    )
SELECT EmployeeName
    ,coalesce([Q12016], 0) AS [Q12016]
    ,coalesce([Q12017], 0) AS [Q12076]
    ,coalesce([Q22016], 0) AS [Q22016]
    ,coalesce([Q22017], 0) AS [Q12017]
    ,coalesce([Q32016], 0) AS [Q32016]
    ,coalesce([Q32017], 0) AS [Q32017]
    ,coalesce([Q42016], 0) AS [Q42016]
    ,coalesce([Q42017], 0) AS [Q42017]
FROM cte_Paymenthistory
PIVOT(SUM(TotalCheckAmt) FOR ColumnLabel IN (
            [Q12016]
            ,[Q12017]
            ,[Q22016]
            ,[Q22017]
            ,[Q32016]
            ,[Q32017]
            ,[Q42016]
            ,[Q42017]
            )) AS pvt
ORDER BY employeename;

Did you notice the data outside the CASE expressions didn't show up?

Let's try to solve that by using a dynamic PIVOT.

Dynamic PIVOT

How do I pivot data if I don’t know the values in the pivoted column?

(Due to using dynamic SQL, we have to switch to using a temporary table instead of a table variable)

set nocount on
DECLARE @sql AS NVARCHAR(2000);
DECLARE @col AS NVARCHAR(2000);
DECLARE @colCoalesceNull AS NVARCHAR(2000);

IF OBJECT_ID('tempdb..#PaymentHistory') IS NOT NULL drop Table #PaymentHistory
CREATE TABLE #PaymentHistory (
    EmployeeName VARCHAR(100)
    ,DatePaid DATE
    ,TotalCheckAmt DECIMAL(11, 2)
    );
insert into #Paymenthistory values ('James','2016-01-01',100.00);
insert into #Paymenthistory values ('James','2016-02-01',100.00);
insert into #Paymenthistory values ('James','2016-03-01',100.00);
insert into #Paymenthistory values ('James','2016-04-01',100.00);
insert into #Paymenthistory values ('James','2016-05-01',100.00);
insert into #Paymenthistory values ('James','2016-06-01',100.00);
insert into #Paymenthistory values ('James','2016-07-01',100.00);
insert into #Paymenthistory values ('James','2017-01-01',100.00);
insert into #Paymenthistory values ('James','2018-10-01',900.00);

insert into #Paymenthistory values ('Roger','2016-01-01',100.00);
insert into #Paymenthistory values ('Roger','2016-02-01',100.00);
insert into #Paymenthistory values ('Roger','2016-03-01',100.00);
insert into #Paymenthistory values ('Roger','2016-04-01',100.00);
insert into #Paymenthistory values ('Roger','2016-05-01',100.00);
insert into #Paymenthistory values ('Roger','2016-06-01',100.00);
insert into #Paymenthistory values ('Roger','2016-07-01',100.00);
insert into #Paymenthistory values ('Roger','2020-10-01',900.00);
;
;
SELECT @col = 
        Coalesce(@col + ', ', '') + QUOTENAME(PvtColumnName)
    ,@colCoalesceNull = 
        Coalesce(@colCoalesceNull + ', ', '') + 'coalesce(' + QUOTENAME(PvtColumnName) + ',0) as ' + QUOTENAME(PvtColumnName)
FROM (
    SELECT DISTINCT 
    'Q' + 
    CONVERT(VARCHAR(1), DATEPART(Quarter, [DatePaid])) + 
    CONVERT(VARCHAR(4), year([DatePaid])) 
        AS PvtColumnName
    FROM #PaymentHistory
    ) AS PaymentHistory;

PRINT @col
PRINT @colcoalescenull

SET @sql = N'
with cte_PaymentHistory as
(
select 
''Q'' + CONVERT(varchar(1),DATEPART(Quarter, [DatePaid])) + CONVERT(varchar(4),year([DatePaid])) as PvtColumnName
,EmployeeName
,TotalCheckAmt
from #PaymentHistory
)
SELECT EmployeeName, ' + @colCoalesceNull + 'FROM cte_PaymentHistory
    PIVOT(SUM(TotalCheckAmt)
    FOR PvtColumnName IN (' + @col + ')) AS PivotPaymentHistory';

EXEC sp_executesql @sql;

Your question prompted me to research and learn more about PIVOT - thanks.

Note that in your GROUP BY query you don't really need to use ISNULL inside the aggregate functions. The outer ISNULL in each calculated column is enough to eliminate any potential nulls. — Andriy M, Commented Feb 18, 2017 at 20:59

Grimaldi · Accepted Answer · 2017-02-25 17:33:16Z

0

Probably it would pay to have a date dimension table listing dates and any date part that might be necessary (like quarter, halves and months). This way you could get rid of doing the date math over and over again and just join.

Tricks which this also enables are things like comparing to last years quarter (i.e. add a last_year_quarter column and similar).

answered Feb 25, 2017 at 17:33

Grimaldi

4202 silver badges6 bronze badges

Add a comment |

Paul White · Accepted Answer · 2017-02-25 14:23:38Z

-4

Yes, what you are asking is easy to do. You can create a common table expression that groups by both EmployeeName and DATEPART(qq,DatePaid). Then you can use a PIVOT statement on that common table expression to format the data to your target output.

What you are doing with CASE and SUM is what PIVOT is for. See Using PIVOT and UNPIVOT.

PIVOT can be tricky, the mistake most people make is to use PIVOT without a CTE. While in some cases it can be done, in practice it's nearly always going to lead to trouble. Use a CTE to isolate just the columns and rows you need before applying the PIVOT.

edited Feb 25, 2017 at 14:23

Paul White♦

90.3k30 gold badges423 silver badges663 bronze badges

answered Feb 16, 2017 at 15:35

Matthew Sontum

5351 gold badge5 silver badges13 bronze badges

6

this answer can be improved with a useful code sample that demonstrates the solution to the OP and other future readers
– swasheck
Commented Feb 16, 2017 at 22:25
5

I love PIVOT but the syntax is tricky. The additional difficulty in scenarios like the OP's is to make it work when the aggregation depends on more than one column. It's not as if your suggestion without an example has no value at all – it at least serves as a pointer for the reader. But in this specific case just a pointer is poor help – simply because, as I've just said, the PIVOT syntax is difficult to get right.
– Andriy M
Commented Feb 16, 2017 at 23:03

Add a comment |

Stack Exchange Network

Get Quarterly Data For a Year

3 Answers 3

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server-2008
sql-server-2008-r2
t-sql
or ask your own question.

Linked

Hot Network Questions

Get Quarterly Data For a Year

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-server-2008sql-server-2008-r2t-sql or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server-2008
sql-server-2008-r2
t-sql
or ask your own question.