Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
StevePleasants
Starting Member
3 Posts |
 Posted - 2015-04-15 : 00:18:24
|
Ok, first post.Ok, so I am sort of ok with basic SQL things, but I can't seem to get this one phrased right. It's actually been kind of a good thing, because I've learned a lot from reading and trying to make this work ... but now it's time to see what approach someone else would take to this.Ok. I have two tables and I want to do a query, I am not sure if it should be a UNION or a JOIN. I have been getting some unpredictable results. I will set it all up now. I tend to ramble, sorry if I go on and on.I deliver pizzas. For every stop I make a commission. I may or may not make a cash tip, and I may or may not make a credit tip. I track this information in tableStops. For every day I work, I make an hourly salary and I put miles on my car. I may or may not spend money on Fuel that day. I track this in tableDays.Here is what the two tables look like...tableStopsday RefID Commission CashTip CreditTip20150101 609 1.5 2.30 020150101 611 1 0 320150103 858 2.5 2 1.33tableDaystheDay Mileage Fuel Hours20150101 105 15 520150103 77 0 320150105 99 9 9 So I am tracking that in an ASP classic interface to local SQL 2014 free version. Got IIS tweaked, got a nice little website made on my laptop as a form interface, every day I enter in one record for the day in tableDays, then I enter my stops in bulk into tableStops.So that is going well. I am months in to tracking data. I am now trying to write some reporting.The specific thing that I am trying to do at the moment is to come up with a daily report page. This requires a SQL query. I have been toying around with cheating and just storing values in variables and then adding them, but I'd kind of like to learn the right way.So, SQL query. It is ASP classic. I am an old school scripting guy, I like ASP classic. I have an array of SQL commands that I am looping through in in ASP. Here it is.recordList(0,1)="SELECT SUM(Hours) AS HoursWorked FROM tableDays WHERE theDay >= '" & Months(i,1) & "' AND theDay <= '" & Months(i,2) & "';"recordList(1,1)="SELECT COUNT(*) AS DaysWorked FROM tableDays WHERE theDay >= '" & Months(i,1) & "' AND theDay <= '" & Months(i,2) & "';"recordList(2,1)="SELECT Count(*) AS NumberStops FROM tableStops WHERE Day >= '" & Months(i,1) & "' AND Day <= '" & Months(i,2) & "';"recordList(3,1)="SELECT SUM(Commission) AS Commission FROM tableStops WHERE Day >= '" & Months(i,1) & "' AND Day <= '" & Months(i,2) & "';"recordList(4,1)="SELECT SUM(CashTips) AS CashTips FROM tableStops WHERE Day >= '" & Months(i,1) & "' AND Day <= '" & Months(i,2) & "';"recordList(5,1)="SELECT SUM(CreditTips) AS CreditTips FROM tableStops WHERE Day >= '" & Months(i,1) & "' AND Day <= '" & Months(i,2) & "';"recordList(6,1)="SELECT SUM(Hours)*6.5 AS Hourly FROM tableDays WHERE theDay >= '" & Months(i,1) & "' AND theDay <= '" & Months(i,2) & "';"recordList(7,1)="SELECT SUM(Fuel) AS Fuel FROM tableDays WHERE theDay >= '" & Months(i,1) & "' AND theDay <= '" & Months(i,2) & "';"recordList(8,1)="SELECT SUM(Mileage) AS Mileage FROM tableDays WHERE theDay >= '" & Months(i,1) & "' AND theDay <= '" & Months(i,2) & "';"'recordList(9,1)="SELECT * as Gross FROM ((SELECT (SUM(CreditTips)+SUM(CashTips)+SUM(Commission)) FROM tableStops WHERE theDay >= '" & Months(i,1) & "' AND theDay <= '" & Months(i,2) & "') UNION ALL (((SUM(Hours)*6.5)/COUNT(Hours)) FROM tableDays WHERE tableStops.Day >= '" & Months(i,1) & "' AND tableStops.Day <= '" & Months(i,2) & "'));"recordList(9,1)="SELECT * as Gross FROM ((SELECT (SUM(CreditTips)+SUM(CashTips)+SUM(Commission)) FROM tableStops WHERE theDay >= '" & Months(i,1) & "' AND theDay <= '" & Months(i,2) & "') UNION ALL (((SUM(Hours)*6.5)/COUNT(Hours)) FROM tableDays WHERE tableStops.Day >= '" & Months(i,1) & "' AND tableStops.Day <= '" & Months(i,2) & "'));"recordList(10,1)="SELECT SUM(tableDays.fuel) AS Net FROM (tableStops INNER JOIN tableDays ON tableStops.Day=tableDays.theDay) WHERE (tableStops.Day >= '" & Months(i,1) & "' AND tableStops.Day <= '" & Months(i,2) & "');"Months is an array with the beginning and end of each month.They all work except GROSS and NET. The ones where I have to get data from more than one table. You can see on GROSS I have tried to do it a couple different ways.I feel like I might have my join backwards - When I get a day where I have worked 5 hours and there are 10 stops, the query reports I have worked 50 hours. That is why I am dividing by the count(), to get it back to normal.in plain English - for a given day, I want to get the sum of the hours worked times 6.5, plus the total commission, cash and credit tips from all of that day's stops. One daily record to many stop records. For NET, I want to also subtract the Fuel cost from the day record.So I guess I would like a reality check - this has to be pretty basic, I need to ask if I am doing it in a goofy manner. And if it is ok, can I get some help with the two queries for GROSS and NET?...His name is Robert Paulson... |
|
|
StevePleasants
Starting Member
3 Posts |
Posted - 2015-04-15 : 00:27:53
|
| Oh yeah, I actually posted the code from my masterReport.asp page. Same need. Here is another version I have tried, from a different page trying to get the same results. You can see how complicated I am trying to make it...ReportItems(8,1)="SELECT ((SUM(tableDays.Hours)/COUNT(tableDays.Hours))*6.5)+SUM(tableStops.CreditTips)+SUM(tableStops.CashTips)+SUM(tableStops.Commission) AS Gross FROM (tableStops INNER JOIN tableDays ON tableDays.theDay = '" & ReportDay & "') WHERE Day = '" & ReportDay & "';" ReportItems(9,1)="SELECT ((SUM(tableDays.Hours)/COUNT(tableDays.Hours))*6.5)+SUM(tableStops.CreditTips)+SUM(tableStops.CashTips)+SUM(tableStops.Commission)-(SUM(tableDays.Fuel)/COUNT(tableDays.Hours)) AS Net FROM (tableStops INNER JOIN tableDays ON tableStops.Day=tableDays.theDay) WHERE Day = '" & ReportDay & "' UNION ALL Select SUM(Hours) FROM tableDays WHERE theDay = '" & ReportDay & "';" ReportItems(10,0)="RealHourly"ReportItems(10,1)="SELECT (((SUM(tableDays.Hours)/COUNT(tableDays.Hours))*6.5)+SUM(tableStops.CreditTips)+SUM(tableStops.CashTips)+SUM(tableStops.Commission)-(SUM(tableDays.Fuel)/COUNT(tableDays.Hours)))/(SUM(tableDays.Hours)/COUNT(tableDays.Hours)) AS RealHourly FROM (tableStops INNER JOIN tableDays ON tableStops.Day=tableDays.theDay) WHERE Day = '" & ReportDay & "' UNION ALL Select SUM(Hours) FROM tableDays WHERE theDay = '" & ReportDay & "';" ...His name is Robert Paulson... |
 |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2015-04-15 : 07:05:45
|
Take a more set based approach.Return your values as columns and the months as rows.SELECT D.theMonth ,D.HoursWorked, D.DaysWorked, S.NumberStops, S.Commission ,S.CashTips, S.CreditTips, D.Hourly, D.Fuel, D.Mileage ,D.Hourly + S.Commission + S.CashTips + S.CreditTips AS Gross ,D.Hourly + S.Commission + S.CashTips + S.CreditTips - D.Fuel AS NetFROM( SELECT DATEADD(month, DATEDIFF(month, 0, theDay), 0) AS theMonth ,SUM([Hours]) AS HoursWorked ,COUNT(*) AS DaysWorked ,SUM(Hours)*6.5 AS Hourly ,SUM(Fuel) AS Fuel ,SUM(Mileage) AS Mileage FROM tableDays -- return multiple months as rows WHERE theDay >= '20150101' AND theDay < '20150401' GROUP BY DATEADD(month, DATEDIFF(month, 0, theDay), 0)) D JOIN ( SELECT DATEADD(month, DATEDIFF(month, 0, [day]), 0) AS theMonth ,COUNT(*) AS NumberStops ,SUM(Commission) AS Commission ,SUM(CashTips) AS CashTips ,SUM(CreditTips) AS CreditTips FROM tableStops WHERE [day] >= '20150101' AND [day] < '20150401' GROUP BY DATEADD(month, DATEDIFF(month, 0, [day]), 0) ) S ON D.theMonth = S.theMonth; |
|
|
|
StevePleasants
Starting Member
3 Posts |
Posted - 2015-04-15 : 11:05:15
|
| Thank you kind sir or madam. You are blowing my mind a little, just what I needed....His name is Robert Paulson... |
|
|
|
|
|
|
|
|
Join? Union?