Conditional Where - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Conditional Where

Author

Topic

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2014-05-06 : 20:04:17

I'm trying to create what I imagine would be a conditional where statement - a user selects from a small group of options and the where close depends on which option is pressed. This is what I have so far. Any suggestions are warmly welcomed.

DECLARE @Calendar1 AS DateTime
DECLARE @Calendar2 AS DateTime
DECLARE @Weekends char(1)

SET @Calendar1 = '{{{ Please choose a start date. }}}'
SET @Calendar2 = '{{{ Please choose an end date. <(non inclusive)> }}}'

SET @Weekends = '<<View (W)weekends only, (D) Weekdays or (A)ll?>>'

SELECT @Weekends =
CASE
WHEN @Weekends = 'W' THEN '1'
WHEN @Weekends = 'w' THEN '1'
WHEN @Weekends = 'D' THEN '2'
WHEN @Weekends = 'd' THEN '2'
ELSE '3'
END

CASE @Weekends = '1' THEN BEGIN

SELECT

DATEPART(HOUR,O.ReadyTimeFrom) AS Hour,
COUNT(DISTINCT P.OrderID) as orderCount,
SUM(P.Price) as Total

FROM
tblorder as O
INNER JOIN tblOrderDrivers as OD ON OD.OrderID = O.OrderID
INNER JOIN tblDrivers AS D ON D.DriverID = OD.DriverID
INNER JOIN tblOrderItems AS P ON P.OrderID = OD.OrderID
INNER JOIN tblOrderTypes AS OT ON OT.OrderTypeID = O.OrderType

WHERE O.ReadyTimeFrom >= @Calendar1 AND O.ReadyTimeFrom <= @Calendar2 + 1
AND O.Status <> 4
AND ((DATEPART(dw, O.ReadyTimeFrom) + @@DATEFIRST) % 7) IN (0, 1)

GROUP BY DATEPART(HOUR,O.ReadyTimeFrom)

END

CASE @Weekends = '1' THEN BEGIN

SELECT

DATEPART(HOUR,O.ReadyTimeFrom) AS Hour,
COUNT(DISTINCT P.OrderID) as orderCount,
SUM(P.Price) as Total,
SUM(O.Distance) AS TotalMiles, @Weekends AS 'Weekends'

FROM
tblorder as O
INNER JOIN tblOrderDrivers as OD ON OD.OrderID = O.OrderID
INNER JOIN tblDrivers AS D ON D.DriverID = OD.DriverID
INNER JOIN tblOrderItems AS P ON P.OrderID = OD.OrderID
INNER JOIN tblOrderTypes AS OT ON OT.OrderTypeID = O.OrderType

WHERE O.ReadyTimeFrom >= @Calendar1 AND O.ReadyTimeFrom <= @Calendar2 + 1
AND O.Status <> 4
AND O.CSR NOT LIKE 'Scheduler%'
AND ((DATEPART(dw, O.ReadyTimeFrom) + @@DATEFIRST) % 7) NOT IN (0, 1)

GROUP BY DATEPART(HOUR,O.ReadyTimeFrom)
END

CASE @Weekends = '3' THEN BEGIN

SELECT

DATEPART(HOUR,O.ReadyTimeFrom) AS Hour,
COUNT(DISTINCT P.OrderID) as orderCount,
SUM(P.Price) as Total,
SUM(O.Distance) AS TotalMiles, @Weekends AS 'Weekends'

FROM
tblorder as O
INNER JOIN tblOrderDrivers as OD ON OD.OrderID = O.OrderID
INNER JOIN tblDrivers AS D ON D.DriverID = OD.DriverID
INNER JOIN tblOrderItems AS P ON P.OrderID = OD.OrderID
INNER JOIN tblOrderTypes AS OT ON OT.OrderTypeID = O.OrderType

WHERE O.ReadyTimeFrom >= @Calendar1 AND O.ReadyTimeFrom <= @Calendar2 + 1
AND O.Status <> 4
AND O.CSR NOT LIKE 'Scheduler%'
AND ((DATEPART(dw, O.ReadyTimeFrom) + @@DATEFIRST) % 7) NOT IN (0, 1)

GROUP BY DATEPART(HOUR,O.ReadyTimeFrom)
END

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-06 : 20:14:20

I have not gone through your logic entirely, but it seems complex to follow, and it does not appear to be scalable. Rather than trying to write case expressions for all possible cases, you might consider using dynamic SQL. Dynamic SQL is discouraged in most cases, but this is one of those cases where it is well-suited. It results in simpler code, can be written without SQL injection risks, and generates a good execution plan.

Take a look at these articles for insight into this approach, and how to write those:
http://www.sommarskog.se/dyn-search-2008.html
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2014-05-06 : 20:41:02

I may have overcomplicated it.

The same columns are pulled but there are three possible WHERE conditions.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-05-07 : 04:56:48

condition @Weekends = '1' appear twice, which should be correct?


SELECT
	Hour = DATEPART(HOUR,O.ReadyTimeFrom), 
	orderCount = COUNT(DISTINCT P.OrderID),
	Total = SUM(P.Price),
	TotalMiles = SUM(O.Distance),
	'Weekends' = @Weekends

FROM	tblorder as O
	INNER JOIN tblOrderDrivers as OD 
		ON OD.OrderID = O.OrderID
	INNER JOIN tblDrivers AS D 
		ON D.DriverID = OD.DriverID
	INNER JOIN tblOrderItems AS P 
		ON P.OrderID = OD.OrderID
	INNER JOIN tblOrderTypes AS OT 
		ON OT.OrderTypeID = O.OrderType

WHERE	(O.ReadyTimeFrom >= @Calendar1 
	AND O.ReadyTimeFrom <= @Calendar2 + 1
	AND O.Status <> 4)
	AND (
			(	@Weekends = '1'
				AND ((DATEPART(dw, O.ReadyTimeFrom) + @@DATEFIRST) % 7) IN (0, 1)
			)OR
			(	@Weekends <> '1'
				AND O.CSR NOT LIKE 'Scheduler%'
				AND ((DATEPART(dw, O.ReadyTimeFrom) + @@DATEFIRST) % 7) NOT IN (0, 1)
			)
		)

GROUP BY DATEPART(HOUR,O.ReadyTimeFrom)

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2014-05-07 : 13:46:47

That was awesome. Thank you very much.

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2014-05-07 : 14:57:12

I wanted to give a user the option of selecting (S)aturdays only. I defined @Weekends as '4' and added the following but it does not work:

OR
( @Weekends <> '4'
AND O.CSR NOT LIKE 'Scheduler%'
AND ((DATEPART(dw, O.ReadyTimeFrom) + @@DATEFIRST) % 6) IN (0, 1)
)

Any idea what I'm doing wrong?

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-05-08 : 19:55:36

due to yours upper query?

quote:
SELECT @Weekends =
CASE
WHEN @Weekends = 'W' THEN '1'
WHEN @Weekends = 'w' THEN '1'
WHEN @Weekends = 'D' THEN '2'
WHEN @Weekends = 'd' THEN '2'
ELSE '3'
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources