• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

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
0
Seven price
Asked:
Seven price
7 Solutions
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now