August 20, 2015 at 2:10 am
Hi everyone
I have a table of customer sales (dbo].[CustomerSales] which has a complete record od sales of our products for the past 5 years. I would like to create a report that extracts all the sales per customer on one row ie [CustomerID] 00011 has had 25 sales in the past 5 years and I would like to have their sales from left to right (starting with their earliest sale date at the beginning [SaleDate] field on the one row. I would also like to include the store location field [Location] along with the date the sale took place. So in other words the extract would look something like:
[CustomerID], [SaleDate], [Location], [SaleDate], [Location], [SaleDate], [Location], [SaleDate], [Location], [SaleDate], [Location], etc etc
Obviously some customers will have had less sales than others in which case I’m assuming these fields would just contain NULL values.
Any help would be greatly appreciated.
BO
August 20, 2015 at 2:23 am
-- expand this repeating sequence to cover the maximum number of sales by customer
SELECT
CustomerID,
SaleDate1 = MAX(CASE WHEN rn = 1 THEN SaleDate ELSE NULL END),
Location1 = MAX(CASE WHEN rn = 1 THEN Location ELSE NULL END),
SaleDate2 = MAX(CASE WHEN rn = 2 THEN SaleDate ELSE NULL END),
Location2 = MAX(CASE WHEN rn = 2 THEN Location ELSE NULL END),
SaleDate3 = MAX(CASE WHEN rn = 3 THEN SaleDate ELSE NULL END),
Location3 = MAX(CASE WHEN rn = 3 THEN Location ELSE NULL END)
FROM (
SELECT
CustomerID,
SaleDate,
Location,
rn = ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY SaleDate, Location)
FROM dbo.CustomerSales
) d
GROUP BY CustomerID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 20, 2015 at 3:13 am
Cheers Chris
I'll give this a whirl.
Thanks for the really speedy response!
BO
August 20, 2015 at 8:07 am
ByronOne (8/20/2015)
Cheers ChrisI'll give this a whirl.
Thanks for the really speedy response!
BO
The question now is, do you understand how and why it works?
I ask because Chris' fine solution (known as a CROSSTAB and really only one (likely the best) of two ways to do this in T-SQL) could require a fair bit of code especially since you have a 5 year requirement. That's more than 1,800 columns. Are you sure that's what you actually want/need? And, if the answer is "yes", the question remains of what you might want to do with any dates that have no sales for the given day.
Either way, instead of hand coding this, this could be the perfect place for a bit of dynamic SQL to build the required query so that it auto-magically changes with time. You can find out more about how to easily do such a thing at the following article. Chris' code would be perfect as the working model to convert to dynamic SQL as mentioned in the article.
http://www.sqlservercentral.com/articles/Crosstab/65048/
For what could be quite a bit of added performance, you could do a bit of "Pre-Aggregation", which is a bit of magic that I first learned about from Peter Larsson and is mentioned in the companion article to the one above.
http://www.sqlservercentral.com/articles/T-SQL/63681/
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2015 at 9:06 am
Hi BO
just wondering and perhaps you can explain the rationale behind your request please?
here is some test data to use and using ChrisM's code
SELECT TOP 1000000
CustomerID = 1 + CAST(Abs(Checksum(Newid()) % 90000 ) AS INT),
Location = 1 + CAST(Abs(Checksum(Newid()) % 90 ) AS INT),
SaleDate = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2010', '2015'), '2009')
INTO #CustomerSales
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
--SELECT CustomerID, COUNT(*) AS CNT
--FROM #CustomerSales
--GROUP BY CustomerID
--ORDER BY CNT DESC
--SELECT MIN(SaleDate) AS MINDATE, MAX(SaleDate) AS MAXDATE
--FROM #CustomerSales
SELECT
CustomerID,
SaleDate1 = MAX(CASE WHEN rn = 1 THEN CONVERT(VARCHAR(10),SaleDate,103) ELSE NULL END),
Location1 = MAX(CASE WHEN rn = 1 THEN Location ELSE NULL END),
SaleDate2 = MAX(CASE WHEN rn = 2 THEN CONVERT(VARCHAR(10),SaleDate,103) ELSE NULL END),
Location2 = MAX(CASE WHEN rn = 2 THEN Location ELSE NULL END),
SaleDate3 = MAX(CASE WHEN rn = 3 THEN CONVERT(VARCHAR(10),SaleDate,103) ELSE NULL END),
Location3 = MAX(CASE WHEN rn = 3 THEN Location ELSE NULL END),
SaleDate4 = MAX(CASE WHEN rn = 4 THEN CONVERT(VARCHAR(10),SaleDate,103) ELSE NULL END),
Location4 = MAX(CASE WHEN rn = 4 THEN Location ELSE NULL END),
SaleDate5 = MAX(CASE WHEN rn = 5 THEN CONVERT(VARCHAR(10),SaleDate,103) ELSE NULL END),
Location5 = MAX(CASE WHEN rn = 5 THEN Location ELSE NULL END),
SaleDate6 = MAX(CASE WHEN rn = 6 THEN CONVERT(VARCHAR(10),SaleDate,103) ELSE NULL END),
Location6 = MAX(CASE WHEN rn = 6 THEN Location ELSE NULL END),
SaleDate7 = MAX(CASE WHEN rn = 7 THEN CONVERT(VARCHAR(10),SaleDate,103) ELSE NULL END),
Location7 = MAX(CASE WHEN rn = 7 THEN Location ELSE NULL END),
SaleDate8 = MAX(CASE WHEN rn = 8 THEN CONVERT(VARCHAR(10),SaleDate,103) ELSE NULL END),
Location8 = MAX(CASE WHEN rn = 8 THEN Location ELSE NULL END),
SaleDate9 = MAX(CASE WHEN rn = 9 THEN CONVERT(VARCHAR(10),SaleDate,103) ELSE NULL END),
Location9 = MAX(CASE WHEN rn = 9 THEN Location ELSE NULL END),
SaleDate10 = MAX(CASE WHEN rn = 10 THEN CONVERT(VARCHAR(10),SaleDate,103) ELSE NULL END),
Location10 = MAX(CASE WHEN rn = 10 THEN Location ELSE NULL END)
--- AND SO ON AND SO FORTH...
FROM (
SELECT
CustomerID,
SaleDate,
Location,
rn = ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY SaleDate, Location)
FROM #CustomerSales
) d
GROUP BY CustomerID
ORDER BY CustomerID
this provides data similar to the following:
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
¦ CustomerID ¦ SaleDate1 ¦ Location1 ¦ SaleDate2 ¦ Location2 ¦ SaleDate3 ¦ Location3 ¦ SaleDate4 ¦ Location4 ¦ SaleDate5 ¦ Location5 ¦ SaleDate6 ¦ Location6 ¦ SaleDate7 ¦ Location7 ¦ SaleDate8 ¦ Location8 ¦ SaleDate9 ¦ Location9 ¦ SaleDate10 ¦ Location10 ¦
¦------------+------------+-----------+------------+-----------+------------+-----------+------------+-----------+------------+-----------+------------+-----------+------------+-----------+------------+-----------+------------+-----------+------------+------------¦
¦ 1 ¦ 08/03/2009 ¦ 83 ¦ 27/04/2009 ¦ 39 ¦ 21/02/2010 ¦ 13 ¦ 18/03/2010 ¦ 59 ¦ 29/03/2010 ¦ 18 ¦ 31/03/2010 ¦ 12 ¦ 14/06/2010 ¦ 46 ¦ 22/07/2010 ¦ 16 ¦ 17/11/2010 ¦ 34 ¦ 03/02/2011 ¦ 21 ¦
¦ 2 ¦ 15/02/2009 ¦ 24 ¦ 07/12/2009 ¦ 87 ¦ 14/01/2010 ¦ 61 ¦ 15/05/2010 ¦ 61 ¦ 30/06/2010 ¦ 74 ¦ 23/06/2011 ¦ 31 ¦ 05/09/2011 ¦ 78 ¦ 09/04/2012 ¦ 35 ¦ 04/06/2012 ¦ 75 ¦ 29/11/2012 ¦ 3 ¦
¦ 3 ¦ 02/07/2010 ¦ 8 ¦ 03/08/2010 ¦ 39 ¦ 18/08/2010 ¦ 68 ¦ 25/08/2010 ¦ 4 ¦ 20/11/2010 ¦ 71 ¦ 11/10/2011 ¦ 40 ¦ 22/05/2012 ¦ 61 ¦ 28/08/2012 ¦ 25 ¦ NULL ¦ NULL ¦ NULL ¦ NULL ¦
¦ 4 ¦ 07/06/2009 ¦ 6 ¦ 08/06/2009 ¦ 66 ¦ 10/06/2009 ¦ 76 ¦ 21/11/2009 ¦ 11 ¦ 30/06/2010 ¦ 54 ¦ 23/10/2010 ¦ 86 ¦ 07/08/2011 ¦ 41 ¦ 02/01/2012 ¦ 37 ¦ 06/03/2012 ¦ 24 ¦ 21/07/2012 ¦ 54 ¦
¦ 5 ¦ 06/06/2010 ¦ 13 ¦ 08/08/2010 ¦ 84 ¦ 27/05/2011 ¦ 65 ¦ 29/06/2011 ¦ 67 ¦ 07/08/2011 ¦ 63 ¦ 27/02/2012 ¦ 90 ¦ 05/08/2012 ¦ 49 ¦ 11/11/2012 ¦ 1 ¦ 11/09/2013 ¦ 44 ¦ NULL ¦ NULL ¦
¦ 6 ¦ 25/11/2009 ¦ 39 ¦ 06/01/2010 ¦ 36 ¦ 17/06/2011 ¦ 18 ¦ 20/08/2011 ¦ 28 ¦ 11/10/2011 ¦ 49 ¦ 21/10/2011 ¦ 57 ¦ 01/12/2011 ¦ 11 ¦ 24/02/2012 ¦ 53 ¦ 02/07/2012 ¦ 4 ¦ 02/12/2012 ¦ 81 ¦
¦ 7 ¦ 23/05/2010 ¦ 63 ¦ 01/09/2010 ¦ 8 ¦ 21/09/2010 ¦ 42 ¦ 25/08/2012 ¦ 90 ¦ 20/09/2012 ¦ 43 ¦ 30/12/2012 ¦ 61 ¦ 20/06/2013 ¦ 8 ¦ 31/07/2013 ¦ 37 ¦ 05/10/2013 ¦ 42 ¦ 09/12/2013 ¦ 69 ¦
¦ 8 ¦ 25/05/2009 ¦ 80 ¦ 12/11/2009 ¦ 89 ¦ 12/01/2010 ¦ 42 ¦ 13/08/2010 ¦ 21 ¦ 11/12/2011 ¦ 18 ¦ 11/10/2012 ¦ 31 ¦ 16/11/2012 ¦ 54 ¦ 17/11/2012 ¦ 87 ¦ NULL ¦ NULL ¦ NULL ¦ NULL ¦
¦ 9 ¦ 18/01/2009 ¦ 9 ¦ 01/06/2009 ¦ 44 ¦ 16/02/2010 ¦ 10 ¦ 21/02/2011 ¦ 29 ¦ 22/12/2012 ¦ 57 ¦ 17/05/2013 ¦ 86 ¦ NULL ¦ NULL ¦ NULL ¦ NULL ¦ NULL ¦ NULL ¦ NULL ¦ NULL ¦
¦ 10 ¦ 02/04/2009 ¦ 51 ¦ 26/06/2009 ¦ 89 ¦ 09/08/2009 ¦ 12 ¦ 16/12/2009 ¦ 34 ¦ 18/02/2011 ¦ 76 ¦ 01/04/2011 ¦ 52 ¦ 07/01/2012 ¦ 9 ¦ 09/06/2013 ¦ 63 ¦ 08/12/2013 ¦ 54 ¦ NULL ¦ NULL ¦
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
so...what are you going to do with this data as delivered?
I can imagine analysing data based on customers, location, sales date, duration between salesdates and locations etc...but the above isnt going to help you......
your thoughts?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply