CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Turtleman10
Turtleman10

Reputation: 119

Createing a report using financial periods

I have created a report for management that will total everything up by month with in a date range. Management has now decided that rather than by month they would like to go by period. We have 13 periods in a year each is 28 days except the last one is 29 or 30 depending on if its a leap year. The beginning of the first period is always 1-1-YYYY. So now I will need to figure out what the beginning and end of each period is and total up each period. I am not really sure how to do this since every year the dates will change and they may want to look at periods from the previous year through the current period. The code and results I am currently using are enclosed

SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, finspecteddate), 0) AS 'Date' 
    ,COUNT(*) AS Lots
    ,sum(flotSize) as 'Lot Size'
    ,sum(LReject) 'Lots Rejected'
    ,sum(fnumreject) as Rejected
    ,sum(fsampleSize) as 'Sample Size'
    ,sum(BDueDate) as 'Before Due Date'
FROM 
    ReportData
WHERE 
    finspecteddate >= '01-01-2014' 
    AND finspecteddate <= '10-15-2014' 
GROUP BY 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, finspecteddate), 0) 
ORDER BY 
    date

enter image description here

Upvotes: 0

Views: 274

Answers (2)

Code Different
Code Different

Reputation: 93161

Modify the following queries to suit your needs:

;WITH Period AS (
    SELECT      1 AS ReportingPeriod,
                CAST('2013-01-01' AS datetime) AS PeriodStartDate,
                CAST('2013-01-28' AS datetime) AS PeriodEndDate
    UNION ALL
    SELECT      CASE
                    WHEN p.ReportingPeriod = 13 THEN 1
                    ELSE p.ReportingPeriod + 1
                END,
                CASE
                    WHEN p.ReportingPeriod = 13 THEN DATEADD(YEAR,YEAR(p.PeriodStartDate)-1899,'1900-01-01')
                    ELSE DATEADD(DAY,28,p.PeriodStartDate)
                END,
                CASE
                    WHEN p.ReportingPeriod = 12 THEN DATEADD(YEAR,YEAR(p.PeriodStartDate)-1900,'1900-12-31')
                    ELSE DATEADD(DAY,28,p.PeriodEndDate)
                END
    FROM        Period p
    WHERE       p.PeriodStartDate < '2017-12-03'
)

SELECT 
    P.PeriodStartDate
    ,P.PeriodEndDate
    ,COUNT(*) AS Lots
    ,sum(flotSize) as 'Lot Size'
    ,sum(LReject) 'Lots Rejected'
    ,sum(fnumreject) as Rejected
    ,sum(fsampleSize) as 'Sample Size'
    ,sum(BDueDate) as 'Before Due Date'
FROM 
    ReportData  R
INNER JOIN Period P ON R.finspecteddate >= P.PeriodStartDate AND R.finspecteddate <= P.PeriodEndDate
WHERE 
    finspecteddate >= '01-01-2014' 
    AND finspecteddate <= '10-15-2014' 
GROUP BY 
    P.PeriodStartDate
    ,P.PeriodEndDate
ORDER BY 
    P.PeriodStartDate

It uses a recursive CTE to build a period table, which is then joined to ReportData to aggregate asccording to your requirements. I don't have SQL Server 2005 to test it on. It works with 2008. Post a SQL Fiddle if you need help in 2005.

Upvotes: 1

Bacon Bits
Bacon Bits

Reputation: 32170

If you haven't got one, create a period calendar table with a year, period number, start date and end date columns. Then when you need to refer to periods, you can refer to the table. When they change the definition of what a period is, you can change the table. When they decide that February 29 doesn't count as one of the 28 days, you can change the table. When they decide to use the first Monday instead of the first Thursday as the start of the year, you just change the table. And best of all, changing how next year works won't change how last year works.

Then you just join to the table to determine which period you're in.

Upvotes: 0

Related Questions