Basic SQL join issue

bmsjeff · April 19, 2019, 10:04pm

New at this and trying to get a query to provide me this:

I am trying to find all hours that were clocked in "TimeSlip" for a specific time period.
I want to then match any of those clocks with the actual "BilledHours"
There is not a direct relation between "TimeSlip" and "ServiceDocumentDetail", so I need to include "ServiceDocument".

When I run this query, I receive the data I want.
select "ServiceDocument"."Document_ID",
"ServiceDocumentDetail"."ServiceDocument_ID",
"ServiceDocumentDetail"."ActualHours",
"ServiceDocumentDetail"."BilledHours"
from ("dbo"."ServiceDocument" "ServiceDocument"
inner join "dbo"."ServiceDocumentDetail"
"ServiceDocumentDetail"
on ("ServiceDocumentDetail"."ServiceDocument_ID" = "ServiceDocument"."ServiceDocument_ID"))
where
("ServiceDocumentDetail"."Technician_ID" = 87)

I want to add a third table to give me the date that the "ServiceDocumentDetail"."BilledHours" where entered.
When I add the third table, all my data is duplicated and the previous data is not accurate.
I am obviously doing something wrong.

Here is the query that is not working properly.
select "TimeSlip"."ID", "TimeSlip"."DateIn",
"ServiceDocument"."Document_ID",
"ServiceDocumentDetail"."ServiceDocument_ID",
"ServiceDocumentDetail"."ActualHours",
"ServiceDocumentDetail"."BilledHours"
from (("dbo"."TimeSlip" "TimeSlip"
inner join "dbo"."ServiceDocument"
"ServiceDocument"
on ("ServiceDocument"."Document_ID" = "TimeSlip"."RepairOrderDocumentID"))
inner join "dbo"."ServiceDocumentDetail"
"ServiceDocumentDetail"
on ("ServiceDocumentDetail"."ServiceDocument_ID" = "ServiceDocument"."ServiceDocument_ID"))
where (((convert (datetime,
'2019-03-31 00:00:00',
120) <= "TimeSlip"."DateIn")
and ("TimeSlip"."DateIn" <= convert
(datetime, '2019-04-06 00:00:00', 120)))
and ("ServiceDocumentDetail"."Technician_ID" = 87))

I am obviously doing something wrong, but cannot figure it out???

bitsmed · April 20, 2019, 5:57am

I took the liberty of:

formating you query
removing unnessasary brackets and quotation marks
implementing best practice dealing filtering on datetime field
moving the filter on technician_id from where clause to inner join

select TimeSlip.ID
      ,TimeSlip.DateIn
      ,ServiceDocument.Document_ID
      ,ServiceDocumentDetail.ServiceDocument_ID
      ,ServiceDocumentDetail.ActualHours
      ,ServiceDocumentDetail.BilledHours
  from dbo.TimeSlip
       inner join dbo.ServiceDocument
               on ServiceDocument.Document_ID=TimeSlip.RepairOrderDocumentID
       inner join dbo.ServiceDocumentDetail
               on ServiceDocumentDetail.ServiceDocument_ID=ServiceDocument.ServiceDocument_ID
              and ServiceDocumentDetail.Technician_ID=87
 where TimeSlip.DateIn>=cast('2019-03-31 00:00:00' as datetime)
   and TimeSlip.DateIn< cast('2019-04-07 00:00:00' as datetime)

This makes you query much more readable.

Dealing with what you see as duplicates, you probably need to filter on more fields when you join servicedocumentdetail. To do that, one needs to know the table and its content.

As I see it, you can:

run your query to locate "a duplicate"
select * from dbo.ServiceDocumentDetail where ServiceDocument_ID=the_duplicate_you_just_found and Technician_ID=87
now see if there's a way to add additional filter to avoid duplicates

Or you can try:

select distinct
       TimeSlip.ID
      ,TimeSlip.DateIn
... (the rest you your query)

bmsjeff · April 20, 2019, 12:42pm

