Help with SQL statement

[endomorph1]

Hi,

Have a basic knowledge of SQL statement building but need to create a query and I am lost.

I need to count records in a table and group by the month between two dates and to export as a .csv file so I get a result like -

mm/yy count

01/07 3245
02/07 8734

or something similar. It is to be run through Interactive SQL.

Can anyone help please?

 

[PDreyer]

Something like this?
select right(convert(char(8),crdate,3),5) 'mm/yy'
, count(*) 'count'
from sysobjects o
where crdate between '20040710' and '20041130'
group by right(convert(char(8),crdate,3),5)

 

[endomorph1]

Mmm. I think I started off on the wrong foot here. Well the closest I have got is -

SELECT
"F_LEADS"."SOURCE",Count("F_LEADS"."LEAD_DATE") AS "COUNT of LEAD_DATE"
FROM
"DBA"."F_LEADS" "F_LEADS"
GROUP BY
"F_LEADS"."SOURCE"
ORDER BY
"F_LEADS"."SOURCE" ASC

Which gives me the record count for each lead source, but for the whole datbase !

I now need to narrow this down to each month within a given date range so I can get -

M1 M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12

Source 1 xx xx xx xx
Source 2 xx xx xx xx xx
Source 3 xx xx xx xx xx


Also, I am trying to get this done in DBExtra 2005 (if this helps)

 

[PDreyer]

I don't know DBExtra

select SOURCE,
sum(case when month(LEAD_DATE)=1 then 1 else 0 end) 'M1',
sum(case when month(LEAD_DATE)=2 then 1 else 0 end) 'M2',
sum(case when month(LEAD_DATE)=3 then 1 else 0 end) 'M3',
sum(case when month(LEAD_DATE)=4 then 1 else 0 end) 'M4',
sum(case when month(LEAD_DATE)=5 then 1 else 0 end) 'M5',
sum(case when month(LEAD_DATE)=6 then 1 else 0 end) 'M6',
sum(case when month(LEAD_DATE)=7 then 1 else 0 end) 'M7',
sum(case when month(LEAD_DATE)=8 then 1 else 0 end) 'M8',
sum(case when month(LEAD_DATE)=9 then 1 else 0 end) 'M9',
sum(case when month(LEAD_DATE)=10 then 1 else 0 end) 'M10',
sum(case when month(LEAD_DATE)=11 then 1 else 0 end) 'M11',
sum(case when month(LEAD_DATE)=12 then 1 else 0 end) 'M12'
from F_LEAD
where LEAD_DATE between '20070101' and '20071231'
group by SOURCE

 

[endomorph1]

Fantastic, works a treat. Now I need one little extension on this.

In a separate table (f_source) we keep the full name of the source.

How would I show the full name of the source in this query ?

 

[PDreyer]

Just join to f_source on source and use the name in your select and group by

 

[endomorph1]

Sorry, could you show me how ?

 

[endomorph1]

Got it -

select F_LEADS.SOURCE, F_LEADSOURCE.SOURCE_NAME,
sum(case when month(F_LEADS.LEAD_DATE)=1 then 1 else 0 end) 'M1',
sum(case when month(F_LEADS.LEAD_DATE)=2 then 1 else 0 end) 'M2',
sum(case when month(F_LEADS.LEAD_DATE)=3 then 1 else 0 end) 'M3',
sum(case when month(F_LEADS.LEAD_DATE)=4 then 1 else 0 end) 'M4',
sum(case when month(F_LEADS.LEAD_DATE)=5 then 1 else 0 end) 'M5',
sum(case when month(F_LEADS.LEAD_DATE)=6 then 1 else 0 end) 'M6',
sum(case when month(F_LEADS.LEAD_DATE)=7 then 1 else 0 end) 'M7',
sum(case when month(F_LEADS.LEAD_DATE)=8 then 1 else 0 end) 'M8',
sum(case when month(F_LEADS.LEAD_DATE)=9 then 1 else 0 end) 'M9',
sum(case when month(F_LEADS.LEAD_DATE)=10 then 1 else 0 end) 'M10',
sum(case when month(F_LEADS.LEAD_DATE)=11 then 1 else 0 end) 'M11',
sum(case when month(F_LEADS.LEAD_DATE)=12 then 1 else 0 end) 'M12'
from F_LEADS

INNER JOIN F_LEADSOURCE ON F_LEADS.SOURCE = F_LEADSOURCE.SOURCE

where LEAD_DATE between '20070101' and '20071231'
group by F_LEADS.SOURCE,F_LEADSOURCE.SOURCE_NAME

 

[PDreyer]

Note: If you do not specify a time it defaults to midnight
If your LEAD_DATE also have a time portion then to avoid excluding the 31st change the where clause to:

where LEAD_DATE>='20070101' -- default time midnight
and LEAD_DATE<'20080101'