Query Help

[rkrause]

I have a qurey in a data set in reporting services. What im trying to do is use the shipdateyear field as a parameter but i keep getting an error when im running it. I think its because how im getting the name shipdate year its coming from a field where im formatting the data to just show year.
heres my query. its erroring in the where Clause i believe. any help would be awesome. Thanks.

select
bol.loadnumber,
bol.ldhshipdate,
bol.shiptocode,
bol.destination,
bol.shiptoname,
bol.itemdesc,
bol.abbrev,
Datepart(week,bol.ldhshipdate) as 'weeknum',
Datepart(weekday,bol.ldhshipdate) as 'weekdaynum',
--case statement--
Case when Datepart(weekday,bol.ldhshipdate) = 1 then 'Sunday'
else case when Datepart(weekday,bol.ldhshipdate) = 2 then 'monday'
else case when Datepart(weekday,bol.ldhshipdate) = 3 then 'tuesday'
else case when Datepart(weekday,bol.ldhshipdate) = 4 then 'wednesday'
else case when Datepart(weekday,bol.ldhshipdate) = 5 then 'thursday'
else case when Datepart(weekday,bol.ldhshipdate) = 6 then 'Friday'
else case when Datepart(weekday,bol.ldhshipdate) = 7 then 'saturday'
else 'none'
end
end
end
end
end
end
end as 'dayofweek',
--case statement--
ofstm.ostabr, ofstm.ostnam,ofstm.ostccd,
Datepart(year,bol.ldhshipdate) as shipdateyear,
--case
Case when destination = 'KFTNU' then 'Kraft Direct'
else case when destination = 'KFTSP' then 'Kraft Direct'
else case when destination = 'KFTBD' then 'Kraft Direct'
else case when left(destination, 3) = 'KGV' then 'Kraft Resale'
else 'Others'
end
end
end
end as 'custgroup'
--case
from dbo.v_nlp_BillOfLading_BK bol
inner join ofstm on bol.abbrev = ofstm.ostabr
where bol.itemdesc = 'Pasteurized Sweet Cream' and shipdateyear = @Year

 

[Vassago]

Try changing shipdateyear in your wear clause to Datepart(year,bol.ldhshipdate).

 

[pbaldy]

FYI, the Case statements can be consolidated. Here's an example:

SELECT ReqDateTime, 
  Case Datepart(weekday, ReqDateTime)
    When 1 then 'Sun'
    When 2 then 'Mon'
    When 3 then 'Tue'
    When 4 then 'Wed'
    When 5 then 'Thu'
    When 6 then 'Fri'
    When 7 then 'Sat'
  End As WeekDayDesc
FROM tblReservations