September 19, 2013 at 7:05 pm
Hey all, I have a problem I'm trying to figure out here. I am trying to figure out the availability of a contact by comparing their available flag to the current day of the week. IE, the contact has 7 BIT fields in the table,1 for each day of the week, being T or F depending on if they are available. I'm trying to figure out how to read the correct field based on the day of the week to see if their available (T) that day for a notification. Each field name in the table is as such: mon, tue, wed, etc. I can get the current DOW from sql and trim it to the same length and case of the field names to try and figure out which one I need to check whether that fields contents are true or not, but I'm stumped on where to go from here. I can basically get around sql server MOST of the time, but I am by no means a pro, and this one has me stumped. I'm sure I have to be missing something obvious here. Any help is greatly appreciated!
Kevin
September 19, 2013 at 7:53 pm
A little DDL and sample data would have helped, but in this case it was easy enough to generate.
DECLARE @Contacts TABLE
(
ContactID INT IDENTITY PRIMARY KEY
,Sun BIT
,Mon BIT
,Tue BIT
,Wed BIT
,Thu BIT
,Fri BIT
,Sat BIT
);
INSERT INTO @Contacts
SELECT 1, 0, 1, 0, 1, 0, 1
UNION ALL SELECT 0, 1, 0, 1, 1, 0, 1
UNION ALL SELECT 1, 0, 1, 1, 1, 1, 1
UNION ALL SELECT 1, 0, 1, 1, 1, 1, 1
UNION ALL SELECT 1, 0, 0, 0, 0, 1, 1;
WITH Tally (n) AS (
SELECT 0 UNION ALL SELECT TOP 9 ROW_NUMBER() OVER (ORDER BY (SELECT $))
FROM sys.all_columns)
SELECT DATEADD(day, n, CAST(GETDATE() AS DATE)), ContactID
FROM Tally a
CROSS APPLY @Contacts b
CROSS APPLY (
VALUES (1, Sun),(2, Mon),(3, Tue),(4, Wed),(5, Thu),(6, Fri),(7, Sat)) c(m, Available)
WHERE Available = 1 AND m=DATEPART(weekday, DATEADD(day, n, CAST(GETDATE() AS DATE)));
This code will depend on how days are numbered (SET DATEFIRST) in your SQL Server. But basically I'm:
- generating a list of 10 dates starting today using the Tally table
- applying those dates to each contact (CROSS APPLY @Contacts)
- UNPIVOTing your date flag columns (see my signature article on the CROSS APPLY VALUES approach to UNPIVOT)
- Using the day number I put into the table row constructor (VALUES) to match with the day number of the date.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 19, 2013 at 8:00 pm
Dwain,
Thanks much for the response! I actually solved it by cheating a little in a select statement:
select *
from callflow_data.tmp_available_contacts
where ${tmp_dow} = 'true' ;tmp_dow contains the current DOW, 3 chars, named the same as my fields, so ${tmp_dow} = 'thu' and the field in my table is named the same so I can compare.
However, now that that works, I'm trying to compare 2 fields in the same table, start_time and end_time, both of the 'time' data type to see if the current time falls between those 2. However trying to compare them against getdate() gives me an error because of the different field types. Any pointers?
Kevin
September 19, 2013 at 8:17 pm
Do you mean something like this?
WITH SampleData (MyDate, StartTime, EndTime) AS (
SELECT '2013-09-20 08:20', '09:00', '11:00'
UNION ALL SELECT '2013-09-20 09:20', '09:00', '11:00'
UNION ALL SELECT '2013-09-20 11:20', '09:00', '11:00')
SELECT b.MyDate, b.StartTime, b.EndTime
,IsBetween=CASE WHEN CAST(b.MyDate AS TIME) BETWEEN b.StartTime AND b.EndTIme THEN 1 ELSE 0 END
FROM SampleData a
CROSS APPLY (
SELECT MyDate=CAST(MyDate AS DATETIME), StartTime=CAST(StartTime AS TIME), EndTime=CAST(EndTime AS TIME)
) b;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 19, 2013 at 8:21 pm
Well, that's a good question. I'm still relatively new to sql server and all it's nuances, especially dynamic stuff. So I'm not sure how to make this work using 2 variables. Something pseudo like this:
get * from table_name where getdate() between start_date and end_date
Does that make sense?
Kevin
September 19, 2013 at 8:30 pm
kevin 32621 (9/19/2013)
Well, that's a good question. I'm still relatively new to sql server and all it's nuances, especially dynamic stuff. So I'm not sure how to make this work using 2 variables. Something pseudo like this:get * from table_name where getdate() between start_date and end_date
Does that make sense?
Kevin
SELECT *
FROM table_name
WHERE CAST(GETDATE() AS TIME) BETWEEN start_date AND end_date;
Note that I'm a stickler for punctuation, indentation and capitalizing my keywords. All stylistic concerns that in the end make code more readable.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 19, 2013 at 8:35 pm
Thanks much dwain, I'll give that a try! I agree with proper formatting and try to do the same. Right now I just need to get it working properly (deadline this weekend) and I'll run it through the formatter when everything is complete. 🙂 I'll let you know how I make out. I appreciate the help.
Kevin
September 19, 2013 at 9:12 pm
Thanks Dwain that worked great. Now on to the next thing to bang my head against the wall for! 😀
Kevin
September 19, 2013 at 9:29 pm
kevin 32621 (9/19/2013)
Thanks Dwain that worked great. Now on to the next thing to bang my head against the wall for! 😀Kevin
You need what I have, which is a vibration sensor on my laptop so that when I bang my head against the desk, it pops up the SSC forum site in my browser window. 😛
Glad I could help.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 19, 2013 at 9:51 pm
I will DEFINITELY be back in the future as I feebly attempt to get more proficient at T-SQL and end up putting the vibration sensors mettle to the test! 😉
Kevin
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply