June 5, 2012 at 7:11 am
In one project, I need to create some cross tab reports.
For example, from one table in which there are three columns: CostumerName, OrderDate and Amount
Each month, OrderDate will move one month automatically like:(I solved this input issue)
For Jun 2012, OrderDate is:05/01/2011-04/30/2012;
For July 2012, OrderDate is:06/01/2011-05/31/2012;
And so on...
The report need to display one year amount like:
CostumerName---201105---201106---201107 ... 201204
AAA----------------------$100-----$35-------$45-------$26
BBB-----------------------$29------$38-------$44-------$77
I used to do it manually.
Is it possible to make automation processing working?
June 5, 2012 at 7:36 am
This was removed by the editor as SPAM
June 5, 2012 at 9:36 am
I don't know if this is really what you're looking for, but I took a stab for fun.
First, I loaded up some test data...
CREATE TABLE #Orders
(
OrderID INT IDENTITY(1,1),
CustomerName VARCHAR(5),
OrderDate DATE,
YearMonth VARCHAR(6),
OrderTotal NUMERIC(12,2)
)
INSERT INTO #Orders
(CustomerName, OrderDate, OrderTotal)
VALUES
('AAA', '05/15/2011', 175.00)
INSERT INTO #Orders
(CustomerName, OrderDate, OrderTotal)
SELECT CustomerName, DATEADD(dd, 8, OrderDate), OrderTotal + 55
FROM #Orders
INSERT INTO #Orders
(CustomerName, OrderDate, OrderTotal)
SELECT CustomerName, DATEADD(dd, 12, OrderDate), OrderTotal + 55
FROM #Orders
INSERT INTO #Orders
(CustomerName, OrderDate, OrderTotal)
SELECT CustomerName, DATEADD(dd, 16, OrderDate), OrderTotal + 88
FROM #Orders
INSERT INTO #Orders
(CustomerName, OrderDate, OrderTotal)
SELECT CustomerName, DATEADD(dd, 41, OrderDate), OrderTotal + 107
FROM #Orders
INSERT INTO #Orders
(CustomerName, OrderDate, OrderTotal)
SELECT CustomerName, DATEADD(dd, 60, OrderDate), OrderTotal + 207
FROM #Orders
INSERT INTO #Orders
(CustomerName, OrderDate, OrderTotal)
SELECT CustomerName, DATEADD(dd, 83, OrderDate), OrderTotal + 350
FROM #Orders
INSERT INTO #Orders
(CustomerName, OrderDate, OrderTotal)
SELECT CustomerName, DATEADD(dd, 92, OrderDate), OrderTotal + 516
FROM #Orders
INSERT INTO #Orders
(CustomerName, OrderDate, OrderTotal)
SELECT CustomerName, DATEADD(dd, 101, OrderDate), OrderTotal + 721
FROM #Orders
INSERT INTO #Orders
(CustomerName, OrderDate, OrderTotal)
SELECT 'BBB', DATEADD(dd, 1, OrderDate), OrderTotal -17
FROM #Orders
UPDATE #Orders
SET YearMonth = CAST(YEAR(OrderDate) AS VARCHAR)
+ RIGHT('0' + CAST(MONTH(OrderDate) AS VARCHAR), 2)
Then, I coded up a dynamic pivot query for you...
DECLARE @CSV VARCHAR(4000)
DECLARE @sql VARCHAR(8000)
DECLARE @StartDate DATE
DECLARE @EndDate DATE
SET @StartDate = '05/01/2011'
SET @EndDate = '04/30/2012'
SELECT @CSV = COALESCE(@CSV + ', ','') + QUOTENAME(YearMonth)
FROM (
SELECT DISTINCT YearMonth
FROM #Orders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
) D
SET @sql = '
SELECT *
FROM (SELECT CustomerName, YearMonth, OrderTotal FROM #Orders) AS D
PIVOT(SUM(OrderTotal) FOR YearMonth IN(' + @CSV + ')) AS P;'
EXEC (@SQL)
Is this what you want?
June 5, 2012 at 9:48 am
Thank you for help.
I studied and ran it but got an error:
Msg 325, Level 15, State 1, Line 4
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature
June 5, 2012 at 10:00 am
Since this is the 2008 forum, I would guess you would be okay running this:EXEC sp_dbcmptlevel 'DatabaseName', 100
... but you should run that by any DBAs you may have available.
June 5, 2012 at 10:17 am
Thank you so much.
I ran it in my local 2008 express, it works great!
June 5, 2012 at 11:56 am
Just wondering, but why do you need to do the crosstab inside SQL Server? If you create a Matrix report, you can do a crosstab there and it's stupid easy... you can run a wizard to do it...
June 6, 2012 at 7:50 am
Hi Jeffem,
If I want to get sum of 12 months, how to modify your existing code?
June 6, 2012 at 4:32 pm
adonetok (6/6/2012)
Hi Jeffem,If I want to get sum of 12 months, how to modify your existing code?
Try this...
DECLARE @CSV VARCHAR(4000)
DECLARE @sql VARCHAR(8000)
DECLARE @StartDate DATE
DECLARE @EndDate DATE
SET @StartDate = '05/01/2011'
SET @EndDate = '04/30/2012'
SELECT @CSV = COALESCE(@CSV + ', ','') + QUOTENAME(YearMonth)
FROM (
SELECT DISTINCT YearMonth
FROM #Orders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
) D
SET @sql = '
SELECT *
FROM (SELECT CustomerName, YearMonth, OrderTotal,
(SELECT SUM(OrderTotal)
FROM #Orders
WHERE CustomerName = O.CustomerName
AND OrderDate BETWEEN ''' +
CAST(@StartDate AS VARCHAR(10)) + ''' AND ''' +
CAST(@EndDate AS VARCHAR(10)) + '''
) AS Total FROM #Orders O) AS D
PIVOT(SUM(OrderTotal) FOR YearMonth IN(' + @CSV + ')) AS P;'
EXEC (@SQL)
June 7, 2012 at 9:09 am
adonetok (6/7/2012)
Thak you very much.It works.
Since the date is controled by @CSV I deleted the following statement in @sql.
AND OrderDate BETWEEN ''' +
CAST(@StartDate AS VARCHAR(10)) + ''' AND ''' +
CAST(@EndDate AS VARCHAR(10)) + '''
Feel free to remove that portion, but then you'll be getting a total for ALL records for the Customer, not just the individual months shown from @CSV. Take the monthly results and sum them in Excel (or whatever is simplest for you), and you'll see that the Total without this OrderDate filter is incorrect.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply