Be Excellent To Each Other

And, you know, party on. Dude.

All times are UTC [ DST ]




Reply to topic  [ 110 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: 47775
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: 53025
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: 12367
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: 8264
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: 8264
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: 12367
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: 12367
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: 8264
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: 12367
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  
 
 Post subject: Re: SQL Help
PostPosted: Tue May 14, 2019 15:40 
User avatar

Joined: 31st Mar, 2008
Posts: 8264
Thinking cap time!
I have a stored procedure that returns a summary table of data (it will always return data, even if it's all 0s). I would like to know when that sp returns a table full of zeroes.
My original plan was to store the results of the sp in a tmp table, and analyse that, but the summary sp already does that, so I'm stuck at the "insert exec statement cannot be nested" error.
I can't really change the underlying procedure, is there a quick and nasty way I can get at the data?


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

All times are UTC [ DST ]


Who is online

Users browsing this forum: Kern, MaliA, Mimi, Warhead 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.