topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Thursday March 19, 2026, 8:50 pm
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Recent Posts

Pages: prev • 1 ... 154 155 156 157 158 [159] 160 161 162 163 164 ... 246 • next
3951
Living Room / Re: More ammunition why patents are EVIL
« Last post by Stoic Joker on July 08, 2011, 06:36 AM »
George Carlin (and I) agree with you, which is particularly apt today!
-zridling (July 08, 2011, 12:42 AM)

Amen to that! I have long maintained that George Carlin was the only true sage of the 20th century. He forced us to look at things (and ourselves) for what they really are...and not what we believed, or wanted them to be.
3952
Living Room / Re: More ammunition why patents are EVIL
« Last post by Stoic Joker on July 07, 2011, 07:56 PM »
As for the term "Big Pharma", yeah, I don't much like it either, but it was already brought up. It does sound a tad nutty.
-Renegade (July 07, 2011, 04:56 PM)

*Shrug* If the shoe fits...
3953
Living Room / Re: 64 Bit OS - When to Switch ?
« Last post by Stoic Joker on July 07, 2011, 07:51 PM »
I have been using Vista x64 and Win 7 x64 for years now.
-Cloq (July 07, 2011, 07:16 PM)

Same here, performance, compatibility, all good.
3954
General Software Discussion / Re: Google+
« Last post by Stoic Joker on July 07, 2011, 12:58 PM »
According to a couple of German websites, some profiles with an obviously faked name were removed.
-Tuxman (July 07, 2011, 11:00 AM)

Well that sucks, I really don't want to use my real name online. Nor will I (it's boring). Sure the G+ thing is a bit tempting to try - for the hell of it - Mainly because I completely ignored the last 500 fads... But I'll not be signing up with anything close to what it says on my drivers license.
-Stoic Joker (July 07, 2011, 11:40 AM)

+1 with that. A Google and Bing search of my first/last name returns a grand total of 6 valid hits.
-40hz (July 07, 2011, 11:52 AM)

Shit, I just ran my name through Bing, and it says I'm a Photographer from Austrailia...??
3955
General Software Discussion / Re: Data Recovery for SCO OpenServer
« Last post by Stoic Joker on July 07, 2011, 11:44 AM »
I'm with ^them^ ... Don't chance making it worse.
3956
General Software Discussion / Re: Google+
« Last post by Stoic Joker on July 07, 2011, 11:40 AM »
According to a couple of German websites, some profiles with an obviously faked name were removed.
-Tuxman (July 07, 2011, 11:00 AM)

Well that sucks, I really don't want to use my real name online. Nor will I (it's boring). Sure the G+ thing is a bit tempting to try - for the hell of it - Mainly because I completely ignored the last 500 fads... But I'll not be signing up with anything close to what it says on my drivers license.
3957
N.A.N.Y. 2012 / Re: NANY 2012 Pledge: De-stress
« Last post by Stoic Joker on July 06, 2011, 11:25 AM »
Okay, I'm intrigued (Stressed, burned-out, and on the verge of cracking).

What'cha got??

3958
Developer's Corner / Re: SQL Query is Kicking my Ass.
« Last post by Stoic Joker on July 06, 2011, 08:18 AM »
Occam's Razor anyone?

The answer was simple, after I got the question right.

Code: Text [Select]
  1. SELECT ClientID, PC.SerialNo, PC.ModelName, CurLocation, Max(TimeStamp) As LastValidCount
  2. FROM PageCounts PC
  3. INNER JOIN Printer_Inv PiX ON PC.SerialNo = PiX.SerialNo
  4. WHERE PiX.Active = 1 AND ClientID = 'Client X' AND TotalPP > 0
  5. GROUP BY PC.SerialNo
  6. ORDER BY LastValidCount DESC, CurLocation
  7. LIMIT 0, 300
3959
Developer's Corner / Re: SQL Query is Kicking my Ass.
« Last post by Stoic Joker on July 06, 2011, 06:48 AM »
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)

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)

