Home » SQL & PL/SQL » SQL & PL/SQL » Action done by client on same date of service as a specific procedure code
Action done by client on same date of service as a specific procedure code [message #278972] Tue, 06 November 2007 08:38 Go to next message
PatrickAylmer
Messages: 4
Registered: December 2005
Junior Member
HI,

Here's the fields I am using:

-Client ID
-Procedure code
-Service Date

I need to query on Clients who received any other Procedure Code on the same service date then procedure code 20111.

What kind of formula I can use?

thanks for your expertise.

Pat
Re: Action done by client on same date of service as a specific procedure code [message #278973 is a reply to message #278972] Tue, 06 November 2007 08:39 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
What did you try so far?
Re: Action done by client on same date of service as a specific procedure code [message #278975 is a reply to message #278973] Tue, 06 November 2007 08:43 Go to previous messageGo to next message
PatrickAylmer
Messages: 4
Registered: December 2005
Junior Member
Here's what I try:

I query on clients with a procedure code 20111. then I exported the client list and query on clients: but that don't give me the others procedures on same service date.



I've also try this formula:

COUNT_DISTINCT(Procedure Code) OVER(PARTITION BY CLAIMS HISTORY DETAILS2.Provider Number,Client ID,Service Date )

that gives me a count of distinct procedures. Then I query on procedure count greater then 1, but this doesn't proove me that every client have a code 20111 and every actions after.

hope it's clear...thanks
Re: Action done by client on same date of service as a specific procedure code [message #278997 is a reply to message #278975] Tue, 06 November 2007 09:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This formula does not give anything but an error, it is NOT a valid one for Oracle.
Post the actual query.

Regards
Michel

[Edit: silly me, missing NOT]

[Updated on: Tue, 06 November 2007 11:02]

Report message to a moderator

Re: Action done by client on same date of service as a specific procedure code [message #279006 is a reply to message #278997] Tue, 06 November 2007 09:56 Go to previous messageGo to next message
PatrickAylmer
Messages: 4
Registered: December 2005
Junior Member
Yes I am trying this formula in Oracle.
Sorry I think I put the post in the wrong group.


Here's the conditions used....see attachment

Merci Michel!

Pat
  • Attachment: Snap6.zip
    (Size: 38.35KB, Downloaded 123 times)
Re: Action done by client on same date of service as a specific procedure code [message #279007 is a reply to message #279006] Tue, 06 November 2007 10:13 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Hi Patrick,
you will generally find that people will not download files here unless they are txt. In fact, since it is really just a simple bit of sql, why not just post the query in the forum surrounded by [code]your code here[/code] tags.
Cheers
Re: Action done by client on same date of service as a specific procedure code [message #279009 is a reply to message #279007] Tue, 06 November 2007 10:29 Go to previous messageGo to next message
PatrickAylmer
Messages: 4
Registered: December 2005
Junior Member
"why not just post the query in the forum"

thanks, but what else do you need? I have poste the formula, and what I wanted to result from it, and the fields I am using.
the end result shoud be 'Clients who received any other Procedure Code on the same service date then procedure code 20111'

conditions used are:

-STATUS= PAID
-Provider Region=Quebec
-Dist. Procedure Code Count is greater then 1.

Do you need anything else?

thanks again

sorry I'm not a superuser....


Re: Action done by client on same date of service as a specific procedure code [message #279012 is a reply to message #279009] Tue, 06 November 2007 10:56 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:

sorry I'm not a superuser....

What makes you think we expect you to be a superuser? Have we given you the impression that you will only get an answer if you have enough knowledge to not need us to give you an answer?

We have quite simply, asked you to provide the actual query. That's all, nothing complex, just a cut and paste of the query that you are trying to run.

Quote:

thanks, but what else do you need?
The query, as previously asked for by Michel.
Quote:

I have poste the formula,
Well, you posted A formula, but it's not valid Oracle SQL syntax, from the looks of it, it is a Discoverer syntax
Quote:

the end result shou[l]d be 'Clients who received any other Procedure Code on the same service date then procedure code 20111
Tells us very little. Remember we have No Idea as to your data.

OK, you asked what we need. Supply a test case. i.e. supply create table statements for the relevant tables along with representative data to allow for replication of your issue along with the set of results that you require the query to return.

I'm not going to waste my time to-ing and fro-ing, continually seeking clarifications, or supplying more guesses whereby you say "but what if..." or "Well actually the REAL situation is..."

Do not attach a file, simply paste the code, surrounded by code tags.

Thanks
Previous Topic: image
Next Topic: More stale connections
Goto Forum:
  


Current Time: Tue Dec 06 15:42:55 CST 2016

Total time taken to generate the page: 0.06211 seconds