SQL Problem

[colin]

Hi All,

can ayone help with this SQL problem I have ?? I have got a table which holds various fields:

problemID, area, date, etc

what I am trying to do is calculate how many records have been added each month of the year and then group them by the area the occured in. This is the code I am trying :

SELECT id_st_ref AS Area, count(DATEPART(month, id_todays_date)) as Jan,
count(DATEPART(month, id_todays_date)) as Feb

FROM dbo.bv142i_AllPrimary_Ex_Fa

GROUP BY id_st_ref
ORDER BY id_st_ref

which returns the results:

Area Jan Feb
------- ----------- -----------
14 14
A1 104 104
B1 151 151
D1 50 50
E1 148 148
F1 18 18
G1 67 67
I1 9 9
J1 47 47
K1 33 33
L1 7 7
M1 42 42
N1 91 91
O1 3 3
P1 9 9
Q1 215 215

I was trying to use:

count(DATEPART(month, id_todays_date) = 2 ) as Feb

using the =2 to specify the month but this does not seem to work using transact SQL.

can anyone help me out,

many thanks.

Colin

[Ecniv]

Just a thought - perhaps a field for creation date time.

Then you can filter on that more easily.


If that field is the date and you only want the month try making a field in the SQL statement summat like :
format([datefield],"mmm") as MonthDate

and then adding a filter at the bottom on it.


Regards

Vince

[colin]

Thanks for your reply,

I don't think you can use the format function in Transact SQL. I need to be able to calculate the results at the database end and not the application.

Colin

[RSINGH]

Were you specifying

count(DATEPART(month, id_todays_date) = 2

in a WHERE clause?

[colin]

I have sorted it now, this makes it work:

SELECT id_st_ref, COUNT(id_todays_date) AS Total,
{ fn MONTH(id_todays_date) } AS Monthly
FROM dbo.bv142i_2001
GROUP BY id_st_ref, fn MONTH(id_todays_date)