Other Software > Developer's Corner
[Solved] SQL Query is Kicking my Ass.
Stoic Joker:
Greetings
So when the boss first asked me about doing this report it sounded simple enough. But after 4 days of hammering on it I've come to the conclusion that my initial assessment may have been (dead) wrong. I'm going to try to be both detailed and succinct ... So please bear with me if this gets a bit long.
One of our internal billing systems contains historical information (Client, serial#, date, pages printed, etc.) about printers that are under contract. Circumstances being what they are (IT Happens), occasionally there are devices that don't get their counts collected. Now usually this gets caught up at a later date ... but some times certain devices slide long enough to start raising eyebrows as to their existence and condition. Which brings "us" to the report in question.
I'm trying to create a SQL query (for a MySQL db) that will list all the devices for client X that:
1. Does not recently/currently have a billable count (Total Pages Printed = 0).
2. Shows the date of the last billable count (or the first zero count date in a contiguous historical block between then and now).
Sound simple enough? Hopefully it is for some one here ... 'cause I'm getting my ass kicked by this thing. The relevant table structure and usage is as follows:
Columns:
Index - auto incremented (int).
SerialNo - of the device (string).
ModelName - of the device (string).
ClientID - Internal identified of customer (string).
TimeStamp - a.k.a. billing date / all devices for a given customer/date will have an identical time stamp (timestamp).
TotalPP - Total Pages Printed this will reflect the number of pages printed by that device for that billing cycle (int).
I've got close to 9 pages of failed queries and notes on what doesn't work. Nested selects tend to make the server race and die (neat trick with dual Xeons), so I've been concentrating on various self-join variations. But nothing is giving me a resultant data set that isn't either wrong or total garbage.
So I'm hoping to find a more informed opinion on how to approach this.
Thank you,
Slightly frazzled but still vaguely Stoic Joker
justice:
(you could try <1 in case the default value for TotalPP columns empty instead of 0)
What's wrong with something like:
SELECT Max(TimeStamp) As LastBillableDate,Index,SerialNo,ModelName, ClientID FROM <TableName> WHERE TotalPP=0.
* Split the problem up into small problems you can get working, don't try it all at once.
* Microsoft Reporting Services (if you can get your hands on it) is a great tool for making reports and it can connect to MySQL databases too, this might make it easier to create reports.
Stoic Joker:
(you could try <1 in case the default value for TotalPP columns empty instead of 0)-justice (July 04, 2011, 09:30 AM)
--- End quote ---
Default column value is 0, so no NULLs to trip over/around.
What's wrong with something like:
SELECT Max(TimeStamp) As LastBillableDate,Index,SerialNo,ModelName, ClientID FROM <TableName> WHERE TotalPP=0.
-justice (July 04, 2011, 09:30 AM)
--- End quote ---
That's along the lines of what I have been playing with ... But no *joy* as of yet.
* Split the problem up into small problems you can get working, don't try it all at once.-justice (July 04, 2011, 09:30 AM)
--- End quote ---
Sound advice, but it hasn't panned out yet. :)
* Microsoft Reporting Services (if you can get your hands on it) is a great tool for making reports and it can connect to MySQL databases too, this might make it easier to create reports.-justice (July 04, 2011, 09:30 AM)
--- End quote ---
That may be handy for the future, but I'm not sure I want to introduce a new tools learning curve to the mix at this point.
Here's one of the Close-but-no-Cigar queries I've been playing with. Note it does include references to another table which is required for the final product. But isn't part of the current sticking point.
--- Code: Text ---SELECT Pc1.ClientID, Pc1.SerialNo, Pc1.ModelName, PiX.CurLocation, 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 PiX.Active = 1 GROUP BY Pc1.SerialNo, Pc1.TimeStamp HAVING Pc1.TimeStamp = MAX(Pc2.TimeStamp) ORDER BY Pc1.TimeStamp DESC LIMIT 0, 300
justice:
It's very hard to help you without seeing an example database, but I think the problem is between line 5-7.
If you look at the
documentation on group by / having you see Having is used as a where on groups.
I'm still not really sure what you want because the query you are posting is not the same as your description of the query in the starting post.
Your query is trying to search for a Client that is Active where the billing date from pc1 historyical information is identical to the last billing date from some other place (pc2), then grouping by printer and timestamp. It doesn't make sense.
Also to me it's not clear how Pc2 is related to pc1 :P in english? Without being clear how you want the query in english noone can translate that to SQL. hope that helps
Stoic Joker:
It's very hard to help you without seeing an example database, but I think the problem is between line 5-7.
If you look at the
documentation on group by / having you see Having is used as a where on groups.-justice (July 04, 2011, 10:25 AM)
--- End quote ---
Having is something I just ran across the other day while Googling for an example of how best to get a list of max(dates) out of a table during/using a self join. So given that it has yet to create a result set that made complete sense it's quite likely I'm using it wrong...or expecting it to do more than it actually does (hell if I knew what I was doing I'd probably be done by now... (hehe)). Also that's just one of a multitude of things I tried none of which worked properly.
Not sure how well this will paste, but here's a small (cleaned) piece of the table's content.
--- Code: Text ---Index SerialNo ModelName ClientID CurPageCount OldPageCount TimeStamp TotalPP29 CNBM044207 3380 mfp MIKLA01 2404 26 2005-10-27 09:04:17 2378 9 USLH039092 1100 CRITR01 39339 38834 2005-09-29 11:47:17 50510 CNDIF23087 1160 CRITR01 7947 7632 2005-09-29 11:47:17 31530 USBNL12704 4200 MIKLA01 199451 193626 2005-10-27 09:04:17 582512 CN09506649 4300 CRITR01 401922 383635 2005-09-29 11:47:17 1828713 JPCG012675 4v CRITR01 449756 449206 2005-09-29 11:47:17 55014 USBB019753 8000 series CRITR01 844310 839591 2005-09-29 11:47:17 471915 USBB020920 8000 series CRITR01 760125 756990 2005-09-29 11:47:17 313516 U61036C5J482752 Brother MFC8840D CRITR01 3271 2314 2005-09-29 11:47:17 95718 USBB031505 3100 fax SNIFFERT_B 167316 167316 2005-10-02 11:47:23 019 USCC000104 4050 UFORA03 258810 255488 2005-10-04 08:50:26 332220 JPJB002010 Color 8550 series ECODE01 162675 160416 2005-10-18 16:27:32 225921 USLH039092 1100 CRITR01 39945 39339 2005-10-19 08:52:25 60622 CNDIF23087 1160 CRITR01 9131 7947 2005-10-19 08:52:25 118424 CN09506649 4300 CRITR01 426537 401922 2005-10-19 08:52:25 2461525 JPCG012675 4v CRITR01 450190 449756 2005-10-19 08:52:25 43426 USBB019753 8000 series CRITR01 848516 844310 2005-10-19 08:52:25 420627 USBB020920 8000 series CRITR01 768063 760125 2005-10-19 08:52:25 793828 U61036C5J482752 Brother MFC8840D CRITR01 4225 3271 2005-10-19 08:52:25 95431 USSC016017 4000 series OCLAD01 124823 124164 2005-10-27 13:25:31 659
I'm still not really sure what you want because the query you are posting is not the same as your description of the query in the starting post.-justice (July 04, 2011, 10:25 AM)
--- End quote ---
The relevant part is. There are just some other (device specific) values being pulled in from another (PiX) table.
Your query is trying to search for a Client that is Active where the billing date from pc1 historyical information is identical to the last billing date from some other place (pc2), then grouping by printer and timestamp. It doesn't make sense.-justice (July 04, 2011, 10:25 AM)
--- End quote ---
I had a feeling that might confuse thing, which is why I didn't lead with it. Just ignore all the PiX.* stuff. It's only verifying that the device with SN X is "active" to thin out the data set.
Also to me it's not clear how Pc2 is related to pc1 :P in english? Without being clear how you want the query in english noone can translate that to SQL. hope that helps-justice (July 04, 2011, 10:25 AM)
--- End quote ---
Hm... (a bit sketchy on that myself), Best I can tell table is joined to itself on SN so that each SN can be searched for/aligned to its maximum date with a TotallPP of 0.
I'm at a point where the more I look at it the less sense it makes (forest for trees??) even after taking a break.
Navigation
[0] Message Index
[#] Next page
Go to full version