grouping week numbers of a month [Resolved]

Posted by Sureshij under Sql Server on 8/31/2013 | Points: 10 | Views : 1040 | Status : [Member] | Replies : 3
hi everyone,
i have number of records with datetime column dare_record_in and table name is rooms
how to get the number of times each room get booked per week
so far i'm not able to use group by in correct way...
output should be
weeknum numofTimes

ij


Responses

Posted by: Bandi on: 8/31/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved

the following query will give u the week wise count
select (((day(DATE_record_in)-1) / 7) + 1) AS WeekNum, Count(*) from rooms where date = Current Month group by (((day(DATE_record_in)-1) / 7) + 1)


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sureshij, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/31/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
just include roomid in the select as well as group by clauses......

select roomid, (((day(DATE_record_in)-1) / 7) + 1) AS WeekNum, Count(*) from rooms where datepart(MM, date_record_in )= datepart(MM, getdate()) group by roomid, (((day(DATE_record_in)-1) / 7) + 1)




Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sureshij, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sureshij on: 8/31/2013 [Member] Starter | Points: 25

Up
0
Down
if i want to count for each room and week?

ij

Sureshij, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response