Declare and use a varible in a Select satement???

[H2wk1]

Is it possible to declare and use a varible in a select statement?

Is there anyway you can use a varible in a select statement in SQL?

Reason i ask is because i have a very complect calculationin the select statement and i need to work out percentages still.

Whole query below.

SELECT Equipment.EquipmentCategory, IIf([Invoice].[HiredOnWeekend]=TRUE,
SUM((((DATEDIFF("d",Invoice.HiringDate,Invoice.ReturnDate)-1)*Equipment.EquipmentPriceAfter)+Equipment.EquipmentPriceWeekend)*Invoice.NumberEquipmentHired),
SUM((((DATEDIFF("d",Invoice.HiringDate,Invoice.ReturnDate)-1)*Equipment.EquipmentPriceAfter)+Equipment.EquipmentPriceWeek)*Invoice.NumberEquipmentHired)) AS TotalIncome
FROM Membership INNER JOIN (CustomerDetails INNER JOIN ((Equipment INNER JOIN Invoice ON Equipment.EquipmentID = Invoice.EquipmentID) INNER JOIN Stock ON Equipment.EquipmentID = Stock.EquipmentID) ON CustomerDetails.CustomerID = Invoice.CustomerID) ON Membership.MembershipID = CustomerDetails.MembershipID
WHERE (((Invoice.hiringDate)>=[forms].[FormA5].[startdate].[value]) AND ((Invoice.ReturnDate)<=[forms].[FormA5].[enddate].[value]))
GROUP BY Equipment.EquipmentCategory, Invoice.HiredOnWeekend, Invoice.ReturnDate, Invoice.HiringDate;

Thanks for the help

 

[ajetrumpet]

is it possible to declare and use a varible in a select statement?

no.

is there anyway you can use a varible in a select statement in sql?

yes.

reason i ask is because i have a very complect calculationin the select statement and i need to work out percentages still.

you can still get percentages other ways. How would you like to do it? In the query itself? What fields, etc..?

 

[H2wk1]

yes i the query its self. For each of the parts of the if statement i above. i need to add 14% onto the calculation and then subtract 10%25%or 40% depending on the companies membership level. This applies to both branches of the above calculation.

 

[ajetrumpet]

Perhaps you could post the database? HARD TO TELL WHAT'S GOING ON WITHOUT THE WHOLE PICTURE, BUT MAYBE SOMEONE ELSE HAS AN IDEA.

 

[raskew]

... depending on the companies membership level.

How is that level determined?

Bob

 

[H2wk1]

is there a function in sql to add 14% vat onto a total?

if so what is the structure?

 

[raskew]

is there a function in sql to add 14% vat onto a total?

if so what is the structure?

x = 100 'amt to be taxed
y = 0.14 'tax rate
z = x * (1 + y) 'computation
? z 'amt + tax
 114

Bob

 

[H2wk1]

ok thanks a million. as usual i'm making things more complicated than they seem. Thanks guys for all your help.