Rewrite update to get rid of subquery 1

[kaht]

I have 2 tables, a calendar table and a vacation table. In the calendar table there is a date column and a slotsFull column. The slotsFull column represents how many people have taken a vacation for that particular time. In the vacation table there is a vacStart and a vacStop column that denotes when the vacation starts and stops.

I am trying to rewrite a procedure called updateSlots that will count the number of rows in the vacation table that span each time in the calendar and set the slotsFull column to that number. A version of this code was written by U.R. already that uses a subquery to update the data and it works correctly:

[COLOR=blue]create[/color] [COLOR=blue]table[/color] #calendar (vacDate [COLOR=blue]smalldatetime[/color], slotsFull [COLOR=blue]tinyint[/color])
[COLOR=blue]set[/color] [COLOR=#FF00FF]nocount[/color] [COLOR=blue]on[/color]
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #calendar [COLOR=blue]select[/color] [COLOR=red]'2007-01-01 08:00'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #calendar [COLOR=blue]select[/color] [COLOR=red]'2007-01-01 08:15'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #calendar [COLOR=blue]select[/color] [COLOR=red]'2007-01-01 08:30'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #calendar [COLOR=blue]select[/color] [COLOR=red]'2007-01-01 08:45'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #calendar [COLOR=blue]select[/color] [COLOR=red]'2007-01-02 08:00'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #calendar [COLOR=blue]select[/color] [COLOR=red]'2007-01-02 08:15'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #calendar [COLOR=blue]select[/color] [COLOR=red]'2007-01-02 08:30'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #calendar [COLOR=blue]select[/color] [COLOR=red]'2007-01-02 08:45'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #calendar [COLOR=blue]select[/color] [COLOR=red]'2007-01-03 08:00'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #calendar [COLOR=blue]select[/color] [COLOR=red]'2007-01-03 08:15'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #calendar [COLOR=blue]select[/color] [COLOR=red]'2007-01-03 08:30'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #calendar [COLOR=blue]select[/color] [COLOR=red]'2007-01-03 08:45'[/color], 0

[COLOR=blue]create[/color] [COLOR=blue]table[/color] #vacation (vacStart [COLOR=blue]smalldatetime[/color], vacStop [COLOR=blue]smalldatetime[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #vacation [COLOR=blue]select[/color] [COLOR=red]'2007-01-01 08:00'[/color], [COLOR=red]'2007-01-01 09:00'[/color]
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #vacation [COLOR=blue]select[/color] [COLOR=red]'2007-01-01 08:00'[/color], [COLOR=red]'2007-01-01 08:30'[/color]
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #vacation [COLOR=blue]select[/color] [COLOR=red]'2007-01-02 08:30'[/color], [COLOR=red]'2007-01-02 08:45'[/color]
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #vacation [COLOR=blue]select[/color] [COLOR=red]'2007-01-03 08:15'[/color], [COLOR=red]'2007-01-03 08:45'[/color]

[COLOR=green]--update using crappy subquery
[/color][COLOR=blue]update[/color] #calendar 
[COLOR=blue]set[/color] slotsFull = (
   [COLOR=blue]select[/color] [COLOR=#FF00FF]count[/color](*) 
   [COLOR=blue]from[/color] #vacation 
   [COLOR=blue]where[/color] vacStart <= #calendar.vacDate 
      and vacStop > #calendar.vacDate
)

[COLOR=green]--display results
[/color][COLOR=blue]select[/color] * [COLOR=blue]from[/color] #calendar

[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #calendar
[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #vacation

It provides the following results, which is correct:

vacDate                                                slotsFull 
------------------------------------------------------ --------- 
2007-01-01 08:00:00                                    2
2007-01-01 08:15:00                                    2
2007-01-01 08:30:00                                    1
2007-01-01 08:45:00                                    1
2007-01-02 08:00:00                                    0
2007-01-02 08:15:00                                    0
2007-01-02 08:30:00                                    1
2007-01-02 08:45:00                                    0
2007-01-03 08:00:00                                    0
2007-01-03 08:15:00                                    1
2007-01-03 08:30:00                                    1
2007-01-03 08:45:00                                    0

Now... I can recreate the desired result set by adding a tinyint column to the vacation table to have a column to sum up the values and running the following query:

[COLOR=blue]create[/color] [COLOR=blue]table[/color] #vacation (vacStart [COLOR=blue]smalldatetime[/color], vacStop [COLOR=blue]smalldatetime[/color], blah [COLOR=blue]tinyint[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #vacation [COLOR=blue]select[/color] [COLOR=red]'2007-01-01 08:00'[/color], [COLOR=red]'2007-01-01 09:00'[/color], 1
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #vacation [COLOR=blue]select[/color] [COLOR=red]'2007-01-01 08:00'[/color], [COLOR=red]'2007-01-01 08:30'[/color], 1
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #vacation [COLOR=blue]select[/color] [COLOR=red]'2007-01-02 08:30'[/color], [COLOR=red]'2007-01-02 08:45'[/color], 1
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #vacation [COLOR=blue]select[/color] [COLOR=red]'2007-01-03 08:15'[/color], [COLOR=red]'2007-01-03 08:45'[/color], 1

[COLOR=blue]select[/color] c.vacDate, sum([COLOR=#FF00FF]coalesce[/color](v.blah, 0))
[COLOR=blue]from[/color] #calendar c
   [COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] #vacation v
   [COLOR=blue]on[/color] v.vacStart <= c.vacDate
      and v.vacStop > c.vacDate
[COLOR=blue]group[/color] [COLOR=blue]by[/color] c.vacDate

However, this brings up 2 issues. I'm running into an error that says "An aggregate may not appear in the set list of an UPDATE statement." when I try to run the following update:

[COLOR=blue]update[/color] #calendar 
[COLOR=blue]set[/color] vacDate = sum([COLOR=#FF00FF]coalesce[/color](v.blah, 0))
[COLOR=blue]from[/color] #calendar c
   [COLOR=blue]inner[/color] [COLOR=blue]join[/color] #vacation v
   [COLOR=blue]on[/color] v.vacStart <= c.vacDate
      and v.vacStop > c.vacDate
[COLOR=blue]group[/color] [COLOR=blue]by[/color] c.vacDate

So I have 2 questions. Can this update be ran using a join instead of the subquery? And if so, will I need to add the blah column to form a basis for the sum - or should I use a cast operator on data that already exists in the table?

Or, did U.R. already write the most efficient solution? I'm guessing that (as usual) this is not the case.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[gmmastros]

There is a difference between a sub-query and a derived table. The query I show below is an example of a derived table. In my experience, they usually perform better than sub-queries.

[COLOR=blue]Update[/color] #Calendar
[COLOR=blue]Set[/color]    slotsFull = A.SlotsFull
[COLOR=blue]From[/color]   #Calendar
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
         [COLOR=blue]select[/color] c.vacDate, sum([COLOR=#FF00FF]coalesce[/color](v.blah, 0)) [COLOR=blue]As[/color] SlotsFull
         [COLOR=blue]from[/color] #calendar c
            [COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] #vacation v
            [COLOR=blue]on[/color] v.vacStart <= c.vacDate
               and v.vacStop > c.vacDate
         [COLOR=blue]group[/color] [COLOR=blue]by[/color] c.vacDate
         ) [COLOR=blue]As[/color] A
         [COLOR=blue]On[/color] #Calendar.vacDate = A.vacDate

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kaht]

Thanks for the reply George. What is your opinion on the blah column? Is it necessary or should I put in some other way to generate the sum?

Normally, you can do something like this:

[COLOR=blue]select[/color] 1 [COLOR=blue]from[/color] table1

But in the case of the join, I'd want the vacation table to return 1 if it meets the criteria declared in the "on" clause. However, you can't do this:

[COLOR=blue]select[/color] a.something, sum([COLOR=#FF00FF]coalesce[/color]([!]b.1[/!], 0))
[COLOR=blue]from[/color] table1 a
   [COLOR=blue]left[/color] [COLOR=blue]join[/color] table2 b
   [COLOR=blue]on[/color] a.thing = b.thing

In this case I want table b to return a 1 when a row is found that matches the "on" clause, or a 0 if it's not found. In my example above I created the blah column to give me a 1 to return, but this seems like a lot of wasted space to create a column specfically for that reason. I guess this code would produce the same thing w/o the need for the extra column, but just kinda seems sloppy. Is there an easier way to do this?

[COLOR=blue]select[/color] a.something, sum([COLOR=#FF00FF]coalesce[/color]([COLOR=blue]case[/color] [COLOR=blue]when[/color] (b.thing = b.thing) [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color], 0))
[COLOR=blue]from[/color] table1 a
   [COLOR=blue]left[/color] [COLOR=blue]join[/color] table2 b
   [COLOR=blue]on[/color] a.thing = b.thing

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[gmmastros]

Do you have an identity column in the 'b' table. If so...

select a.something, sum(coalesce([!]Sign(b.IdentityCol)[/!], 0))
from table1 a
   left join table2 b
   on a.thing = b.thing

Sign can only return -1, 0, or 1.

Ex:
[tt][blue]Select Sign(-2), Sign(-1), sign(0), Sign(1), sign(2)[/blue][/tt]

You could also try converting to bit.

sum(coalesce([!]Convert(Bit, b.SomeDateCol)[/!], 0))

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kaht]

No, the vacation table does not have an identity column. I did consider the bit conversion because I had a bit column in another table that I did something similar to a few weeks ago. However, I haven't played around with converting other datatypes to bits, so I wasn't sure what would become 1s and what would become 0s. I'll play around with it and see what I can come up with. Thanks again George.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[gmmastros]

The only date, when converted to bit, that becomes a 0 is Jan 1, 1900 (at midnight). This is probably NOT an issue for a vacation planner application. (that ship has sailed).



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kaht]

Thanks for the tip George. Sadly, the application does use 1900-01-01 as a default date for some functions (mainly because I knew it wouldn't ever be selected for vacation time). However, I don't think it's ever stored in a table that way, only passed as 0 as a stored procedure parameter to tell the application to find a default date from the calendar for example.

Thanks again.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson