All righty, on a recent thread I realized I may be biased against using JOINS in ExecuteSQL() statements and hence losing a valuable tool. I've decided to rectify this and have a complex result I want, but now I'm stuck.

I have a table TICKETS. This represents a work order performed on a specific date and time with a cost to the customer. The time converts to a relevant shift ( of 1,2, or 3 each 8 hours long, though rarely is there a 3rd shift). There may be 0-50 Tickets run in a Shift. The relevant fields are work_Date, Shift, and Subtotal. Subtotal represents how much a customer is getting charged.

I also have a table TIMES. This represents an employee's work shift. It's got fields like work_Date, Shift, and Total (how much an employee gets paid in his/her shift).

My goal is to create a list of Shifts (actually Date/Shift combos) with the total paid to all employees

 and the totals charged on all tickets for those Date/Shift combos. I could do this with relationships, but I'm hoping to understand ExSQL() better by using it.

I thought I had a decent ExecuteSQL() statement, but the totals aren't correct. They're off by a lot,

My statement is:

SELECT m.work_Date,m.Shift,SUM(m.Total),SUM(k.Subtotal)

FROM Times m LEFT OUTER JOIN Tickets k on m.work_Date = k.Work_Date AND m.Shift = k.Shift

WHERE m.work_Date>='4/1/2013' AND m.work_Date<='4/15/2013' 

GROUP BY M.work_Date,m.shift
ORDER BY m.work_Date,m.shift

and it returns results along the lines of:

2013-04-01,1,2712.88,3661.45
2013-04-01,2,5607.14,5934.28
2013-04-02,1,4476.24,3974.96
2013-04-02,2,14146.54,10219.14

 

I've also tried LEFT JOIN and JOIN instead of LEFT OUT JOIN with the same results.

The format is fine, but the totals aren't right. They should be:

2013-04-01,1,339.11,732.29
2013-04-01,254.87,506.88
2013-04-02,1,248.68,976.69
2013-04-02,2,456.34,993.74
 

Any tips on figuring out why the SUM() on both sides of the JOIN is wrong would be appreciated...

 

DJ

 

Automatic message

This topic has been moved from "The Left Brain" to "FQL - Internal SQL".

Perhaps it's time to change the tag line for this forum:

 

"FQL or FileMaker Query Language - local calls to your database using SQL syntax - using plug-ins"

 

Since plug-ins are no longer required.

Change it.

 

We just created it with the introduction of Execute SQL. I'll pass your comments on to Stephen.

 

Lee

Do you have any tickets that dont have related times records?

Nope.

 

The Times are workers clocking in and out. Tickets represent the work the the workers did. There won't be a Ticket unless there was a worker (with a Times record) to enter it. There may be (rarely) Times with no Tickets (if no work was done).

 

Also, I did a test by adding Count() to the line and it looks like each combination of Ticket and Times record is being counted, and so I assume the same is happening with Sum().

Edited April 26, 201312 yr by David Jondreau

are the Total and SubTotal fields calculations or summary fields?