October 9, 2011 at 6:45 pm
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
Change is inevitable... Change for the better is not.
October 10, 2011 at 12:47 am
J Livingston SQL (10/9/2011)
different approach....may be give you some ideaspls note ...date range vailidity needs scrutiny
comments in code
USE [tempdb] --- a safe placeGO
--== 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
October 10, 2011 at 5:22 am
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
October 10, 2011 at 8:28 am
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
October 10, 2011 at 12:38 pm
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
October 10, 2011 at 12:55 pm
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
October 10, 2011 at 1:10 pm
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
October 10, 2011 at 1:32 pm
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
October 12, 2011 at 11:55 am
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