Aging Payments query help

  • Tom,

    Take a look at the following article so you can understand how "J Livingston SQL's" method works... it' quite effective.

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • J Livingston SQL (10/9/2011)


    different approach....may be give you some ideas

    pls note ...date range vailidity needs scrutiny

    comments in code

    USE [tempdb] --- a safe place

    GO

    --== condtionally drop table for retesting in SSMS

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_invoices]') AND type in (N'U'))

    DROP TABLE [dbo].[tbl_invoices]

    GO

    CREATE TABLE [dbo].[tbl_invoices](

    [InvoiceNumber] [int] IDENTITY(1,1) NOT NULL,

    [InvoiceDate] [date] NOT NULL,

    [PaidDate] [date] NULL,

    [IsPaid] [bit] NOT NULL

    ) ON [PRIMARY]

    GO

    SET IDENTITY_INSERT [dbo].[tbl_invoices] ON;

    INSERT INTO [dbo].[tbl_invoices]([InvoiceNumber], [InvoiceDate], [PaidDate], [IsPaid])

    SELECT 1, '20110614 00:00:00.000', '20110720 00:00:00.000', 1 UNION ALL

    SELECT 2, '20110617 00:00:00.000', '20110802 00:00:00.000', 1 UNION ALL

    SELECT 3, '20110618 00:00:00.000', '20110901 00:00:00.000', 1 UNION ALL

    SELECT 4, '20110710 00:00:00.000', '20110901 00:00:00.000', 1 UNION ALL

    SELECT 5, '20110730 00:00:00.000', NULL, 0 UNION ALL

    SELECT 6, '20110730 00:00:00.000', '20110815 00:00:00.000', 1 UNION ALL

    SELECT 7, '20110801 00:00:00.000', NULL, 0 UNION ALL

    SELECT 8, '20110821 00:00:00.000', NULL, 0 UNION ALL

    SELECT 9, '20110830 00:00:00.000', NULL, 0

    SET IDENTITY_INSERT [dbo].[tbl_invoices] OFF;

    --- the following add cols and update could be added into the results select statement

    --- but for clarity of thought I have added them as a separate process

    --== add a couple of new columns

    ALTER TABLE dbo.tbl_invoices ADD

    PayDueDate datetime NULL,

    PayInTerms varchar (1) NULL

    GO

    --== update columns

    UPDATE tbl_invoices

    SET PayDueDate = dateadd(d,45,InvoiceDate),

    PayInTerms = CASE when PaidDate < dateadd(d,45,InvoiceDate) then 'Y' ELSE 'N' END

    ---results

    DECLARE @TD DATETIME; --- not necessary, used for testing, replace with GETDATE() in code below

    SET @TD = Getdate(); --- see above

    --- I have not had time to check the validity of the dates...but hopefully you will get the gist of what this is about.

    --- ND...dependent on your data volume , pls check execution plan and create required indexes.

    SELECT SUM(CASE

    WHEN PayDueDate > ( Dateadd(MM, Datediff(MM, 0, @TD), 0) - 1 )

    AND PayDueDate < ( Getdate() )

    THEN 1

    ELSE 0

    END) AS CURMTH,

    SUM(CASE

    WHEN PayDueDate > ( Dateadd(MM, Datediff(MM, 0, @TD) - 1, 0) - 1 )

    AND PayDueDate < ( Dateadd(MM, Datediff(MM, 0, @TD), 0) )

    THEN 1

    ELSE 0

    END) AS MTHMINUS1,

    SUM(CASE

    WHEN PayDueDate > ( Dateadd(MM, Datediff(MM, 0, @TD) - 2, 0) - 1 )

    AND PayDueDate < ( Dateadd(MM, Datediff(MM, 0, @TD) - 1, 0) )

    THEN 1

    ELSE 0

    END) AS MTHMINUS2,

    SUM(CASE

    WHEN PayDueDate > ( Dateadd(MM, Datediff(MM, 0, @TD) - 3, 0) - 1 )

    AND PayDueDate < ( Dateadd(MM, Datediff(MM, 0, @TD) - 2, 0) )

    THEN 1

    ELSE 0

    END) AS MTHMINUS3

    FROM tbl_invoices

    WHERE PayInTerms = 'N'

    Results are not as expected....maybe will get time later to revisit

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Thanks everybody for the responses. J Livingston SQL's looks the most promising. I'm guessing that I would do that, and then UNPIVOT the resulting row into multiple rows so that it can be put into a chart?

    BUT, one clarification, and this is where I seem to get stuck:

    The result for the current month is MTD (GETDATE()). The count for previous months is for the last day of the month. If an invoice was > 45 days old on Sept 28, and got paid on Sept 29, it doesn't get counted in Sept's total.

    Cheers,

    Tom

  • What if you use the crosstab in a CTE, then do a final sum(case...) in the select statement. Here's a start without all of the logic, but I think you can figure it out from here and eliminate columns once verified:

    --USE tempdb

    --CREATE table #invoices(

    -- InvoiceNumber int IDENTITY(1,1) NOT NULL,

    -- InvoiceDate date NOT NULL,

    -- PaidDate date NULL,

    -- IsPaid bit NOT NULL

    --)

    --INSERT #Invoices (InvoiceDate, PaidDate, IsPaid)

    --VALUES ('2011-06-14', '2011-07-20', 1), -- < 45 days

    -- ('2011-06-17', '2011-08-02', 1), -- > 45 days in July

    -- ('2011-06-18', '2011-09-01', 1), -- > 45 days in July, Aug

    -- ('2011-07-10', '2011-09-01', 1), -- > 45 days in Aug

    -- ('2011-07-30', NULL, 0), -- > 45 days in Sept and Oct MTD

    -- ('2011-07-30', '2011-08-15', 1), -- < 45 days

    -- ('2011-08-01', NULL, 0), -- > 45 days in Sept and Oct MTD

    -- ('2011-08-21',NULL, 0), -- > 45 days in Oct MTD

    -- ('2011-08-30', NULL, 0) -- < 45 days

    ;WITH invoices AS (

    SELECT InvoiceNumber,

    YEAR(InvoiceDate) AS InvoiceYear,

    MONTH(InvoiceDate) AS InvoiceMonth,

    YEAR(PaidDate) AS PaidYear,

    MONTH(PaidDate) AS PaidMonth,

    YEAR(DATEADD(D,45, InvoiceDate)) AS Year45,

    MONTH(DATEADD(D,45, InvoiceDate)) AS Month45,

    DATEADD(D,45, InvoiceDate) Date45Days,

    CASE

    WHEN PaidDate IS NULL THEN DATEDIFF(D,InvoiceDate, GETDATE())

    WHEN PaidDate > InvoiceDate THEN DATEDIFF(D,InvoiceDate, PaidDate)

    WHEN PaidDate < InvoiceDate THEN 0

    ELSE -1

    END AS TotalDaysLate,

    CASE IsPaid

    WHEN 1 THEN 0

    ELSE DATEDIFF(D,InvoiceDate, GETDATE())

    END AS TotalDaysLateToday,

    CASE IsPaid WHEN 0 THEN 1 ELSE 0 END AS CURMONTH ,

    0 MTHMINUS1,/*case logic set to 1 that should count in this month*/

    0 MTHMINUS2,/*case logic set to 1 that should count in this month*/

    0 MTHMINUS3,/*case logic set to 1 that should count in this month*/

    0 MTHMINUS4,/*case logic set to 1 that should count in this month*/

    0 MTHMINUS5,/*case logic set to 1 that should count in this month*/

    0 MTHMINUS6,/*case logic set to 1 that should count in this month*/

    0 MTHMINUS7,/*case logic set to 1 that should count in this month*/

    0 MTHMINUS8,/*case logic set to 1 that should count in this month*/

    0 MTHMINUS9,/*case logic set to 1 that should count in this month*/

    0 MTHMINUS10,/*case logic set to 1 that should count in this month*/

    0 MTHMINUS11/*case logic set to 1 that should count in this month*/

    FROM #invoices

    WHERE PaidDate IS NULL

    OR (PaidDate > DATEADD(D,45, InvoiceDate))

    )

    SELECT * /*include sum(case...) code here once you have above data correct*/

    FROM invoices

    Is this an approach that will work for you?

    EDIT: comments looked wrong so added /* and */

    EDIT: Still didn't work, but you get the idea 🙂

    Jared
    CE - Microsoft

  • Tom Bakerman (10/10/2011)


    BUT, one clarification, and this is where I seem to get stuck:

    The result for the current month is MTD (GETDATE()). The count for previous months is for the last day of the month. If an invoice was > 45 days old on Sept 28, and got paid on Sept 29, it doesn't get counted in Sept's total.

    Cheers,

    Tom

    Tom...I realise that my previous post didnt deliver the necessary solution...but I am questioning the "logic" behind your request.

    If, as I understand from your post above, an invoice was 45 days old on the 1st Sept...but paid on the 30th Sept...you still wish to exclude this from your "retrospective analysis" ...because it was paid in the month of September....even though it was 74 days old when paid...???..

    this debtor has just taken nearly another months worth of credit (and reduced your cashflow) yet you appear to be ignoring this ....or am I barking up the wrong tree?

    debtor reporting should be perfomed periodically, at a point in time (daily,weekly,monthly) and if required the results archived (based on extraction date) so that they can then be analysed over a range of periods to see any trends.

    ...or maybe I am just ol'fashioed when it comes to accounting 😀

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • J Livingston SQL (10/10/2011)


    Tom Bakerman (10/10/2011)


    BUT, one clarification, and this is where I seem to get stuck:

    The result for the current month is MTD (GETDATE()). The count for previous months is for the last day of the month. If an invoice was > 45 days old on Sept 28, and got paid on Sept 29, it doesn't get counted in Sept's total.

    Cheers,

    Tom

    Tom...I realise that my previous post didnt deliver the necessary solution...but I am questioning the "logic" behind your request.

    If, as I understand from your post above, an invoice was 45 days old on the 1st Sept...but paid on the 30th Sept...you still wish to exclude this from your "retrospective analysis" ...because it was paid in the month of September....even though it was 74 days old when paid...???..

    this debtor has just taken nearly another months worth of credit (and reduced your cashflow) yet you appear to be ignoring this ....or am I barking up the wrong tree?

    debtor reporting should be perfomed periodically, at a point in time (daily,weekly,monthly) and if required the results archived (based on extraction date) so that they can then be analysed over a range of periods to see any trends.

    ...or maybe I am just ol'fashioed when it comes to accounting 😀

    The thing is, according to most accounting principals, it was resolved in the month... Therefore, at the end of the month you would not record it as a liability for that month since at the close of the month it was no longer outstanding. Accounting "typically" looks at anything that happened within the month as 1 bucket; i.e. Whether it is paid on the 1st or the 31st of a month, it cleared in that period thereby not being a liabililty for that month.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • jared-709193 (10/10/2011)


    J Livingston SQL (10/10/2011)


    Tom Bakerman (10/10/2011)


    BUT, one clarification, and this is where I seem to get stuck:

    The result for the current month is MTD (GETDATE()). The count for previous months is for the last day of the month. If an invoice was > 45 days old on Sept 28, and got paid on Sept 29, it doesn't get counted in Sept's total.

    Cheers,

    Tom

    Tom...I realise that my previous post didnt deliver the necessary solution...but I am questioning the "logic" behind your request.

    If, as I understand from your post above, an invoice was 45 days old on the 1st Sept...but paid on the 30th Sept...you still wish to exclude this from your "retrospective analysis" ...because it was paid in the month of September....even though it was 74 days old when paid...???..

    this debtor has just taken nearly another months worth of credit (and reduced your cashflow) yet you appear to be ignoring this ....or am I barking up the wrong tree?

    debtor reporting should be perfomed periodically, at a point in time (daily,weekly,monthly) and if required the results archived (based on extraction date) so that they can then be analysed over a range of periods to see any trends.

    ...or maybe I am just ol'fashioed when it comes to accounting 😀

    The thing is, according to most accounting principals, it was resolved in the month... Therefore, at the end of the month you would not record it as a liability for that month since at the close of the month it was no longer outstanding. Accounting "typically" looks at anything that happened within the month as 1 bucket; i.e. Whether it is paid on the 1st or the 31st of a month, it cleared in that period thereby not being a liabililty for that month.

    Thanks,

    Jared

    The other thing is that my application isn't really about invoicing. But, as I mentioned earlier, the company I'm currently at is in stealth mode, and this is a very good analogy that I can use in a public forum.

    You've all been a great help. Keep up the good work. Thanks.

    Tom

  • Tom Bakerman (10/10/2011)


    You've all been a great help. Keep up the good work. Thanks.

    Tom

    Tom..what code have you got so far...can you post please...maybe it will help.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • J Livingston SQL (10/10/2011)


    Tom Bakerman (10/10/2011)


    You've all been a great help. Keep up the good work. Thanks.

    Tom

    Tom..what code have you got so far...can you post please...maybe it will help.

    Ok. Here's what I've come up with (based on my invoicing story. There are more tables here than this post started with). Thanks for all your help. If you see simplifications, I'm all ears.

    With Invoices AS (

    --

    -- Invoice id, type, date created, and if it has been paid, the pay date

    --

    SELECT sd.InvoiceId, sd.InvoiceType, CAST(sd.StartDate AS DATE) StartDate, CAST(pd.PaidDate AS DATE) PaidDate,

    CAST(YEAR(sd.StartDate) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(sd.StartDate) AS NVARCHAR(2)), 2) YYYYMM

    FROM (

    SELECT id.InvoiceId, wt.InvoiceType, MIN(id.InvoiceDate) StartDate

    FROM tbInvoiceDetail id

    JOIN tbInvoices inv

    ON id.InvoiceId = inv.InvoiceId

    JOIN tbInvoiceLocationTypes ilt

    ON inv.InvoiceLocTypeID = ilt.InvoiceLocTypeID

    JOIN tbInvoiceTypes wt

    ON ilt.InvoiceTypeID = wt.InvoiceTypeID

    GROUP BY id.InvoiceId, wt.InvoiceType

    ) sd -- Invoice start date

    LEFT JOIN (

    SELECT InvoiceId, MAX(InvoiceDate) PaidDate

    FROM tbInvoiceDetail

    WHERE IsPaid = 1

    GROUP BY InvoiceId

    ) pd -- Paid date

    ON sd.InvoiceId = pd.InvoiceId

    WHERE pd.PaidDate IS NULL

    OR DATEDIFF(dd, sd.StartDate, pd.PaidDate) > 45

    ),

    -- Current month date information

    mm0 as (SELECT CAST(GETDATE() AS DATE) eom, CAST(YEAR(GETDATE()) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(GETDATE()) AS NVARCHAR(2)), 2) YYYYMM),

    firstofmonth as (SELECT CAST(DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()) AS DATE) mm0),

    --

    -- Calculate the Month Ending dates for the past 11 months

    -- I only need the Year and the Month of the date field, in this format so they'll group and order correctly

    -- When the chart displays, I translate the MM part to the month abbreviation

    --

    mm1 as (SELECT eom, CAST(YEAR(eom) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(eom) AS NVARCHAR(2)), 2) YYYYMM

    FROM (SELECT CAST(DATEADD(DD, -1, (select mm0 from firstofmonth)) AS DATE) eom) qry),

    mm2 as (SELECT eom, CAST(YEAR(eom) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(eom) AS NVARCHAR(2)), 2) YYYYMM

    FROM (SELECT CAST(DATEADD(DD, -1, DATEADD(mm, -1, (select mm0 from firstofmonth))) AS DATE) eom) qry),

    mm3 as (SELECT eom, CAST(YEAR(eom) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(eom) AS NVARCHAR(2)), 2) YYYYMM

    FROM (SELECT CAST(DATEADD(DD, -1, DATEADD(mm, -2, (select mm0 from firstofmonth))) AS DATE) eom) qry),

    mm4 as (SELECT eom, CAST(YEAR(eom) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(eom) AS NVARCHAR(2)), 2) YYYYMM

    FROM (SELECT CAST(DATEADD(DD, -1, DATEADD(mm, -3, (select mm0 from firstofmonth))) AS DATE) eom) qry),

    mm5 as (SELECT eom, CAST(YEAR(eom) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(eom) AS NVARCHAR(2)), 2) YYYYMM

    FROM (SELECT CAST(DATEADD(DD, -1, DATEADD(mm, -4, (select mm0 from firstofmonth))) AS DATE) eom) qry),

    mm6 as (SELECT eom, CAST(YEAR(eom) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(eom) AS NVARCHAR(2)), 2) YYYYMM

    FROM (SELECT CAST(DATEADD(DD, -1, DATEADD(mm, -5, (select mm0 from firstofmonth))) AS DATE) eom) qry),

    mm7 as (SELECT eom, CAST(YEAR(eom) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(eom) AS NVARCHAR(2)), 2) YYYYMM

    FROM (SELECT CAST(DATEADD(DD, -1, DATEADD(mm, -6, (select mm0 from firstofmonth))) AS DATE) eom) qry),

    mm8 as (SELECT eom, CAST(YEAR(eom) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(eom) AS NVARCHAR(2)), 2) YYYYMM

    FROM (SELECT CAST(DATEADD(DD, -1, DATEADD(mm, -7, (select mm0 from firstofmonth))) AS DATE) eom) qry),

    mm9 as (SELECT eom, CAST(YEAR(eom) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(eom) AS NVARCHAR(2)), 2) YYYYMM

    FROM (SELECT CAST(DATEADD(DD, -1, DATEADD(mm, -8, (select mm0 from firstofmonth))) AS DATE) eom) qry),

    mm10 as (SELECT eom, CAST(YEAR(eom) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(eom) AS NVARCHAR(2)), 2) YYYYMM

    FROM (SELECT CAST(DATEADD(DD, -1, DATEADD(mm, -9, (select mm0 from firstofmonth))) AS DATE) eom) qry),

    mm11 as (SELECT eom, CAST(YEAR(eom) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(eom) AS NVARCHAR(2)), 2) YYYYMM

    FROM (SELECT CAST(DATEADD(DD, -1, DATEADD(mm, -10, (select mm0 from firstofmonth))) AS DATE) eom) qry),

    --

    -- Get the raw data

    -- For each invoice, determine if it has a) not been paid, or b) paid after 45 days, in each month period

    --

    tbl as (SELECT InvoiceType, SUM(m0) m0, SUM(m1) m1, SUM(m2) m2, SUM(m3) m3, SUM(m4) m4, SUM(m5) m5, SUM(m6) m6, SUM(m7) m7, SUM(m8) m8, SUM(m9) m9, SUM(m10) m10, SUM(m11) m11

    FROM (

    SELECT InvoiceId, InvoiceType, StartDate, PaidDate,

    CASE WHEN StartDate <= DATEADD(dd, -45, (SELECT eom FROM mm0))

    AND PaidDate IS NULL THEN 1 ELSE 0 END m0,

    CASE WHEN StartDate <= DATEADD(dd, -45, (SELECT eom FROM mm1))

    AND (PaidDate IS NULL

    OR PaidDate > (SELECT eom FROM mm1)) THEN 1 ELSE 0 END m1,

    CASE WHEN StartDate <= DATEADD(dd, -45, (SELECT eom FROM mm2))

    AND (PaidDate IS NULL

    OR PaidDate > (SELECT eom FROM mm2)) THEN 1 ELSE 0 END m2,

    CASE WHEN StartDate <= DATEADD(dd, -45, (SELECT eom FROM mm3))

    AND (PaidDate IS NULL

    OR PaidDate > (SELECT eom FROM mm3)) THEN 1 ELSE 0 END m3,

    CASE WHEN StartDate <= DATEADD(dd, -45, (SELECT eom FROM mm4))

    AND (PaidDate IS NULL

    OR PaidDate > (SELECT eom FROM mm4)) THEN 1 ELSE 0 END m4,

    CASE WHEN StartDate <= DATEADD(dd, -45, (SELECT eom FROM mm5))

    AND (PaidDate IS NULL

    OR PaidDate > (SELECT eom FROM mm5)) THEN 1 ELSE 0 END m5,

    CASE WHEN StartDate <= DATEADD(dd, -45, (SELECT eom FROM mm6))

    AND (PaidDate IS NULL

    OR PaidDate > (SELECT eom FROM mm6)) THEN 1 ELSE 0 END m6,

    CASE WHEN StartDate <= DATEADD(dd, -45, (SELECT eom FROM mm7))

    AND (PaidDate IS NULL

    OR PaidDate > (SELECT eom FROM mm7)) THEN 1 ELSE 0 END m7,

    CASE WHEN StartDate <= DATEADD(dd, -45, (SELECT eom FROM mm8))

    AND (PaidDate IS NULL

    OR PaidDate > (SELECT eom FROM mm8)) THEN 1 ELSE 0 END m8,

    CASE WHEN StartDate <= DATEADD(dd, -45, (SELECT eom FROM mm9))

    AND (PaidDate IS NULL

    OR PaidDate > (SELECT eom FROM mm9)) THEN 1 ELSE 0 END m9,

    CASE WHEN StartDate <= DATEADD(dd, -45, (SELECT eom FROM mm10))

    AND (PaidDate IS NULL

    OR PaidDate > (SELECT eom FROM mm10)) THEN 1 ELSE 0 END m10,

    CASE WHEN StartDate <= DATEADD(dd, -45, (SELECT eom FROM mm11))

    AND (PaidDate IS NULL

    OR PaidDate > (SELECT eom FROM mm11)) THEN 1 ELSE 0 END m11

    FROM Invoices w

    ) qry

    GROUP BY InvoiceType

    )

    --

    -- Get monthly invoice data by invoice type

    --

    SELECT InvoiceType, Mth AS YYYYMM, SUM(Cnt) Cnt

    FROM (

    SELECT InvoiceType,

    CASE Mth WHEN 'm0' THEN (SELECT YYYYMM FROM mm0)

    WHEN 'm1' THEN (SELECT YYYYMM FROM mm1)

    WHEN 'm2' THEN (SELECT YYYYMM FROM mm2)

    WHEN 'm3' THEN (SELECT YYYYMM FROM mm3)

    WHEN 'm4' THEN (SELECT YYYYMM FROM mm4)

    WHEN 'm5' THEN (SELECT YYYYMM FROM mm5)

    WHEN 'm6' THEN (SELECT YYYYMM FROM mm6)

    WHEN 'm7' THEN (SELECT YYYYMM FROM mm7)

    WHEN 'm8' THEN (SELECT YYYYMM FROM mm8)

    WHEN 'm9' THEN (SELECT YYYYMM FROM mm9)

    WHEN 'm10' THEN (SELECT YYYYMM FROM mm10)

    WHEN 'm11' THEN (SELECT YYYYMM FROM mm11)

    END AS

    Mth,

    Cnt

    FROM tbInvoiceTypes wt

    LEFT JOIN (

    SELECT InvoiceType, Mth, Cnt

    FROM tbl

    UNPIVOT (Cnt FOR Mth IN (m0, m1, m2, m3, m4, m5, m6, m7, m8, m9, m10, m11)) AS unpvt

    ) qry

    ON wt.InvoiceType = qry.InvoiceType

    ) qry

    GROUP BY InvoiceType, Mth

    UNION

    --

    -- Get the total per month as another series for the chart

    --

    SELECT 'Total', Mth AS YYYYMM, SUM(Cnt) Cnt

    FROM (

    SELECT InvoiceType,

    CASE Mth WHEN 'm0' THEN (SELECT YYYYMM FROM mm0)

    WHEN 'm1' THEN (SELECT YYYYMM FROM mm1)

    WHEN 'm2' THEN (SELECT YYYYMM FROM mm2)

    WHEN 'm3' THEN (SELECT YYYYMM FROM mm3)

    WHEN 'm4' THEN (SELECT YYYYMM FROM mm4)

    WHEN 'm5' THEN (SELECT YYYYMM FROM mm5)

    WHEN 'm6' THEN (SELECT YYYYMM FROM mm6)

    WHEN 'm7' THEN (SELECT YYYYMM FROM mm7)

    WHEN 'm8' THEN (SELECT YYYYMM FROM mm8)

    WHEN 'm9' THEN (SELECT YYYYMM FROM mm9)

    WHEN 'm10' THEN (SELECT YYYYMM FROM mm10)

    WHEN 'm11' THEN (SELECT YYYYMM FROM mm11)

    END AS

    Mth,

    Cnt

    FROM tbInvoiceTypes wt

    LEFT JOIN (

    SELECT InvoiceType, Mth, Cnt

    FROM tbl

    UNPIVOT (Cnt FOR Mth IN (m0, m1, m2, m3, m4, m5, m6, m7, m8, m9, m10, m11)) AS unpvt

    ) qry

    ON wt.InvoiceType = qry.InvoiceType

    ) qry

    GROUP BY Mth

    ORDER BY InvoiceType, Mth

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply