Other Software > Developer's Corner
[Solved] SQL Query is Kicking my Ass.
Stoic Joker:
Take out the #7 Having clause and run the query.
Is this the subset that you want the "max" timestamp to be chosen from?
-patthecat (July 05, 2011, 08:34 AM)
--- End quote ---
I'll have to get back to you on that ... I gotta be on-Site here shortly.
Stoic Joker:
Take out the #7 Having clause and run the query.
Is this the subset that you want the "max" timestamp to be chosen from?-patthecat (July 05, 2011, 08:34 AM)
--- End quote ---
Okay, I take this to mean: When looking at the output, how do I know it's wrong? ...Which is actually a hell of a good GD question, now that I think of it ... Or rather, now that you asked me to think of it. ;)
Now here's the weird part. Most of it ain't. Here's an updated version of the query as just used for above:
--- Code: Text ---SELECT Pc1.ClientID, Pc1.SerialNo, Pc1.ModelName, PiX.CurLocation, Pc1.TotalPP, Pc1.TimeStamp FROM PageCounts Pc1 INNER JOIN PageCounts Pc2 ON Pc1.SerialNo = Pc2.SerialNo INNER JOIN Printer_Inv PiX ON Pc1.SerialNo = PiX.SerialNo WHERE Pc1.ClientID = 'Client X' AND Pc1.TotalPP = 0 AND PiX.Active = 1 GROUP BY Pc1.SerialNo, Pc1.TimeStamp HAVING Pc1.TimeStamp = MAX(Pc2.TimeStamp) ORDER BY Pc1.TimeStamp DESC LIMIT 0, 300
This produces a data set of the correct size...and with the correct items. The problem is the dates are "wrong". Sure they're what I asked for ... But that's not (exactly...) what I meant (to ask for) ... And computers are of course basically shit for interpretation. (...Oops!)
So here's how I got ^^there^^. I was (assuming) looking for a data set size somewhere in the 30's, so when I kept getting 80's it looked/felt/appeared "wrong" ... Which it would have indeed been (wrong) if I hadn't just created another bill last Friday which contained 80 something devices with absent page counts. :wallbash:
So here's the - Newly updated with correct-er information - fun part. The date column (actually) needs to reflect the date of the last non-zero value. I went with max to get the latest info. because the table contains the complete billing history. So I didn't want to have any old zeros surfacing if they'd been "washed" by a valid, more current, non-zero count. However I do need this report query to show how (far back) long its been since device X has had a valid count retrieved from it ... So we can look into the why who is most guilty of (zero count) silence.
I think it's where the issue lies when you try to bring PC2 timestamp into the picture. If it is then at least you can try the other methods (sub queries, temp files, etc) you've tried to see if it get's the one "max" timestamp from your subset.-patthecat (July 05, 2011, 08:34 AM)
--- End quote ---
No that part - Best I can tell - is Okay ... I was just on the wrong end of the problem. ;)
Thank you!
justice:
Now I'm not an expert, but it seems because you need the last date from a bunch of non zero pagecounts, but you also need values from a bunch of zero pagecounts, that you have to do a subselect to get the max date and other information from the zero pagecounts data and then reuse that in your main query. :)
So if that means you're back in the "nested select" performance problem then perhaps it's worth restructuring the data if possible, or wait for someone with better knowledge to help you out :D Google for subselect alternatives
Stoic Joker:
Now I'm not an expert, but it seems because you need the last date from a bunch of non zero pagecounts, but you also need values from a bunch of zero pagecounts, that you have to do a subselect to get the max date and other information from the zero pagecounts data and then reuse that in your main query. :)-justice (July 05, 2011, 06:35 PM)
--- End quote ---
That's about where I got too. Unfortunately the passing values between queries thing just isn't cooperating. I could resort to bolting this thing to a web page and just doing the subquery in the result set's while loop processing ... But I really wanted to keep this as a single free standing SQL query script so it could be dumped to Excel quickly.
So if that means you're back in the "nested select" performance problem then perhaps it's worth restructuring the data if possible, or wait for someone with better knowledge to help you out :D Google for subselect alternatives-justice (July 05, 2011, 06:35 PM)
--- End quote ---
...That's why I'm here... :) ...Google just got me into the joins fiasco. Restructuring the data is not an option as it's tied into/used by more than one of our internal billing systems (can U say Ripple Effect..? :D). There has got to be a simple answer for this...I just haven't stumbled across it yet.
Stoic Joker:
Occam's Razor anyone?
The answer was simple, after I got the question right.
--- Code: Text ---SELECT ClientID, PC.SerialNo, PC.ModelName, CurLocation, Max(TimeStamp) As LastValidCount FROM PageCounts PC INNER JOIN Printer_Inv PiX ON PC.SerialNo = PiX.SerialNo WHERE PiX.Active = 1 AND ClientID = 'Client X' AND TotalPP > 0 GROUP BY PC.SerialNo ORDER BY LastValidCount DESC, CurLocation LIMIT 0, 300
Navigation
[0] Message Index
[#] Next page
[*] Previous page
Go to full version