CODEWITHSUNDEEP
sqlsql-server-2012
Bulbul
Bulbul

Reputation: 157

Weekly to Monthly Reports

I was running weekly reports as in the below script. However, I now want to run monthly report so needed some assistance. Weekly:

SELECT * FROM TABLE WHERE 
(ARRIVAL_DATE>GETDATE()-7)//7 days before
AND 
(ARRIVAL_DATE<GETDATE()) //NOW

For monthly report, will below script be right if I run on every 1st?

SELECT Column1,...
FROM TableName
WHERE
MONTH(DateColumn) = MONTH(dateadd(dd, -1, GetDate()))
AND
YEAR(DateColumn) = YEAR(dateadd(dd, -1, GetDate()))

Thanks.

Upvotes: 1

Views: 145

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270693

Your logic looks okay, although I would use day instead of dd, because I think it is clearer.

However, your query will prevent the use of an index on datecolumn. The following is a "better" form of the logic, because it allows the use of an index:

where datecolumn >= dateadd(month, -1,
                            dateadd(day, 1 - day(getdate()), cast(getdate() as date))
                           ) and
      datecolumn < dateadd(day, 1 - day(getdate()), cast(getdate() as date))

This looks more complicated, but all the functions are on getdate(), so the engine can use an index. Also note the explicit conversion to date. getdate() returns a time component which might throw off the logic if datecolumn only has a date.

Upvotes: 3

Mohammed
Mohammed

Reputation: 313

where datecolumn between dateadd(mm, -1,getdate()) and getdate()

Upvotes: 0

Related Questions