December 28, 2009 at 11:20 pm
I am generating a report which displays the "networth" of investments over time (months). I would like the user to enter a start date and end date to determine the "dynamic" range of months to view the cummulative networth of his/her investments. The source data is as follows:
Account Period Amount
--------- --------- ----------
Acct1 2009-02 $100.00
Acct1 2009-02 $ 50.00
Acct2 2009-03 $ 10.00
Acct1 2009-03 $ 5.00
QUERY OUTPUT DESIRED:
Account 2009-02 2009-03
-------- --------- ---------
Acct1 $150.00 $155.00
Acct2 $ 0.00 $ 10.00
Total $150.00 $165.00
I have been able to generate the cummulative values in row format using the following query but have not been able to determine a means to create a crosstab table with the dynamically generated columns for each month in the date range (including months with "0" values):
SELECT Account, CONVERT(varchar(7), Date, 121) AS Expr1,
(SELECT SUM(Total) AS Expr1
FROM dbo.tblTestNetworth
WHERE (CONVERT(varchar(7), Date, 121) <= CONVERT(varchar(7), a.Date, 121)) AND (Account = a.Account)) AS cummTTL
FROM dbo.tblTestNetworth AS a
GROUP BY Account, CONVERT(varchar(7), Date, 121)
Any examples / assistance would be appreciated on how to:
1) Create the crosstab with dynamically generated column headings based on a user supplied start and end date. The crosstab needs to be in the YYYY-MM format and must include months with "0" values.
2) The crosstab must be cummulative by month
December 28, 2009 at 11:38 pm
I've got just the thing for ya...
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2009 at 12:13 am
Thanks for the information. I believe I would simply need to modify this section of the article with the query content I referenced earlier:
--===== Create the "Mostly Static" section of the code
SELECT @SQL3 =
' SUM(Total) AS Total
FROM (--==== Derived table "d" does preaggregation by whole month
SELECT DATEADD(mm,DATEDIFF(mm,0,Date),0) AS MonthDate,
Account,
SUM(Total) AS Total
FROM dbo.JBMTest
WHERE Date >= ' + QUOTENAME(@StartDate,'''') + '
AND Date < ' + QUOTENAME(@EndDate,'''') + '
GROUP BY DATEADD(mm,DATEDIFF(mm,0,Date),0), Account
) d
GROUP BY Account WITH ROLLUP
'
Is this correct? If so, could you help me rewrite the following sql query that provides the cummulative data by month for the "mostly static" section of the procedure:
SELECT Account, CONVERT(varchar(7), Date, 121) AS Expr1,
(SELECT SUM(Total) AS Expr1
FROM dbo.tblTestNetworth
WHERE (CONVERT(varchar(7), Date, 121) <= CONVERT(varchar(7), a.Date, 121)) AND (Account = a.Account)) AS cummTTL
FROM dbo.tblTestNetworth AS a
GROUP BY Account, CONVERT(varchar(7), Date, 121)
This query currently generates the following output:
Account Expr1 cummTTL
-------- ------- ---------
Acct1 2009-0977.0000
Acct1 2009-10227.0000
Acct2 2009-0955.0000
December 29, 2009 at 1:12 am
I made an attempt at modifying the code suggested to support the cummulative monthly networth table but I receive an error when I Execute the procedure -- Msg 102, Level 15, State 1, Procedure SQLNetworth3, Line 73
Incorrect syntax near ')'.
I have attached the procedure and would appreciate it if someone could help me identify the cause of the error. I am not a sql expert (more like a beginner) and am tackling something that is probably beyond my expertise without some assistance.
December 29, 2009 at 5:56 am
tm3 (12/29/2009)
I made an attempt at modifying the code suggested to support the cummulative monthly networth table but I receive an error when I Execute the procedure -- Msg 102, Level 15, State 1, Procedure SQLNetworth3, Line 73Incorrect syntax near ')'.
I have attached the procedure and would appreciate it if someone could help me identify the cause of the error. I am not a sql expert (more like a beginner) and am tackling something that is probably beyond my expertise without some assistance.
Just a note... I'd say that 90% of the people who use MS Word, don't have Word 2007. I'd suggest attaching a Word 97-2003 compatible file and, yes, I'm one of those people who don't have it.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2009 at 5:58 am
My other recommendation is that people like to test their solutions before posting them and, since you're probably under the gun won't have much of an appreciation for it just now, but do like what is suggested in the article at the first link in my signature below and people will trip over each other trying to help you. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2009 at 8:23 am
Thanks for the feeback. I have taken your recommendations and attached the revised MS-Word document in a more standard format and added a section at the beginning for sample data. Hope you or others are able to identify the source of the error.
December 29, 2009 at 10:06 am
tm3 (12/29/2009)
Thanks for the feeback. I have taken your recommendations and attached the revised MS-Word document in a more standard format and added a section at the beginning for sample data. Hope you or others are able to identify the source of the error.
tm3 you can see the SQL it is generating by commenting out the EXEC line and uncommenting out the PRINT line. Try changing this...
--===== Print the Dynamic SQL (uncomment for troubleshooting
-- PRINT @SQL1 + @SQL2 + @SQL3
--===== Execute the Dynamic SQL to create the desired report
EXEC (@SQL1 + @SQL2 + @SQL3)
to this...
--===== Print the Dynamic SQL (uncomment for troubleshooting
PRINT @SQL1 + @SQL2 + @SQL3
--===== Execute the Dynamic SQL to create the desired report
-- EXEC (@SQL1 + @SQL2 + @SQL3)
You may have to work on getting the strings in good order one at a time by commenting out all but the one you are working on.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
December 29, 2009 at 10:25 am
Also, your procedure has a BEGIN, and no END. Try putting the END just after the ORDER BY in @SQL2. You may still have errors, but when I did this I was able to print the statements.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
December 29, 2009 at 4:15 pm
Thanks Greg as your idea (END) solved the compilation issue.
I have modified the code and revised the "--===== Create the "static" section of the code (SQL3)" and when I execute the procedure from within my VB.net application I receive the following error --
Invalid column name 'MonthDate'. Invalid column name 'MonthDate'. Invalid column name 'MonthDate'. Invalid column name 'MonthDate'. Invalid column name 'MonthDate'. Invalid column name 'MonthDate'. Invalid column name 'MonthDate'. Invalid column name 'MonthDate'. Invalid column name 'MonthDate'. Invalid column name 'MonthDate'. Invalid column name 'MonthDate'. Invalid column name 'MonthDate'. Invalid
I attached the revised PROCEDURE for your reference.
I modified the original SQL3 section to support my cummulative total requirements by month and it appears the "MonthDate" field is no longer accessible to SQL2. Can you explain why? As a troubleshooting test, I stripped the SELECT statement out of the procedure and executed it within a query "VIEW" and it displays the rows as expected with the MonthDate column available for each row.
Any idea why the error is occuring?
December 29, 2009 at 9:02 pm
Heh... as you can tell, there are several problems with the code you have. Lemme see if I can help... I'll be back.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2009 at 10:13 pm
Jeff, much appreciated if you can help me achieve the desire result of a cummulative total by month for the networth table I require.
Hopefully others requiring cummulative totals will benefit from your/our work on this procedure. I'll wait for your next reply before I make any further attempts to solve the problem. The interaction between SQL2 and SQL3 are a bit more complex than I bargained for at this point in my SQL coding development.
Todd
December 29, 2009 at 10:16 pm
Ok... first, we need a lot of test data to demo this code. Using tm3's code to create a test table and to save later CPR errors (Copy, Paste, Replace), I decided to create a real table with the same name as the last proc tm3 posted but in TempDB so we don't get into trouble while testing. Since we also need a Tally Table in TempDB so we can test this, I also added the code in for that. Don't forget to drop these tables from TempDB when you're all done testing.
Here's the code... ALWAYS read the comments in the code...
--=====================================================================================================================
-- Create a test table and populate it.
-- Read the warning below... I'm not responsible for your data if you wreck it.
-- I tried to make everything as safe as possible. If you change it, you could overwrite real data.
-- This section is not a part of the solution. It's just to build a test table in TempDB.
--=====================================================================================================================
--===== Do this test in a nice safe place.
-- Warning... if you leave this out, it may/will damage any real tables.
USE TempDB
--===== Conditionally drop the test table
IF OBJECT_ID('TempDB.dbo.tblTestNetworth','U') IS NOT NULL
DROP TABLE TempDB.dbo.tblTestNetworth
--===== Create the test table
CREATE TABLE TempDB.dbo.tblTestNetworth
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Account NCHAR(50), --Bad design here. SHOULD BE NVARCHAR!!!
Date DATETIME,
Total MONEY
)
--===== This populates the test table with a whole bunch of highly randomized but constrained test data.
-- Don't let the TOP number scare you... this doesn't take long.
INSERT INTO TempDB.dbo.tblTestNetworth
SELECT TOP 100000
Account = 'Acct'+RIGHT('00'+CAST(ABS(CHECKSUM(NEWID()))%20+1 AS VARCHAR(2)),2),
Date = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'20080101','20100101')+CAST('20080101' AS DATETIME),
Total = ROUND(RAND(CHECKSUM(NEWID()))*100,2)
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Well add an index for some wicked speed
CREATE NONCLUSTERED INDEX IX_tblTestNetworth_ReportComposite01 ON dbo.tblTestNetworth
(Date ASC, Account ASC, Total)
--===== We also need a Tally table for this project
--===== Create and populate the Tally table on the fly
SELECT TOP 1000000
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
--===== Let's see what the first 1,000 rows look like
-- SELECT TOP 1000 * FROM TempDB.dbo.tblTestNetworth
Here's the code to solve the problem. Let me tell you how I did it. I copied the code from my own article and was real careful about replacing things like column and table names. It worked first time. You've just got to be careful on these things. In the code tm3 posted, the whole preaggregation by month was skipped and that's why nothing was being returned.
--=====================================================================================================================
-- Solve the problem... this is where the rubber meets the road.
-- You get to turn it into a stored procedure. ;-)
--=====================================================================================================================
--===== These will become parameters in the final code
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008-06-29'
SET @EndDate = '2008-01-15'
--===== Supress the auto-display of row counts to prevent false error reporting
SET NOCOUNT ON
--===== This variable gives us "room" to swap dates if needed
DECLARE @SwapDate DATETIME
--===== Declare the variables that will contain the dynamic SQL.
-- I decided to use the VARCHAR version (no QUOTENAME allowed, though)
DECLARE @SQL1 VARCHAR(8000),
@SQL2 VARCHAR(8000),
@SQL3 VARCHAR(8000)
--===== If the dates are not in the correct order, swap them...
-- WITHOUT using an IF
SELECT @SwapDate = @EndDate,
@EndDate = @StartDate,
@StartDate = @SwapDate
WHERE @EndDate < @StartDate
--===== Create the "static" section of the code
SELECT @SQL1 = 'SELECT CASE WHEN GROUPING(Account) = 1 THEN ''Total'' ELSE Account END AS Account,'+CHAR(10)
--===== The "Hard" Part - Concatenation to Form the Date Columns
SELECT @SQL2 = COALESCE(@SQL2,'')
+ ' SUM(CASE WHEN MonthDate = ' + QUOTENAME(d.MonthName,'''')
+ ' THEN Total ELSE 0 END) AS [' + d.MonthName + '],' + CHAR(10)
FROM
( --=== Produces the list of MonthNames in the mmm yyyy format
SELECT N,
STUFF(CONVERT(CHAR(11),DATEADD(mm, N-1, @StartDate),100),4,3,'') AS MonthName
FROM dbo.Tally
WHERE N <= DATEDIFF(mm,@StartDate,@EndDate)
) d
ORDER BY d.N
--===== Create the "Mostly Static" section of the code
SELECT @SQL3 =
' SUM(Total) AS Total
FROM ( --=== Derived table "d" does preaggregation by whole month
SELECT DATEADD(mm,DATEDIFF(mm,0,Date),0) AS MonthDate,
RTRIM(Account) AS Account,
SUM(Total) AS Total
FROM dbo.tblTestNetworth
WHERE Date >= ''' + RIGHT(CONVERT(VARCHAR(11),@StartDate,106),8) + '''
AND Date < ''' + RIGHT(CONVERT(VARCHAR(11),@EndDate,106),8) + '''
GROUP BY DATEADD(mm,DATEDIFF(mm,0,Date),0), Account
) d
GROUP BY Account WITH ROLLUP
' --<<LOOK! GOTTA KEEP THIS!!!!
--===== Print the SQL we just built in the messages tab just in case
-- we need to trouble shoot it
PRINT @SQL1+@SQL2+@SQL3
--===== And now we execute the SQL
EXEC (@SQL1+@SQL2+@SQL3)
The code above should work directly on your real table, as well, because I used the same table name and column names for the test table.
Send beer, please... I already have enough pretzels.:-P
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2009 at 11:17 pm
Jeff, thanks for generating the code to this point. What is missing is the "cummulative" total by month. For example if the following data exists:
ACCOUNT............DATE..............TOTAL
------------- ------------- -----------------
ACCT1.........10/01/09.............$100.00
ACCT1.........11/01/09.............$ 40.00
... the output table should look like this -- cummulative by month.
ACCOUNT........ OCT 2009 ......... NOV 2009
------------- ------------- -----------------
ACCT1............. $100.00 .......... $140.00...
That is why I was trying to use the following SQL3 section to replace the one you provided so that the total would be cummulative by month but it creates errors.
--===== Create the "Mostly Static" section of the code
SELECT @SQL3 =
'Account, DATEADD(mm, DATEDIFF(mm, 0, Date), 0) AS MonthDate,
(SELECT SUM(Total) AS Expr1
FROM dbo.tblTestNetworth
WHERE(DATEADD(mm, DATEDIFF(mm, 0, Date), 0) <= DATEADD(mm, DATEDIFF(mm, 0, a.Date), 0)) AND (Account = a.Account)) AS cummTTL
FROM dbo.tblTestNetworth AS a
WHERE Date >= ' + QUOTENAME(@StartDate,'''') + '
AND Date < ' + QUOTENAME(@EndDate,'''') + '
GROUP BY Account, DATEADD(mm, DATEDIFF(mm, 0, Date), 0)
How should I modify SQL3 to generate the desired cummulative monthly totals?
Todd
'
December 30, 2009 at 12:19 am
Ah... you mean a classic "running total". In order to do that, we'll need to do the "preaggregation" in a temp table, run the running total algorithm over it, and then gen from that.
It's 2:18AM here and I've gotta get up in a couple of hours... I take a whack at it tomorrow night after I get home from work. If you want to read up on what I'm getting ready to do, see the following article...
http://www.sqlservercentral.com/Forums/Topic581526-236-9.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply