Be Excellent To Each Other

And, you know, party on. Dude.

All times are UTC [ DST ]




Reply to topic  [ 109 posts ]  Go to page Previous  1, 2, 3
Author Message
 Post subject: Re: SQL Help
PostPosted: Mon Mar 04, 2019 17:22 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47580
Much better, cheers both!

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Mon Mar 04, 2019 22:03 
User avatar
Can't re-member

Joined: 27th Mar, 2008
Posts: 52507
Location: Liberty City
Have you tried using Oracle instead?

_________________
I am currently under construction.
Thank you for your patience.


Image


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu Mar 21, 2019 17:52 
User avatar
gooby pls

Joined: 30th Mar, 2008
Posts: 12279
Location: Shrewsbury, UK
Ok, this one's fucking me off.

Scenario: Producing a report of clients that have not paid their bloody invoices, with outstanding amounts split up into 30 day balance, 60 day balance etc.

Problem: Everything works fine, except where the client has made more than one payment (which obviously will end up being all of them) - the totals are multiplied by the number of payments.

Current SQL:
Code:
SELECT
    a.clientid, a.client_name,
    SUM(a.balance) AS total,
    SUM(a.balance30) AS total30,
    SUM(a.balance60) AS total60,
    SUM(a.balance90) AS total90,
    SUM(a.balance120) AS total120,
    SUM(a.balance_over) AS total_over,
    MAX(p.datereceived) AS last_payment_date
FROM (
    SELECT
        i.clientid, c.NAME AS client_name,
        i.outstanding_total AS balance,
        CASE WHEN ( DATEDIFF('2019-03-21', i.datepaymentdue) <= 30 ) THEN i.outstanding_total ELSE 0 END AS balance30,
        CASE WHEN ( DATEDIFF('2019-03-21', i.datepaymentdue) > 30 AND DATEDIFF('2019-03-21', i.datepaymentdue) <= 60 ) THEN i.outstanding_total ELSE 0 END AS balance60,
        CASE WHEN ( DATEDIFF('2019-03-21', i.datepaymentdue) > 60 AND DATEDIFF('2019-03-21', i.datepaymentdue) <= 90 ) THEN i.outstanding_total ELSE 0 END AS balance90,
        CASE WHEN ( DATEDIFF('2019-03-21', i.datepaymentdue) > 90 AND DATEDIFF('2019-03-21', i.datepaymentdue) <= 120 ) THEN i.outstanding_total ELSE 0 END AS balance120,
        CASE WHEN ( DATEDIFF('2019-03-21', i.datepaymentdue) > 120 ) THEN i.outstanding_total ELSE 0 END AS balance_over
        FROM accounts_invoices i
            INNER JOIN clients c ON i.clientid=c.clientid
        WHERE
            i.outstanding_total > 0 AND i.dateinvoiced <= '2019-03-21'
    ) AS a
LEFT JOIN accounts_payments p ON a.clientid=p.clientid
GROUP BY a.clientid
ORDER BY client_name


I'm assuming it's something to do with the JOIN on accounts_payments being outside of the subquery but I can't seem to get it to work with it inside the subquery (it has to be a LEFT JOIN so that it also picks up clients that have made no payments, incidentally)

Halp!

_________________
Mostly pointless witterings of a fool. gazchap.com
Source Design - Graphic Design and Web Design Shropshire


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu Mar 21, 2019 18:08 
User avatar

Joined: 31st Mar, 2008
Posts: 8227
Can you left join on (SELECT clientid, max(datereceived) from accounts_payments GROUP BY clientid) p instead of on accounts_payments?


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu Mar 21, 2019 18:09 
User avatar

Joined: 31st Mar, 2008
Posts: 8227
Oh, and alias max(datereceived) as something, else it won't work.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu Mar 21, 2019 18:12 
User avatar
gooby pls

Joined: 30th Mar, 2008
Posts: 12279
Location: Shrewsbury, UK
Just managed to get the LEFT JOIN working, I needed to group by the invoice ID to get around the problem I was having moving it into the subquery before, so now it's:
Code:
SELECT
    a.clientid, a.client_name,
    SUM(a.balance) AS total,
    SUM(a.balance30) AS total30,
    SUM(a.balance60) AS total60,
    SUM(a.balance90) AS total90,
    SUM(a.balance120) AS total120,
    SUM(a.balance_over) AS total_over,
    a.last_payment_date
FROM (
    SELECT
        i.clientid, c.NAME AS client_name,
        i.outstanding_total AS balance,
        CASE WHEN ( DATEDIFF('2019-03-21', i.datepaymentdue) <= 30 ) THEN i.outstanding_total ELSE 0 END AS balance30,
        CASE WHEN ( DATEDIFF('2019-03-21', i.datepaymentdue) > 30 AND DATEDIFF('2019-03-21', i.datepaymentdue) <= 60 ) THEN i.outstanding_total ELSE 0 END AS balance60,
        CASE WHEN ( DATEDIFF('2019-03-21', i.datepaymentdue) > 60 AND DATEDIFF('2019-03-21', i.datepaymentdue) <= 90 ) THEN i.outstanding_total ELSE 0 END AS balance90,
        CASE WHEN ( DATEDIFF('2019-03-21', i.datepaymentdue) > 90 AND DATEDIFF('2019-03-21', i.datepaymentdue) <= 120 ) THEN i.outstanding_total ELSE 0 END AS balance120,
        CASE WHEN ( DATEDIFF('2019-03-21', i.datepaymentdue) > 120 ) THEN i.outstanding_total ELSE 0 END AS balance_over,
        MAX(p.datereceived) as last_payment_date
        FROM accounts_invoices i
            INNER JOIN clients c ON i.clientid=c.clientid
            LEFT JOIN accounts_payments p ON p.clientid=i.clientid
        WHERE
            i.outstanding_total > 0 AND i.dateinvoiced <= '2019-03-21'
        GROUP BY i.invoiceid
    ) AS a
GROUP BY a.clientid
ORDER BY client_name

_________________
Mostly pointless witterings of a fool. gazchap.com
Source Design - Graphic Design and Web Design Shropshire


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu Mar 21, 2019 18:18 
User avatar
gooby pls

Joined: 30th Mar, 2008
Posts: 12279
Location: Shrewsbury, UK
Joans wrote:
Can you left join on (SELECT clientid, max(datereceived) from accounts_payments GROUP BY clientid) p instead of on accounts_payments?

Yeah, think that'd work too, but managed to rubber duck a way out of it :) Cheers though.

_________________
Mostly pointless witterings of a fool. gazchap.com
Source Design - Graphic Design and Web Design Shropshire


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu Mar 21, 2019 18:21 
User avatar

Joined: 31st Mar, 2008
Posts: 8227
GazChap wrote:
Joans wrote:
Can you left join on (SELECT clientid, max(datereceived) from accounts_payments GROUP BY clientid) p instead of on accounts_payments?

Yeah, think that'd work too, but managed to rubber duck a way out of it :) Cheers though.

Your way is neater, apparently this stuff can be hard when you don't have the data in front of you.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu Mar 21, 2019 18:31 
User avatar
gooby pls

Joined: 30th Mar, 2008
Posts: 12279
Location: Shrewsbury, UK
Joans wrote:
Your way is neater, apparently this stuff can be hard when you don't have the data in front of you.

Yeah, it's hard enough when you do have the data in front of you it seems, too!

_________________
Mostly pointless witterings of a fool. gazchap.com
Source Design - Graphic Design and Web Design Shropshire


Top
 Profile  
 
Display posts from previous:  Sort by  
Reply to topic  [ 109 posts ]  Go to page Previous  1, 2, 3

All times are UTC [ DST ]


Who is online

Users browsing this forum: TheVision and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search within this thread:
You are using the 'Ted' forum. Bill doesn't really exist any more. Bogus!
Want to help out with the hosting / advertising costs? That's very nice of you.
Are you on a mobile phone? Try http://beex.co.uk/m/
RIP, Owen.

Powered by a very Grim... version of phpBB © 2000, 2002, 2005, 2007 phpBB Group.