Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Pivot Table

Posted on 2014-08-12
11
Medium Priority
?
413 Views
Last Modified: 2014-08-18
I have to do a report based on Customer Count for the last 5 years. Output will look like a Pivot Table format.

I have to pick Distinct Customers based on their DOB, assuming that two person can share same name  but on different DOB.

Total Distinct Customer Count, Total Distinct Customer count based on their Account Type and Total missing Customer count from Previous year.
Sample-DB.xlsx
0
Comment
Question by:chokka
  • 8
  • 3
11 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40257620
You have both sql 2005 and 2008 listed - that is confusing.
Please inform us exactly what version you are actually using.
(I would particularly like to know if it is SQL Server 2012 or later)
0
 

Author Comment

by:chokka
ID: 40257627
I have SQL 2008 and SQL 2012. I am writing the SQL Query on SQL 2012.

Sorry for the confusion about the version.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40257651
Probably doesn't matter on version, but initially thought I might want to use LAG(), but turns out that isn't needed. As a general rule knowing your version is very helpful though. Use a tag or note it in the question please.

From your sample I get this:
| C_YR | WALKIN | MAILORDER | OTHER | TOTAL | MISSING |
|------|--------|-----------|-------|-------|---------|
| 2009 |      2 |         1 |     1 |     4 |       4 |
| 2010 |      0 |         5 |     1 |     6 |       1 |
| 2011 |      1 |         3 |     0 |     4 |       3 |
| 2012 |      1 |         3 |     0 |     4 |       4 |

Open in new window

produced by this:
;WITH CTE
      AS (
                  SELECT DISTINCT
                        FirstName
                      , LastName
                      , DOB
                      , YEAR(DateFilled) - 1 AS yr
                  FROM yourtable
            )
SELECT
      YEAR(T.DateFilled)                                AS c_yr
    , COUNT(CASE
            WHEN AccountType = 'WalkIn' THEN 1 END)     AS WalkIn
    , COUNT(CASE
            WHEN AccountType = 'Mail Order' THEN 1 END) AS MailOrder
    , COUNT(CASE
            WHEN AccountType IS NULL THEN 1 END)        AS Other
    , COUNT(*)                                          AS Total
    , COUNT(CASE
            WHEN CTE.yr IS NULL THEN 1 END)             AS missing
FROM yourtable AS T
      LEFT JOIN CTE
                  ON T.FirstName = CTE.FirstName
                        AND T.LastName = CTE.LastName
                        AND T.DOB = CTE.DOB
                        AND YEAR(T.dateFilled) = CTE.yr
GROUP BY
      YEAR(T.DateFilled)

http://sqlfiddle.com/#!6/2d522/1

Open in new window

{+ edit}
Using a CTE isn't essential I just started the query that way so it stuck. You could just replace FROM CTE with FROM ( query of cte here ) instead
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Accepted Solution

by:
chokka earned 0 total points
ID: 40258403
Thank you @Porlet Paul. Amazing. Thank you for your efforts,

Just a correction on the Missing Value Part.

In the year 2009, i have mentioned two walk-in customers with same name but on different date of birth.

On 2010, i am getting the output as one missing. actually two customers are missing.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40259618
matching IS by firstname, lastname AND dob
I used a minus instead of a plus in the CTE
I have made that correction in what you see below.

There are 18 rows of data, so my results add to 18, your spreadsheet only added to 13, so the results will differ. I have no idea which 13 you used.
First Name	Last Name	DOB	Date Filled	Account Type
Alpha	Bette	1980-10-02	2009-02-01	Walk-In
Alpha	Bette	1984-11-03	2009-04-04	Walk-In
Benny	Thomas	1982-04-22	2009-05-01	NULL
Bob	Williams	1975-11-02	2012-03-01	Mail Order
Bob	Williams	1975-11-02	2012-04-01	Mail Order
Bob	Williams	1975-11-02	2012-05-01	Mail Order
Chrysler	Jeep	1970-10-02	2010-02-01	Mail Order
Chrysler	Jeep	1970-10-02	2010-03-01	Mail Order
Chrysler	Jeep	1970-10-02	2011-02-01	Mail Order
Dodge	Ram	1976-11-02	2009-02-01	Mail Order
Dodge	Ram	1995-10-02	2010-02-01	Mail Order
Dodge	Ram	1995-10-02	2011-02-01	Mail Order
Honda	Fit	1980-10-02	2011-02-01	Walk-In
Honda	Fit	1980-10-02	2012-02-01	Walk-In
Ric	Elvarez	1979-03-21	2010-06-01	NULL
Toyota	Corolla	1990-10-02	2010-02-01	Mail Order
Toyota	Corolla	1990-10-02	2010-04-01	Mail Order
Toyota	Corolla	1990-10-02	2011-02-01	Mail Order

Open in new window

compare.pngHere are the details of the matching:
| FIRSTNAME | LASTNAME |        DOB | CURR_YR | PREV_YR | WALKIN | MAILORDER |  OTHER | MISSING |
|-----------|----------|------------|---------|---------|--------|-----------|--------|---------|
|     Alpha |    Bette | 1980-10-02 |    2009 |  (null) |      1 |    (null) | (null) |       1 |
|     Alpha |    Bette | 1984-11-03 |    2009 |  (null) |      1 |    (null) | (null) |       1 |
|     Benny |   Thomas | 1982-04-22 |    2009 |  (null) | (null) |    (null) |      1 |       1 |
|       Bob | Williams | 1975-11-02 |    2012 |  (null) | (null) |         1 | (null) |       1 |
|       Bob | Williams | 1975-11-02 |    2012 |  (null) | (null) |         1 | (null) |       1 |
|       Bob | Williams | 1975-11-02 |    2012 |  (null) | (null) |         1 | (null) |       1 |
|  Chrysler |     Jeep | 1970-10-02 |    2010 |  (null) | (null) |         1 | (null) |       1 |
|  Chrysler |     Jeep | 1970-10-02 |    2010 |  (null) | (null) |         1 | (null) |       1 |
|  Chrysler |     Jeep | 1970-10-02 |    2011 |    2011 | (null) |         1 | (null) |  (null) |
|     Dodge |      Ram | 1976-11-02 |    2009 |  (null) | (null) |         1 | (null) |       1 |
|     Dodge |      Ram | 1995-10-02 |    2010 |  (null) | (null) |         1 | (null) |       1 |
|     Dodge |      Ram | 1995-10-02 |    2011 |    2011 | (null) |         1 | (null) |  (null) |
|     Honda |      Fit | 1980-10-02 |    2011 |  (null) |      1 |    (null) | (null) |       1 |
|     Honda |      Fit | 1980-10-02 |    2012 |    2012 |      1 |    (null) | (null) |  (null) |
|       Ric |  Elvarez | 1979-03-21 |    2010 |  (null) | (null) |    (null) |      1 |       1 |
|    Toyota |  Corolla | 1990-10-02 |    2010 |  (null) | (null) |         1 | (null) |       1 |
|    Toyota |  Corolla | 1990-10-02 |    2010 |  (null) | (null) |         1 | (null) |       1 |
|    Toyota |  Corolla | 1990-10-02 |    2011 |    2011 | (null) |         1 | (null) |  (null) |

Open in new window

and here is the amended query the change is inside the CTE where instead of deducting one year I add one year.
;WITH CTE
      AS (
                  SELECT DISTINCT
                        FirstName
                      , LastName
                      , DOB
                      , YEAR(DateFilled) + 1 AS yr
                  FROM yourtable
            )
SELECT
      YEAR(T.DateFilled)                                AS c_yr
    , COUNT(CASE
            WHEN AccountType = 'WalkIn' THEN 1 END)     AS WalkIn
    , COUNT(CASE
            WHEN AccountType = 'Mail Order' THEN 1 END) AS MailOrder
    , COUNT(CASE
            WHEN AccountType IS NULL THEN 1 END)        AS Other
    , COUNT(*)                                          AS Total
    , COUNT(CASE
            WHEN CTE.yr IS NULL THEN 1 END)             AS missing
FROM yourtable AS T
      LEFT JOIN CTE
                  ON T.FirstName = CTE.FirstName
                        AND T.LastName = CTE.LastName
                        AND T.DOB = CTE.DOB
                        AND YEAR(T.dateFilled) = CTE.yr
GROUP BY
      YEAR(T.DateFilled)

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40259641
The total number of clients is correct. It differs to the "expected result" which is missing 3.

My method of calculating "missing" result in a different set of numbers due to a difference in the total numbers and in how it is calculated, for example in the first year there has to be 100% missing, but that has arbitrarily been cut to zero. (this can be accommodated but needs conversation).

So, yes, I do object.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40259677
Mmm, maybe too early in the morning. I have overlooked the desire for "distinct" counts which was requested. Sorry.

But I still cannot arrive at 13, instead I get 14. I have arbitrarily set missing to zero in the first year too.

| CURR_YR | WALKIN | MAILORDER | OTHER | TOTAL | MISSING |
|---------|--------|-----------|-------|-------|---------|
|    2009 |      2 |         1 |     1 |     4 |       0 |
|    2010 |      0 |         3 |     1 |     4 |       4 |
|    2011 |      1 |         3 |     0 |     4 |       1 |
|    2012 |      1 |         1 |     0 |     2 |       1 |

