September 12, 2011 at 10:11 am
Hi All, Can anyone help me with a date issue in T-SQL (SQL 2008).
I have a table of contractors (tblContactor), a table of linked invoices (tblInvoice), and a lookup table of Invoice Frequencies (tblInvoiceFrequency)...
The Invoice Frquency table is hardcoded and looks like this...
intFrequencyID, strFrequencyDescriptor
eg...
1, Day(s)
2, Week(s)
3, Month(s)
4, Day Of Week
5, Day Of Month
tblContractor
intContractorID, strName, SelfBillingFrequencyUnits, SelfBillingFrequencyDescriptorID
eg
1, Fred, 14, 1 --14, 1 = invoice every 14 days --
2, John, 3, 2 -- 3, 2 = invoice every 3 weeks --
3, Alan, 2, 3 -- 2, 3 = invoice every 2 months --
4, Sue, 3, 4 -- 3, 4 = invoice on 3rd day of week --
5, James, 2, 5 -- 2, 5 = invoice on 2nd day of month --
tblInvoice
intInvoiceID, intContractorID, dtInvoiceDate
eg,
1, 1, '2011-01-01 10:10:10'
1, 1, '2011-01-02 08:32:01'
1, 1, '2011-01-02 08:32:01'
1, 1, '2011-01-03 14:11:45'
1, 2, '2011-01-02 08:31:33'
1, 2, '2011-01-03 16:01:22'
I need to calculate when the next invoice date is due for each contractor. To do this I get the maximum last date of any linked invoices then I need to calculate when the next date will occur.
I can easily get the last date using the code shown below. I struggling with the next date calculation.
SELECTA.[intContractorID],
A.[strName],
XX.[LastInvoiceDate]
-- Next Date in Here --
FROM [tblContractor]
LEFT OUTER JOIN (SELECT A.[intContractorID], MAX(ISNULL(A.[InvoiceDate], '1900-01-01')) AS [LastInvoiceDate]
FROM [tblSelfBillingInvoice] A
GROUP BY A.[intContractorID]) XX ON A.[intContractorID] = XX.[intContractorID]
Can anyone help me?
Many Thanks
Charlotte CB
September 12, 2011 at 10:30 am
I'd use a calendar table for this.
Here's mine http://www.sqlservercentral.com/Forums/Attachment8839.aspx
You can edit out the filegroup, I don't put mine on default.
From there on out it's just a matter of using the calendar to join by adding months or whatknot.
Let me know if you need more help.
September 12, 2011 at 12:11 pm
Hi, thanks for the calendar code, however, I'm not sure how this will help me.
I've got it working I think using a nasty Case statement.
I would welcome comments on this - can it be improved?
CASE
WHEN A.[SelfBillingFrequency] = 0 THEN NULL
WHEN ISNULL(XX.[LastInvoiceDate], '1900-01-01') = '1900-01-01' THEN
CASE ISNULL(A.[SelfBillingFrequencyDescriptorID], 0)
WHEN 1 THEN @CurrentDate -- X Days - No Last Date so invoice due today --
WHEN 2 THEN@CurrentDate -- X Weeks - No Last Date so invoice due today --
WHEN 3 THEN @CurrentDate -- X Months - No Last Date so invoice due today --
WHEN 4 THENCASE
WHEN DATEPART(DW, @CurrentDate) = A.[SelfBillingFrequency] THEN @CurrentDate --'Day Of Week'
ELSE CASE
WHEN DATEPART(DW, @CurrentDate) < A.[SelfBillingFrequency] THEN DATEADD(DAY, A.[SelfBillingFrequency] - DATEPART(DW, @CurrentDate), @CurrentDate)
ELSE DATEADD(DAY, (7 - DATEPART(DW, @CurrentDate)) + A.[SelfBillingFrequency], @CurrentDate)
END
END
WHEN 5 THENCASE
WHEN DATEPART(DD, @CurrentDate) = A.[SelfBillingFrequency] THEN @CurrentDate --'Day Of Month'
ELSE CASE
WHEN DATEPART(DD, @CurrentDate) < A.[SelfBillingFrequency] THEN CAST( CAST(DATEPART(YY, @CurrentDate)AS VARCHAR(10)) + '-' + + CAST(DATEPART(MM, @CurrentDate) AS VARCHAR(10)) + '-' + CAST(A.[SelfBillingFrequency] AS VARCHAR(10)) AS DATETIME)
ELSE -- Need to move forward a month and possibly a year --
CASE
WHEN DATEPART(MM, @CurrentDate) < 12 THEN CAST( CAST(DATEPART(YY, @CurrentDate)AS VARCHAR(10)) + '-' + + CAST(DATEPART(MM, @CurrentDate) + 1 AS VARCHAR(10)) + '-' + CAST(A.[SelfBillingFrequency] AS VARCHAR(10)) AS DATETIME) -- Move forward a month --
ELSE CAST( CAST(DATEPART(YY, @CurrentDate) + 1 AS VARCHAR(10)) + '-01-' + CAST(A.[SelfBillingFrequency] AS VARCHAR(10)) AS DATETIME) -- Move forward a year and put month to Jan --
END
END
END
ELSE NULL
END
ELSE
CASE ISNULL(A.[SelfBillingFrequencyDescriptorID], 0)
WHEN 1 THEN DATEADD(DD, ISNULL(A.[SelfBillingFrequency], 0), XX.[LastInvoiceDate]) -- X Days -No Last Date so invoice due today --
WHEN 2 THENDATEADD(DD, ISNULL(A.[SelfBillingFrequency], 0) * 7, XX.[LastInvoiceDate]) -- X Weeks - No Last Date so invoice due today --
WHEN 3 THEN DATEADD(MM, ISNULL(A.[SelfBillingFrequency], 0), XX.[LastInvoiceDate]) -- X Months - No Last Date so invoice due today --
WHEN 4 THENCASE
WHEN DATEPART(DW, XX.[LastInvoiceDate]) = A.[SelfBillingFrequency] THEN DATEADD(DAY, 7, XX.[LastInvoiceDate])--'Day Of Week'
ELSE CASE
WHEN DATEPART(DW, XX.[LastInvoiceDate]) < A.[SelfBillingFrequency] THEN DATEADD(DAY, A.[SelfBillingFrequency] - DATEPART(DW, XX.[LastInvoiceDate]), XX.[LastInvoiceDate])
ELSE DATEADD(DAY, (7 - DATEPART(DW, XX.[LastInvoiceDate])) + A.[SelfBillingFrequency], XX.[LastInvoiceDate])
END
END
WHEN 5 THENCASE
WHEN DATEPART(DD, XX.[LastInvoiceDate]) = A.[SelfBillingFrequency] THEN XX.[LastInvoiceDate] --'Day Of Month'
ELSE CASE
WHEN DATEPART(DD, XX.[LastInvoiceDate]) < A.[SelfBillingFrequency] THEN CAST( CAST(DATEPART(YY, XX.[LastInvoiceDate])AS VARCHAR(10)) + '-' + + CAST(DATEPART(MM, XX.[LastInvoiceDate]) AS VARCHAR(10)) + '-' + CAST(A.[SelfBillingFrequency] AS VARCHAR(10)) AS DATETIME)
ELSE -- Need to move forward a month and possibly a year --
CASE
WHEN DATEPART(MM, XX.[LastInvoiceDate]) < 12 THEN CAST( CAST(DATEPART(YY, XX.[LastInvoiceDate])AS VARCHAR(10)) + '-' + + CAST(DATEPART(MM, XX.[LastInvoiceDate]) + 1 AS VARCHAR(10)) + '-' + CAST(A.[SelfBillingFrequency] AS VARCHAR(10)) AS DATETIME) -- Move forward a month --
ELSE CAST( CAST(DATEPART(YY, XX.[LastInvoiceDate]) + 1 AS VARCHAR(10)) + '-01-' + CAST(A.[SelfBillingFrequency] AS VARCHAR(10)) AS DATETIME) -- Move forward a year and put month to Jan --
END
END
END
ELSE NULL
END
END AS [NextInvoiceDate],
Thanks
September 12, 2011 at 3:25 pm
I can't code this for you now.
The way you use the calendar table is to use each column as a group to jump timezones with.
Something like
Assumes billing cycle is on the 15th of every month.
SELECT TOP 1 dt FROM dbo.Calendar where d = 15 AND dt >= @LastBill ORDER by dt
Every monday
SELECT TOP 1 dt FROM dbo.Calendar WHERE dw = 1 (or 2, not sure) AND dt >= @LastBill ORDER BY dt.
Remove top 1 to get all further billings.
With that you can just do 3 -4 outer applies and everything's done with very little code.
Of course now you can get tricky... ignore holidays, week-ends, bill only on full moon, etc.
September 13, 2011 at 2:19 am
Ahh, I'm with you now - yes, that's very clever and a lot simpler to maintain in the future. I'll have a go implementing it your way.
Many thanks.:-)
September 14, 2011 at 5:00 am
vilonel (9/14/2011)
@Ninja's_RGR'us - this will help me - great work - thanks
2+ birds, 1 stone, HTH :-).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply