Instead of this,SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead, dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted, dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown, dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticipated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusiness.OtherBusinessCommissionAnticipated] AS Expr1,[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommissionReceived]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBusinessCommissionReceived] AS Expr2
, IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS Expr3
, IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS Expr4FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID = dbo_Lead.ID) LEFT JOIN dbo_Mortgage ON dbo_Personal.ID = dbo_Mortgage.ID) LEFT JOIN dbo_OtherBusiness ON dbo_Personal.ID = dbo_OtherBusiness.ID) LEFT JOIN dbo_BuildingsAndContents ON dbo_Personal.ID = dbo_BuildingsAndContents.ID) LEFT JOIN dbo_Commissions ON dbo_Personal.ID = dbo_Commissions.ID) LEFT JOIN dbo_Life ON dbo_Personal.ID = dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS Expr3
Use
case
when [Expr1]<1000 then [Expr1]*0.3
when [Expr1]<2000 then [Expr1]*0.4
else [Expr1]*0.5
end as Expr3
similar syntax for Expr4 too.
Hope that helps.|||ill give it a go thanks!|||ive got as far as this :-
CREATE PROCEDURE solnorth AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.SourceOfLead, dbo.Lead.DateOfLead, dbo.Mortgage.MortgageAppSubmitted, dbo.Mortgage.MortgageOfferedAccepted, dbo.Mortgage.MortgageDrawndown, dbo.Mortgage.MortgageApplicationClosed,
dbo.Mortgage.MortgageCommissionAnticipated+dbo.Life.LifeCommissionAnticipated+dbo.BuildingsAndContents.BandCCommissionAnticipated+dbo.OtherBusiness.OtherBusinessCommissionAnticipated AS Expr1,
dbo.commissions.MortgageCommissionReceived+dbo.commissions.LifeCommissionReceived+dbo.commissions.BandCCommissionReceived+dbo.commissions.OtherBusinessCommissionReceived AS Expr2,case
when Expr1<1000 then Expr1*0.3
when Expr1<2000 then Expr1*0.4
else Expr1*0.5
end as Expr3,case
when Expr2<1000 then Expr2*0.3
when Expr2<2000 then Expr2*0.4
else Expr2*0.5
end as Expr4FROM (((((dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID) LEFT JOIN dbo.OtherBusiness ON dbo.Personal.ID = dbo.OtherBusiness.ID)
LEFT JOIN dbo.BuildingsAndContents ON dbo.Personal.ID = dbo.BuildingsAndContents.ID) LEFT JOIN dbo.Commissions ON dbo.Personal.ID = dbo.Commissions.ID)
LEFT JOIN dbo.Life ON dbo.Personal.ID = dbo.Life.ID
WHERE (((dbo.Lead.SourceOfLead) Like 'Solutions*'));
GO
but im getting invalid column name for Expr1 and Expr2 ?
where am i going wrong ?
thanks
mark|||You cannot use Expr1 and Expr2 (an alias) in the case. Just replace Expr1 and Expr2 with the same expressions you use to create Expr1 and Expr2.|||You will have to replace
Expr1
with
dbo.Mortgage.MortgageCommissionAnticipated+dbo.Life.LifeCommissionAnticipated+dbo.BuildingsAndContents.BandCCommissionAnticipated+dbo.OtherBusiness.OtherBusinessCommissionAnticipated
and
Expr2
dbo.commissions.MortgageCommissionReceived+dbo.commissions.LifeCommissionReceived+dbo.commissions.BandCCommissionReceived+dbo.commissions.OtherBusinessCommissionReceived|||great thanks that worked - well theres no errors now - just some errors with the datagrid but i should be able to sort that - thanks for the help everyone!|||quick (question how do i create Expr1 ?
eg
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticipated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusiness.OtherBusinessCommissionAnticipated] AS Expr1
id still like to show expr1 on a datagrid if i could (eg add the columns together)
thanks
mark|||Umm, you create it by placing what you have in the select list. Am I missing something?|||heh my bad (im still learning!)
all works great now thanks for all the help
Lee Redhead wrote: