clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335859 fiddles created (27449 in the last week).

CREATE TABLE #FuelPrice ( PropulsionID INT, PriceStartDate DATETIME2, Price FLOAT )
 hidden batch(es)


INSERT INTO #FuelPrice VALUES ( 1, '1/1/2018', 2.20), ( 1, '2/1/2018', 2.22), ( 1, '3/1/2018', 2.50), ( 1, '4/1/2018', 2.45), ( 1, '5/1/2018', 2.50), ( 1, '6/1/2018', 2.22), ( 1, '7/1/2018', 2.05), ( 1, '8/1/2018', 2.00), ( 1, '9/1/2018', 1.98), ( 1, '10/1/2018', 2.06), ( 1, '11/1/2018', 2.25), ( 1, '12/1/2018', 2.60), ( 2, '1/1/2018', 5.00), ( 2, '2/1/2018', 4.85), ( 2, '3/1/2018', 4.80), ( 2, '4/1/2018', 4.90), ( 2, '5/1/2018', 4.75), ( 2, '6/1/2018', 4.65), ( 2, '7/1/2018', 4.15), ( 2, '8/1/2018', 4.00), ( 2, '9/1/2018', 4.30), ( 2, '10/1/2018', 4.80), ( 2, '11/1/2018', 5.10), ( 2, '12/1/2018', 5.02), ( 3, '1/1/2018', 1.15), ( 3, '2/1/2018', 1.18), ( 3, '3/1/2018', 1.18), ( 3, '4/1/2018', 1.22), ( 3, '5/1/2018', 1.32), ( 3, '6/1/2018', 1.45), ( 3, '7/1/2018', 1.50), ( 3, '8/1/2018', 1.45), ( 3, '9/1/2018', 1.40), ( 3, '10/1/2018', 1.43), ( 3, '11/1/2018', 1.39), ( 3, '12/1/2018', 1.37)
36 rows affected
 hidden batch(es)


CREATE TABLE #Propulsion ( PropulsionID INT, Description VARCHAR(50) )
 hidden batch(es)


INSERT INTO #Propulsion VALUES ( 1, 'Antimatter'), ( 2, 'Fusion'), ( 3, 'Neutron')
3 rows affected
 hidden batch(es)


DECLARE @PropulsionType VARCHAR(50) = 'Fusion' DECLARE @year INT = 2018 SELECT p.Description AS Fueltype, fp.PriceStartDate, fp.Price FROM #FuelPrice fp INNER JOIN #Propulsion p ON fp.PropulsionID = p.PropulsionID -- Corrected Logic WHERE ((YEAR(fp.PriceStartDate) = @year) AND (p.Description = @PropulsionType)) -- Year of 2018 and parameter of 'Fusion', so True and return 12 records OR ((YEAR(fp.PriceStartDate) = @year) AND (@PropulsionType = 'All')) -- Year of 2018 and parameter is 'All, this is False ORDER BY Fueltype, fp.PriceStartDate
Fueltype PriceStartDate Price
Fusion 01/01/2018 00:00:00 5
Fusion 01/02/2018 00:00:00 4.85
Fusion 01/03/2018 00:00:00 4.8
Fusion 01/04/2018 00:00:00 4.9
Fusion 01/05/2018 00:00:00 4.75
Fusion 01/06/2018 00:00:00 4.65
Fusion 01/07/2018 00:00:00 4.15
Fusion 01/08/2018 00:00:00 4
Fusion 01/09/2018 00:00:00 4.3
Fusion 01/10/2018 00:00:00 4.8
Fusion 01/11/2018 00:00:00 5.1
Fusion 01/12/2018 00:00:00 5.02
 hidden batch(es)


