• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

Common Table Expression and Parameters for Calculated Field

I have a common table expression which calculates the miles per gallon in a nested subquery.
It is currently working now but I would like to pass one or more parameters to the existing query.
Attached is the current query.
I would like to pass an operator and a value- to the calculation of milespergallon-
such as <= 10 MPG, >= 10 MPG, etc...

Can I do this? I have tried numerous versions in the where clause but it doesnt seem correct.
Maybe it is because of where I have the parameter placed in the stored procedure.
I figured it should be in the where clause outside of the calculation but maybe this is wrong;
Maybe it should be in the same inner query along with the mpg calculation.


Thanks in advance.
John  
--
ALTER Procedure [dbo].[usprptAuditorFuelUsage]
(
@StartDate DATETIME,
@EndDate DATETIME,
@Code VARCHAR(5)=NULL,
@Type VARCHAR(15)=NULL,
@Class VARCHAR(10)=NULL,
@MPG VARCHAR(4)=NULL     
   

)
AS 
--
BEGIN 

; with QuantitiesAndDistance_CTE(VEHICLE_ID_FW,SYSTEM_FW,AMOUNT_FW,
                        COST_FW,FUEL_TYPE_FW,[TRANSACTION_DATE_FW],
                        VEHICLE_RECORD_NUMBER_FW,TotalQuantity,TotalDistance,TotalCost)
      as
      (
      select 
                  i.VEHICLE_ID_FW,
                  i.SYSTEM_FW,
                  i.AMOUNT_FW,
                  i.COST_FW,
                  i.FUEL_TYPE_FW,
                  i.[TRANSACTION_DATE_FW],
                  i.VEHICLE_RECORD_NUMBER_FW,
            sum(i.AMOUNT_FW) as TotalQuantity, 
            (max(i.ODOMETER_FW)-min(i.ODOMETER_FW)) as TotalDistance,
                  SUM(i.COST_FW) as TotalCost
      from FUEL_ISSUES_FW i 
WHERE
(i.[TRANSACTION_DATE_FW] >= @StartDate)
AND (i.[TRANSACTION_DATE_FW] <= @EndDate)
AND i.SYSTEM_FW <> 'DMP' 
--   
       group by 
            i.VEHICLE_ID_FW, 
            i.SYSTEM_FW,
            i.AMOUNT_FW,
            i.COST_FW,
            i.FUEL_TYPE_FW,
            i.[TRANSACTION_DATE_FW],
            i.VEHICLE_RECORD_NUMBER_FW,
            month(i.TRANSACTION_DATE_FW),
            year(i.TRANSACTION_DATE_FW)
      ),
MAXES AS (SELECT
VEHICLE_ID_FW,  
MIN(TRANSACTION_DATE_FW) AS ODO_START_DATE,
MAX(TRANSACTION_DATE_FW) AS ODO_END_DATE,
MIN(ODOMETER_FW) AS ODO_START,
MAX(ODOMETER_FW) AS ODO_END,
MAX(ODOMETER_FW)-MIN(ODOMETER_FW) AS TOT_MILES,
SUM(AMOUNT_FW) AS TotalQuantity
from FUEL_ISSUES_FW
WHERE
([TRANSACTION_DATE_FW] >= @StartDate)
AND ([TRANSACTION_DATE_FW] <= @EndDate)
AND SYSTEM_FW <> 'DMP'  
GROUP BY VEHICLE_ID_FW   
--
)     
      SELECT * FROM (
      select 
      v.VEHICLE_ID_FW,  
      v.CHASSIS_NUMBER_FW AS VIN,
      v.COMPANY_CODE_FW,
      v.OGS_DOB_CLASS_FW,
      v.VEHICLE_STATUS_FW,
      v.URBAN_MPG_FW AS CITY_MPG,
      v.COUNTRY_MPG_FW AS HWY_MPG,
      v.ODOMETER_FW,
      v.ODOMETER_DATE_FW,
      v.TANK_SIZE_FW AS TANK_SIZE,
      v.[VEHICLE_TYPE_FW],
ISNULL(v.[OGS_MODEL_YEAR_FW], '') + ' ' + ISNULL(v.[MAKE_CODE_FW], '') + ' ' + ISNULL(v.[MODEL_CODE_FW], '')  AS VEHICLE,
      v.RECORD_NUMBER_FW,
      qd_cte.SYSTEM_FW,
      ROUND((qd_cte.AMOUNT_FW),1) AS AMOUNT_FW,
      qd_cte.COST_FW,
      qd_cte.FUEL_TYPE_FW,
      qd_cte.[TRANSACTION_DATE_FW],
      qd_cte.TotalQuantity AS [Total_Fuel_Gallons],
      ROUND((qd_cte.TotalDistance),1) AS [Total_Miles],
      ROUND((qd_cte.TotalDistance/qd_cte.TotalQuantity),1) AS MPG, 
      qd_cte.TotalCost AS [Total_Fuel_Cost],
      qd_cte.VEHICLE_RECORD_NUMBER_FW AS VEH_REC_NUM,
      MIN(MAXES.ODO_START_DATE) AS odo_start_date,
      MAX(MAXES.ODO_END_DATE) AS odo_end_date,
      MIN(MAXES.ODO_START) AS odo_start,
      MAX(MAXES.ODO_END) AS odo_end,
      --do calculations here for mpg
      MAX(MAXES.ODO_END)-MIN(MAXES.ODO_START) AS TOT_MILES,
      SUM(MAXES.TotalQuantity) AS TotalQuantity,

convert(char(20),
CASE WHEN SUM(MAXES.TotalQuantity) <> 0 THEN cast((MAX(MAXES.TOT_MILES)/MAX(MAXES.TotalQuantity)) as integer) ELSE '' END) as MilesPerGallon

      from VEHICLES_FW v 
      INNER JOIN QuantitiesAndDistance_CTE qd_cte
      on v.RECORD_NUMBER_FW=qd_cte.VEHICLE_RECORD_NUMBER_FW
      INNER JOIN MAXES 
      ON qd_cte.VEHICLE_ID_FW = MAXES.VEHICLE_ID_FW

WHERE (@Code is null or v.COMPANY_CODE_FW = @Code)
AND (@Type is null or v.VEHICLE_TYPE_FW = @Type) 
AND (@Class is null or v.OGS_DOB_CLASS_FW = @Class) 

GROUP BY
      v.VEHICLE_ID_FW,
      v.VEHICLE_TYPE_FW,
      v.CHASSIS_NUMBER_FW,  
      v.COMPANY_CODE_FW,  
      v.OGS_DOB_CLASS_FW,
      v.VEHICLE_STATUS_FW,
      v.URBAN_MPG_FW,
      v.COUNTRY_MPG_FW,
      v.ODOMETER_FW,
      v.ODOMETER_DATE_FW,
      v.TANK_SIZE_FW,
      v.OGS_MODEL_YEAR_FW,
      v.MAKE_CODE_FW,
      v.MODEL_CODE_FW,
      v.RECORD_NUMBER_FW,
      qd_cte.SYSTEM_FW,
      qd_cte.AMOUNT_FW,
      qd_cte.COST_FW,
      qd_cte.FUEL_TYPE_FW,
      qd_cte.TRANSACTION_DATE_FW,
      qd_cte.TotalQuantity,
      qd_cte.TotalDistance,
      qd_cte.TotalCost,
      qd_cte.VEHICLE_RECORD_NUMBER_FW  
) SQ 
--
--
--WHERE (MilesPerGallon <= convert(varchar(4),@MPG) or @MPG is null)
--
--WHERE ISNULL(MilesPerGallon,0)<=ISNULL(@MPG,0) 
WHERE (MilesPerGallon <= @MPG or @MPG is null)
--WHERE (@MPG is NULL OR MilesPerGallon <= (@MPG)) 
--
ORDER BY VEHICLE_ID_FW

END

Open in new window

0
jtrapat1
Asked:
jtrapat1
  • 4
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
First, this code makes me curious: MilesPerGallon <= convert(varchar(4), @MPG)
I know it is commented out, but when you look at @MPG VARCHAR(4)=NULL it suggest you are indeed passing a character string. So what exactly are you passing (i.e., what value)?

I would leave the MPG as a numerical value and compare to parameter that way.
Remember '10' < '9' as a string.
0
 
jtrapat1Author Commented:
mwvisa1-
thanks for the reply;
ok - i dont have the datatypes all worked out yet;
But, this is the format i would like to use if possible.
Basically, I have this query working for five parameters and now i would like to add two more- an operator and a value-to filter the report (i.e. less than 10)
This is in reporting services and the dataset is this stored procedure;
i am passing a start and end date, and three drop down values to the inner portion.
Now, i would like pass the operator and value but i dont know if it can be done.
I've seen this written as a dynamic query but could i write it without exec(sql)?

Thanks
John
0
 
jtrapat1Author Commented:
mwvisa1:
I have the basics of this query working- just need one more part-
I declared the MPG as decimal (9,1) and I can pass this value to my sp- and get the correct results.
But, I would also like to pass an operator - <=, >=, =.
But whenever I code this logic in my where clause - it doesnt compile.
Im trying to string together two parameters - operator and value;
If I hard code this in my where clause - it runs and gives me the correct results-
WHERE (MilesPerGallon <= 11.0)
But when I try to code this as:
WHERE (MilesPerGallon + @Operator + @MPG)
where @Operator is varchar(4)
I get this error:
An expression of non-boolean type specified in a context where a condition is expected, near 'ORDER'.

Is there some other way I could write this without using dynamic sql?

John


0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
jtrapat1Author Commented:
I got a little farther but I need some suggestions for tying both operator and value parameters together -
since they are dependent on each other.
below is my query which passes the operator and the mpg value.
Now, the only 2 cases I'll have are (>= value) AND (<= value)
Right now, the operator selections are from a drop down but they could be radio buttons, etc.
I dont know the best approach.
And I would like to make the mpg value a varchar string value since it is an optional parameter.
But, again, since they are linked, I may need to enable/disable the mpg textbox based on the drop down selected.

