October 24, 2016 at 6:54 am
I have the following select statement. What I am basically trying to do is select all rows where the manager and year are equal to what is shown in the sql below and the columns fcst_jan through fcst_dec does not contain one of those values. It seems to work find for some, but I am still getting rows that contain 205 in one of the columns. So basically i want to select rows where any column jan through dec doesnt contain one of those values. Please help. I can't figure it out. I know my table design isnt the best way of doing it, but it is what I have, so i need to figure it out.
SELECT [fcst_id]
,[fcst_emplname]
,[fcst_emplid]
,[fcst_posid]
,[fcst_mgrid]
,[fcst_sect_id]
,[fcst_year]
,[fcst_jan]
,[fcst_feb]
,[fcst_mar]
,[fcst_apr]
,[fcst_may]
,[fcst_jun]
,[fcst_jul]
,[fcst_aug]
,[fcst_sep]
,[fcst_oct]
,[fcst_nov]
,[fcst_dec]
,[fcst_comments]
FROM [EMS].[dbo].[TEMSFCST]
where fcst_mgrid='00809571' and fcst_year=2016 and
(
fcst_jan not in (1,2,3,4,5,205,211) or
fcst_feb not in (1,2,3,4,5,205,211) or
fcst_mar not in (1,2,3,4,5,205,211) or
fcst_apr not in (1,2,3,4,5,205,211) or
fcst_may not in (1,2,3,4,5,205,211) or
fcst_jun not in (1,2,3,4,5,205,211) or
fcst_jul not in (1,2,3,4,5,205,211) or
fcst_aug not in (1,2,3,4,5,205,211) or
fcst_sep not in (1,2,3,4,5,205,211) or
fcst_oct not in (1,2,3,4,5,205,211) or
fcst_nov not in (1,2,3,4,5,205,211) or
fcst_dec not in (1,2,3,4,5,205,211)
)
October 24, 2016 at 7:14 am
You could try something like this
😎
;WITH FILTER_VALUES(FVAL) AS
(
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 205 UNION ALL
SELECT 211
)
SELECT TF.[fcst_id]
,TF.[fcst_emplname]
,TF.[fcst_emplid]
,TF.[fcst_posid]
,TF.[fcst_mgrid]
,TF.[fcst_sect_id]
,TF.[fcst_year]
,TF.[fcst_jan]
,TF.[fcst_feb]
,TF.[fcst_mar]
,TF.[fcst_apr]
,TF.[fcst_may]
,TF.[fcst_jun]
,TF.[fcst_jul]
,TF.[fcst_aug]
,TF.[fcst_sep]
,TF.[fcst_oct]
,TF.[fcst_nov]
,TF.[fcst_dec]
,TF.[fcst_comments]
FROM [EMS].[dbo].[TEMSFCST] TF
LEFT OUTER JOIN FILTER_VALUES FV01 ON TF.fcst_jan = FV01.FVAL
LEFT OUTER JOIN FILTER_VALUES FV02 ON TF.fcst_feb = FV02.FVAL
LEFT OUTER JOIN FILTER_VALUES FV03 ON TF.fcst_mar = FV03.FVAL
LEFT OUTER JOIN FILTER_VALUES FV04 ON TF.fcst_apr = FV04.FVAL
LEFT OUTER JOIN FILTER_VALUES FV05 ON TF.fcst_may = FV05.FVAL
LEFT OUTER JOIN FILTER_VALUES FV06 ON TF.fcst_jun = FV06.FVAL
LEFT OUTER JOIN FILTER_VALUES FV07 ON TF.fcst_jul = FV07.FVAL
LEFT OUTER JOIN FILTER_VALUES FV08 ON TF.fcst_aug = FV08.FVAL
LEFT OUTER JOIN FILTER_VALUES FV09 ON TF.fcst_sep = FV09.FVAL
LEFT OUTER JOIN FILTER_VALUES FV10 ON TF.fcst_oct = FV10.FVAL
LEFT OUTER JOIN FILTER_VALUES FV11 ON TF.fcst_nov = FV11.FVAL
LEFT OUTER JOIN FILTER_VALUES FV12 ON TF.fcst_dec = FV12.FVAL
where fcst_mgrid = '00809571'
and fcst_year = 2016
AND FV01.FVAL IS NULL
AND FV02.FVAL IS NULL
AND FV03.FVAL IS NULL
AND FV04.FVAL IS NULL
AND FV05.FVAL IS NULL
AND FV06.FVAL IS NULL
AND FV07.FVAL IS NULL
AND FV08.FVAL IS NULL
AND FV09.FVAL IS NULL
AND FV10.FVAL IS NULL
AND FV11.FVAL IS NULL
AND FV12.FVAL IS NULL
;
October 24, 2016 at 7:23 am
Wow, not pretty but it works. THANK YOU so much.
EDIT: Not pretty as in a lot more than I thought. It is very eloquent.
October 24, 2016 at 7:41 am
david.kiellar (10/24/2016)
Wow, not pretty but it works. THANK YOU so much.
You are very welcome.
😎
October 24, 2016 at 9:26 am
If I understand correctly, just change your ORs to ANDs in your original query. You're getting rows where ANY column does not contain any of those values, but you want rows where ALL columns do not contain any of those values.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 24, 2016 at 1:35 pm
drew.allen (10/24/2016)
If I understand correctly, just change your ORs to ANDs in your original query. You're getting rows where ANY column does not contain any of those values, but you want rows where ALL columns do not contain any of those values.Drew
Spot on Drew as always, just thought the approach was more legible
😎
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply