Grouping by DOW then HR or just by HR for the whole week

[bigmelon]

I have a stored procedure that I use for a report on SSRS. They enter in a date range, and DOW. I have it working for all specific DOW, but I also want them to be able to chose all and haven't been able to figure this out yet.

My where statement is:

where DATEPART(dw,e.adm_date)LIKE @DOW and 
e.adm_date >=@startdate AND e.adm_date<=@ENDdate

and my group by is:

DATEPART(dw,e.adm_date)

How can I set this up to work for the week as a whole?

Thanks in advance,
Jeremy

 

[SQLDenis]

look at DATEPART(wk,e.adm_date) or DATEPART(ww,e.adm_date)

http://sqlservercode.blogspot.com/

 

[bigmelon]

Yea but I can't figure out how to use them together, I know that functionality exists.

Thanks,
Jeremy

 

[SQLDenis]

You have to take out where DATEPART(dw,e.adm_date)LIKE @DOW

What can they choose now??

Date Of Week doesn't make sense

http://sqlservercode.blogspot.com/

 

[bigmelon]

Right now they can just select a day of the week, 1-7, and it will get the data for that specific day. I'm trying to set it up so they can still do that, or select the week in its entirety. How can I put that in my WHERE and GROUP BY to accomplish this?

Thanks again,
Jeremy

 

[rafrancisco]

Hi Jeremy,

One option that you can do is if @DOW = 0, then it means that the user wants all the days for that week. Given this, your query will be as follows:

where (DATEPART(dw,e.adm_date)LIKE @DOW OR @DOW = 0) and 
e.adm_date >=@startdate AND e.adm_date<=@ENDdate

On another note, if you want to get all records beginning the start of the current week, here's a function that will give you the date for the first day of the week:

http://www.sql-server-helper.com/functions/get-first-day-of-week.aspx

Hope this helps.