DBVis SQL Query - Using Date Variables

Question

Firstly it's been a very long time since I did any SQL and even then it wasn't at a high level.

I have created a SQL query which works but I want it so the date range changes automatically, for example to run for the previous month. I have struggled for hours but cant seem to get any type of variable to work.

Here is the simple SQL I have created.

SELECT   DEST, COUNT (DEST),  SUM(TALK_TIME), SUM(HOLD_TIME)


    FROM ACC.CALLDETAIL a

    where a.ORIG_DATE>='1140201'
    and a.ORIG_DATE<'1140301'
    and a.APPLIC_NUM = 185

GROUP BY a.DEST;

The date format is strange, its years after 1900 month day

Any help will be appreciated.

Katherine Villyard · Answer 1 · 2014-03-21T13:25:42.437

Try:

SELECT   DEST, COUNT (DEST),  SUM(TALK_TIME), SUM(HOLD_TIME)
    FROM ACC.CALLDETAIL a
    where a.ORIG_DATE >= '1' + (right(convert(varchar,DateAdd(mm, DateDiff(mm, 0, GETDATE()) - 1, 0), 112),6))
    and a.ORIG_DATE < '1' + (right(convert(varchar,DateAdd(mm, DateDiff(mm, 0, GETDATE()) - 0, 0), 112),6))
    and a.APPLIC_NUM = 185
GROUP BY a.DEST;

And see if that does what you want.

(If it doesn't, you may have to declare @startdate and @enddate and define them before the query.)

DBVis SQL Query - Using Date Variables

1 Answers1