Current calls logged this month

[yelyah.nodrog]

Morning all,

I am trying to create a widget to tell me the current calls logged this month so far. so from the 1st nov to current date, I don't want to have to change the query every month to keep up and want it to roll each month.

So I have written this (I'm quite new to T-sql so it wont be right!):

(h_requesttype = 'Service Request' OR h_requesttype = 'Incident')
AND
DATE(h_datelogged) BETWEEN dateadd(DAY,-(DATEPART(DAY,GETDATE())-1),GETDATE()) AND GETDATE())

however its not working, is this anywhere near right? Could someone point me in the correct direction with this query, also is there a easier way to do it?

Thanks

Hayley

 

 

[yelyah.nodrog]

I have tried again using this:

[Victor]

@yelyah.nodrog try this:

h_datelogged BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW()

[yelyah.nodrog]

That works perfectly Victor, thankyou, is there a way we could apply this to count the calls logged in a week? so Monday to Sunday?

[Victor]

@yelyah.nodrog try this:

h_datelogged BETWEEN (NOW() - INTERVAL (WEEKDAY(NOW())) DAY) AND NOW()

[yelyah.nodrog]

Hey Victor, I have given this a go but it is not reporting correctly?

Today is the start of a new week and we have logged 16 calls so far this morning, however its not showing any as being logged? - I have refreshed it to make sure and still nothing.

This is the code we are currently using:

These are the logged calls so far today

[Dan Munns]

@yelyah.nodrog try this instead

(h_datelogged >= (CURDATE() - INTERVAL (WEEKDAY(CURDATE())) DAY)) AND (h_requesttype='incident' OR h_requesttype='service request')

[Steve Giller]

@shamaila.yousaf - You might find this useful.