select if


I have a select statement and I like to know the correct syntax.
I would I implement this if
SELECT      
'TheWeek' = t.Fri,
      'Dept' = d.name
FROM prog p
--Joins here --
 WHERE if dept = gro then
@Week = t.Fri-4
else
@Week = t.Fri
LVL 9
Seven priceFull StackAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lluddenCommented:
Not sure what exactly you are trying to do, but here is my guess

SELECT      
t.Fri AS TheWeek,
d.name AS Dept
FROM prog p
--Joins here --
 WHERE dept = gro AND @Week = DATEADD(D,-4,t.Fri)
OR dept <> gro AND @Week = t.Fri
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MuffyBunnyCommented:
SELECT @Week = CASE WHEN d.name = 'gro' THEN t.Fri-4 ELSE t.Fri END 
FROM prog p
--Joins here--

Open in new window


what is t.Fri-4? is t.Fri holding an integer value that 4 can be subtracted from? Or are you trying to subtract a particular time interval from t.Fri?
0
tbsolutionsCommented:
I'm not sure if what you need is this:

SELECT t.Fri  AS 'TheWeek',
             d.name AS 'Dept'
  FROM prog p
--Joins here --
 WHERE (SELECT CASE
                       WHEN dept  = gro
                                      THEN @Week = t.Fri-4
                                      ELSE @Week = t.Fri
                END) = 'YOUR RULE'

Hope it helps!
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

SharathData EngineerCommented:
try like this.
SELECT      
'TheWeek' = t.Fri,
      'Dept' = d.name
FROM prog p
--Joins here --
 WHERE @Week = case when dept = gro then t.Fri-4 else t.Fri end

Open in new window

0
Seven priceFull StackAuthor Commented:
tbsolutions:>> Incorrect syntax near '='.  THEN @Week = t.Fri


>>lludden: Problem with the d part trying to use ww (weeks) but even if I just sample the day part it does not go back 4 days  but goes foward.

>>MuffyBunny:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

0
Seven priceFull StackAuthor Commented:
Ok

WHERE
d.name = 'gro' AND @Week = DATEADD(D,+4,t.Fri)

This seems to work but when I add the other 3 departments It does not show the correct info for example


WHERE
d.name = 'gro' AND @Week = DATEADD(D,+4,t.Fri) Or d.name = 'deli' AND @Week = DATEADD(D,+4,t.Fri) Or d.name = 'produce'' AND @Week = DATEADD(D,+4,t.Fri)

any suggestions on this final part, seems to work only by itself.
0
SharathData EngineerCommented:
Did you try my post? Regarding the WHERE condition in your last post, you can try like this.
WHERE (d.name = 'gro' AND @Week = DATEADD(D,+4,t.Fri)) 
   Or (d.name = 'deli' AND @Week = DATEADD(D,+4,t.Fri))
   Or (d.name = 'produce'' AND @Week = DATEADD(D,+4,t.Fri)) 

Open in new window

0
Seven priceFull StackAuthor Commented:
What is happening is lets say gro is the first one then it is showing double everyone else it is showing once.
0
SharathData EngineerCommented:
Try posts and let me know. If nothing is working, post some sample data with expected result.
0
Seven priceFull StackAuthor Commented:
   WHERE (d.name = 'grocery' AND @Week = DATEADD(D,0,t.FriTheWeek))
  Or (d.name = 'Bakery' AND @Week = DATEADD(D,+3,t.FriTheWeek))
 Or (d.name = 'Deli' AND @Week = DATEADD(D,+4,t.FriTheWeek))

The problem is it seems like a loop, for one dept results show correctly then the rest of the departments  it looks like it is in a loop because it will show the data repeatedly. at least twice
0
Seven priceFull StackAuthor Commented:
I try a common table expression  but having problem on the final part.

;WITH Dept_CTE As
(
SELECT * from program p
 WHERE
(d.Department  = '1' AND @Week = DATEADD(D,0,t.FriTheWeek))
)-- 1 >> grocery
, t
as
(
SELECT * from program pm
  WHERE
(d.Department  = '10' AND @Week = DATEADD(D,+4,t.FriTheWeek))
)-- 10 >> deli

select * from t as a
inner join Dept_CTE D on d.dept = a.dept
0
SharathData EngineerCommented:
As asked earlier, can you post some sample data with expected result.
0
Seven priceFull StackAuthor Commented:
;WITH Dept_CTE As
(
  SELECT *
  FROM program
  WHERE
  (Department  = '1' AND @Week = DATEADD(D,0,t.FriTheWeek))
Union all
  (Department  = '10' AND @Week = DATEADD(D,+4,t.FriTheWeek))
)


select * from  Dept_CTE
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.