Here is a select query i have,
What i would like to do is sum the results [ATP Sales]\[ATP Leads] * 100
I thought i could just do
But that just errors on the last line.
Help anybody please.
Code:
SELECT Tame_cust.Rep_Name,
sum (Case Tame_cust.hlp_benefits When 'No' Then 1 Else 0 End) As [ATP Leads],
sum (Case Tame_cust.hlp_benefits
When 'Child Tax Credit (less than £14200pa)' Then 1
When 'ATT Allowance' Then 1
When 'Disability living Allowance' Then 1
When 'State Pension Credit' Then 1
When 'Income based JSA' Then 1
When 'Council Tax' Then 1
When 'Housing' Then 1
When 'Income Support' Then 1
When 'War Disablement Pension' Then 1
When 'Disablement Allowance Benefit' Then 1
When 'Working Tax Credit (Less than £14200pa)' Then 1
When 'Industrial Injurys' Then 1
When 'Over 70' Then 1
Else 0
End) As [PG Leads],
sum (Case Tame_cust.tsr When 'HOT' Then 1 Else 0 End) As [HOT Leads],
sum (Case When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'L' Then 1
When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'C' Then 1
When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'D' Then 2
Else 0 End) As [ATP Sales],
sum (Case When Tame_cust.hlp_benefits <> 'No' And Tame_Cust.tsr <> 'HOT' And Tame_Cust.Status_code = 'L' Then 1
When Tame_cust.hlp_benefits <> 'No' And Tame_Cust.tsr <> 'HOT' And Tame_Cust.Status_code = 'C' Then 1
When Tame_cust.hlp_benefits <> 'No' And Tame_Cust.tsr <> 'HOT' And Tame_Cust.Status_code = 'D' Then 2
Else 0 End) As [PG Sales],
sum (Case When Tame_Cust.tsr = 'HOT' And Tame_Cust.Status_code = 'L' Then 1
When Tame_Cust.tsr = 'HOT' And Tame_Cust.Status_code = 'C' Then 1
When Tame_Cust.tsr = 'HOT' And Tame_Cust.Status_code = 'D' Then 2
Else 0 End) As [HL Sales]
FROM Tame_cust
Where tame_cust.Status_code <>'PB'
GROUP BY Tame_cust.Rep_Name, Tame_cust.appt_date
HAVING CONVERT(DATETIME, tame_cust.appt_date, 103) = CONVERT(nvarchar, '07/07/2009', 103)
What i would like to do is sum the results [ATP Sales]\[ATP Leads] * 100
I thought i could just do
Code:
SELECT sum([ATP Sales]\[ATP Leads]*100) AS Ratio
From (
SELECT Tame_cust.Rep_Name,
sum (Case Tame_cust.hlp_benefits When 'No' Then 1 Else 0 End) As [ATP Leads],
sum (Case Tame_cust.hlp_benefits
When 'Child Tax Credit (less than £14200pa)' Then 1
When 'ATT Allowance' Then 1
When 'Disability living Allowance' Then 1
When 'State Pension Credit' Then 1
When 'Income based JSA' Then 1
When 'Council Tax' Then 1
When 'Housing' Then 1
When 'Income Support' Then 1
When 'War Disablement Pension' Then 1
When 'Disablement Allowance Benefit' Then 1
When 'Working Tax Credit (Less than £14200pa)' Then 1
When 'Industrial Injurys' Then 1
When 'Over 70' Then 1
Else 0
End) As [PG Leads],
sum (Case Tame_cust.tsr When 'HOT' Then 1 Else 0 End) As [HOT Leads],
sum (Case When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'L' Then 1
When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'C' Then 1
When Tame_cust.hlp_benefits = 'No' And Tame_Cust.Status_code = 'D' Then 2
Else 0 End) As [ATP Sales],
sum (Case When Tame_cust.hlp_benefits <> 'No' And Tame_Cust.tsr <> 'HOT' And Tame_Cust.Status_code = 'L' Then 1
When Tame_cust.hlp_benefits <> 'No' And Tame_Cust.tsr <> 'HOT' And Tame_Cust.Status_code = 'C' Then 1
When Tame_cust.hlp_benefits <> 'No' And Tame_Cust.tsr <> 'HOT' And Tame_Cust.Status_code = 'D' Then 2
Else 0 End) As [PG Sales],
sum (Case When Tame_Cust.tsr = 'HOT' And Tame_Cust.Status_code = 'L' Then 1
When Tame_Cust.tsr = 'HOT' And Tame_Cust.Status_code = 'C' Then 1
When Tame_Cust.tsr = 'HOT' And Tame_Cust.Status_code = 'D' Then 2
Else 0 End) As [HL Sales]
FROM Tame_cust
Where tame_cust.Status_code <>'PB'
GROUP BY Tame_cust.Rep_Name, Tame_cust.appt_date
HAVING CONVERT(DATETIME, tame_cust.appt_date, 103) = CONVERT(nvarchar, '07/07/2009', 103)
)
But that just errors on the last line.
Help anybody please.