SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE #FuelPrice
(
	PropulsionID	INT,
	PriceStartDate	DATETIME2,
	Price	FLOAT
)
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

CREATE TABLE #Propulsion
(
	PropulsionID	INT,
	Description		VARCHAR(50)
)
INSERT INTO #Propulsion
VALUES
( 1, 'Antimatter'), ( 2, 'Fusion'), ( 3, 'Neutron')
3 rows affected

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


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


-- 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

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

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