CASE statement to only show one day of the week

Question

Post reply

CASE statement to only show one day of the week

donato1026

SSCrazy

Points: 2765
More actions
March 11, 2011 at 1:21 pm

#236561

I have a simple condition:
If we have someone in our database that has a delivery charge of $99 or more, then we only want to show Saturday deliveries as an option, else if their delivery charge is less than that, then we want to show only WEEKDAYS. No Saturdays.
Here is the code. It's not working right. It either works one way or the other depending on how I write it. Any help would be greatly appreciated.
select TOP 6
email.zone_cd,
del.del_dt,
sum(points) as points_needed,
sum(points) + 15 as adj_points,
del_stops - actual_stops as points_avail
from
asap_email_listTrain email,
TRAIN..SALES.DEL,
TRAIN..MISC.ZIP2ZONE ZIP
where
pu_del = 'D'
and email.zone_cd is not null
and cust_cd = @CUST_CD
and del.del_dt > (dateadd(day, 4, getDate()))
and email.ship_to_zip_cd = zip.zip_cd
and del.zone_cd = zip.zone_cd
and (del.closed is null or del.closed = 'N')
and stat_cd = 'O'
and datename(weekday, del.del_dt) != case when email.del_chg < 99 then 'Saturday' else '' end
and datename(weekday, del.del_dt) not in ('Sunday','Monday')
group by email.zone_cd, del.del_dt, del_stops - actual_stops
having (del_stops - actual_stops) >= (sum(points) + 15)
ORDER BY del.del_dt
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Please post DDL and some DML to create test data for your tables:

asap_email_listTrain

TRAIN..SALES.DEL

TRAIN..MISC.ZIP2ZONE

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

donato1026 SSCrazy Points: 2765 More actions · Answer 2

opc.three (3/11/2011)
Please post DDL and some DML to create test data for your tables:
asap_email_listTrain
TRAIN..SALES.DEL
TRAIN..MISC.ZIP2ZONE
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Sorry, but I won't be able to do that. This can be used on any database . . . it's just the case statement I am having an issue with.

and datename(weekday, del.del_dt) != case when email.del_chg < 99 then 'Saturday' else '' end

Basically, if it is less than $99, then show any weekday - otherwise, ONLY show Saturdays.

Or you could write it, if it is $99 or more, then only show Saturdays as an option.

Thank you.

LutzM SSC Guru Points: 107049 More actions · Answer 3

donato1026 (3/11/2011)
...
Sorry, but I won't be able to do that. This can be used on any database . . . it's just the case statement I am having an issue with.
and datename(weekday, del.del_dt) != case when email.del_chg < 99 then 'Saturday' else '' end
Basically, if it is less than $99, then show any weekday - otherwise, ONLY show Saturdays.
Or you could write it, if it is $99 or more, then only show Saturdays as an option.
Thank you.

We're not talking about actual data. Set up a test scenario with fake data or use AdventureWorks table(s) to describe what you're looking for and what your final result should look like.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 4

donato1026 (3/11/2011)
opc.three (3/11/2011)
Please post DDL and some DML to create test data for your tables:
asap_email_listTrain
TRAIN..SALES.DEL
TRAIN..MISC.ZIP2ZONE
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
Sorry, but I won't be able to do that. This can be used on any database . . . it's just the case statement I am having an issue with.
and datename(weekday, del.del_dt) != case when email.del_chg < 99 then 'Saturday' else '' end
Basically, if it is less than $99, then show any weekday - otherwise, ONLY show Saturdays.
Or you could write it, if it is $99 or more, then only show Saturdays as an option.
Thank you.

Since it can be used on any database, then it really shouldn't be that hard to have some sample data. For instance:

-- See how you start off by actually creating a table and then

-- inserting the data into it? Your doing this makes it a lot easier

-- for all of us volunteers to help you. So, help us help you.

-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

DECLARE @test-2 TABLE (RowID int IDENTITY, del_chg numeric(5,2));

INSERT into @test-2

SELECT 55 UNION ALL

SELECT 99 UNION ALL

SELECT 1 UNION ALL

SELECT 100 UNION ALL

SELECT 150 UNION ALL

SELECT 90;

with cteDays (RowId, DayOfWeek, StartPrice, EndPrice) AS

(

-- need a table of the days of the week,

-- with the range of rates to include them for.

SELECT 1, 'Monday', .00, 89.99 UNION ALL

SELECT 2, 'Tuesday', .00, 89.99 UNION ALL

SELECT 3, 'Wednesday', .00, 89.99 UNION ALL

SELECT 4, 'Thursday', .00, 89.99 UNION ALL

SELECT 5, 'Friday', .00, 89.99 UNION ALL

SELECT 6, 'Saturday', 99.00, NULL

)

SELECT t.RowID,

t.del_chg,

ca.DayOfWeek

FROM @test-2 t

-- for each row, get the days that can be shipped to

CROSS APPLY (SELECT DayOfWeek

FROM cteDays

WHERE t.del_chg BETWEEN StartPrice AND EndPrice

OR (t.del_chg >= StartPrice AND EndPrice IS NULL)) ca;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

donato1026 SSCrazy Points: 2765 More actions · Answer 5

WayneS (3/11/2011)
donato1026 (3/11/2011)
opc.three (3/11/2011)
Please post DDL and some DML to create test data for your tables:
asap_email_listTrain
TRAIN..SALES.DEL
TRAIN..MISC.ZIP2ZONE
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
Sorry, but I won't be able to do that. This can be used on any database . . . it's just the case statement I am having an issue with.
and datename(weekday, del.del_dt) != case when email.del_chg < 99 then 'Saturday' else '' end
Basically, if it is less than $99, then show any weekday - otherwise, ONLY show Saturdays.
Or you could write it, if it is $99 or more, then only show Saturdays as an option.
Thank you.
Since it can be used on any database, then it really shouldn't be that hard to have some sample data. For instance:
-- See how you start off by actually creating a table and then
-- inserting the data into it? Your doing this makes it a lot easier
-- for all of us volunteers to help you. So, help us help you.
-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/
-- for more details on how to do all of this.
DECLARE @test-2 TABLE (RowID int IDENTITY, del_chg numeric(5,2));
INSERT into @test-2
SELECT 55 UNION ALL
SELECT 99 UNION ALL
SELECT 1 UNION ALL
SELECT 100 UNION ALL
SELECT 150 UNION ALL
SELECT 90;
with cteDays (RowId, DayOfWeek, StartPrice, EndPrice) AS
(
-- need a table of the days of the week,
-- with the range of rates to include them for.
SELECT 1, 'Monday', .00, 89.99 UNION ALL
SELECT 2, 'Tuesday', .00, 89.99 UNION ALL
SELECT 3, 'Wednesday', .00, 89.99 UNION ALL
SELECT 4, 'Thursday', .00, 89.99 UNION ALL
SELECT 5, 'Friday', .00, 89.99 UNION ALL
SELECT 6, 'Saturday', 99.00, NULL
)
SELECT t.RowID,
t.del_chg,
ca.DayOfWeek
FROM @test-2 t
-- for each row, get the days that can be shipped to
CROSS APPLY (SELECT DayOfWeek
FROM cteDays
WHERE t.del_chg BETWEEN StartPrice AND EndPrice
OR (t.del_chg >= StartPrice AND EndPrice IS NULL)) ca;

Thank you. I have caught on to your point/sarcasm, but you didn't even give me a chance to reply to the post before you. Thank you though. I wasn't trying to be difficult or rude, I simply misunderstood. And believe it or not . . . not all of us are SQL veterans, perhaps, such as yourself. So your volunteer services as well as others are greatly appreciated.

Cheers.