Solved

SQL Server pivot on DateAdded

Posted on 2016-07-26
2
33 Views
Last Modified: 2016-07-26
I have a table with 3 columns

Select CustID,  IndividualID,   DateAdded from mytable where YEAR(DateAdded) = YEAR(Getdate())

I prefer to do this with a pivit query if possible?

CustID  IndividualID   DateAdded

I need a count of individuals by month by for each CustIDl
There are 1.5 Million records and growing (25k per year) in the table


Example
CustID   Jan   Feb   etc...
1             10     11   ....
0
Comment
Question by:lrbrister
2 Comments
 
LVL 25

Accepted Solution

by:
Shaun Kline earned 500 total points
Comment Utility
Here is a sample query:
DECLARE @Test TABLE
(
    CustID INT,
    IndividualID INT,
    DateAdded DATETIME
)

INSERT INTO @Test ( CustID, IndividualID, DateAdded )
VALUES ( 1, 1, '2016-01-01'),
    ( 1, 2, '2016-01-15'),
    ( 1, 3, '2016-02-01'),
    ( 1, 4, '2016-02-15'),
    ( 1, 5, '2016-02-28'),
    ( 1, 6, '2016-03-04'),
    ( 1, 7, '2016-03-12'),
    ( 1, 8, '2016-03-16'),
    ( 1, 9, '2016-03-31'),
    ( 1, 10, '2016-04-04'),
    ( 1, 21, '2015-12-25'),
    ( 1, 22, '2015-11-30'),
    ( 2, 11, '2016-01-03'),
    ( 2, 12, '2016-02-02'),
    ( 2, 13, '2016-02-05'),
    ( 2, 14, '2016-02-15'),
    ( 2, 15, '2016-02-21'),
    ( 2, 16, '2016-02-24'),
    ( 2, 17, '2016-03-12'),
    ( 2, 18, '2016-03-16'),
    ( 2, 19, '2016-03-31'),
    ( 2, 20, '2016-04-04'),
    ( 2, 23, '2014-10-31'),
    ( 2, 24, '2013-09-09')

SELECT CustID, [1] Jan, [2] Feb, [3] Mar, [4] Apr
FROM (
    SELECT CustID, IndividualID, MONTH(DateAdded) MonthAdded
    FROM @Test
    WHERE YEAR(DateAdded) = YEAR(GETDATE())
    ) Data
PIVOT (COUNT(IndividualID) FOR MonthAdded IN ([1], [2], [3], [4])) Pvt

Open in new window


It is only putting out the first four months, but you should be able to extend it to your needs.

One concern is the performance of PIVOT on the volume of data in your table. You may be better off using this PIVOT type query:
SELECT CustID,
    SUM(CASE WHEN MONTH(DateAdded) = 1 THEN 1 ELSE 0 END) Jan,
    SUM(CASE WHEN MONTH(DateAdded) = 2 THEN 1 ELSE 0 END) Feb,
    SUM(CASE WHEN MONTH(DateAdded) = 3 THEN 1 ELSE 0 END) Mar,
    SUM(CASE WHEN MONTH(DateAdded) = 4 THEN 1 ELSE 0 END) Apr
FROM @Test
WHERE YEAR(DateAdded) = YEAR(GETDATE())
GROUP BY CustID

Open in new window

0
 

Author Closing Comment

by:lrbrister
Comment Utility
Because I am filtering on a Type code thie Pivot works very well.
There will never be an all "everything" select on this.

Here is my final code... thanks

;
WITH    cte
          AS ( SELECT   OriginID ,
                        [1] Jan ,
                        [2] Feb ,
                        [3] Mar ,
                        [4] Apr ,
                        [5] May ,
                        [6] Jun ,
                        [7] Jul ,
                        [8] Aug ,
                        [9] Sept ,
                        [10] Oct ,
                        [11] Nov ,
                        [12] Dec
               FROM     ( SELECT    o.OriginID ,
                                    i.IndividualID ,
                                    MONTH(i.DateAdded) MonthAdded
                          FROM      Individuals i
                                    JOIN dbo.IndividualOrigins o ON o.IndividualID = i.IndividualID
                                    JOIN Origins og ON og.OriginID = o.OriginID
                          WHERE     YEAR(i.DateAdded) = 2016
                                    AND i.DateAdded < GETDATE() + 1
                                    AND o.OriginID IN ( 7867, 8907, 10100,
                                                        10130, 10132, 10146,
                                                        10151, 10157, 12271 )
                        ) Data PIVOT ( COUNT(IndividualID) FOR MonthAdded IN ( [1],
                                                              [2], [3], [4],
                                                              [5], [6], [7],
                                                              [8], [9], [10],
                                                              [11], [12] ) ) Pvt
             )
    SELECT  o.OriginName ,c.*
    FROM    cte c
            JOIN Origins o ON o.OriginID = c.OriginID
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now