Daily report generating Monthly rollup stats

Daily report generating Monthly rollup stats

I have a daily report which each morning generates monthly information for the current month which was implemented in December. Everything was working correctly untill January 1st. On the 1st the report generated blank since it was suppose to generate 1-31 Dec but but the currently month was Jan, so it failed. How do I program it so if it is the 1st of a month generates the previous month but still would generate current month but while in the current month? Any help is appreciated.

SELECT GETDATE() - 1 AS rptdate, Errors.WTG_ID, lookup.Phase, Errors.STATUS_TYPE, Errors.STATUS_CODE, STATUS_CODES.STATUS_DEF, Errors.TIME_STAMP,
Errors.ANSI_TIME, lookup.WTG_TYPE, Errors.POSITION
FROM Errors INNER JOIN lookup ON Errors.WTG_ID = lookup.WTG_id RIGHT OUTER JOIN STATUS_CODES ON Errors.STATUS_CODE = STATUS_CODES.STATUS_CODE AND lookup.WTG_TYPE = STATUS_CODES.WTG_TYPE
WHERE (STATUS_CODES.STATUS_DEF IS NOT NULL) AND (Errors.TIME_STAMP BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))
ORDER BY Errors.WTG_ID, Errors.TIME_STAMP, position

SELECT rptdate = GETDATE() - 1
, Errors.WTG_ID
, lookup.Phase
, Errors.STATUS_TYPE
, Errors.STATUS_CODE
, STATUS_CODES.STATUS_DEF
, Errors.TIME_STAMP
, Errors.ANSI_TIME
, lookup.WTG_TYPE
, Errors.POSITION

FROM Errors

INNER JOIN lookup
ON Errors.WTG_ID = lookup.WTG_id

RIGHT JOIN STATUS_CODES
ON Errors.STATUS_CODE = STATUS_CODES.STATUS_CODE
AND lookup.WTG_TYPE = STATUS_CODES.WTG_TYPE

WHERE STATUS_CODES.STATUS_DEF IS NOT NULL
-- You're interested in summarising all days in a
-- month so only perform comparisson on Year and Month
-- For 1st of Jan subtract a year
-- For first of any month subtract a month

AND Year(Errors.TIME_STAMP) = Year(GETDATE())
- CASE
WHEN Month(GETDATE()) = 1
AND Day(GETDATE()) = 1 -- 1st Jan
THEN 1
ELSE 0
END
AND Month(Errors.TIME_STAMP) = Month(
DATEADD(
, mm
, GETDATE()
, CASE
WHEN Day(GETDATE()) = 1
-- 1st of the month
THEN -1
ELSE 0
END
)
)

ORDER BY Errors.WTG_ID
, Errors.TIME_STAMP
, position

|||I appreciate the information - I used Jan 1 as an example because they when it truely happened. Forgivie my ignorance, but will the same code work for the 1st of any month thus pulling the previous month's data?|||The above code caters for both cases (read the comments). Normally you just subtract a month, but with 1st of Jan you also have to subtract a year (at least that's how my example works).