CODEWITHSUNDEEP
sqlviewreport
Matt Lightbourn
Matt Lightbourn

Reputation: 597

How to report service recognition per month from single year record

I want to view an amount per month from an invoice which has a money value which is for services where serviceFromDate and serviceToDate are for an entire year.

Let's say I issue an invoice on the 2015-10-16 and it has a lineItem which has the following information:

lineItemDescription: product subscription
customerRetailPrice: 4000.00
serviceFromDate:     2015-10-16
serviceToDate:       2016-10-15
invoiceNumber:       NZ-416

So what I want to get is the service recognition which is to equate the revenue per month (accurate to days) across the entire year.

What I would expect is the following as either columns or individual rows (not sure which is easier)

yearMonth:           2015-10
daysofService:       15       // days diff between 16th and EOM
serviceRevenue:      164.38   // ($4000/365=10.96) * 15
lineItemDescription: product subscription
invoiceNumber:       NZ-416

yearMonth:           2016-10
daysofService:       15       // days left in last month
serviceRevenue:      328.76   // ($4000/365=10.96) * 15
lineItemDescription: product subscription
invoiceNumber:       NZ-416

... each month value for the year based upon days per month

yearMonth:           2015-11
daysofService:       30       // days of full month November
serviceRevenue:      164.38   // ($4000/365=10.96) * 30
lineItemDescription: product subscription
invoiceNumber:       NZ-416

The view would be to show all invoices generated this month so I guess it would be great to have one row group by invoiceNumber and columns auto generated for each month from current month up to as many months as required to max serviceTo yearMonth. Some of the invoices will only be for the current or three month period and so would have NULL for other month columns.

I can do what I want in Excel and the report result is shown below, thanks enter image description here

Upvotes: 2

Views: 76

Answers (1)

Tom H
Tom H

Reputation: 47464

Your best bet (IMO) is to make sure that your database has a Calendar table. You can then JOIN on this table for requirements like this one and make these tasks much simpler. For example:

SELECT
    CAST(YEAR(C.calendar_date) AS CHAR(4)) + '-' + RIGHT('0' + CAST(MONTH(C.calendar_date) AS VARCHAR(2)), 2) AS yearMonth,
    COUNT(*) AS daysofService,
    T.customerRetailPrice/(DATEDIFF(DY, serviceFromDate, serviceToDate) * COUNT(*)) AS serviceRevenue,
    T.lineItemDescription,
    T.invoiceNumber
FROM
    My_Table T
INNER JOIN Calendar C ON C.calendar_date BETWEEN T.serviceFromDate AND T.serviceToDate
GROUP BY
    CAST(YEAR(C.calendar_date) AS CHAR(4)) + '-' + RIGHT('0' + CAST(MONTH(C.calendar_date) AS VARCHAR(2)), 2),
    T.customerRetailPrice,
    T.lineItemDescription,
    T.invoiceNumber

I haven't tested this code, so it might need some tweaking, but it should get you pretty close.

The Calendar table is just a table that has an entry in it for every day. It's pretty easy to generate rows for it that extend 100 years into the future (or more if you need it). You can then add columns for things like business_quarter, is_holiday, etc.

Here's an example of what a Calendar table might look like. You can add whatever other columns you might need that are specific to your own business/application(s):

CREATE TABLE Calendar (
    calendar_date    DATE    NOT NULL,
    is_holiday       BIT     NOT NULL DEFAULT (0),
    quarter          CHAR(2) NOT NULL CHECK quarter IN ('Q1', 'Q2', 'Q3', 'Q4'),    
    CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (calendar_date)
)

Upvotes: 1

Related Questions