Thank you for your reply.
I am still having an issue with duplicates. I made the query smaller to show the issue:
select distinct
TimeSlip.ID
,TimeSlip.DateIn
,TimeSlip.RepairOrderDocumentNumber
,TimeSlip.TotalHours
,ServiceDocument.ServiceDocument_ID
from dbo.TimeSlip
inner join dbo.ServiceDocument
on ServiceDocument.Document_ID=TimeSlip.RepairOrderDocumentID
where TimeSlip.EmployeeID = 87
and TimeSlip.DateIn>=cast('2019-04-01 00:00:00' as datetime)
and TimeSlip.DateIn<= cast('2019-04-03 00:00:00' as datetime)

Provides me with
2535 2019-04-01 07:23:00.000 30301 4.9000 862
2636 2019-04-01 12:59:00.000 30301 3.8000 862
2692 2019-04-01 16:47:00.000 30301 0.6167 862
2707 2019-04-02 07:22:00.000 30301 1.1333 862

When I change it to this:
select distinct
TimeSlip.ID
,TimeSlip.DateIn
,TimeSlip.RepairOrderDocumentNumber
,TimeSlip.TotalHours
,ServiceDocument.ServiceDocument_ID
,ServiceDocumentDetail.ServiceDocument_ID
,ServiceDocumentDetail.ActualHours
,ServiceDocumentDetail.BilledHours
from dbo.TimeSlip
inner join dbo.ServiceDocument
on ServiceDocument.Document_ID=TimeSlip.RepairOrderDocumentID
inner join dbo.ServiceDocumentDetail
on ServiceDocumentDetail.ServiceDocument_ID=ServiceDocument.ServiceDocument_ID
where TimeSlip.EmployeeID = 87
and TimeSlip.DateIn>=cast('2019-04-01 00:00:00' as datetime)
and TimeSlip.DateIn<= cast('2019-04-03 00:00:00' as datetime)