Can anyone suggest a solution for this issue?
thanks

ALTER Procedure [dbo].[usprptAuditorFuelUsage]
(
@StartDate DATETIME,
@EndDate DATETIME,
@Code VARCHAR(5)=NULL,
@Type VARCHAR(15)=NULL,
@Class VARCHAR(10)=NULL,
--optional parameters
@Operator VARCHAR(1)=NULL,     
--@MPG DECIMAL(9,1)=NULL
@MPG varchar(10)=NULL       
)
AS 
--
IF (SELECT @Operator) = 1
--Case: @Operator <= MPG  
--
BEGIN 
--
; with QuantitiesAndDistance_CTE(VEHICLE_ID_FW,SYSTEM_FW,AMOUNT_FW,
				COST_FW,FUEL_TYPE_FW,[TRANSACTION_DATE_FW],
				VEHICLE_RECORD_NUMBER_FW,TotalQuantity,TotalDistance,TotalCost)
	as
	(
	 select 
			i.VEHICLE_ID_FW,
			i.SYSTEM_FW,
			i.AMOUNT_FW,
			i.COST_FW,
			i.FUEL_TYPE_FW,
			i.[TRANSACTION_DATE_FW],
			i.VEHICLE_RECORD_NUMBER_FW,
		sum(i.AMOUNT_FW) as TotalQuantity, 
		(max(i.ODOMETER_FW)-min(i.ODOMETER_FW)) as TotalDistance,
			SUM(i.COST_FW) as TotalCost
	 from FUEL_ISSUES_FW i 
WHERE
(i.[TRANSACTION_DATE_FW] >= @StartDate)
AND (i.[TRANSACTION_DATE_FW] <= @EndDate)
AND i.SYSTEM_FW <> 'DMP' 
--   
	 group by 
		i.VEHICLE_ID_FW, 
		i.SYSTEM_FW,
		i.AMOUNT_FW,
		i.COST_FW,
		i.FUEL_TYPE_FW,
		i.[TRANSACTION_DATE_FW],
		i.VEHICLE_RECORD_NUMBER_FW,
		month(i.TRANSACTION_DATE_FW),
		year(i.TRANSACTION_DATE_FW)
	),
MAXES AS (SELECT
VEHICLE_ID_FW,  
MIN(TRANSACTION_DATE_FW) AS ODO_START_DATE,
MAX(TRANSACTION_DATE_FW) AS ODO_END_DATE,
MIN(ODOMETER_FW) AS ODO_START,
MAX(ODOMETER_FW) AS ODO_END,
MAX(ODOMETER_FW)-MIN(ODOMETER_FW) AS TOT_MILES,
SUM(AMOUNT_FW) AS TotalQuantity
from FUEL_ISSUES_FW
WHERE
([TRANSACTION_DATE_FW] >= @StartDate)
AND ([TRANSACTION_DATE_FW] <= @EndDate)
AND SYSTEM_FW <> 'DMP'  
GROUP BY VEHICLE_ID_FW   
--
)	
	SELECT * FROM (
	select 
	v.VEHICLE_ID_FW,	
	v.CHASSIS_NUMBER_FW AS VIN,
	v.COMPANY_CODE_FW,
	v.OGS_DOB_CLASS_FW,
	v.VEHICLE_STATUS_FW,
	v.URBAN_MPG_FW AS CITY_MPG,
	v.COUNTRY_MPG_FW AS HWY_MPG,
	v.ODOMETER_FW,
	v.ODOMETER_DATE_FW,
	v.TANK_SIZE_FW AS TANK_SIZE,
	v.[VEHICLE_TYPE_FW],
ISNULL(v.[OGS_MODEL_YEAR_FW], '') + ' ' + ISNULL(v.[MAKE_CODE_FW], '') + ' ' + ISNULL(v.[MODEL_CODE_FW], '')  AS VEHICLE,
	v.RECORD_NUMBER_FW,
	qd_cte.SYSTEM_FW,
	ROUND((qd_cte.AMOUNT_FW),1) AS AMOUNT_FW,
	qd_cte.COST_FW,
	qd_cte.FUEL_TYPE_FW,
	qd_cte.[TRANSACTION_DATE_FW],
	qd_cte.TotalQuantity AS [Total_Fuel_Gallons],
	ROUND((qd_cte.TotalDistance),1) AS [Total_Miles],
	ROUND((qd_cte.TotalDistance/qd_cte.TotalQuantity),1) AS MPG, 
	qd_cte.TotalCost AS [Total_Fuel_Cost],
	qd_cte.VEHICLE_RECORD_NUMBER_FW AS VEH_REC_NUM,
	MIN(MAXES.ODO_START_DATE) AS odo_start_date,
	MAX(MAXES.ODO_END_DATE) AS odo_end_date,
	MIN(MAXES.ODO_START) AS odo_start,
	MAX(MAXES.ODO_END) AS odo_end,
	--do calculations here for mpg
	MAX(MAXES.ODO_END)-MIN(MAXES.ODO_START) AS TOT_MILES,
	SUM(MAXES.TotalQuantity) AS TotalQuantity,
	round(MAX(MAXES.TOT_MILES)/MAX(MAXES.TotalQuantity),1) AS MilesPerGallon,
	CONVERT(DECIMAL(9,1),(MAX(MAXES.TOT_MILES)/MAX(MAXES.TotalQuantity))) as MPGallon 
	from VEHICLES_FW v 
	INNER JOIN QuantitiesAndDistance_CTE qd_cte
	on v.RECORD_NUMBER_FW=qd_cte.VEHICLE_RECORD_NUMBER_FW
	INNER JOIN MAXES 
	ON qd_cte.VEHICLE_ID_FW = MAXES.VEHICLE_ID_FW
--
WHERE (@Code is null or v.COMPANY_CODE_FW = @Code)
AND (@Type is null or v.VEHICLE_TYPE_FW = @Type) 
AND (@Class is null or v.OGS_DOB_CLASS_FW = @Class)
--
GROUP BY
	v.VEHICLE_ID_FW,
	v.VEHICLE_TYPE_FW,
	v.CHASSIS_NUMBER_FW,  
	v.COMPANY_CODE_FW,  
	v.OGS_DOB_CLASS_FW,
	v.VEHICLE_STATUS_FW,
	v.URBAN_MPG_FW,
	v.COUNTRY_MPG_FW,
	v.ODOMETER_FW,
	v.ODOMETER_DATE_FW,
	v.TANK_SIZE_FW,
	v.OGS_MODEL_YEAR_FW,
	v.MAKE_CODE_FW,
	v.MODEL_CODE_FW,
	v.RECORD_NUMBER_FW,
	qd_cte.SYSTEM_FW,
	qd_cte.AMOUNT_FW,
	qd_cte.COST_FW,
	qd_cte.FUEL_TYPE_FW,
	qd_cte.TRANSACTION_DATE_FW,
	qd_cte.TotalQuantity,
	qd_cte.TotalDistance,
	qd_cte.TotalCost,
	qd_cte.VEHICLE_RECORD_NUMBER_FW  
) SQ 
--
WHERE (@MPG IS NULL OR MilesPerGallon <= @MPG)  
--
ORDER BY VEHICLE_ID_FW
--
END 
--
IF (SELECT @Operator) = 2
--
--CASE: @Operator >= @MPG
--
BEGIN 
; with QuantitiesAndDistance_CTE(VEHICLE_ID_FW,SYSTEM_FW,AMOUNT_FW,
				COST_FW,FUEL_TYPE_FW,[TRANSACTION_DATE_FW],
				VEHICLE_RECORD_NUMBER_FW,TotalQuantity,TotalDistance,TotalCost)
	as
	(
	 select 
			i.VEHICLE_ID_FW,
			i.SYSTEM_FW,
			i.AMOUNT_FW,
			i.COST_FW,
			i.FUEL_TYPE_FW,
			i.[TRANSACTION_DATE_FW],
			i.VEHICLE_RECORD_NUMBER_FW,
		sum(i.AMOUNT_FW) as TotalQuantity, 
		(max(i.ODOMETER_FW)-min(i.ODOMETER_FW)) as TotalDistance,
			SUM(i.COST_FW) as TotalCost
	 from FUEL_ISSUES_FW i 
WHERE
(i.[TRANSACTION_DATE_FW] >= @StartDate)
AND (i.[TRANSACTION_DATE_FW] <= @EndDate)
AND i.SYSTEM_FW <> 'DMP' 
--   
	 group by 
		i.VEHICLE_ID_FW, 
		i.SYSTEM_FW,
		i.AMOUNT_FW,
		i.COST_FW,
		i.FUEL_TYPE_FW,
		i.[TRANSACTION_DATE_FW],
		i.VEHICLE_RECORD_NUMBER_FW,
		month(i.TRANSACTION_DATE_FW),
		year(i.TRANSACTION_DATE_FW)
	),
MAXES AS (SELECT
VEHICLE_ID_FW,  
MIN(TRANSACTION_DATE_FW) AS ODO_START_DATE,
MAX(TRANSACTION_DATE_FW) AS ODO_END_DATE,
MIN(ODOMETER_FW) AS ODO_START,
MAX(ODOMETER_FW) AS ODO_END,
MAX(ODOMETER_FW)-MIN(ODOMETER_FW) AS TOT_MILES,
SUM(AMOUNT_FW) AS TotalQuantity
from FUEL_ISSUES_FW
WHERE
([TRANSACTION_DATE_FW] >= @StartDate)
AND ([TRANSACTION_DATE_FW] <= @EndDate)
AND SYSTEM_FW <> 'DMP'  
GROUP BY VEHICLE_ID_FW   
--
)	
	SELECT * FROM (
	select 
	v.VEHICLE_ID_FW,	
	v.CHASSIS_NUMBER_FW AS VIN,
	v.COMPANY_CODE_FW,
	v.OGS_DOB_CLASS_FW,
	v.VEHICLE_STATUS_FW,
	v.URBAN_MPG_FW AS CITY_MPG,
	v.COUNTRY_MPG_FW AS HWY_MPG,
	v.ODOMETER_FW,
	v.ODOMETER_DATE_FW,
	v.TANK_SIZE_FW AS TANK_SIZE,
	v.[VEHICLE_TYPE_FW],
ISNULL(v.[OGS_MODEL_YEAR_FW], '') + ' ' + ISNULL(v.[MAKE_CODE_FW], '') + ' ' + ISNULL(v.[MODEL_CODE_FW], '')  AS VEHICLE,
	v.RECORD_NUMBER_FW,
	qd_cte.SYSTEM_FW,
	ROUND((qd_cte.AMOUNT_FW),1) AS AMOUNT_FW,
	qd_cte.COST_FW,
	qd_cte.FUEL_TYPE_FW,
	qd_cte.[TRANSACTION_DATE_FW],
	qd_cte.TotalQuantity AS [Total_Fuel_Gallons],
	ROUND((qd_cte.TotalDistance),1) AS [Total_Miles],
	ROUND((qd_cte.TotalDistance/qd_cte.TotalQuantity),1) AS MPG, 
	qd_cte.TotalCost AS [Total_Fuel_Cost],
	qd_cte.VEHICLE_RECORD_NUMBER_FW AS VEH_REC_NUM,
	MIN(MAXES.ODO_START_DATE) AS odo_start_date,
	MAX(MAXES.ODO_END_DATE) AS odo_end_date,
	MIN(MAXES.ODO_START) AS odo_start,
	MAX(MAXES.ODO_END) AS odo_end,
	--do calculations here for mpg
	MAX(MAXES.ODO_END)-MIN(MAXES.ODO_START) AS TOT_MILES,
	SUM(MAXES.TotalQuantity) AS TotalQuantity,
	round(MAX(MAXES.TOT_MILES)/MAX(MAXES.TotalQuantity),1) AS MilesPerGallon
	--CONVERT(DECIMAL(9,1),(MAX(MAXES.TOT_MILES)/MAX(MAXES.TotalQuantity))) as MPGallon 
	from VEHICLES_FW v 
	INNER JOIN QuantitiesAndDistance_CTE qd_cte
	on v.RECORD_NUMBER_FW=qd_cte.VEHICLE_RECORD_NUMBER_FW
	INNER JOIN MAXES 
	ON qd_cte.VEHICLE_ID_FW = MAXES.VEHICLE_ID_FW
--
WHERE (@Code is null or v.COMPANY_CODE_FW = @Code)
AND (@Type is null or v.VEHICLE_TYPE_FW = @Type) 
AND (@Class is null or v.OGS_DOB_CLASS_FW = @Class)
--
GROUP BY
	v.VEHICLE_ID_FW,
	v.VEHICLE_TYPE_FW,
	v.CHASSIS_NUMBER_FW,  
	v.COMPANY_CODE_FW,  
	v.OGS_DOB_CLASS_FW,
	v.VEHICLE_STATUS_FW,
	v.URBAN_MPG_FW,
	v.COUNTRY_MPG_FW,
	v.ODOMETER_FW,
	v.ODOMETER_DATE_FW,
	v.TANK_SIZE_FW,
	v.OGS_MODEL_YEAR_FW,
	v.MAKE_CODE_FW,
	v.MODEL_CODE_FW,
	v.RECORD_NUMBER_FW,
	qd_cte.SYSTEM_FW,
	qd_cte.AMOUNT_FW,
	qd_cte.COST_FW,
	qd_cte.FUEL_TYPE_FW,
	qd_cte.TRANSACTION_DATE_FW,
	qd_cte.TotalQuantity,
	qd_cte.TotalDistance,
	qd_cte.TotalCost,
	qd_cte.VEHICLE_RECORD_NUMBER_FW  
) SQ 
--
WHERE (@MPG IS NULL OR MilesPerGallon >= @MPG)  
--
ORDER BY VEHICLE_ID_FW
--
END 
--
ELSE 
--default case: operator is 0
--return all
BEGIN 
; with QuantitiesAndDistance_CTE(VEHICLE_ID_FW,SYSTEM_FW,AMOUNT_FW,
				COST_FW,FUEL_TYPE_FW,[TRANSACTION_DATE_FW],
				VEHICLE_RECORD_NUMBER_FW,TotalQuantity,TotalDistance,TotalCost)
	as
	(
	 select 
			i.VEHICLE_ID_FW,
			i.SYSTEM_FW,
			i.AMOUNT_FW,
			i.COST_FW,
			i.FUEL_TYPE_FW,
			i.[TRANSACTION_DATE_FW],
			i.VEHICLE_RECORD_NUMBER_FW,
		sum(i.AMOUNT_FW) as TotalQuantity, 
		(max(i.ODOMETER_FW)-min(i.ODOMETER_FW)) as TotalDistance,
			SUM(i.COST_FW) as TotalCost
	 from FUEL_ISSUES_FW i 
WHERE
(i.[TRANSACTION_DATE_FW] >= @StartDate)
AND (i.[TRANSACTION_DATE_FW] <= @EndDate)
AND i.SYSTEM_FW <> 'DMP' 
--   
	 group by 
		i.VEHICLE_ID_FW, 
		i.SYSTEM_FW,
		i.AMOUNT_FW,
		i.COST_FW,
		i.FUEL_TYPE_FW,
		i.[TRANSACTION_DATE_FW],
		i.VEHICLE_RECORD_NUMBER_FW,
		month(i.TRANSACTION_DATE_FW),
		year(i.TRANSACTION_DATE_FW)
	),
MAXES AS (SELECT
VEHICLE_ID_FW,  
MIN(TRANSACTION_DATE_FW) AS ODO_START_DATE,
MAX(TRANSACTION_DATE_FW) AS ODO_END_DATE,
MIN(ODOMETER_FW) AS ODO_START,
MAX(ODOMETER_FW) AS ODO_END,
MAX(ODOMETER_FW)-MIN(ODOMETER_FW) AS TOT_MILES,
SUM(AMOUNT_FW) AS TotalQuantity
from FUEL_ISSUES_FW
WHERE
([TRANSACTION_DATE_FW] >= @StartDate)
AND ([TRANSACTION_DATE_FW] <= @EndDate)
AND SYSTEM_FW <> 'DMP'  
GROUP BY VEHICLE_ID_FW   
--
)	
	SELECT * FROM (
	select 
	v.VEHICLE_ID_FW,	
	v.CHASSIS_NUMBER_FW AS VIN,
	v.COMPANY_CODE_FW,
	v.OGS_DOB_CLASS_FW,
	v.VEHICLE_STATUS_FW,
	v.URBAN_MPG_FW AS CITY_MPG,
	v.COUNTRY_MPG_FW AS HWY_MPG,
	v.ODOMETER_FW,
	v.ODOMETER_DATE_FW,
	v.TANK_SIZE_FW AS TANK_SIZE,
	v.[VEHICLE_TYPE_FW],
ISNULL(v.[OGS_MODEL_YEAR_FW], '') + ' ' + ISNULL(v.[MAKE_CODE_FW], '') + ' ' + ISNULL(v.[MODEL_CODE_FW], '')  AS VEHICLE,
	v.RECORD_NUMBER_FW,
	qd_cte.SYSTEM_FW,
	ROUND((qd_cte.AMOUNT_FW),1) AS AMOUNT_FW,
	qd_cte.COST_FW,
	qd_cte.FUEL_TYPE_FW,
	qd_cte.[TRANSACTION_DATE_FW],
	qd_cte.TotalQuantity AS [Total_Fuel_Gallons],
	ROUND((qd_cte.TotalDistance),1) AS [Total_Miles],
	ROUND((qd_cte.TotalDistance/qd_cte.TotalQuantity),1) AS MPG, 
	qd_cte.TotalCost AS [Total_Fuel_Cost],
	qd_cte.VEHICLE_RECORD_NUMBER_FW AS VEH_REC_NUM,
	MIN(MAXES.ODO_START_DATE) AS odo_start_date,
	MAX(MAXES.ODO_END_DATE) AS odo_end_date,
	MIN(MAXES.ODO_START) AS odo_start,
	MAX(MAXES.ODO_END) AS odo_end,
	--do calculations here for mpg
	MAX(MAXES.ODO_END)-MIN(MAXES.ODO_START) AS TOT_MILES,
	SUM(MAXES.TotalQuantity) AS TotalQuantity,
	round(MAX(MAXES.TOT_MILES)/MAX(MAXES.TotalQuantity),1) AS MilesPerGallon
	--CONVERT(DECIMAL(9,1),(MAX(MAXES.TOT_MILES)/MAX(MAXES.TotalQuantity))) as MPGallon 
	from VEHICLES_FW v 
	INNER JOIN QuantitiesAndDistance_CTE qd_cte
	on v.RECORD_NUMBER_FW=qd_cte.VEHICLE_RECORD_NUMBER_FW
	INNER JOIN MAXES 
	ON qd_cte.VEHICLE_ID_FW = MAXES.VEHICLE_ID_FW
--
WHERE (@Code is null or v.COMPANY_CODE_FW = @Code)
AND (@Type is null or v.VEHICLE_TYPE_FW = @Type) 
AND (@Class is null or v.OGS_DOB_CLASS_FW = @Class)
--
GROUP BY
	v.VEHICLE_ID_FW,
	v.VEHICLE_TYPE_FW,
	v.CHASSIS_NUMBER_FW,  
	v.COMPANY_CODE_FW,  
	v.OGS_DOB_CLASS_FW,
	v.VEHICLE_STATUS_FW,
	v.URBAN_MPG_FW,
	v.COUNTRY_MPG_FW,
	v.ODOMETER_FW,
	v.ODOMETER_DATE_FW,
	v.TANK_SIZE_FW,
	v.OGS_MODEL_YEAR_FW,
	v.MAKE_CODE_FW,
	v.MODEL_CODE_FW,
	v.RECORD_NUMBER_FW,
	qd_cte.SYSTEM_FW,
	qd_cte.AMOUNT_FW,
	qd_cte.COST_FW,
	qd_cte.FUEL_TYPE_FW,
	qd_cte.TRANSACTION_DATE_FW,
	qd_cte.TotalQuantity,
	qd_cte.TotalDistance,
	qd_cte.TotalCost,
	qd_cte.VEHICLE_RECORD_NUMBER_FW  
) SQ 
--
--no where clause? - return all
WHERE (@MPG IS NULL)  
--
ORDER BY VEHICLE_ID_FW
--
END

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
I have been in meetings all day, so my apologies. I am leaving work and should be back online later -- and I will comment then.
0
 
Kevin CrossChief Technology OfficerCommented:
Okay, I see, you are trying to dynamically add in the comparison operator. That is difficult. As you saw, you can use IF, but that is now a maintenance nightmare. You can resort to dynamic SQL of course, but another approach I have seen in lieu of that is a CASE statement in the WHERE.

e.g.,
CASE @op WHEN '<='
   THEN CASE WHEN mpg <= @mpg THEN 1 ELSE 0 END
   ...
END = 1

You basically create a 1 or 0 based on whichever condition matches, then evaluate for 1 at the end. Another approach that may be my preference is to build the query on the presentation side, so only the complete SQL is sent back to SQL server versus dealing with change in operator as it sounds like you are really allowing the client to build the SQL syntax visually. This can often be problematic and security risk of sql injection, but if controlled it can be useful also. I am sure you likely have a good reason to do this, so that is why I would offer that as a potential solution.

Good luck!
0
 
jtrapat1Author Commented:
thanks;
I got a little farther but I still have some issues regarding using two depending parameters -
attached is a screen shot of the parameters
and the stored procedure I am currently working with;
Basically I have to account for most combinations of the parameters for the input mpg textbox and the drop down for less than or greater than.
If the user stays on this report page and just changes parameters and filters and keeps submitting the page, I need to make sure this works in all cases.
Ideally, if I could enable and disable certain parameter fields, that would be good.
But, Ill probably have to take care of these values in the sp.
I may need to replace blank values with NULL in some cases.
But I'd like to fix this case to start with:
I got rid of the ALL case so its just the less than / greater than cases.
1.  If a user clears out the mpg textfield and leaves the drop down of less than and hits run report

 parametersI wont copy and paste the entire code block again because it is really long;
I just have to keep in mind that all of my variables are declared as varchar in the stored procedure except for the start and end dates.
this is important because i have to test the null and blank values from reporting services when nothing is entered.

AS 
--
IF (SELECT @Operator) = '1'
--Case: @Operator <= MPG  
--
BEGIN 
--
; with QuantitiesAndDistance_CTE(VEHICLE_ID_FW,SYSTEM_FW,AMOUNT_FW,
				COST_FW,FUEL_TYPE_FW,[TRANSACTION_DATE_FW],
				VEHICLE_RECORD_NUMBER_FW,TotalQuantity,TotalDistance,TotalCost)
	as
	(
	 select 
			i.VEHICLE_ID_FW,
			i.SYSTEM_FW,
			i.AMOUNT_FW,
			i.COST_FW,
			i.FUEL_TYPE_FW,
			i.[TRANSACTION_DATE_FW],
			i.VEHICLE_RECORD_NUMBER_FW,
		sum(i.AMOUNT_FW) as TotalQuantity, 
		(max(i.ODOMETER_FW)-min(i.ODOMETER_FW)) as TotalDistance,
			SUM(i.COST_FW) as TotalCost 
	 from FUEL_ISSUES_FW i 
WHERE
(i.[TRANSACTION_DATE_FW] >= @StartDate)
AND (i.[TRANSACTION_DATE_FW] <= @EndDate)
AND i.SYSTEM_FW <> 'DMP' 
--   
	 group by 
		i.VEHICLE_ID_FW, 
		i.SYSTEM_FW,
		i.AMOUNT_FW,
		i.COST_FW,
		i.FUEL_TYPE_FW,
		i.[TRANSACTION_DATE_FW],
		i.VEHICLE_RECORD_NUMBER_FW,
		month(i.TRANSACTION_DATE_FW),
		year(i.TRANSACTION_DATE_FW)
	),
