question

noob avatar image
noob asked

Query Help

Hello my friends, I have a query that I can't figure out how to master. (Still in school with little experience) I have 2 tables. Simplified. Notes* there can be more than 1 driver assigned to a move. More movements = more miles just looking for total miles for each driver with drivers on team moves there miles split. ---------Movement Table ------------- Movement.ID ------ Loaded/Empty ------- Miles 1 ---------------------------- E ------------------- 456 2 ---------------------------- L --------------------798 3 -----------------------------L-------------------1000 4 ---------------------------- E---------------------688 ------------EquipmentTable------------- Equipment.movement_ID--------Equipment_item-------Equipmenttype 1-------------------------------------------Bill------------------------Driver 1------------------------------------------John ----------------------Driver 2------------------------------------------Fred----------------------Driver 3------------------------------------------Sandy---------------------Driver 4------------------------------------------Ruth----------------------Driver 4------------------------------------------Steve---------------------Driver Now my Query... SELECT Equipment.Equipmentitem COUNT(CASE WHEN movement.loaded = 'L' THEN movement.loaded END) AS LoadedMovement , COUNT(CASE WHEN movement.loaded = 'E' THEN movement.loaded END) AS emptymovement , SUM(CASE WHEN movement.loaded = 'L' THEN movement.miles ELSE 0 END) AS revenue_miles , SUM(CASE WHEN movement.loaded = 'E' THEN movement.miles ELSE 0 END) AS deadheadmiles , SUM(CASE WHEN movement.loaded = 'E' OR movement.loaded = 'L' THEN movement.miles ELSE 0 END) AS totalmiles FROM movement , Equipment WHERE ( movement.id = equipment.movement_id ) AND ( equipment.equipmenttype = 'driver' ) GROUP BY equipment.equipmentitem The query I have comes out looking like this.. Equipmenitem--Loadedmove----Emptymove----Revenuemiles----deadheadmiles----totalmiles Bill-----------------------0------------------1----------------------0------------------456-------------------456 John---------------------0------------------1----------------------0------------------456-------------------456 fred---------------------1------------------0---------------------798------------------0----------------------798 fred---------------------1------------------0---------------------1000------------------0--------------------1000 Ruth---------------------0------------------1----------------------0------------------688-------------------688 Steve---------------------0------------------1----------------------0------------------688-------------------688 What I need is the drivers that were part of a team for their miles to be split! Example for Bill and John the deadhead miles and total miles need to be 228.
count
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I've reformatted your query - incidentally, there's a comma missing after the first item in the `SELECT` list.
0 Likes 0 ·
What version of SQL Server are you using? It might make a difference as to the approach we suggest.
0 Likes 0 ·
Hello Thomas, I am using SQL Server 2008 Thanks!
0 Likes 0 ·
You guys da bomb!
0 Likes 0 ·
KenJ avatar image
KenJ answered
If you're still in school, you should be learning to use the proper JOIN syntax. Rather than `FROM x, y WHERE x.col = y.col` you should be using `FROM x JOIN y ON x.col = y.col` For the problem at hand, you need to divide each of the miles columns by the number of drivers assigned to the movement. You can JOIN in the number of drivers with a subquery then use those results to determine the appropriate miles. Here is the JOIN to get the driver count: INNER JOIN (SELECT movement_id, COUNT(*) AS DriverCount FROM @equipment GROUP BY movement_id) AS AssignedDrivers ON AssignedDrivers.movement_id = equipment.Movement_ID Post a comment if you need help setting up the division for the miles columns.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

askmlx121 avatar image
askmlx121 answered
Hope it may help see below for right answer using JOIN condition........... DECLARE @Movementtable TABLE ( mvtid INT , loadedempty CHAR(5) , miles INT ) DECLARE @equipmenttable TABLE ( emvtid INT , equipmentitem CHAR(10) , equipmenttype CHAR(10) ) INSERT INTO @Movementtable SELECT 1 , 'E' , 456 UNION SELECT 2 , 'L' , 798 UNION SELECT 3 , 'L' , 1000 UNION SELECT 4 , 'E' , 688 INSERT INTO @equipmenttable SELECT 1 , 'Bill' , 'Driver' UNION SELECT 1 , 'John ' , 'Driver' UNION SELECT 2 , 'Fred' , 'Driver' UNION SELECT 3 , 'Sandy' , 'Driver' UNION SELECT 4 , 'Ruth' , 'Driver' UNION SELECT 4 , 'Steve' , 'Driver' SELECT * FROM @Movementtable SELECT * FROM @equipmenttable SELECT e.equipmentitem , COUNT(CASE WHEN m.loadedempty = 'L' THEN m.loadedempty END) AS LoadedMovement , COUNT(CASE WHEN m.loadedempty = 'E' THEN m.loadedempty END) AS emptymovement , SUM(CASE WHEN m.loadedempty = 'L' THEN M.miles / 2 ELSE 0 END) AS revenuemiles , SUM(CASE WHEN m.loadedempty = 'E' THEN M.miles / 2 ELSE 0 END) AS deadheademiles , SUM(miles) AS totalmiles FROM @Movementtable m JOIN @equipmenttable e ON m.mvtid = e.emvtid AND e.equipmenttype = 'driver' GROUP BY e.equipmentitem ORDER BY e.equipmentitem
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This divides everybody's miles by 2, not just the movements that had two drivers assigned.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.