...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.
3960
Developer's Corner / Re: SQL Query is Kicking my Ass.
« Last post by Stoic Joker on July 05, 2011, 01:34 PM »
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)

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]
  1. SELECT Pc1.ClientID, Pc1.SerialNo, Pc1.ModelName, PiX.CurLocation, Pc1.TotalPP, Pc1.TimeStamp
  2. FROM PageCounts Pc1
  3. INNER JOIN PageCounts Pc2 ON Pc1.SerialNo = Pc2.SerialNo
  4. INNER JOIN Printer_Inv PiX ON Pc1.SerialNo = PiX.SerialNo
  5. WHERE Pc1.ClientID = 'Client X' AND Pc1.TotalPP = 0 AND PiX.Active = 1
  6. GROUP BY Pc1.SerialNo, Pc1.TimeStamp HAVING Pc1.TimeStamp = MAX(Pc2.TimeStamp)
  7. ORDER BY Pc1.TimeStamp DESC
  8. 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)

No that part - Best I can tell - is Okay ... I was just on the wrong end of the problem. ;)

Thank you!
3961
Living Room / Re: Lots of New Members
« Last post by Stoic Joker on July 05, 2011, 11:07 AM »
i don't get it.  :huh:
-mouser (July 04, 2011, 08:40 AM)

(playing off laser) Lazar, as in Lazarus: Popular back-from-the-dead guy in the bible. ;)

3962
Developer's Corner / Re: SQL Query is Kicking my Ass.
« Last post by Stoic Joker on July 05, 2011, 08:40 AM »
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)

I'll have to get back to you on that ... I gotta be on-Site here shortly.
3963
Developer's Corner / Re: SQL Query is Kicking my Ass.
« Last post by Stoic Joker on July 05, 2011, 08:38 AM »
Um... Perry? I may have translated the query incorrectly. The below modified version held a pair of 3.0GHz Xeons at 25% usage for 10 minutes (with no output in sight) ... So I gave up (had to) and killed it. Table's only got 20,000 lines in it. So I'm guessing something is awry (or you've got some seriously fast hardware).

Code: Text [Select]
  1. SELECT DISTINCT
  2.         Pc1.ClientID,
  3.         Pc1.SerialNo,
  4.         Pc1.ModelName,
  5.         Pc1.TimeStamp
  6. FROM
  7.         PageCounts Pc1
  8.         INNER JOIN PageCounts Pc2
  9.                 ON Pc1.SerialNo = Pc2.SerialNo
  10.                         AND Pc2.ClientID = 'Client X'
  11.                         AND Pc1.TimeStamp = (
  12.                                 SELECT MAX(TimeStamp)
  13.                                 FROM PageCounts
  14.                                 WHERE Pc1.SerialNo = SerialNo
  15.                                 )
  16. ORDER BY Pc1.TimeStamp DESC
3964
Living Room / Re: nearby lightning skrike kills neighbours computers (and mine)
« Last post by Stoic Joker on July 05, 2011, 07:03 AM »
@nudone - I feel ya man - venting thread - been there after a lightening strike myself. :)

@westom - You really need to take into consideration that there is a lot of specialization in IT. Your skillset corner of the world is not the same as everybody elses. Soften your tone a bit and you'll get much better responses.

---------------------------------------------------------

That being said, I do have to agree that in this type of situation the best usage of $100 going forward is to hand it to an electrician to investigate how well ones dwelling is actually grounded. I did my own investigating back when (because I can), and did find a few things what needed attention.
3965
Developer's Corner / Re: SQL Query is Kicking my Ass.
« Last post by Stoic Joker on July 05, 2011, 06:43 AM »
Have you tried using temporary tables? Joins can be very expensive. (Sorry -- I'd love to jump in and help more, but need to run out the door.) Multiple queries can sometimes help.
-Renegade (July 04, 2011, 04:37 PM)

My understanding was that joins were less expensive than nested selects, so I went that route to keep the server from racing itself to death (had to kill it a few times). This isn't for something that has to be done frequently by multiple users. It's a one-shot report that has great potential to be useful going forward on a ~monthly basis. So if it took a few minutes to run...That's Okay...If it comes up with the right answer.

OTOH, if it could be done quickly, no-one would complain... ;)

Haven't tried temporary tables yet, but I have been slowly resigning myself to the possibility of their need. In a nutshell, the downside of being completely self taught, is that when you're stuck, you are stuck... :) (e.g. I'm stuck'ed)