MAXES AS (SELECT
VEHICLE_ID_FW,  
MIN(TRANSACTION_DATE_FW) AS ODO_START_DATE,
MAX(TRANSACTION_DATE_FW) AS ODO_END_DATE,
MIN(ODOMETER_FW) AS ODO_START,
MAX(ODOMETER_FW) AS ODO_END,
MAX(ODOMETER_FW)-MIN(ODOMETER_FW) AS TOT_MILES,
SUM(AMOUNT_FW) AS TotalQuantity
from FUEL_ISSUES_FW
WHERE
([TRANSACTION_DATE_FW] >= @StartDate)
AND ([TRANSACTION_DATE_FW] <= @EndDate)
AND SYSTEM_FW <> 'DMP'  
GROUP BY VEHICLE_ID_FW   
--
)	
	SELECT * FROM (
	select 
	v.VEHICLE_ID_FW,	
	v.CHASSIS_NUMBER_FW AS VIN,
	v.COMPANY_CODE_FW,
	v.OGS_DOB_CLASS_FW,
	v.VEHICLE_STATUS_FW,
	v.URBAN_MPG_FW AS CITY_MPG,
	v.COUNTRY_MPG_FW AS HWY_MPG,
	v.ODOMETER_FW,
	v.ODOMETER_DATE_FW,
	v.TANK_SIZE_FW AS TANK_SIZE,
	v.[VEHICLE_TYPE_FW],
ISNULL(v.[OGS_MODEL_YEAR_FW], '') + ' ' + ISNULL(v.[MAKE_CODE_FW], '') + ' ' + ISNULL(v.[MODEL_CODE_FW], '')  AS VEHICLE,
	v.RECORD_NUMBER_FW,
	qd_cte.SYSTEM_FW,
	ROUND((qd_cte.AMOUNT_FW),1) AS AMOUNT_FW,
	qd_cte.COST_FW,
	qd_cte.FUEL_TYPE_FW,
	qd_cte.[TRANSACTION_DATE_FW],
	qd_cte.TotalQuantity AS [Total_Fuel_Gallons],
	ROUND((qd_cte.TotalDistance),1) AS [Total_Miles],
	ROUND((qd_cte.TotalDistance/qd_cte.TotalQuantity),1) AS MPG, 
	qd_cte.TotalCost AS [Total_Fuel_Cost],
	qd_cte.VEHICLE_RECORD_NUMBER_FW AS VEH_REC_NUM,
	MIN(MAXES.ODO_START_DATE) AS odo_start_date,
	MAX(MAXES.ODO_END_DATE) AS odo_end_date,
	MIN(MAXES.ODO_START) AS odo_start,
	MAX(MAXES.ODO_END) AS odo_end,
	--do calculations here for mpg
	MAX(MAXES.ODO_END)-MIN(MAXES.ODO_START) AS TOT_MILES,
	SUM(MAXES.TotalQuantity) AS TotalQuantity,
	round(MAX(MAXES.TOT_MILES)/MAX(MAXES.TotalQuantity),1) AS MilesPerGallon,
	CONVERT(DECIMAL(9,1),(MAX(MAXES.TOT_MILES)/MAX(MAXES.TotalQuantity))) as MPGallon 
	from VEHICLES_FW v 
	INNER JOIN QuantitiesAndDistance_CTE qd_cte
	on v.RECORD_NUMBER_FW=qd_cte.VEHICLE_RECORD_NUMBER_FW
	INNER JOIN MAXES 
	ON qd_cte.VEHICLE_ID_FW = MAXES.VEHICLE_ID_FW
--
WHERE (@Code is null or v.COMPANY_CODE_FW = @Code)
AND (@Type is null or v.VEHICLE_TYPE_FW = @Type) 
AND (@Class is null or v.OGS_DOB_CLASS_FW = @Class)
--
GROUP BY
	v.VEHICLE_ID_FW,
	v.VEHICLE_TYPE_FW,
	v.CHASSIS_NUMBER_FW,  
	v.COMPANY_CODE_FW,  
	v.OGS_DOB_CLASS_FW,
	v.VEHICLE_STATUS_FW,
	v.URBAN_MPG_FW,
	v.COUNTRY_MPG_FW,
	v.ODOMETER_FW,
	v.ODOMETER_DATE_FW,
	v.TANK_SIZE_FW,
	v.OGS_MODEL_YEAR_FW,
	v.MAKE_CODE_FW,
	v.MODEL_CODE_FW,
	v.RECORD_NUMBER_FW,
	qd_cte.SYSTEM_FW,
	qd_cte.AMOUNT_FW,
	qd_cte.COST_FW,
	qd_cte.FUEL_TYPE_FW,
	qd_cte.TRANSACTION_DATE_FW,
	qd_cte.TotalQuantity,
	qd_cte.TotalDistance,
	qd_cte.TotalCost,
	qd_cte.VEHICLE_RECORD_NUMBER_FW  
) SQ 
--
WHERE (@MPG IS NULL OR MilesPerGallon <= @MPG)  
--
ORDER BY VEHICLE_ID_FW
--
END 
--
IF (SELECT @Operator) = '2'

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now