asked on

Minimum Maximum 2 tables

Hello,
Can you please help,
I need to get the [Driver_In] Minimum Date Time/ [Driver_Out] Maximum Date Time between 2 tables (Per Driver) , then calculate the difference (Hours and Minutes),
This is What I have so far (But , it is wrong, I'm getting 2 lines per Driver

Select PickUpDriver AS [Driver], (Select Name From Drivers Where Drivers.DriverNumber = ActiveOrders.PickUpDriver) AS [Name],
CONVERT(date, OrderDate) AS [Order_Date],MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)) AS [Driver_In],Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME)) AS [Driver_Out],
DATEDIFF(second, MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)), Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME))) / 3600.00 AS [Hours_Worked],
DATEDIFF(minute, MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)), Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME))) AS [Minutes_Worked]
From ActiveOrders
where (((OrderDate > = DATEADD(d,0,DATEDIFF(d,0,GetDate()))) And (OrderDate <= DATEADD(s, +86399, DATEADD(d,0,DATEDIFF(d,0,GetDate())))))) AND Cast(datepart(yyyy,PickedUpTime)as varchar) + '-' + RIGHT('0' + RTRIM(MONTH(PickedUpTime)), 2) + '-' + Right('0'+cast(datepart(DD,PickedUpTime)as varchar(2)),2) = Cast(datepart(yyyy,DeliveredTime)as varchar) + '-' + RIGHT('0' + RTRIM(MONTH(DeliveredTime)), 2) + '-' + Right('0'+cast(datepart(DD,DeliveredTime)as varchar(2)),2)
And ((Select Name From Drivers Where Drivers.DriverNumber = ActiveOrders.PickUpDriver) Like 'AMV%' OR (Select Name From Drivers Where Drivers.DriverNumber = ActiveOrders.PickUpDriver) Like 'AMT%')
Group By PickUpDriver ,CONVERT(date, OrderDate)
UNION ALL
Select PickUpDriver AS [Driver],(Select Name From Drivers Where Drivers.DriverNumber = FinalizedOrders.PickUpDriver) AS [Name],
CONVERT(date, OrderDate) AS [Order_Date], MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME))AS [Driver_In],Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME)) AS [Driver_Out],
DATEDIFF(second, MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)), Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME))) / 3600.00 AS [Hours_Worked],
DATEDIFF(minute, MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)), Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME))) AS [Minutes_Worked]
From FinalizedOrders
where (((OrderDate > = DATEADD(d,0,DATEDIFF(d,0,GetDate()))) And (OrderDate <= DATEADD(s, +86399, DATEADD(d,0,DATEDIFF(d,0,GetDate())))))) AND Cast(datepart(yyyy,PickedUpTime)as varchar) + '-' + RIGHT('0' + RTRIM(MONTH(PickedUpTime)), 2) + '-' + Right('0'+cast(datepart(DD,PickedUpTime)as varchar(2)),2) = Cast(datepart(yyyy,DeliveredTime)as varchar) + '-' + RIGHT('0' + RTRIM(MONTH(DeliveredTime)), 2) + '-' + Right('0'+cast(datepart(DD,DeliveredTime)as varchar(2)),2)
And ((Select Name From Drivers Where Drivers.DriverNumber = FinalizedOrders.PickUpDriver) Like 'AMV%' OR (Select Name From Drivers Where Drivers.DriverNumber = FinalizedOrders.PickUpDriver) Like 'AMT%')
Group By PickUpDriver ,CONVERT(date, OrderDate)
Order By PickUpDriver ,CONVERT(date, OrderDate)

Your help is greatly appreciated.
Sample of my wrong results attached.
Sample.xlsx

ste5an

First of all: Use the CODE button to embed code into your posts.

- Use always table alias names. And remove unnecessary parentheses.
- Use a JOIN instead your sub-query.
- Columns named OrderDate must contain only dates. Let the model ensure this (use constraints).
- Columns named PickedupTime must contain only time . Let the model ensure this (use constraints). But according to your string fiddling, it has also a date part. This is inconcistent naming.
- To compare the DATE part of a DATETIME column use CAST(column AS DATE) , not string fiddling

You're getting duplicates, cause you need to aggregate the UNION's result, not the single branches. So in a clean model, it should look like this:

