Be Excellent To Each Other

And, you know, party on. Dude.

All times are UTC [ DST ]




Reply to topic  [ 130 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: 48489
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: 54722
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: 12883
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: 8394
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: 8394
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: 12883
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: 12883
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: 8394
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: 12883
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: 8394
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  
 
 Post subject: Re: SQL Help
PostPosted: Thu May 14, 2020 17:22 
User avatar

Joined: 31st Mar, 2008
Posts: 8394
Happy I don't know SQL day!

I would Google, but I'm not entirely sure how to phrase it concisely, and I'm tired and my brain has stopped.

Let's, for the sake of argument, say I have two tables, each with 1 column:
tbl a has values 1, 5, 7, 12
tbl b has values 8, 10 , 15, 19 (totally unrelated, I might have dates in one, ints in the other, probably going to have some text in there too).

I want a results set that is:
1 8
5 10
7 15
12 19
(so ideally, all the values from tbl a in order, and then all the values in tbl b in order, there might not necessarily be the same number of values in each table)

Am I just going to have to create a tmp table, give it an identity column and cycle through each row of each table (which isn't the end of the world) or am I just missing something obvious?


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu May 14, 2020 17:42 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 48489
Ideally you shouldn't be using row number to link two sets of data. Is there anything else that says why row 1 of table one should match with row 1 of column 2?

You can do it, just do a join on row_number - but it's making assumptions about your source data

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


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu May 14, 2020 17:45 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 48489
Something like this:

select A.val,B.val
from(
SELECT val,row_number() over (order by select(0)) as row_num
FROM A)A
join
(SELECT val,row_number() over (order by select(0)) as row_num
FROM B)B
on A.row_num=B.row_num

You have to have an order by clause to use row_number but order by select (0) should work I think.

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


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu May 14, 2020 17:48 
User avatar

Joined: 31st Mar, 2008
Posts: 8394
Cras wrote:
Ideally you shouldn't be using row number to link two sets of data. Is there anything else that says why row 1 of table one should match with row 1 of column 2?

You can do it, just do a join on row_number - but it's making assumptions about your source data


I'd forgotten about row_number. The sources will all be temp tables that have been populated in order, so theoretically row_number should work, but I'm not sure I can bring myself to go upstairs and test it right now.

Each table will have literally nothing else in it, so there's no other criteria for joining, I'm just trying to circumvent exporting multiple datasets and putting them together in Excel or something.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu May 14, 2020 17:50 
User avatar

Joined: 31st Mar, 2008
Posts: 8394
Cras wrote:
Something like this:

select A.val,B.val
from(
SELECT val,row_number() over (order by select(0)) as row_num
FROM A)A
join
(SELECT val,row_number() over (order by select(0)) as row_num
FROM B)B
on A.row_num=B.row_num

You have to have an order by clause to use row_number but order by select (0) should work I think.


Ta, I knew there'd be some batshit sql way to do it, rather than plodding through manually.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Fri May 15, 2020 15:42 
User avatar

Joined: 31st Mar, 2008
Posts: 8394
That seems to have worked perfectly. I had to do full outer joins as I've no idea which table might contain the most rows, but otherwise, exactly what I needed. Thanks again.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Fri May 15, 2020 16:18 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 48489
It's still a fugly hack, mind - but so is the majority of my output.

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


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Fri May 15, 2020 16:31 
User avatar
ugvm'er at heart...

Joined: 4th Mar, 2010
Posts: 21474
Fugly Hack is the name of my github account


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Fri May 15, 2020 16:32 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 48489
It's the title of my résumé.

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


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Fri May 15, 2020 18:41 
User avatar

Joined: 31st Mar, 2008
Posts: 8394
If it works...


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu Jul 23, 2020 9:33 
User avatar
gooby pls

Joined: 30th Mar, 2008
Posts: 12883
Location: Shrewsbury, UK
I feel like I've asked this question a million times before, or at least a variant of the same scenario...

So, I have a table that records stock transactions, i.e. stock coming in, stock being sold, stock being transferred between locations, general adjustments etc.

It looks a little like this (some fields missed off for brevity):

Code:
+------+--------------+------------+---------------+---------------+-------------+-------------+----------------+----------------+---------------+---------------+
|  id  |  product_id  |  batch_id  |  location_id  |  reason_code  |  qty_inner  |  qty_outer  |  before_inner  |  before_outer  |  after_inner  |  after_outer  |
+------+--------------+------------+---------------+---------------+-------------+-------------+----------------+----------------+---------------+---------------+
|   1  |           1  |         1  |            1  |  received     |          0  |         20  |             0  |             0  |            0  |           20  |
|   2  |           1  |         2  |            1  |  received     |          0  |         10  |             0  |             0  |            0  |           10  |
|   3  |           1  |         1  |            1  |  xfer-out     |          0  |         -5  |             0  |            20  |            0  |           15  |
|   4  |           1  |         1  |            2  |  xfer-in      |          0  |          5  |             0  |             0  |            0  |            5  |
|   5  |           1  |         1  |            1  |  sold         |       -250  |          0  |             0  |            15  |          750  |           14  |
+------+--------------+------------+---------------+---------------+-------------+-------------+----------------+----------------+---------------+---------------+


What I want to do is create a query that pulls out the single most recent stock record for the combination of product, batch, and location.

Although you can't see it on the example table there, batch_id is unique (so product_id 2 might have batch_ids 3, 4 and 5, but not 1 and 2)

So the data I'd want out would be:

Code:
+------+--------------+------------+---------------+---------------+-------------+-------------+----------------+----------------+---------------+---------------+
|  id  |  product_id  |  batch_id  |  location_id  |  reason_code  |  qty_inner  |  qty_outer  |  before_inner  |  before_outer  |  after_inner  |  after_outer  |
+------+--------------+------------+---------------+---------------+-------------+-------------+----------------+----------------+---------------+---------------+
|   2  |           1  |         2  |            1  |  received     |          0  |         10  |             0  |             0  |            0  |           10  |
|   4  |           1  |         1  |            2  |  xfer-in      |          0  |          5  |             0  |             0  |            0  |            5  |
|   5  |           1  |         1  |            1  |  sold         |       -250  |          0  |             0  |            15  |          750  |           14  |
+------+--------------+------------+---------------+---------------+-------------+-------------+----------------+----------------+---------------+---------------+


Basically, I'm interested in the after_inner/after_outer numbers.

I've tried various GROUP BYs and subqueries and nothing seems to work quite as I need it to.

_________________
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 Jul 23, 2020 10:00 
User avatar

Joined: 31st Mar, 2008
Posts: 8394
I'm assuming there's a date field in there somewhere?
So something like:
Code:
SELECT a.* from tbl a
JOIN
(SELECT max(date) maxdate, product_id, location_id, batch_id FROM tbl GROUP BY product_id, location_id, batch_id) b
on a.date = b.maxdate and a.product_id = b.product_id and a.location_id = b.location_id and a.batch_id = b.batch_id


Disclaimer - I'm on a week off, so if that's absolute crap then I apologise.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu Jul 23, 2020 10:19 
User avatar
gooby pls

Joined: 30th Mar, 2008
Posts: 12883
Location: Shrewsbury, UK
Sorry yeah, there is a date field.

That certainly looks like it's doing what I was hoping for. Cheers dude, much appreciated.

Didn't realise you could do a subquery as a join like that, but I guess it's just like joining to a temporary table??

_________________
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 Jul 23, 2020 10:31 
User avatar

Joined: 31st Mar, 2008
Posts: 8394
No worries, glad it turns out I'm not a complete fraud :p

Functionally, yes, I think that would just be the same as dumping that in a temp table, and joining on that. Someone better at SQL optimisation can probably tell you which is better, although the answer is probably "it depends."


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu Jul 23, 2020 10:33 
User avatar
gooby pls

Joined: 30th Mar, 2008
Posts: 12883
Location: Shrewsbury, UK
Well, it runs super fast as is so I would assume that going to the hassle of having the server create a temporary table as a separate operation would be less optimal in this case, for sure :)

_________________
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 Jul 23, 2020 10:41 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 67981
Location: Your Mum
SQL will build a temporary table by itself. If you watch the processlist really carefully you might see it happening.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu Jul 23, 2020 10:51 
User avatar
gooby pls

Joined: 30th Mar, 2008
Posts: 12883
Location: Shrewsbury, UK
Ain't nobody got time for that ;)

_________________
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 Jul 23, 2020 11:35 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 67981
Location: Your Mum
Honestly, it doesn't really matter. The optimisation engine in modern MySQL is so good that 99.9999% of the time you are better off leaving it the hell alone.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu Jul 23, 2020 12:07 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 48489
Hell yes. Database servers are smarter than you are.

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


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Thu Jul 23, 2020 13:21 
User avatar
gooby pls

Joined: 30th Mar, 2008
Posts: 12883
Location: Shrewsbury, UK
Yeah, I'm quite happy to just leave it looking after itself.

Anyone on here ever set up MySQL (or even better, MariaDB) to use encryption at rest?

I mean, I get the concept and know how to enable it for particular tables or databases and what-not, but what I just cannot wrap my head around is how the hell key management works - given that you're supposed to have your keys in rotation via a separate key server, I just haven't got any clue how I'd go about setting such a key server up.

_________________
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  [ 130 posts ]  Go to page Previous  1, 2, 3

All times are UTC [ DST ]


Who is online

Users browsing this forum: No registered users 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.