CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2005select
Simon Kiely
Simon Kiely

Reputation: 6040

Unsure as to how to join two working queries together

I have two queries that function perfectly. I'd like to join the two queries together, but I can't figure out how to do this.

I have an SQL Server table structured as follows :

Table name : calendar.

Columns : 

Calendar Date (smalldatetime)
Working Day (bit)

Calendar date has all dates, structured in the format yyyy-mm-dd. Working day means that I have work if it is a 1, and if it is a weekend or a holiday it is marked as a 0 :).

Example of what I want to retrieve :

CalendarDate   NumWorkingDaysInMonthSoFar   NumWorkingDaysInThisMonthTotal
------------------------------------
2013-06-01    0 (--Due to this being marked a 0 as above (Saturday))      22
2013-06-02    0 (--Due to this being marked a 0 as above (Sunday) -- All 3 of these dates marked as 0)                22
2013-06-03    1 (--Due to this being marked a 0 as above (Bank Holiday))          22
2013-06-04    1             22
2013-06-05    2             22
2013-06-06    3             22

I have two queries to do both of these things separately, but I am struggling to figure out how to combine the two to produce the result set as above.

This is the query to return the NumberWorkingDaysInThisMonthTotal (22 in the example, should be a different value for every month): 

SELECT 
SUM(WorkingDay) As NumWorkingDaysInThisMonthTotal
FROM [calendar]
GROUP BY YEAR(CalendarDate), MONTH(CalendarDate)

And to return the number of working days in the month so far for each day (adds one every working day, resets at the beginning of every month): 

select c.[CalendarDate],
       (select sum(cast(WorkingDay as int))
        from calendar c2
        where year(c.[CalendarDate]) = year(c2.[CalendarDate]) and
              month(c.[CalendarDate]) = month(c2.[CalendarDate]) and
              c2.[CalendarDate] <= c.[CalendarDate]
       ) as NumWorkingDaysInMonthSoFar
from calendar c

How can I combine these to produce the result set above? I'd really appreciate a little bit of information on how you figured out how to do this - both in this specific case; and in where you gained your background in SQL so that I may improve myself. Thanks very much.

Upvotes: 0

Views: 72

Answers (2)

Matthew Walton
Matthew Walton

Reputation: 9959

This isn't likely to be the most efficient way to do it, but you could just stick your first query in as a subquery of the second (it's also untested, so it's entirely possible this will get you the price of silken tofu in Timbuktu last Wednesday instead of what you want):

select c.[CalendarDate],
   (SELECT SUM(C3.WorkingDay)
    FROM Calendar C3
    WHERE month(C3.CalendarDate) = month(c.CalendarDate)
      AND year(C3.CalendarDate) = year(c.CalendarDate)
   ) AS NumWorkingDaysInThisMonthTotal,
   (select sum(cast(WorkingDay as int))
    from calendar c2
    where year(c.[CalendarDate]) = year(c2.[CalendarDate]) and
          month(c.[CalendarDate]) = month(c2.[CalendarDate]) and
          c2.[CalendarDate] <= c.[CalendarDate]
   ) as NumWorkingDaysInMonthSoFar
from calendar c

It might even be fast enough for your purposes.

As you asked for some thought-process explanation, this kind of solution comes when I start thinking about two queries I want to combine as if they were both defined as views, or both had data sets that just happened to be compatible but were in different tables. How would I then join them together?

I do tend to end up doing things differently with pure subqueries than if I was using views, but thinking about it that way helps me figure out what the subqueries should actually be. I like to think about taking bits of data and sticking them together because it helps me figure out what I'm actually doing.

Admittedly I may then study it for a while longer and come up with some way to unify it into a more efficient solution. First-pass attempts aren't usually the most efficient, and obviously there are many situations where the speed of your query matters, but getting the right answer is the most important thing for me at first.

Upvotes: 1

user359040
user359040

Reputation:

You should be able to use a windowed analytical function, like so:

select c.[CalendarDate],
       SUM(WorkingDay) over (partition by YEAR(CalendarDate), MONTH(CalendarDate))
          As NumWorkingDaysInThisMonthTotal,
       (select sum(cast(WorkingDay as int))
        from calendar c2
        where year(c.[CalendarDate]) = year(c2.[CalendarDate]) and
              month(c.[CalendarDate]) = month(c2.[CalendarDate]) and
              c2.[CalendarDate] <= c.[CalendarDate]
       ) as NumWorkingDaysInMonthSoFar
from calendar c

Upvotes: 2

Related Questions