April 10, 2013 at 4:10 pm
I have tried pivoting - no success since the numbers of rows in the source table that have to tacked on as trailing columns in not fixed.
The base table holds the following data
[font="Courier New"]Date Invoice # Client Amount
2012-06-21INV-01013-M8R1R1ABC Leasing Ltd108000
2012-06-21INV-01013-M8R1R1ABC Leasing Ltd11500
2012-12-11INV-01017-S3T2W0ABC Leasing Ltd121500
2012-12-11INV-01018-Y7V5J8ABC Leasing Ltd121500
2012-12-11INV-01019-L8R2V7ABC Leasing Ltd25000
2013-02-01INV-01024-X0M4T7ABC Leasing Ltd8100[/font]
Creating additional rows to distribute the payments across various months is done. The original record have the fk NULL and the additional records have the fk corresponding to the primary of the original records.
I want to end up with :
[font="Courier New"]Date Invoice # Client Amount Month_1Year_1 Month_2Year_1 Month_3Year_1 ...
[/font]
Any pointers on this ?
This is what I am trying to get :
[font="Courier New"]Date Invoice # Client Amount june-12 july-12 aug-12 sept-12 oct-12 nov-12 déc-12 jan-13 feb-13 march-13
2012-06-21 INV-01013-M8R1R1 ABC Leasing Ltd 10800 900 900 900 900 900 900 900 900 900 900
2012-06-21 INV-01013-M8R1R1 ABC Leasing Ltd 1150 11500
2012-12-11 INV-01017-S3T2W0 ABC Leasing Ltd 121.50 101.25 101.25 101.25 101.25
2012-12-11 INV-01018-Y7V5J8 ABC Leasing Ltd 1215.00 101.25 101.25 101.25 101.25
2012-12-11 INV-01019-L8R2V7 ABC Leasing Ltd 2500 2500
2013-02-01 INV-01024-X0M4T7 ABC Leasing Ltd 810 810
2013-02-05 INV-01025-Z5N5M8 Test Company 123 1500 1500
2013-02-05 INV-01026-R3R7B6 Test Company 123 1500 1500
2013-02-19 INV-01030-C6D0G2 ABC Leasing Ltd 1215.00 101.25 101.25
2013-02-19 INV-01031-G2K7M3 Test Company 123 2500 2500
2013-02-26 INV-01035-V8X2K8 Test Gestion ABC - MG 1215.00 101.25 10125
2013-02-26 INV-01036-L2C7W0 Test Gestion ABC - MG 405 405
[/font]
The table and some data :
CREATE TABLE Source
(
pk int,
fk int,
InvDate datetime,
InvNo varchar(20),
Client varchar(40),
Amount money,
InvYear int,
InvMonth int,
PaymentMonthYear int,
Month_Amount money
)
INSERT INTO Source
(
pk,
fk,
InvDate,
InvNo,
Client,
Amount,
InvYear,
InvMonth,
PaymentMonthYear,
Month_Amount
)
SELECT 1, NULL, '2012-06-22', 'INV-01013-M8R1R1', 'ABC Leasing Ltd', 108000.00, 2012, 6, NULL, NULL
UNION SELECT 2, NULL, '2012-06-22', 'INV-01013-M8R1R1', 'ABC Leasing Ltd', 11500.00, 2012, 6, NULL, NULL
UNION SELECT 3, NULL, '2012-12-12', 'INV-01017-S3T2W0', 'ABC Leasing Ltd', 121500.00, 2012, 12, NULL, NULL
UNION SELECT 4, NULL, '2012-12-12', 'INV-01018-Y7V5J8', 'ABC Leasing Ltd', 121500.00, 2012, 12, NULL, NULL
UNION SELECT 5, NULL, '2012-12-12', 'INV-01019-L8R2V7', 'ABC Leasing Ltd', 25000.00, 2012, 12, NULL, NULL
UNION SELECT 6, NULL, '2013-02-02', 'INV-01024-X0M4T7', 'ABC Leasing Ltd', 8100.00, 2013, 2, NULL, NULL
UNION SELECT 7, NULL, '2013-02-06', 'INV-01025-Z5N5M8', 'Test Company 123', 15000.00, 2013, 2, NULL, NULL
UNION SELECT 8, NULL, '2013-02-06', 'INV-01026-R3R7B6', 'Test Company 123', 15000.00, 2013, 2, NULL, NULL
UNION SELECT 9, NULL, '2013-02-20', 'INV-01030-C6D0G2', 'ABC Leasing Ltd', 121500.00, 2013, 2, NULL, NULL
UNION SELECT 10, NULL, '2013-02-20', 'INV-01031-G2K7M3', 'Test Company 123', 25000.00, 2013, 2, NULL, NULL
UNION SELECT 11, NULL, '2013-02-27', 'INV-01035-V8X2K8', 'Test Gestion ABC - MG', 121500.00, 2013, 2, NULL, NULL
UNION SELECT 12, NULL, '2013-02-27', 'INV-01036-L2C7W0', 'Test Gestion ABC - MG', 40500.00, 2013, 2, NULL, NULL
UNION SELECT 13, NULL, '2013-03-05', 'INV-01039-B4L0W8', 'Test ABC - Calgary - MG', 810000.00, 2013, 3, NULL, NULL
UNION SELECT 14, NULL, '2013-03-05', 'INV-01040-D7Y0Y8', 'Test Gestion ABC - MG', 40500.00, 2013, 3, NULL, NULL
UNION SELECT 15, NULL, '2013-03-06', 'INV-01042-J1H7X6', 'ABC Leasing Ltd', 5.00, 2013, 3, NULL, NULL
UNION SELECT 16, NULL, '2013-03-06', 'INV-01042-J1H7X6', 'ABC Leasing Ltd', 1.00, 2013, 3, NULL, NULL
UNION SELECT 17, NULL, '2013-03-06', 'INV-01042-J1H7X6', 'ABC Leasing Ltd', 8100.00, 2013, 3, NULL, NULL
UNION SELECT 18, NULL, '2013-03-13', 'INV-01043-F5V0T3', 'ABC Leasing Ltd', 5.00, 2013, 3, NULL, NULL
UNION SELECT 19, NULL, '2013-03-13', 'INV-01043-F5V0T3', 'ABC Leasing Ltd', 1.00, 2013, 3, NULL, NULL
UNION SELECT 20, NULL, '2013-03-13', 'INV-01043-F5V0T3', 'ABC Leasing Ltd', 8100.00, 2013, 3, NULL, NULL
UNION SELECT 21, 1, '2012-06-22', 'INV-01013-M8R1R1', 'ABC Leasing Ltd', 108000.00, 2012, 6, 201206, 9000.00
UNION SELECT 22, 1, '2012-06-22', 'INV-01013-M8R1R1', 'ABC Leasing Ltd', 108000.00, 2012, 6, 201207, 9000.00
UNION SELECT 23, 1, '2012-06-22', 'INV-01013-M8R1R1', 'ABC Leasing Ltd', 108000.00, 2012, 6, 201208, 9000.00
UNION SELECT 24, 1, '2012-06-22', 'INV-01013-M8R1R1', 'ABC Leasing Ltd', 108000.00, 2012, 6, 201209, 9000.00
UNION SELECT 25, 1, '2012-06-22', 'INV-01013-M8R1R1', 'ABC Leasing Ltd', 108000.00, 2012, 6, 201210, 9000.00
UNION SELECT 26, 1, '2012-06-22', 'INV-01013-M8R1R1', 'ABC Leasing Ltd', 108000.00, 2012, 6, 201211, 9000.00
UNION SELECT 27, 1, '2012-06-22', 'INV-01013-M8R1R1', 'ABC Leasing Ltd', 108000.00, 2012, 6, 201212, 9000.00
UNION SELECT 28, 1, '2012-06-22', 'INV-01013-M8R1R1', 'ABC Leasing Ltd', 108000.00, 2012, 6, 201301, 9000.00
UNION SELECT 29, 1, '2012-06-22', 'INV-01013-M8R1R1', 'ABC Leasing Ltd', 108000.00, 2012, 6, 201302, 9000.00
UNION SELECT 30, 1, '2012-06-22', 'INV-01013-M8R1R1', 'ABC Leasing Ltd', 108000.00, 2012, 6, 201303, 9000.00
UNION SELECT 31, 2, '2012-06-22', 'INV-01013-M8R1R1', 'ABC Leasing Ltd', 11500.00, 2012, 6, 201206, 11500.00
UNION SELECT 32, 3, '2012-12-12', 'INV-01017-S3T2W0', 'ABC Leasing Ltd', 121500.00, 2012, 12, 201212, 10125.00
UNION SELECT 33, 3, '2012-12-12', 'INV-01017-S3T2W0', 'ABC Leasing Ltd', 121500.00, 2012, 12, 201301, 10125.00
UNION SELECT 34, 3, '2012-12-12', 'INV-01017-S3T2W0', 'ABC Leasing Ltd', 121500.00, 2012, 12, 201302, 10125.00
UNION SELECT 35, 3, '2012-12-12', 'INV-01017-S3T2W0', 'ABC Leasing Ltd', 121500.00, 2012, 12, 201303, 10125.00
UNION SELECT 36, 4, '2012-12-12', 'INV-01018-Y7V5J8', 'ABC Leasing Ltd', 121500.00, 2012, 12, 201212, 10125.00
UNION SELECT 37, 4, '2012-12-12', 'INV-01018-Y7V5J8', 'ABC Leasing Ltd', 121500.00, 2012, 12, 201301, 10125.00
UNION SELECT 38, 4, '2012-12-12', 'INV-01018-Y7V5J8', 'ABC Leasing Ltd', 121500.00, 2012, 12, 201302, 10125.00
UNION SELECT 39, 4, '2012-12-12', 'INV-01018-Y7V5J8', 'ABC Leasing Ltd', 121500.00, 2012, 12, 201303, 10125.00
UNION SELECT 40, 5, '2012-12-12', 'INV-01019-L8R2V7', 'ABC Leasing Ltd', 25000.00, 2012, 12, 201212, 25000.00
UNION SELECT 41, 6, '2013-02-02', 'INV-01024-X0M4T7', 'ABC Leasing Ltd', 8100.00, 2013, 2, 201302, 8100.00
UNION SELECT 42, 7, '2013-02-06', 'INV-01025-Z5N5M8', 'Test Company 123', 15000.00, 2013, 2, 201302, 15000.00
April 10, 2013 at 6:09 pm
What will consume the results of the query? Just about any reporting front-end will have the facility to pivot the results to the format you want. This is the ideal solution.
IF pivoting your result set is unavoidable, then you can dynamically construct the pivot query. There is a recent thread regarding how to do this.
April 11, 2013 at 6:56 am
Truth be known, you've already done the hard part in the form of rows 21 through 42. I'm on my way to work so can't respond with code just now but forget any row that has a NULL fk and use the method in the following article to dynamically pivot the data the way you want. Considering the distribution of data, you might be able to skip the part where the Tally Table is used (you'll understand that statement once you get into the article).
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2013 at 6:58 am
Mansfield (4/10/2013)
What will consume the results of the query? Just about any reporting front-end will have the facility to pivot the results to the format you want. This is the ideal solution.IF pivoting your result set is unavoidable, then you can dynamically construct the pivot query. There is a recent thread regarding how to do this.
There are more than 200 threads per day. It would be nice if you posted the URL for the "recent" thread. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2013 at 8:02 am
Thanks for your answers so far.
As regards my front end, I have to use the reporting facilities of CRM (2011). I doubt its front end reporting is capable of extracting the contents of rows to transpose these into columns. So I thought of generating the data as I wanted it to be printed (Yes, I know SQL's mission is NOT presentation/formatting although this can be done not very effectively).
Jeff:
I was actually hoping you would look at this. " I have already done the hard part" Hah ! For me this was the easy part !
I just started on the information at the url you provided me. Holy cow, I already handled steps 1 to 6 on my own the same way you came up with. And yes, the first rows with null fk were the original data. The non null fx rows were generated from the source and I did not really see a point in using a detail table - Maybe there is ?
I still have to read past that, but my difficulty that since the data is based on a date range sent as parameters and I want the "transposed" column names to be each MonthYears belonging to the date range, I just could not know in advance - when coding the stored proc - what these names would be. And a few examples provided by Microsoft called for specifically knowing the column names.
By the way, I started out with a stored proc because I wanted to print intermediate results for debuggin. This is why I did not use a table-value function.
I' ll get back to report on whether or not I succeeded or for additional information request.
Thank you all.
April 11, 2013 at 8:02 pm
I'd have probably put the details in a separate table so I could just truncate the table for reuse if I needed to.
The rest took a bit of time to type but it's pretty easy if you follow the same steps that I laid out in the article. Of course, comments are in the code. Let me know if you have any questions. And thanks for the great test code setup you did. It made it a whole lot easier to help you in may areas without me having to ask a single question. Very well done.
CREATE PROCEDURE dbo.InvoiceReport
/**********************************************************************************************************************
Purpose:
Given a valid start and end date, aggregate and pivot invoice details by whole month.
Usage Example:
EXEC dbo.InvoiceReport 'Jun 2012','Feb 2013' --Debug mode is OFF. Report is displayed if no errors.
EXEC dbo.InvoiceReport 'Jun 2012','Feb 2013', 1 --Debug mode is ON. The Dynamic SQL is displayed.
Revision History:
Rev 00 - 11 Apr 2013 - Jeff Moden
- Initial creation and unit test.
**********************************************************************************************************************/
--===== Declare the parameters for this stored procedure
@pStartMonth DATETIME
, @pEndMonth DATETIME
, @pDebug BIT = 0
AS
--===== Suppress the auto-display of rowcounts to prevent the rowcounts from causing false errors if a GUI is used
-- and to cleanup the display if a GUI is not used.
SET NOCOUNT ON
;
--===== Local Variables
DECLARE @SQL VARCHAR(MAX)
;
--===== Create the static part of the SELECT list
SELECT @SQL = '
SELECT [Date] = s.InvDate
, [Invoice #] = s.InvNo
, [Client] = s.Client
, [Amount] = s.Amount'
;
--===== Create the dynamic part of the SELECT list
WITH
cteSequenceMonth AS
( --=== This creates a sequence of month dates based on the input parameters
SELECT SequenceMonth = DATEADD(mm,t.N-1,@pStartMonth),
t.N
FROM dbo.Tally t
WHERE t.N <= DATEDIFF(mm,@pStartMonth,@pEndMonth)+1
),
cteMonth AS
( --=== Create the display and comparison month strings for replacement values in the dynamic SQL.
SELECT DisplayMonth = QUOTENAME(REPLACE(SUBSTRING(CONVERT(VARCHAR(15),SequenceMonth,6),4,15),' ','-'))
, CharMonth = CONVERT(CHAR(6),DATEPART(yy,SequenceMonth)*100 + DATEPART(mm,SequenceMonth))
, N
FROM cteSequenceMonth
) --=== Create the dynamic pivot SQL
SELECT @SQL = @SQL
+ REPLACE(
REPLACE('
, <<DisplayMonth>> = SUM(CASE WHEN s.PaymentMonthYear = <<CharMonth>> THEN s.Month_Amount ELSE 0 END)'
, '<<DisplayMonth>>',DisplayMonth)
, '<<CharMonth>>' ,CharMonth)
FROM cteMonth
ORDER BY N
;
--===== Create the from clause using the input parameters to control the data returned.
SELECT @SQL = @SQL
+ REPLACE(
REPLACE('
, [Total] = SUM(s.Month_Amount)
FROM dbo.Source s
WHERE s.PaymentMonthYear BETWEEN <<StartMonth>> AND <<EndMonth>> --Ignores NULLs, as well
GROUP BY s.InvDate, s.InvNo, s.Client, s.Amount
ORDER BY s.InvDate, s.InvNo
;'
,'<<StartMonth>>',CONVERT(CHAR(6),DATEPART(yy,@pStartMonth)*100 + DATEPART(mm,@pStartMonth)))
,'<<EndMonth>>' ,CONVERT(CHAR(6),DATEPART(yy,@pEndMonth) *100 + DATEPART(mm,@pEndMonth)))
;
--===== If @pDebug is ON, display the Dynamic SQL. Otherwise, execute the dynamic SQL
IF @pDebug = 1 PRINT @sql;
ELSE EXEC (@SQL);
;
Notice that there's zero chance for SQL Injection here because the input parameters are NOT character based.
I also use REPLACE(@SQL,'<<SomeToken>>',SomeString) to make the dynamic SQL easier to read and troubleshoot instead of messing around with breaking the string with concatenation and the like.
{EDIT}
If you don't have a Tally Table that starts at "1" or maybe don't even know what it is, now's a good time to learn about it. Please see the following article for what a Tally Table is and how it can replace certain WHILE loops. Think of it as a Jedi Knight building his own Light-Sabre.
http://www.sqlservercentral.com/articles/T-SQL/62867/
I also presumed to take the next logical step of providing a total for each row.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 12, 2013 at 9:16 am
Thank you so much for your help Jeff.
And yes, I was already aware of the Tally table. I must be one of your greatest fans.
Regards
April 12, 2013 at 5:37 pm
j-1064772 (4/12/2013)
Thank you so much for your help Jeff.And yes, I was already aware of the Tally table. I must be one of your greatest fans.
Regards
Thank YOU! :blush: I really appreciate the feedback especially about the Tally Table.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply