CODEWITHSUNDEEP
sql-serverreporting-services
Ali Aslam
Ali Aslam

Reputation: 115

Filling up a table in sql with data from another table only if it does not already exist

I am working on a problem in SQL Server that is mind boggling. What I am trying to accomplish is, I have a table temp2 (picture below) that houses data from a lot of inner joins which is then used for a SSRS report.

The problem I am trying to solve is, how can I fill in the missing titles for each employee even if they have not put any values in it for the dates provided?

Question is, is it possible to fill in the missing titles from ProjectName for each Employee? As seen in the SSRS report, each employee should have all of the ProjectName being returned from the data set which is reading the table temp2...

Temp2ProjectNamessrs reporting

EDIT

So This is what I tried and even Though I have gotten all the projectnames into my temp2, this is ugly and inefficient. The ssrs will take too long to run because of unwanted data. 

Select distinct Employee = Coalesce(a.Employee, @SelectEmployee), EmpId = Coalesce(a.EmpId, (Select PkId from AllRef Where Ness='All')), c.Day, Title=Coalesce((case when a.Title like '%-%'
             then left(a.Title, charindex('-', a.Title))
             else a.Title
        end),''), p.ProjectName, Description =coalesce(a.Description,''), Val = Coalesce(a.Val,''), AbbrevJob = COALESCE(a.abbrevjob, ''), 
                week1Total=(select sum(val) as week1 from temp1 WHERE  day >= Dateadd("d", -14, @WeekEnding) AND day <= Dateadd("d", -7, @WeekEnding)),
                week2Total=(select sum(val) as week2 from temp1 WHERE  day >= Dateadd("d", -7, @WeekEnding) AND day <= @WeekEnding ) 
from dbo.Calender as c
left outer join temp2 as a
on c.Day = a.Day 
cross join ProjectName p
--on p.PkId = a.Abbrevjob-2
Where c.Day >= Dateadd("d",-13,@WeekEnding) and c.Day <= @WeekEnding 
order by EmpId asc

The Cross Join did accomplish the task but the repetition is killing performance. Anyone knows how to deal with that?

Upvotes: 2

Views: 84

Answers (2)

Andomar
Andomar

Reputation: 238296

The usual way to do that is to build a matrix of all employees and all projects, and then optionally query the hours. For example:

; with  Employees as
        (
        select  distinct EmployeeName
        from    TableWithEmployees
        )
,       Projects as
        (
        select  distinct ProjectName
        from    TableWithProjects
        )
select  *
from    Employees e
cross join
        Projects p
left join
        TableWithDetails d
on      d.EmployeeName = e.EmployeeName
        and d.ProjectName = p.ProjectName

The left join means that rows without details will not be filtered out.

Upvotes: 2

GSazheniuk
GSazheniuk

Reputation: 1384

Though I never done it before, solution that comes upon my mind is pretty simple...

The problem with that report is that you try to inner join data with the project name table. It's not actually the problem, it's the right approach that everyone uses when they need such type of report.

But when it comes to the problem you stated, then it is the problem...

The point of getting the whole list of project names is outer joining project name table. But in that case you still get only projects per employee and if there is any project name not filled by employee record, it will still appear on the list, but only once and not assigned to any employee. And that's not what you need.

So solution is to outer join employees and project names first, and then inner join your data table.

You can just pick DISTINCT empId values from your data table and FULL OUTER JOIN it on ProjectNames. Then LEFT OUTER JOIN results to data table, this time by empId = empId and PkId = AbbrevJob (if I got right the columns which hold project name id-s).

It should work, let me know please whether it does or not, good luck with that!

Upvotes: 0

Related Questions