0

I've created a virtual table in SQL Server that has 28 days from the current date and each date has rows for time that range from 12-10 pm incremented by 15 min and another value to indicate that it's turned on/off for availability, so it would be something like this:

date          time          onoff
-------------------------------------------------
2015-04-08    12:00         1
2015-04-08    12:15         1
....continue until 22:00 then start next day
2015-04-09    12:00         1
..... continue for 28 days

I'd like to update the availability based on a query from another table which would return the date, start and end time...

So far I came up with this

update table1 
set onoff = 0 
where tbl1date in (select tbl2date from table2 where userid = 1)

The problem I'm having is adding in the between certain hours part of the equation and I'm not sure how to do something like this in SQL or how to even search for the answer based on not being able to word it properly...

Can someone help or point me in the right direction?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
George
  • 11
  • 4
  • Not sure if I understood the problem correctly, but you can use joins in the update, like "update x set ... from table1 x join table2 y on ...". Maybe that way you can add correct criteria? – James Z Apr 08 '15 at 16:27
  • tried to give visual of table in my question by spacing it out but didn't workout... table1 has 3 columns date, time, onoff the dates will fill from current date to 28 days ahead, and have rows that range from 12:00 to 10:00 pm incremented by 15 min for each date, the onoff value will be set to 1 by default then if a user would like to take time off they have a table where their userid, along with the date, start time, end time need to set the onoff value to 0 in the first table... I'd like to use a query from table2 to do update onoff values in table1 to 0 – George Apr 08 '15 at 16:40
  • if I say update table1 set onoff =0 where date in (date query from table2) I'm missing the between hours part but I can't see how I can add that in as a second query would be called not tying into the first one for the dates, hopefully this makes sense, having a hard time explaining it lol.... – George Apr 08 '15 at 16:43
  • possible duplicate of [Update a table using JOIN in SQL Server?](http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server) – Tab Alleman Apr 08 '15 at 17:58

3 Answers3

0

use a DATETIME, don't use separate DATE and TIME fields.

Will In The World
  • 276
  • 1
  • 7
0

I think you should take a look at DATEDIFF (https://technet.microsoft.com/pl-pl/library/ms189794(v=sql.110).aspx) function.

Your where query could look like this: 

update table1 set onoff = 0 
where 
    DATEDIFF(minute, <MIN_RANGE>, tbl1date) >= 0 and 
    DATEDIFF(minute, tbl1date, <MAX_RANGE>) >= 0

How you calculate MIN_RANGE and MAX_RANGE depends on your table2 structure.

mkarczewski
  • 109
  • 1
  • 9
  • I agree with Will In The World. Keeping DATETIME values will make things easier. – mkarczewski Apr 08 '15 at 16:37
  • I tried giving you a vote up but don't have a strong enough reputation to do so, think your suggestion might help going to try to change a few things around and see if I can make it work that way, thanks again.... – George Apr 08 '15 at 16:55
0

As suggested, if you have control over the structure, use datetime fields as they are easier to do the comparisons on. I'm going to assume you don't have control over the structure.

In order to compare the datetimes you need to create them from your separate date and times. You can either parse the time field for the hours and minutes and use DATEADD to add the appropriate offsets to the date, or you can use CONVERT to interpret a date time string as a date. Something like 

CONVERT(datetime, SUBSTRING(CONVERT(varchar, tbl1date, 121), 1, 10) + ' ' + tbl1time, 121)

What this does is to convert the date to odbc cannonical format and throwaway the time part as it takes only the first 10 characters. Then it appends the time and interprets the whole string as a odbc cannonical datetime string. That format is yyyy-mm-dd hh:mi:ss.mmm. The hours are based on 24 hours. So if your times are in AM/PM format you're going to have to convert them.

If your other table has separate date and times you'd use a similar expression to combine them.

Once you have the datetimes you can do something like this

UPDATE table1
SET onoff = 0
WHERE <expression above> BETWEEN (SELECT min_value FROM table2) AND (SELECT max_value FROM table2)
Will Rickards
  • 2,776
  • 2
  • 19
  • 25