chokka
asked on
SQL Pivot Table
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
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
ASKER
I have SQL 2008 and SQL 2012. I am writing the SQL Query on SQL 2012.
Sorry for the confusion about the version.
Sorry for the confusion about the version.
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:
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
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 |
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
{+ 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
Here 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) |
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)
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.
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.
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.
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 |
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
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) |
see http://sqlfiddle.com/#!6/bd77f/1
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'),
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'),
ASKER
Thanks. But Total and Missing Customer values are not correct.
@chokka
Would you mind showing how you arrive at the correct figures? I cannot figure it out and wondered how you did.
Would you mind showing how you arrive at the correct figures? I cannot figure it out and wondered how you did.
Please inform us exactly what version you are actually using.
(I would particularly like to know if it is SQL Server 2012 or later)