March 4, 2010 at 9:30 am
I have a column(daysWorked) in the installers table that includes a value like "1,2,3,4,5" (without quotes). I am trying to show a schedule when the installer is on or off.
I ran the following command.
SELECT CASE WHEN CONTAINS(daysWorked, DATEPART(dw, GetDate()) THEN 'ON' ELSE 'OFF') AS iAvailable FROM Installers
I receive the following error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'DATEPART'.
March 4, 2010 at 10:08 am
Is this what you are looking for
CREATE TABLE #Installers(id INT,daysWorked INT)
INSERT INTO #Installers
SELECT 1,5 UNION ALL
SELECT 2,4 UNION ALL
SELECT 3,3 UNION ALL
SELECT 4,0
SELECT id, CASE
WHEN (daysWorked = DATEPART(dw, GetDate())) THEN 'ON'
ELSE 'OFF'
END isAvailable
FROM #Installers
Results:
1 ON
2 OFF
3 OFF
4 OFF
Please note that you are more likely to recieve assistance if you post table definition, sample data and expected results as per the first link in my signature block. It helps those who want to help you....
March 4, 2010 at 12:39 pm
This is what I ended up coming up with and it returns the correct data but I have been told that using "LIKE" is a "no-no".
SELECT iAvailable = CASE WHEN daysWorked like '%' + Convert(varchar,DatePart(dw, GetDate())) + '%' THEN 'ON' ELSE 'OFF' END FROM Installers
March 4, 2010 at 12:54 pm
Jonathan Turlington
Not knowing your actual table structure / indexes etc., etc. My temp table did not have an index, so my code would run using a table scan ... not very efficient to say the least.
Run your code on a development DB and using SSMS select on the menu to show the Actual Execution Plan.
Run my suggestion and do the same ... this should allow you to determine which bit of code is most efficient -- then go with that one.
Use of LIKE has it's place, but generally (It depends) results in not being able to efficiently use indexes on a table. For your situation as I said above, test them, then make your decision.
If you run the selected T-SQL frequently you may want to add an index, or a covering index to make it run more efficeintly --
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply