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 2013 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
Aucun commentaire:
Enregistrer un commentaire