SELECT   Q.PickUpDriver ,
         Q.OrderDate ,
         Q.Name , -- Maybe not necessary, then remove it in the sub-query also.
         MIN(Q.PickedupTime) AS [Driver_In] ,
         MAX(Q.DeliveredTime) AS [Driver_Out] ,
         DATEDIFF(SECOND, MIN(Q.PickedupTime), MAX(Q.DeliveredTime)) / 3600.00 AS [Hours_Worked] ,
         DATEDIFF(MINUTE, MIN(Q.PickedupTime), MAX(Q.DeliveredTime)) AS [Minutes_Worked]
FROM     (   SELECT   AO.PickUpDriver AS [Driver] ,
                      D.Name ,
                      AO.OrderDate
             FROM     ActiveOrders AO
                      INNER JOIN Drivers D ON D.DriverNumber = AO.PickUpDriver
                                              AND D.Name LIKE 'AM[V,T]%'
             WHERE    AO.OrderDate >= DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()))
                      AND AO.OrderDate <= DATEADD(s, 86399, DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())))
                      AND CAST(AO.PickedUpTime AS DATE) = CAST(AO.DeliveredTime AS DATE)
             UNION ALL
             SELECT FO.PickUpDriver AS [Driver] ,
                    D.Name ,
                    FO.OrderDate
             FROM   FinalizedOrders FO
                    INNER JOIN Drivers D ON D.DriverNumber = FO.PickUpDriver
                                            AND D.Name LIKE 'AM[V,T]%'
             WHERE  FO.OrderDate >= DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()))
                    AND FO.OrderDate <= DATEADD(s, +86399, DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())))
                    AND CAST(FO.PickedUpTime AS DATE) = CAST(FO.DeliveredTime AS DATE)) Q
GROUP BY Q.PickUpDriver ,
         Q.OrderDate;

Open in new window

p.s. Review your model. Repair it or use at least persisted computed columns. Otherwise your query will be slow, cause index usage is not possible with such attributes.

W.E.B

ASKER

Hello,
Thank you for your help,

I'm getting error messages
Msg 207, Level 16, State 1, Line 27
Invalid column name 'PickUpDriver'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'PickUpDriver'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'PickedupTime'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'DeliveredTime'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'PickedupTime'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'DeliveredTime'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'PickedupTime'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'DeliveredTime'

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

W.E.B

ASKER

Thank you,
I'm getting multiple lines per Driver,
I changed
AO.OrderDate ------ to ---- CONVERT(date, AO.OrderDate) AS OrderDate,
FO.OrderDate ----- to ------ CONVERT(date, FO.OrderDate) AS OrderDate,

It's working, , one line per day per driver,

except for one thing.
The Driver_IN and Driver_out,, some of the dates, are not equal
example below
Driver_in = July 5th
Driver_Out = July 6th -- which is wrong.

Driver Name OrderDate Driver_In Driver_Out Hours_Worked Minutes_Worked
901 AMV ARTUR 2018-07-06 2018-07-05 06:23:00.000 2018-07-06 10:45:00.000 28.3666666 1702

I checked the datatype, (not sure if this has any impact on the results)
PickedUpTime = DateTime
DeliveredTime = Varchar(50)

Thank you again,

ste5an

*cough*

So in a clean model, it should look like this[..]

Your data model is not the best. You should correct that.

I checked the datatype, (not sure if this has any impact on the results)

Sure has it an impact. Cause your naming is incorrect. A column named somethingDate should by of data type DATE. A column named smoehtingTime should be of data type TIME. Anything else would violate the principle of least astonishment.

Well, thus I wrote you should review your model and correct it, if necessary. When this for some strange reasons not possible, then you should at least add persisted computed columns which store the data in a correct manner. This means column naming which reflects the content and using the according data type.

And why on earth do you store DeliveredTime as text???

W.E.B

ASKER

This is the data that I have from the software provider.
I'm trying to deal with what I see in the database.

Jim Horn

What exactly is your question? 'Here's my pile of T-SQL, it's wrong, help figure it out' is not an abundantly actionable question.

Keep in mind that experts here cannot connect to your data sources and run queries, so we are entirely dependent on what you tell us to work a question.

W.E.B

ASKER

Thank you Sir,
I just needed to add another condition,
Where orderdate = PickedupTime (DATE ONLY)

NOW, I have Driver_in AND Driver_out for the same Day. (one line per Driver)

Thank you again for your help, time and Information.

And Thank you Jim.