Please validate my query

Hi,

I have written a query for the following scenario :

I have 2 tables:

RecType - RecID(int), RecType(varchar)

Eg: 1 Joined, 2 Transferred, 3 Rusticated

Student - StudentID(int), RecID, Age(smallint), Sex(nchar(1)), DateOfJoining(datetime)

The query is :

select

[RecType] AS RecType,

cnt as TotalCount

from

(

select

RC.RecType,

count(*)as cnt

from

RecType as RC

innerjoin

Studentas stu

on RC.recid= STU.recid

where

STU.DateOfJoining>=convert(varchar(6),dateadd(month,-6,getdate()), 112)+'01'AND

(recid= 1 OR recid = 2 OR recid = 3)

groupby

RC.RecType

)as t

So the final results are as following

RecType Total Count

Joined 55

Transferred 3

Rusticated 0

However, I want to write a query that displays a grouping of students who have either joined, Transferred or rusticated grouped by each month, during the past 6 months. Please not that the date of joining/transfer etc of each student can be different in a month.

So the final results will be as following

RecType Total Count Month

Joined 23 1

Transferred 3 1

Rusticated 0 1

Joined 32 2

and so on..

How do I do this?

Thanks.

It is not bad idea to include the year while fetching the data for month...

Code Snippet

Select

RecType

,[Month]

,[Year]

,Count(*) as TotalCount

From

(

select

RC.RecType

,Month(STU.DateOfJoining) [Month]

,Year(STU.DateOfJoining) [Year]

from

RecType as RC

inner join Student as stu

on RC.recid= STU.recid

where

STU.DateOfJoining >= convert(varchar(6), dateadd(month, -6, getdate()), 112) + '01'

AND(recid= 1 OR recid = 2 OR recid = 3)

) as Data

Group by

RecType

,[Month]

,[Year]

|||

Hi,

Thanks for the query. I understood where I was going wrong.

Manivannan, I know that writing good T-sql queries comes from practise. However, can you suggest me a good book to learn T-sql for sql 2005.

Also can you please visit this post :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1664669&SiteID=1

Thanks.