DECLARE @PropulsionType VARCHAR(50) = 'All' DECLARE @year INT = 2018 SELECT p.Description AS Fueltype, fp.PriceStartDate, fp.Price FROM #FuelPrice fp INNER JOIN #Propulsion p ON fp.PropulsionID = p.PropulsionID -- Corrected Logic WHERE ((YEAR(fp.PriceStartDate) = @year) AND (p.Description = @PropulsionType)) -- Year of 2018 and parameter doesn't match so False OR ((YEAR(fp.PriceStartDate) = @year) AND (@PropulsionType = 'All')) -- Year of 2018 and parameter is 'All', so True and return all records in 2018 ORDER BY Fueltype, fp.PriceStartDate
Fueltype PriceStartDate Price
Antimatter 01/01/2018 00:00:00 2.2
Antimatter 01/02/2018 00:00:00 2.22
Antimatter 01/03/2018 00:00:00 2.5
Antimatter 01/04/2018 00:00:00 2.45
Antimatter 01/05/2018 00:00:00 2.5
Antimatter 01/06/2018 00:00:00 2.22
Antimatter 01/07/2018 00:00:00 2.05
Antimatter 01/08/2018 00:00:00 2
Antimatter 01/09/2018 00:00:00 1.98
Antimatter 01/10/2018 00:00:00 2.06
Antimatter 01/11/2018 00:00:00 2.25
Antimatter 01/12/2018 00:00:00 2.6
Fusion 01/01/2018 00:00:00 5
Fusion 01/02/2018 00:00:00 4.85
Fusion 01/03/2018 00:00:00 4.8
Fusion 01/04/2018 00:00:00 4.9
Fusion 01/05/2018 00:00:00 4.75
Fusion 01/06/2018 00:00:00 4.65
Fusion 01/07/2018 00:00:00 4.15
Fusion 01/08/2018 00:00:00 4
Fusion 01/09/2018 00:00:00 4.3
Fusion 01/10/2018 00:00:00 4.8
Fusion 01/11/2018 00:00:00 5.1
Fusion 01/12/2018 00:00:00 5.02
Neutron 01/01/2018 00:00:00 1.15
Neutron 01/02/2018 00:00:00 1.18
Neutron 01/03/2018 00:00:00 1.18
Neutron 01/04/2018 00:00:00 1.22
Neutron 01/05/2018 00:00:00 1.32
Neutron 01/06/2018 00:00:00 1.45
Neutron 01/07/2018 00:00:00 1.5
Neutron 01/08/2018 00:00:00 1.45
Neutron 01/09/2018 00:00:00 1.4
Neutron 01/10/2018 00:00:00 1.43
Neutron 01/11/2018 00:00:00 1.39
Neutron 01/12/2018 00:00:00 1.37
 hidden batch(es)


-- Functionally equivalent to the immediate prior statment, but with a differently formatted predicate DECLARE @PropulsionType VARCHAR(50) = 'All' DECLARE @year INT = 2018 SELECT p.Description AS Fueltype, fp.PriceStartDate, fp.Price FROM #FuelPrice fp INNER JOIN #Propulsion p ON fp.PropulsionID = p.PropulsionID -- Corrected Logic WHERE (YEAR(fp.PriceStartDate) = @year) -- Return all records in the year 2018 AND ((p.Description = @PropulsionType) -- Either return a specific Fuel Type OR (@PropulsionType = 'All')) -- OR if the parameter is 'All', don't filter on the Description column ORDER BY Fueltype, fp.PriceStartDate
Fueltype PriceStartDate Price
Antimatter 01/01/2018 00:00:00 2.2
Antimatter 01/02/2018 00:00:00 2.22
Antimatter 01/03/2018 00:00:00 2.5
Antimatter 01/04/2018 00:00:00 2.45
Antimatter 01/05/2018 00:00:00 2.5
Antimatter 01/06/2018 00:00:00 2.22
Antimatter 01/07/2018 00:00:00 2.05
Antimatter 01/08/2018 00:00:00 2
Antimatter 01/09/2018 00:00:00 1.98
Antimatter 01/10/2018 00:00:00 2.06
Antimatter 01/11/2018 00:00:00 2.25
Antimatter 01/12/2018 00:00:00 2.6
Fusion 01/01/2018 00:00:00 5
Fusion 01/02/2018 00:00:00 4.85
Fusion 01/03/2018 00:00:00 4.8
Fusion 01/04/2018 00:00:00 4.9
Fusion 01/05/2018 00:00:00 4.75
Fusion 01/06/2018 00:00:00 4.65
Fusion 01/07/2018 00:00:00 4.15
Fusion 01/08/2018 00:00:00 4
Fusion 01/09/2018 00:00:00 4.3
Fusion 01/10/2018 00:00:00 4.8
Fusion 01/11/2018 00:00:00 5.1
Fusion 01/12/2018 00:00:00 5.02
Neutron 01/01/2018 00:00:00 1.15
Neutron 01/02/2018 00:00:00 1.18
Neutron 01/03/2018 00:00:00 1.18
Neutron 01/04/2018 00:00:00 1.22
Neutron 01/05/2018 00:00:00 1.32
Neutron 01/06/2018 00:00:00 1.45
Neutron 01/07/2018 00:00:00 1.5
Neutron 01/08/2018 00:00:00 1.45
Neutron 01/09/2018 00:00:00 1.4
Neutron 01/10/2018 00:00:00 1.43
Neutron 01/11/2018 00:00:00 1.39
Neutron 01/12/2018 00:00:00 1.37
 hidden batch(es)