@Perry - At this point I'm game to try anything. I'll try your query later today and see what happens. Hopefully it'll give me a data set that's a bit less hellter skelter than what I have been getting.
3966
Developer's Corner / Re: SQL Query is Kicking my Ass.
« Last post by Stoic Joker on July 04, 2011, 11:55 AM »
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)

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 [Select]
  1. Index  SerialNo         ModelName       ClientID        CurPageCount  OldPageCount      TimeStamp               TotalPP
  2. 29      CNBM044207      3380 mfp        MIKLA01         2404            26              2005-10-27 09:04:17     2378
  3.  9      USLH039092      1100            CRITR01         39339           38834           2005-09-29 11:47:17     505
  4. 10      CNDIF23087      1160            CRITR01         7947            7632            2005-09-29 11:47:17     315
  5. 30      USBNL12704      4200            MIKLA01         199451          193626          2005-10-27 09:04:17     5825
  6. 12      CN09506649      4300            CRITR01         401922          383635          2005-09-29 11:47:17     18287
  7. 13      JPCG012675      4v              CRITR01         449756          449206          2005-09-29 11:47:17     550
  8. 14      USBB019753      8000 series     CRITR01         844310          839591          2005-09-29 11:47:17     4719
  9. 15      USBB020920      8000 series     CRITR01         760125          756990          2005-09-29 11:47:17     3135
  10. 16      U61036C5J482752 Brother MFC8840D CRITR01        3271            2314            2005-09-29 11:47:17     957
  11. 18      USBB031505      3100 fax        SNIFFERT_B      167316          167316          2005-10-02 11:47:23     0
  12. 19      USCC000104      4050            UFORA03         258810          255488          2005-10-04 08:50:26     3322
  13. 20      JPJB002010      Color 8550 series ECODE01       162675          160416          2005-10-18 16:27:32     2259
  14. 21      USLH039092      1100            CRITR01         39945           39339           2005-10-19 08:52:25     606
  15. 22      CNDIF23087      1160            CRITR01         9131            7947            2005-10-19 08:52:25     1184
  16. 24      CN09506649      4300            CRITR01         426537          401922          2005-10-19 08:52:25     24615
  17. 25      JPCG012675      4v              CRITR01         450190          449756          2005-10-19 08:52:25     434
  18. 26      USBB019753      8000 series     CRITR01         848516          844310          2005-10-19 08:52:25     4206
  19. 27      USBB020920      8000 series     CRITR01         768063          760125          2005-10-19 08:52:25     7938
  20. 28      U61036C5J482752 Brother MFC8840D CRITR01        4225            3271            2005-10-19 08:52:25     954
  21. 31      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)

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)

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)

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.
3967
Developer's Corner / Re: SQL Query is Kicking my Ass.
« Last post by Stoic Joker on July 04, 2011, 09:53 AM »
(you could try <1 in case the default value for TotalPP columns empty instead of 0)
-justice (July 04, 2011, 09:30 AM)

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)

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)

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)

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]
  1. SELECT Pc1.ClientID, Pc1.SerialNo, Pc1.ModelName, PiX.CurLocation, Pc1.TimeStamp
  2. FROM PageCounts Pc1
  3. INNER JOIN PageCounts Pc2 ON Pc1.SerialNo = Pc2.SerialNo
  4. INNER JOIN Printer_Inv PiX ON Pc1.SerialNo = PiX.SerialNo
  5. WHERE Pc1.ClientID = 'Client X' AND PiX.Active = 1
  6. GROUP BY Pc1.SerialNo, Pc1.TimeStamp
  7. HAVING Pc1.TimeStamp = MAX(Pc2.TimeStamp)
  8. ORDER BY Pc1.TimeStamp DESC
  9. LIMIT 0, 300
3968
Developer's Corner / [Solved] SQL Query is Kicking my Ass.
« Last post by Stoic Joker on July 04, 2011, 09:18 AM »
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
3969
General Software Discussion / Re: When your word processor begins openly mocking you, it's time to call it a day
« Last post by Stoic Joker on June 20, 2011, 07:44 PM »
I have to say I like Word 2010 ... sorry.
-Carol Haynes (June 20, 2011, 07:37 PM)

It tries my patience at times, but I'm getting used to it.
3970
General Software Discussion / Re: When your word processor begins openly mocking you, it's time to call it a day
« Last post by Stoic Joker on June 20, 2011, 07:42 PM »
Well that's a clever (and somewhat frightening) solution. I take it WordPad didn't quite do what she wanted?
-Stoic Joker (June 20, 2011, 06:46 PM)

Wordpad on Win98 vs the beta of AOL 6.0? No comparison! I know it's hard to believe but AOL's email formatting options were better, easier to use, and more reliable.
-app103 (June 20, 2011, 07:20 PM)

Makes sense (been a while since I used 98), I just have a bit of an aversion to all things AOL... :)
3971
General Software Discussion / Re: When your word processor begins openly mocking you, it's time to call it a day
« Last post by Stoic Joker on June 20, 2011, 06:46 PM »
I have a client that was having issues with Word files (.doc) self destructing after they'd been passed around the office a few times in the process of being finalized. The documents were quite large training event reports that contained several groups of pictures in the frequently over 50 pages of text. Office versions were all 2003 & 2007 (2010 wasn't out yet back then), but no common points were found for who was most guilty (think "Hot Potato") of breaking the document. After so many trips through the office it just went boom on who ever was unlucky enough to make the mistake of hitting save.

As it turns out Word Documents are extremely sensitive to formatting. While it doesn't matter how you choose to format the document ... It is extremely critical that you do it consistently. As it seems that it standard method of dealing with change is to react much as you described.

The client had a meeting, at which it was decided how everybody was going to deal with various formatting type instances ... And the problem has not resurfaced again in well over a year.

So given that you were stuck futzing with fanatical formatting fiats. That's probably what caused the problem.


That was when she discovered that AOL email windows are pretty good for formatting reports, as long as you didn't fill in the sendto and subject boxes. That's what she ended up using for the rest of her high school years. (she still hates Word and would rather use Notepad than to ever trust it again)
-app103 (June 20, 2011, 05:21 PM)

Well that's a clever (and somewhat frightening) solution. I take it WordPad didn't quite do what she wanted?
3972
General Software Discussion / Re: Compressing Outlook-Datafiles (PST) for free without Outlook?
« Last post by Stoic Joker on June 20, 2011, 02:44 PM »
I included the PST-Files before the admin disabled the using of these files
-Sascha (June 20, 2011, 01:54 PM)

Meaning it's listed in Account Settings as a seperate secondary storage location? Or did you just do import .pst file from the file menu?

If you did an import then it was actually just streamed into your Exchange mailbox. If it's a secondary storage location them you're at the Admin's mercy as to what can be done with it.

It possible depending on how it was done that you just need to right click-properties on the right Personal Files Folder parent to get to the compaction function for that file. I bring this up because I'm running Outlook 2010 with Exchange and have caching disabled. And the compaction functions are disabled on my machine when I go into properties. Being that I am the Admin I have access to everything ... So I do believe it's safe to assume that these are just/only disabled by Outlook due to there not being enabled/relevent.
3973
General Software Discussion / Re: Compressing Outlook-Datafiles (PST) for free without Outlook?
« Last post by Stoic Joker on June 20, 2011, 07:05 AM »
If Exchange caching mode is enabled (in outlook) Outlook will create an .ost file which is used for (laptops mainly) offline access.

Either way the .ost/.pst files aren't compressed unless they are being exported. They can be compacted for maintenance/performance reasons, but that's not really the same thing. Your not going to gain more storage space on the Exchange server by compressing the local file regardless. Those limits are set by administrators for reasons... ;)
3974
Living Room / Re: New DVD Rewinding Service
« Last post by Stoic Joker on June 17, 2011, 10:10 PM »
Perhaps I can interest you in ocean front property in Nevada. It's a fantastic investment! I'll even cut you a special deal.
-Deozaan (June 17, 2011, 05:02 PM)

You're trying to sell beach front property to a Floridian, and I'm the fool?  :-\
-Stoic Joker (June 17, 2011, 05:17 PM)

When the next big earthquake hits and California falls into the ocean, you'll wish you had taken me up on my offer.

 ;)
-Deozaan (June 17, 2011, 06:40 PM)

Oh sure, play to my cynical side...  :)
3975
Living Room / Re: Scented USB Drives?
« Last post by Stoic Joker on June 17, 2011, 05:31 PM »
If they had one in sandalwood the wife might go for it.
Pages: prev • 1 ... 154 155 156 157 158 [159] 160 161 162 163 164 ... 246 • next