Open in new window

Which is produced by the following:
;WITH CTE
      AS (
                  SELECT DISTINCT
                        FirstName
                      , LastName
                      , DOB
                      , YEAR(DateFilled)     AS curr_yr
                      , YEAR(DateFilled) + 1 AS prev_yr
                      , AccountType
                      , MIN(YEAR(DateFilled)) over() AS min_yr
                  FROM yourtable
            )
SELECT
      T.curr_yr
    , COUNT(CASE
            WHEN T.AccountType = 'WalkIn' THEN 1 END)     AS WalkIn
    , COUNT(CASE
            WHEN T.AccountType = 'Mail Order' THEN 1 END) AS MailOrder
    , COUNT(CASE
            WHEN T.AccountType IS NULL THEN 1 END)        AS Other
    , COUNT(*)                                          AS Total
    , COUNT(CASE
            WHEN T.curr_yr = t.min_yr then null
            WHEN CTE.prev_yr IS NULL THEN 1 END)        AS missing
FROM cte AS T
      LEFT JOIN CTE
                  ON T.FirstName = CTE.FirstName
                        AND T.LastName = CTE.LastName
                        AND T.DOB = CTE.DOB
                        AND T.curr_yr = CTE.prev_yr
GROUP BY
      T.curr_yr

Open in new window

Here is the detail of the 14:
| FIRSTNAME | LASTNAME |        DOB | CURR_YR | PREV_YR | WALKIN | MAILORDER |  OTHER | MISSING |
|-----------|----------|------------|---------|---------|--------|-----------|--------|---------|
|     Alpha |    Bette | 1980-10-02 |    2009 |  (null) |      1 |    (null) | (null) |       1 |
|     Alpha |    Bette | 1984-11-03 |    2009 |  (null) |      1 |    (null) | (null) |       1 |
|     Benny |   Thomas | 1982-04-22 |    2009 |  (null) | (null) |    (null) |      1 |       1 |
|       Bob | Williams | 1975-11-02 |    2012 |  (null) | (null) |         1 | (null) |       1 |
|  Chrysler |     Jeep | 1970-10-02 |    2010 |  (null) | (null) |         1 | (null) |       1 |
|  Chrysler |     Jeep | 1970-10-02 |    2011 |    2011 | (null) |         1 | (null) |  (null) |
|     Dodge |      Ram | 1976-11-02 |    2009 |  (null) | (null) |         1 | (null) |       1 |
|     Dodge |      Ram | 1995-10-02 |    2010 |  (null) | (null) |         1 | (null) |       1 |
|     Dodge |      Ram | 1995-10-02 |    2011 |    2011 | (null) |         1 | (null) |  (null) |
|     Honda |      Fit | 1980-10-02 |    2011 |  (null) |      1 |    (null) | (null) |       1 |
|     Honda |      Fit | 1980-10-02 |    2012 |    2012 |      1 |    (null) | (null) |  (null) |
|       Ric |  Elvarez | 1979-03-21 |    2010 |  (null) | (null) |    (null) |      1 |       1 |
|    Toyota |  Corolla | 1990-10-02 |    2010 |  (null) | (null) |         1 | (null) |       1 |
|    Toyota |  Corolla | 1990-10-02 |    2011 |    2011 | (null) |         1 | (null) |  (null) |

Open in new window

see http://sqlfiddle.com/#!6/bd77f/1
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40259685
compare.png
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40259849
inserts for 2009 transactions
	('Alpha', 'Bette', '19801002', '20090201', 'WalkIn'),
	('Alpha', 'Bette', '19841103', '20090404', 'WalkIn'),
	('Benny', 'Thomas', '19820422', '20090501', NULL),
	('Dodge', 'Ram', '19761102', '20090201', 'Mail Order'),

Open in new window

inserts for 2010 transactions, 4 distinct clients, none of which occur in 2009 = 4 missing (as I understand it)
	('Chrysler', 'Jeep', '19701002', '20100201', 'Mail Order'),
	('Ric', 'Elvarez', '19790321', '20100601', NULL),
	('Toyota', 'Corolla', '19901002', '20100201', 'Mail Order'),
	('Dodge', 'Ram', '19951002', '20100201', 'Mail Order'),
	('Chrysler', 'Jeep', '19701002', '20100301', 'Mail Order'),
	('Toyota', 'Corolla', '19901002', '20100401', 'Mail Order'),

Open in new window

0
 

Author Closing Comment

by:chokka
ID: 40267267
Thanks. But Total and Missing Customer values are not correct.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40267417
@chokka

Would you mind showing how you arrive at the correct figures? I cannot figure it out and wondered how you did.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

578 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