grouping week numbers of a month [Resolved]

Posted by Sureshij under Sql Server on 8/31/2013 | Points: 10 | Views : 1427 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

0	if i want to count for each room and week? ij Sureshij, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts