December 31, 2007 at 4:16 am
Hello everyone, here is my SP:
Create Procedure Test
@Criteria int
As
if (@Criteria =1)
begin
-- All Categories
SELECT dbo.TollingVehicleCategories.TollingVehicleCategoryName, COUNT(t1.TransactionID) AS TotalVehicles,SUM(CASE WHEN TollDiscountID = 4 THEN 1 ELSE 0 END) as VehiclesExempt ,
SUM(CASE WHEN TollDiscountID not in(1,4) THEN 1 ELSE 0 END) as VehiclesDiscounted,SUM(Case WHEN TollDiscountID = 1 THEN 1 ELSE 0 END) as VehiclesCharged, SUM(t1.ChargedAmount) as TollCollected
from Transactions t1 INNER JOIN
dbo.TollingVehicleCategories ON t1.VehicleCategoryCode = dbo.TollingVehicleCategories.TollingVehicleCategoryCode AND
t1.PlazaCode = dbo.TollingVehicleCategories.PlazaCode AND
t1.ProjectCode = dbo.TollingVehicleCategories.ProjectCode AND t1.ClientCode = dbo.TollingVehicleCategories.ClientCode
where t1.shiftid =106
and t1.paymentmodecode = 1
GROUP BY dbo.TollingVehicleCategories.TollingVehicleCategoryName
end
else if (@Criteria =2)
Begin
-- All Operators
SELECT t1.shiftsystemuserid, COUNT(t1.TransactionID) AS TotalVehicles,SUM(CASE WHEN TollDiscountID = 4 THEN 1 ELSE 0 END) as VehiclesExempt ,
SUM(CASE WHEN TollDiscountID not in(1,4) THEN 1 ELSE 0 END) as VehiclesDiscounted,SUM(Case WHEN TollDiscountID = 1 THEN 1 ELSE 0 END) as VehiclesCharged, SUM(t1.ChargedAmount) as TollCollected
from Transactions t1
where t1.shiftid =106
and t1.paymentmodecode = 1
GROUP BY t1.shiftsystemuserid
end
else
Begin
-- All Booths
SELECT t1.BoothCode, COUNT(t1.TransactionID) AS TotalVehicles,SUM(CASE WHEN TollDiscountID = 4 THEN 1 ELSE 0 END) as VehiclesExempt ,
SUM(CASE WHEN TollDiscountID not in(1,4) THEN 1 ELSE 0 END) as VehiclesDiscounted,SUM(Case WHEN TollDiscountID = 1 THEN 1 ELSE 0 END) as VehiclesCharged, SUM(t1.ChargedAmount) as TollCollected
from Transactions t1
where t1.shiftid =106
and t1.paymentmodecode = 1
GROUP BY t1.BoothCode
order Byt1.BoothCode asc
end
I m using SQL Server 2000, In my front end using (ASP.Net), i have 3 radio buttons, if user select 1 radio button first part run (If @Criteria=1){All Categories}, if 2nd(AllOperators) then 2 query run and if 3rd (AllBooths)then last
query return. Is there any other way to make 1 query and on the basis of this query my Group by Clause set and also that value comes in my select stmt list.
its work fine but i need 1 query coz all other fields are same and get data from same table , as u see i removed the join in 2nd and 3rd query coz i din't get any data from
that table, so if i used this no problem just cost of query incresed so i removed join part from last 2 queries. For making 1 query can i add join in last 2 or without join its work in
one query.Plz reply me and guide me. Thanx in Advance.
December 31, 2007 at 7:15 am
Please don't post multiple times for the same problem. It fragments replies and wastes people's time
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply