CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user1735894
user1735894

Reputation: 323

SQL query to create rows for missing range

Thats probably not the best description of the question but here we go.

So I have some data that looks something like this, notice that for the Schoolhouse project Tom only has entries for 12/4 & 12/8 while for the pool project he has an entry through the 25th.

Consultant   Project       PM    Week        Hours
Tom          Schoolhouse  Sue    12/4/2016     4
Tom          Schoolhouse  Sue    12/11/2016    6
Tom          Pool         Joe    12/4/2016     9
Tom          Pool         Joe    12/11/2016    8
Tom          Pool         Joe    12/18/2016    12
Tom          Pool         Joe    12/25/2016    3

I am looking for a query where I can output rows through the entire range for both\all projects. So ideally the result would look something like this

Consultant   Project       PM    Week        Hours
Tom          Schoolhouse  Sue    12/4/2016     4
Tom          Schoolhouse  Sue    12/11/2016    6
Tom          Schoolhouse  Sue    12/18/2016    0
Tom          Schoolhouse  Sue    12/25/2016    0
Tom          Pool         Joe    12/4/2016     9
Tom          Pool         Joe    12/11/2016    8
Tom          Pool         Joe    12/18/2016    12
Tom          Pool         Joe    12/25/2016    3

Now here is the kicker... I am writing this query in a system where I have limited SQL capabilities. I cannot use functions or cursors or variables. I can only write a basic SQL statement. I did try to figure something out where I populated a table with the ranges that I need and then joined to this table but it isn't working like I would expect.

Upvotes: 0

Views: 54

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146499

try this:

select d.Consultant, p.Project, 
     d.PM, w.Week, isnull(d.Hours, 0) hours
From 
    (Select distinct project from mytable) p
      cross join
    (Select distinct week from myTable) w
  left join mytable d
      on d.project = p.Project
         and d.week = w.week

if consultant and pm are dependent on Project, then:

select p.Consultant, p.Project, 
     p.PM, w.Week, isnull(d.Hours, 0) hours
From 
    (Select distinct Consultant, project, PM from mytable) p
      cross join
    (Select distinct week from myTable) w
  left join mytable d
      on d.project = p.Project
         and d.week = w.week

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Charles's idea is on the right track, but you need to include the right columns and select from the correct tables:

select t.Consultant, t.Project, t.PM, w.Week,
       coalesce(t.Hours, 0) as hours
From (Select distinct Consultant, Project, PM from t) cpp cross join
     (Select distinct week from t) w left join
     t 
     on t.consultant = cpp.consultant and .project = cpp.Project and
        t.PM = cpp.PM and t.week = w.week;

Upvotes: 2

Related Questions