JamonTerrell
Programmer
Please Copy/Paste this question into NOTEPAD or a word processor with fixed-width fonts if you have problems seeing the table lines, etc.
[tt]
table 'service':
+----------------+-------------+--------+
| effdate | status | units |
+----------------+-------------+--------+
| 2002-11-28 | APPROVED | 118 |
| 2003-01-05 | APPROVED | 13 |
| 2003-02-05 | APPROVED | 45 |
| 2003-02-06 | APPROVED | 11 |
| 2003-02-15 | DENIED | 48 |
+----------------+-------------+--------+
Goal:
+--------------+--------- ---+-------+---------------+
| Month | Approved | Total | DuringMonth |
+--------------+-------------+-------+---------------+
| 2003-01-01 | 13 | 13 | 44 |
| 2003-02-01 | 56 | 104 | 62 |
| 2003-03-01 | 0 | 0 | 26 |
| 2003-04-01 | 0 | 0 | 0 |
| 2003-05-01 | 0 | 0 | 0 |
+--------------+-------------+-------+---------------+
[/tt]
While the Approved and Total Columns are ultra-simple,
it's the "DuringMonth" column that renders a headache.
Two notes to keep in mind are:
Units = Days.
DuringMonth Days ONLY include Approved days that actually
took place during the month (you'll understand later,
just remember not to include DENIED days).
During the Month of 2003-01, 13 days were approved, 0 denied, 13 total.
"DuringMonth" should be 44, because the entry from 2002-11-28 continues
until 2003-03-26, encompassing all 31 days in January. All of the days
approved as of 2003-01-05 occured during 2003-01, so: 31+13=44.
During the Month of 2003-02, 45 and 11 days were approved, 48 denied.
45+11=56 approved, + 48 denied = 104 total. DuringMonth should be 62.
The days from 2002-11-28 are still going on for the entirity of February,
so that's 28 days. Of the newly APPROVED days from 02-05, 23 occured in
February, and of the newly approved days from 02-06, all 11 occured
in February. 28+23+11=62 DuringMonth days for February.
During the month of 2003-03, no new days were logged. 0 Approved,
0 Total. However, 26 days are left over from the 2002-11-28 entry.
That means a total of 26 DuringMonth days for 2003-03.
No more days are logged during 2003-04 and 2003-05, and no days are
left to carry over, thus all values are 0.
The following procedure will calculate the correct values (or at least did prior
to my sanitizing and simplifying this to only encompass the 'issue', so it might
have a small error here and there but the methodology works).
That leaves the question, why not just use the code you've attached? Because it
returns one query per month, not one row per month inside one query. That means
I'd have to create a table and insert each row into it in order to run the report,
which I would prefer not to do, because the query is already inefficient.
What I would like to see, is a clean, efficient version of the code I already have.
I don't need it to be a stored procedure, if you can create it as a view or query,
that would be fine as well. Just no table creation unless there is no other
reasonable way to do it.
Have fun, and thanks in advance!
--Jamon
[tt]
CREATE PROC my_procedure @STARTDATE datetime, @ENDDATE datetime AS
DECLARE @CM datetime @FDNM datetime, @ED datetime, @TD datetime
SET @CM = FirstDayOfMonth(@STARTDATE)
WHILE @CM < @ENDDATE BEGIN
SET @FDNM = DATEADD(m,1,@CM)
SELECT Months.Month AS 'Month',
service_appr.ApprovedUnits,
service_total.TotalUnits,
service_DaysInMonth.DaysInMonth
FROM (SELECT @CM as 'Month') AS Months
LEFT JOIN (SELECT FirstDayOfMonth(effdate) AS Month, SUM(units) AS ApprovedUnits FROM services WHERE status='APPROVED' GROUP BY FirstDayOfMonth(effdate) AS service_appr ON Months.Month = service_appr.Month
LEFT JOIN (SELECT FirstDayOfMonth(effdate) AS Month, SUM(units) AS ApprovedUnits FROM services GROUP BY FirstDayOfMonth(effdate) AS service_total ON Months.Month = service_total.Month
LEFT JOIN (SELECT SUM(
CASE
WHEN effdate < @CM AND effdate+units >= @CM THEN
CASE
WHEN effdate+units < @FDNM THEN DATEDIFF(d,@CM,effdate+units)
ELSE DATEDIFF(d,@CM,@FDNM)
END
WHEN effdate >= @CM AND effdate < @FDNM THEN
CASE
WHEN effdate+units < @FDNM THEN units
ELSE DATEDIFF(d,effdate,@FDNM)
END
ELSE 0
END) AS 'DaysInMonth'
FROM service
WHERE status='APPROVED'
) AS DaysInMonth ON 1=1
SET @CM = DATEADD(m,1,@CM)
END
[/tt]
[tt]
table 'service':
+----------------+-------------+--------+
| effdate | status | units |
+----------------+-------------+--------+
| 2002-11-28 | APPROVED | 118 |
| 2003-01-05 | APPROVED | 13 |
| 2003-02-05 | APPROVED | 45 |
| 2003-02-06 | APPROVED | 11 |
| 2003-02-15 | DENIED | 48 |
+----------------+-------------+--------+
Goal:
+--------------+--------- ---+-------+---------------+
| Month | Approved | Total | DuringMonth |
+--------------+-------------+-------+---------------+
| 2003-01-01 | 13 | 13 | 44 |
| 2003-02-01 | 56 | 104 | 62 |
| 2003-03-01 | 0 | 0 | 26 |
| 2003-04-01 | 0 | 0 | 0 |
| 2003-05-01 | 0 | 0 | 0 |
+--------------+-------------+-------+---------------+
[/tt]
While the Approved and Total Columns are ultra-simple,
it's the "DuringMonth" column that renders a headache.
Two notes to keep in mind are:
Units = Days.
DuringMonth Days ONLY include Approved days that actually
took place during the month (you'll understand later,
just remember not to include DENIED days).
During the Month of 2003-01, 13 days were approved, 0 denied, 13 total.
"DuringMonth" should be 44, because the entry from 2002-11-28 continues
until 2003-03-26, encompassing all 31 days in January. All of the days
approved as of 2003-01-05 occured during 2003-01, so: 31+13=44.
During the Month of 2003-02, 45 and 11 days were approved, 48 denied.
45+11=56 approved, + 48 denied = 104 total. DuringMonth should be 62.
The days from 2002-11-28 are still going on for the entirity of February,
so that's 28 days. Of the newly APPROVED days from 02-05, 23 occured in
February, and of the newly approved days from 02-06, all 11 occured
in February. 28+23+11=62 DuringMonth days for February.
During the month of 2003-03, no new days were logged. 0 Approved,
0 Total. However, 26 days are left over from the 2002-11-28 entry.
That means a total of 26 DuringMonth days for 2003-03.
No more days are logged during 2003-04 and 2003-05, and no days are
left to carry over, thus all values are 0.
The following procedure will calculate the correct values (or at least did prior
to my sanitizing and simplifying this to only encompass the 'issue', so it might
have a small error here and there but the methodology works).
That leaves the question, why not just use the code you've attached? Because it
returns one query per month, not one row per month inside one query. That means
I'd have to create a table and insert each row into it in order to run the report,
which I would prefer not to do, because the query is already inefficient.
What I would like to see, is a clean, efficient version of the code I already have.
I don't need it to be a stored procedure, if you can create it as a view or query,
that would be fine as well. Just no table creation unless there is no other
reasonable way to do it.
Have fun, and thanks in advance!
--Jamon
[tt]
CREATE PROC my_procedure @STARTDATE datetime, @ENDDATE datetime AS
DECLARE @CM datetime @FDNM datetime, @ED datetime, @TD datetime
SET @CM = FirstDayOfMonth(@STARTDATE)
WHILE @CM < @ENDDATE BEGIN
SET @FDNM = DATEADD(m,1,@CM)
SELECT Months.Month AS 'Month',
service_appr.ApprovedUnits,
service_total.TotalUnits,
service_DaysInMonth.DaysInMonth
FROM (SELECT @CM as 'Month') AS Months
LEFT JOIN (SELECT FirstDayOfMonth(effdate) AS Month, SUM(units) AS ApprovedUnits FROM services WHERE status='APPROVED' GROUP BY FirstDayOfMonth(effdate) AS service_appr ON Months.Month = service_appr.Month
LEFT JOIN (SELECT FirstDayOfMonth(effdate) AS Month, SUM(units) AS ApprovedUnits FROM services GROUP BY FirstDayOfMonth(effdate) AS service_total ON Months.Month = service_total.Month
LEFT JOIN (SELECT SUM(
CASE
WHEN effdate < @CM AND effdate+units >= @CM THEN
CASE
WHEN effdate+units < @FDNM THEN DATEDIFF(d,@CM,effdate+units)
ELSE DATEDIFF(d,@CM,@FDNM)
END
WHEN effdate >= @CM AND effdate < @FDNM THEN
CASE
WHEN effdate+units < @FDNM THEN units
ELSE DATEDIFF(d,effdate,@FDNM)
END
ELSE 0
END) AS 'DaysInMonth'
FROM service
WHERE status='APPROVED'
) AS DaysInMonth ON 1=1
SET @CM = DATEADD(m,1,@CM)
END
[/tt]