I receive:
2535 2019-04-01 07:23:00.000 30301 4.9000 862 862 NULL NULL
2535 2019-04-01 07:23:00.000 30301 4.9000 862 862 NULL 0.5000
2535 2019-04-01 07:23:00.000 30301 4.9000 862 862 NULL 1.0000
2535 2019-04-01 07:23:00.000 30301 4.9000 862 862 NULL 1.4000
2535 2019-04-01 07:23:00.000 30301 4.9000 862 862 NULL 1.5000
2535 2019-04-01 07:23:00.000 30301 4.9000 862 862 NULL 2.0000
2535 2019-04-01 07:23:00.000 30301 4.9000 862 862 0.5000 0.5000
2535 2019-04-01 07:23:00.000 30301 4.9000 862 862 0.6167 0.5000
2535 2019-04-01 07:23:00.000 30301 4.9000 862 862 0.7000 0.7000
2535 2019-04-01 07:23:00.000 30301 4.9000 862 862 2.0000 1.0000
2535 2019-04-01 07:23:00.000 30301 4.9000 862 862 5.4166 1.5000
2535 2019-04-01 07:23:00.000 30301 4.9000 862 862 10.8667 2.0000
2636 2019-04-01 12:59:00.000 30301 3.8000 862 862 NULL NULL
2636 2019-04-01 12:59:00.000 30301 3.8000 862 862 NULL 0.5000
2636 2019-04-01 12:59:00.000 30301 3.8000 862 862 NULL 1.0000
2636 2019-04-01 12:59:00.000 30301 3.8000 862 862 NULL 1.4000
2636 2019-04-01 12:59:00.000 30301 3.8000 862 862 NULL 1.5000
2636 2019-04-01 12:59:00.000 30301 3.8000 862 862 NULL 2.0000
2636 2019-04-01 12:59:00.000 30301 3.8000 862 862 0.5000 0.5000
2636 2019-04-01 12:59:00.000 30301 3.8000 862 862 0.6167 0.5000
2636 2019-04-01 12:59:00.000 30301 3.8000 862 862 0.7000 0.7000
2636 2019-04-01 12:59:00.000 30301 3.8000 862 862 2.0000 1.0000
2636 2019-04-01 12:59:00.000 30301 3.8000 862 862 5.4166 1.5000
2636 2019-04-01 12:59:00.000 30301 3.8000 862 862 10.8667 2.0000
2692 2019-04-01 16:47:00.000 30301 0.6167 862 862 NULL NULL
2692 2019-04-01 16:47:00.000 30301 0.6167 862 862 NULL 0.5000
2692 2019-04-01 16:47:00.000 30301 0.6167 862 862 NULL 1.0000
2692 2019-04-01 16:47:00.000 30301 0.6167 862 862 NULL 1.4000
2692 2019-04-01 16:47:00.000 30301 0.6167 862 862 NULL 1.5000
2692 2019-04-01 16:47:00.000 30301 0.6167 862 862 NULL 2.0000
2692 2019-04-01 16:47:00.000 30301 0.6167 862 862 0.5000 0.5000
2692 2019-04-01 16:47:00.000 30301 0.6167 862 862 0.6167 0.5000
2692 2019-04-01 16:47:00.000 30301 0.6167 862 862 0.7000 0.7000
2692 2019-04-01 16:47:00.000 30301 0.6167 862 862 2.0000 1.0000
2692 2019-04-01 16:47:00.000 30301 0.6167 862 862 5.4166 1.5000
2692 2019-04-01 16:47:00.000 30301 0.6167 862 862 10.8667 2.0000
2707 2019-04-02 07:22:00.000 30301 1.1333 862 862 NULL NULL
2707 2019-04-02 07:22:00.000 30301 1.1333 862 862 NULL 0.5000
2707 2019-04-02 07:22:00.000 30301 1.1333 862 862 NULL 1.0000
2707 2019-04-02 07:22:00.000 30301 1.1333 862 862 NULL 1.4000
2707 2019-04-02 07:22:00.000 30301 1.1333 862 862 NULL 1.5000
2707 2019-04-02 07:22:00.000 30301 1.1333 862 862 NULL 2.0000
2707 2019-04-02 07:22:00.000 30301 1.1333 862 862 0.5000 0.5000
2707 2019-04-02 07:22:00.000 30301 1.1333 862 862 0.6167 0.5000
2707 2019-04-02 07:22:00.000 30301 1.1333 862 862 0.7000 0.7000
2707 2019-04-02 07:22:00.000 30301 1.1333 862 862 2.0000 1.0000
2707 2019-04-02 07:22:00.000 30301 1.1333 862 862 5.4166 1.5000
2707 2019-04-02 07:22:00.000 30301 1.1333 862 862 10.8667 2.0000

It is duplicating the data because the ID is different. 2535,2536,2692,2707

Ultimately, what I am looking for is:
Employee worked 10.45 hours = (2535 = 4.9) + (2536 = 3.8) + (2692 = .6167) + (2707 = 1.1333)
and of the 10.45 hours, we actually billed the customer 12.6 hours = (.5 + 1 + 1.4 + 1.5 + 2 + .5 + .5 + .7 + 1 + 1.5 + 2)

bitsmed · April 20, 2019, 2:50pm

How about this:

select TimeSlip.ID
      ,TimeSlip.DateIn
      ,TimeSlip.RepairOrderDocumentNumber
      ,TimeSlip.TotalHours
      ,sum(isnull(ServiceDocumentDetail.BilledHours,0)) as BilledHours
  from dbo.TimeSlip
       inner join dbo.ServiceDocument
               on ServiceDocument.Document_ID=TimeSlip.RepairOrderDocumentID
       inner join dbo.ServiceDocumentDetail
               on ServiceDocumentDetail.ServiceDocument_ID=ServiceDocument.ServiceDocument_ID
              and ServiceDocumentDetail.Technician_ID=TimeSlip.EmployeeID
 where TimeSlip.EmployeeID=87
   and TimeSlip.DateIn>=cast('2019-04-01 00:00:00' as datetime)
   and TimeSlip.DateIn< cast('2019-04-04 00:00:00' as datetime)
 group by TimeSlip.ID
         ,TimeSlip.DateIn
         ,TimeSlip.RepairOrderDocumentNumber
         ,TimeSlip.TotalHours
;