Re: help with query please!!! max() and joins

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Joe Scalise (jscalise_at_advantageccs.org)
Date: 10/26/04 

Date: Tue, 26 Oct 2004 12:04:19 -0400

actually it still pulls up the refunds.....

thats strange
"Joe Scalise" <jscalise@advantageccs.org> wrote in message
news:uo2UxQ3uEHA.3828@TK2MSFTNGP12.phx.gbl...
> Adam your the man...
>
> So far it looks as if it's exactly what i want! Yeah you are right
> PromiseStatus shouldn't be up there, it was only there because i had
> originally intended not to exclude those clients, but notate that they had
> a promise graphically in Reporting Services, like changing a box to red if
> they had a promise....
>
> what does the r1 do an mean?
>
> Thanks again for your help Adam!
> Anyway to rate you as a poster or MVP?
> "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
> news:etxvoJ3uEHA.1616@TK2MSFTNGP10.phx.gbl...
>> "Joe Scalise" <jscalise@advantageccs.org> wrote in message
>> news:uOrWU72uEHA.2520@TK2MSFTNGP15.phx.gbl...
>>>
>>> To rephrase what i need Adam: clients can have multiple rows in the
>> receipts
>>> table. For each client I want to grab the row with the most recent
>>> [DateReceived] fields as long as [PaymentType] <> REFUND, ALSO these
>> clients
>>> cannot be in the PaymentPromises table. The max function gives me
>>> results
>>> like this:
>>
>>
>> (untested):
>>
>> SELECT
>> dbo.Clients.ClientID,dbo.Clients.WholeName,dbo.PaymentPromises.PromiseStatus
>> ....
>> FROM clients
>> INNER JOIN receipts ON clients.clientid = receipts.clientid
>> LEFT OUTER JOIN dbo.PaymentPromises ON dbo.Clients.ClientID =
>> dbo.PaymentPromises.ClientID
>> WHERE receipts.paymenttype <> 'REFUND'
>> AND receipts.datereceived =
>> (SELECT MAX(datereceived)
>> FROM receipts r1
>> WHERE r1.clientid = receipts.clientid)
>> AND dbo.PaymentPromises.ClientID IS NULL
>>
>>
>> This should give you: Most recent receipt per client, unless that
>> receipt
>> was a refund, and only clients with no rows in the PaymentPromises table.
>> What I am confused about is why you have PaymentPromises in the SELECT
>> list
>> if you don't want clients to have rows in that table?
>>
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.sqljunkies.com/weblog/amachanic
>> --
>>
>>
>
>

Relevant Pages

  • Re: Virtual List View functionality in ADAM and Outlook
    ... Patching is not an issue for clients if required to make it work. ... is that 1million user objects spread out among 9 forests and then ... Use ADAM and VLV's to provide more customized AB's to units and to off-load ...
    (microsoft.public.windows.server.active_directory)
  • Re: [Full-disclosure] [Professional IT Security Providers - Exposed] QuietMove ( D - )
    ... We don't post their names on the website. ... As for my experience - I was also a the security officer for an at ... Most of our clients are referred by others who are very satisfied ... Seeing as Adam ...
    (Full-Disclosure)
  • Re: MS ADAM/AD: Absolute simplest repl/sync solution for MS ADAM on 2 or more WinXP machines?
    ... I would think you could look at it like this: The replication requires ... directory service for clients to query as needed. ... > Each client instance of ADAM will be used by a single user on that WinXP ...
    (microsoft.public.windows.server.active_directory)
  • Re: help with query please!!! max() and joins
    ... > To rephrase what i need Adam: clients can have multiple rows in the ... INNER JOIN receipts ON clients.clientid = receipts.clientid ... and only clients with no rows in the PaymentPromises table. ... Adam Machanic ...
    (microsoft.public.sqlserver.programming)
  • Re: help with query please!!! max() and joins
    ... PromiseStatus shouldn't be up there, it was only there because i had ... originally intended not to exclude those clients, but notate that they had a ... Thanks again for your help Adam! ... > INNER JOIN receipts ON clients.clientid = receipts.clientid ...
    (microsoft.public.sqlserver.programming)