jtrapat1
asked on
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
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
ENDASKER
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
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
ASKER
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
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
ASKER
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
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
I 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.
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
I 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'
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.