trying to get all data to line up

Question

Post reply

trying to get all data to line up

Siten0308

SSCarpal Tunnel

Points: 4404

August 2, 2019 at 3:56 am

#3668108

Hello,

first, thank you all who helped or took the time to look at my last issue, but now i got 1 more, and its a tough this time for me 🙁

so my 2 tables are:

declare @Employees TAble
(
employeeID int,
employeename varchar(256),
Hoursworked decimal(18,2),
PayrollDate date
)

insert into @Employees(employeeID,employeename,Hoursworked,PayrollDate)
values(1,'bob',3.0,'05/01/2019')
insert into @Employees(employeeID,employeename,Hoursworked,PayrollDate)
values(2,'bob',2.0,'05/05/2019')
insert into @Employees(employeeID,employeename,Hoursworked,PayrollDate)
values(2,'bob',3.0,'05/07/2019')
insert into @Employees(employeeID,employeename,Hoursworked,PayrollDate)
values(2,'bob',5.0,'05/11/2019')
insert into @Employees(employeeID,employeename,Hoursworked,PayrollDate)
values(2,'bob',1.0,'05/15/2019')
insert into @Employees(employeeID,employeename,Hoursworked,PayrollDate)
values(2,'Chris',3.0,'05/01/2019')
insert into @Employees(employeeID,employeename,Hoursworked,PayrollDate)
values(2,'Chris',3.0,'05/04/2019')
insert into @Employees(employeeID,employeename,Hoursworked,PayrollDate)
values(2,'Chris',2.0,'05/05/2019')
insert into @Employees(employeeID,employeename,Hoursworked,PayrollDate)
values(2,'Chris',6.0,'05/16/2019')
insert into @Employees(employeeID,employeename,Hoursworked,PayrollDate)
values(2,'Chris',4.0,'05/18/2019')
insert into @Employees(employeeID,employeename,Hoursworked,PayrollDate)
values(2,'Chris',3.0,'05/20/2019')
insert into @Employees(employeeID,employeename,Hoursworked,PayrollDate)
values(3,'Sam',3.0,'05/01/2019')
insert into @Employees(employeeID,employeename,Hoursworked,PayrollDate)
values(3,'Sam',6.0,'05/07/2019')

Declare @StartDate date = '05/01/2019', @EndDate date = '05/29/2019'

Declare @Dates Table
(
[START] Date,
[END] Date
)

INSERT INTO @dates
VALUES (@StartDate,@EndDate)

;WITH DateRange
AS 
(
SELECT 
                [START],ROW_NUMBER() over(partition by [Start] order by [Start]) Rownum
         FROM   @dates
         UNION ALL
         SELECT 
                Dateadd(day, 1, [start]),Rownum+1
         FROM   DateRange
         WHERE  start < @EndDate
)
select *
from DateRange

So i have the two tables, i would love the output to have something shown below:

Name Hours Date

Bob 3.0 5/1/2019

Bob 5/2/2019

Bob 5/3/2019

Bob 5/4/2019

Bob 2.0 5/5/2019

Bob 5/6/2019

Bob 3.0 5/7/2019

etc. etc. for each employee, i can sorta get it, but they only show up in rows, because i am left joining on by dates like so:

select *

from DateRange a

Left Join @Employees b

on a.[Start] = b.PayrollDate

which doesnt come out right... any idea what i can use?

thanks in advance 🙂

August 3, 2019 at 10:34 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65138 More actions · Answer 1

You need to produce a cartesion of unique employees (SELECT DISTINCT) and DateRange (CROSS JOIN) and LEFT JOIN the employees table

Far away is close at hand in the images of elsewhere.
Anon.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 2

Use a CTE to get the cartesian of unique employees, when left join.