So I'm trying to work on some BI reporting for work using info in our MS SQL DB. Found this script online, basically gives you a 'Days Late' value on a document that had a set shipping date.
I know a DECLARE statement won't work in a view, so I was wondering how I could either turn this into a view (with a sql job to alter/update on a regular basis) or a table, so I can pull the data into the reporting tools built into our ERP software frontend.
Declare
@days as int
set @days = 5
select a.sopnumbe, a.docdate, a.ReqShipdate, datediff(day, getdate(), a.ReqShipdate) as days_left
from sop10100 a
where a.soptype = 2 and a.voidstts = 0
and datediff(day, getdate(), a.ReqShipdate) >= (0 - @days) and datediff(day, getdate(), a.ReqShipdate) <= @days
and not exists
(
select b.* from sop10200 b where b.sopnumbe = a.sopnumbe and b.soptype = 2 and b.qtyfulfi > 0
)
select a.sopnumbe, a.docdate, a.ReqShipdate, datediff(day, getdate(), a.ReqShipdate) as days_left
from sop10100 a
where a.soptype = 2 and a.voidstts = 0
and datediff(day, getdate(), a.ReqShipdate) >= (0 - 5) and datediff(day, getdate(), a.ReqShipdate) <= 5
and not exists
(
select b.* from sop10200 b where b.sopnumbe = a.sopnumbe and b.soptype = 2 and b.qtyfulfi > 0
)
? You don't necessarily need the variable there, it's just making it easy to replace the right numbers in one place.
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
STILL working on this stupid selection problem. I'm getting a stack overflow error and I don't know why. When I click on one table it calls both tables three times. When I click on the final row for some reason it just goes into an infinite loop.
The final row probably keeps trying to do stuff with rows after it and goes into a loop forever because there is on row after it, and then finally stack overflow on that. That is all speculation though.
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
The final row probably keeps trying to do stuff with rows after it and goes into a loop forever because there is on row after it, and then finally stack overflow on that. That is all speculation though.
Yeah I'm tracing through it now... It's just frustrating because this is so stupid to add this sort of functionality, but yet it is causing me to pull my (already thinned) hair out.
10:20a JOHN SMITH N DG 1234 1 PER DR BOBBY
Date of Birth 05/15/1934 Age: 77
Memo 1: Contact Son For ALL
Memo 2: Labs/Confappts Wjim
10:40a STEVEN SMITH NEW N HO 567 1 req Dr BOBBY needed Friday polycystic kidney disease-NPP
Date of Birth 11/26/1956 Age: 55 mailed PT CAN NOT RS AGAIN!!!PER JANE OK TO R/S LAST
Memo 1: $25.00 COPAY TIME, WIFE AWARE
Memo 2:
11:00a ////////////////////////// N 1
11:00a JANE DOE N HO 890 1 2 MONTH FU, ALSO CYCLOSPORINE LEVEL
Date of Birth 04/16/1954 Age: 57
Memo 1: Scan Cards 2/22/12
Memo 2: cell-daughter Jessica
This is the kind of file I'm parsing... now I've gotta get that note on the side, up to 4 lines, reliably, separate from those memos. WHAT WONDERFUL FUN THIS IS THANKS EHR COMPANY FOR NOT GIVING ME AN XML/HL7 FILE AND TELLING ME TO GET THE FUCK LOST.
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
Maybe? If I was doing this the C# way I'd do a mouse listener on the table and then on mouse click do something like "getSelectedRow" because otherwise you run into weird situations like this where you want to select a row but don't want to trigger a listener.
bowen on
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
10:20a JOHN SMITH N DG 1234 1 PER DR BOBBY
Date of Birth 05/15/1934 Age: 77
Memo 1: Contact Son For ALL
Memo 2: Labs/Confappts Wjim
10:40a STEVEN SMITH NEW N HO 567 1 req Dr BOBBY needed Friday polycystic kidney disease-NPP
Date of Birth 11/26/1956 Age: 55 mailed PT CAN NOT RS AGAIN!!!PER JANE OK TO R/S LAST
Memo 1: $25.00 COPAY TIME, WIFE AWARE
Memo 2:
11:00a ////////////////////////// N 1
11:00a JANE DOE N HO 890 1 2 MONTH FU, ALSO CYCLOSPORINE LEVEL
Date of Birth 04/16/1954 Age: 57
Memo 1: Scan Cards 2/22/12
Memo 2: cell-daughter Jessica
This is the kind of file I'm parsing... now I've gotta get that note on the side, up to 4 lines, reliably, separate from those memos. WHAT WONDERFUL FUN THIS IS THANKS EHR COMPANY FOR NOT GIVING ME AN XML/HL7 FILE AND TELLING ME TO GET THE FUCK LOST.
Oh Christ, that's parseable, a royal pain in the ass and inconsistent as hell, but its parseable... and it's bad when you'd PREFER HL7 to come out... What are you trying to rip, generated reports?
Yeah, the company that has this EHR doesn't and won't give export modules or text of any sort. The only thing we can do is print reports. Naturally I printed this to a text document (automatic printing if they select the right printer # I set up to dump it to text into a folder).
I've got it working, and down to about 10 seconds for 70 or so records. God is this thing hideous as shit though.
bowen on
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
Maybe? If I was doing this the C# way I'd do a mouse listener on the table and then on mouse click do something like "getSelectedRow" because otherwise you run into weird situations like this where you want to select a row but don't want to trigger a listener.
The problem is that we can't use getSelectedRow only because the two tables aren't necessarily going to be the same size. The problem looks like it's this:
If the user clicks in Table 2, the valueChanged method is fired for that table, which then calls Table 1 to update it's selected row, which fires the valueChanged for that table, and the value changed method in that calls the update on Table 2 which calls valueChanged... etc.
Yeah, the company that has this EHR doesn't and won't give export modules or text of any sort. The only thing we can do is print reports. Naturally I printed this to a text document (automatic printing if they select the right printer # I set up to dump it to text into a folder).
I've got it working, and down to about 10 seconds for 70 or so records. God is this thing hideous as shit though.
there has GOT to be a DB behind it. HAS TO. if i was stuck with something like that, i'd be spelunking thru their system and see what i can figure out from the db, and mangle it myself.
I was going to flip this goddamn table if this worked. No joke. :P This is the right track. Don't waste any more time working on it bowen, I'll figure it out... I hate to waste your time for something stupid like this.
Yeah, the company that has this EHR doesn't and won't give export modules or text of any sort. The only thing we can do is print reports. Naturally I printed this to a text document (automatic printing if they select the right printer # I set up to dump it to text into a folder).
I've got it working, and down to about 10 seconds for 70 or so records. God is this thing hideous as shit though.
there has GOT to be a DB behind it. HAS TO. if i was stuck with something like that, i'd be spelunking thru their system and see what i can figure out from the db, and mangle it myself.
There is. It's an old c-tree like database (from what the old account manager told us before he got canned). Completely locked down on a proprietary server with a proprietary unix operating system (from my poking and prodding it looks like some sort of redhat/fedora system).
I tried that route and it was far easier to do this... god what I wouldn't give to have a SQL<->C-tree(whatever) ODBC driver right now and full access to that system.
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
I was going to flip this goddamn table if this worked. No joke. :P This is the right track. Don't waste any more time working on it bowen, I'll figure it out... I hate to waste your time for something stupid like this.
If there's one thing I've learned from nearly 10 years of programming it's that simplicity is always the answer. If you can't do something, refactor it and pretend like you're in C++ 101.
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
Yeah, the company that has this EHR doesn't and won't give export modules or text of any sort. The only thing we can do is print reports. Naturally I printed this to a text document (automatic printing if they select the right printer # I set up to dump it to text into a folder).
I've got it working, and down to about 10 seconds for 70 or so records. God is this thing hideous as shit though.
there has GOT to be a DB behind it. HAS TO. if i was stuck with something like that, i'd be spelunking thru their system and see what i can figure out from the db, and mangle it myself.
There is. It's an old c-tree like database (from what the old account manager told us before he got canned). Completely locked down on a proprietary server with a proprietary unix operating system (from my poking and prodding it looks like some sort of redhat/fedora system).
I tried that route and it was far easier to do this... god what I wouldn't give to have a SQL<->C-tree(whatever) ODBC driver right now and full access to that system.
shit like this is kindof one of my specialities... not sure how i can help you, the dark arts don't communicate well when you're not in person. i'm sure that you'd be happy with a C/B-Tree ->CSV converter, too. If you want to go that route, the first step is identifying what the exact db/datastore engine is.
They appear to have SSH open too, locked down tight. I'm confident if I had access to the shell as root I could probably do the workaround I had planned a few years back.
They refused to give me SSH as root unless we signed off that any future issue was up to me to fix. Regardless if I did anything or not. Honestly it wasn't worth it because they have so many issues. I think they call me at least twice a week with an issue and I refer them to the EHR and voila it's fixed. And we only use it for billing/scheduling.
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
They appear to have SSH open too, locked down tight. I'm confident if I had access to the shell as root I could probably do the workaround I had planned a few years back.
They refused to give me SSH as root unless we signed off that any future issue was up to me to fix. Regardless if I did anything or not. Honestly it wasn't worth it because they have so many issues. I think they call me at least twice a week with an issue and I refer them to the EHR and voila it's fixed. And we only use it for billing/scheduling.
force a reboot of the machine into init 1. tralalalalaala as root all you want.
I expect he meant to type cooler, but forgot the o.
or mistyped it and since comments don't get compiled, he never looked at it again.
start getting snippy when he sends emails with misspellings. code comments.. meh.. its nice that he has them, and you should declare victory and go home.
They appear to have SSH open too, locked down tight. I'm confident if I had access to the shell as root I could probably do the workaround I had planned a few years back.
They refused to give me SSH as root unless we signed off that any future issue was up to me to fix. Regardless if I did anything or not. Honestly it wasn't worth it because they have so many issues. I think they call me at least twice a week with an issue and I refer them to the EHR and voila it's fixed. And we only use it for billing/scheduling.
force a reboot of the machine into init 1. tralalalalaala as root all you want.
I contemplated it.
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
I expect he meant to type cooler, but forgot the o.
or mistyped it and since comments don't get compiled, he never looked at it again.
start getting snippy when he sends emails with misspellings. code comments.. meh.. its nice that he has them, and you should declare victory and go home.
I am terrible because color looks bad and I always instinctively add the u to the silent u words like honor and color. I am unsure how this would come across to another code when I type that in the comments.
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
Posts
So I'm trying to work on some BI reporting for work using info in our MS SQL DB. Found this script online, basically gives you a 'Days Late' value on a document that had a set shipping date.
I know a DECLARE statement won't work in a view, so I was wondering how I could either turn this into a view (with a sql job to alter/update on a regular basis) or a table, so I can pull the data into the reporting tools built into our ERP software frontend.
Thoughts?
? You don't necessarily need the variable there, it's just making it easy to replace the right numbers in one place.
Thanks!
No highlighting. Sad.
The final row probably keeps trying to do stuff with rows after it and goes into a loop forever because there is on row after it, and then finally stack overflow on that. That is all speculation though.
Yeah I'm tracing through it now... It's just frustrating because this is so stupid to add this sort of functionality, but yet it is causing me to pull my (already thinned) hair out.
This is the kind of file I'm parsing... now I've gotta get that note on the side, up to 4 lines, reliably, separate from those memos. WHAT WONDERFUL FUN THIS IS THANKS EHR COMPANY FOR NOT GIVING ME AN XML/HL7 FILE AND TELLING ME TO GET THE FUCK LOST.
Oh Christ, that's parseable, a royal pain in the ass and inconsistent as hell, but its parseable... and it's bad when you'd PREFER HL7 to come out... What are you trying to rip, generated reports?
Joe's Stream.
I've got it working, and down to about 10 seconds for 70 or so records. God is this thing hideous as shit though.
The problem is that we can't use getSelectedRow only because the two tables aren't necessarily going to be the same size. The problem looks like it's this:
If the user clicks in Table 2, the valueChanged method is fired for that table, which then calls Table 1 to update it's selected row, which fires the valueChanged for that table, and the value changed method in that calls the update on Table 2 which calls valueChanged... etc.
there has GOT to be a DB behind it. HAS TO. if i was stuck with something like that, i'd be spelunking thru their system and see what i can figure out from the db, and mangle it myself.
Joe's Stream.
I was going to flip this goddamn table if this worked. No joke. :P This is the right track. Don't waste any more time working on it bowen, I'll figure it out... I hate to waste your time for something stupid like this.
There is. It's an old c-tree like database (from what the old account manager told us before he got canned). Completely locked down on a proprietary server with a proprietary unix operating system (from my poking and prodding it looks like some sort of redhat/fedora system).
I tried that route and it was far easier to do this... god what I wouldn't give to have a SQL<->C-tree(whatever) ODBC driver right now and full access to that system.
If there's one thing I've learned from nearly 10 years of programming it's that simplicity is always the answer. If you can't do something, refactor it and pretend like you're in C++ 101.
shit like this is kindof one of my specialities... not sure how i can help you, the dark arts don't communicate well when you're not in person. i'm sure that you'd be happy with a C/B-Tree ->CSV converter, too. If you want to go that route, the first step is identifying what the exact db/datastore engine is.
Joe's Stream.
They refused to give me SSH as root unless we signed off that any future issue was up to me to fix. Regardless if I did anything or not. Honestly it wasn't worth it because they have so many issues. I think they call me at least twice a week with an issue and I refer them to the EHR and voila it's fixed. And we only use it for billing/scheduling.
force a reboot of the machine into init 1. tralalalalaala as root all you want.
Joe's Stream.
"Mac Instillation Guide" should not go into a final product. Ugh.
code is understandable. really, it isnt meant for public consumption, and getting any comments in code is usually a good thing.
customer facing documentation? yeh, that's a lack of professionalism right there.
Joe's Stream.
I review an intern's code and see "coler" and "requirment" all the time and it bothers me.
This made me laugh out loud and now I look crazy, thanks zeeny.
or mistyped it and since comments don't get compiled, he never looked at it again.
start getting snippy when he sends emails with misspellings. code comments.. meh.. its nice that he has them, and you should declare victory and go home.
Joe's Stream.
I contemplated it.
That's the funny thing, this is what it's like:
It's not in comments, it's actual code.
:^: