Be Excellent To Each Other
https://www.beexcellenttoeachother.com/forum/

SQL Help
https://www.beexcellenttoeachother.com/forum/viewtopic.php?f=3&t=10861		 Page 3 of 3
Author:  Cras [ Mon Mar 04, 2019 17:22 ]
Post subject:  Re: SQL Help
Much better, cheers both!
Author:  myp [ Mon Mar 04, 2019 22:03 ]
Post subject:  Re: SQL Help
Have you tried using Oracle instead?
Author:  GazChap [ Thu Mar 21, 2019 17:52 ]
Post subject:  Re: SQL Help
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!
Author:  Joans [ Thu Mar 21, 2019 18:08 ]
Post subject:  Re: SQL Help
Can you left join on (SELECT clientid, max(datereceived) from accounts_payments GROUP BY clientid) p instead of on accounts_payments?
Author:  Joans [ Thu Mar 21, 2019 18:09 ]
Post subject:  Re: SQL Help
Oh, and alias max(datereceived) as something, else it won't work.
Author:  GazChap [ Thu Mar 21, 2019 18:12 ]
Post subject:  Re: SQL Help
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
Author:  GazChap [ Thu Mar 21, 2019 18:18 ]
Post subject:  Re: SQL Help
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.
Author:  Joans [ Thu Mar 21, 2019 18:21 ]
Post subject:  Re: SQL Help
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.
Author:  GazChap [ Thu Mar 21, 2019 18:31 ]
Post subject:  Re: SQL Help
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!
Author:  Joans [ Tue May 14, 2019 15:40 ]
Post subject:  Re: SQL Help
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?
Author:  Joans [ Thu May 14, 2020 17:22 ]
Post subject:  Re: SQL Help
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?
Author:  Cras [ Thu May 14, 2020 17:42 ]
Post subject:  Re: SQL Help
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
Author:  Cras [ Thu May 14, 2020 17:45 ]
Post subject:  Re: SQL Help
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.
Author:  Joans [ Thu May 14, 2020 17:48 ]
Post subject:  Re: SQL Help
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.
Author:  Joans [ Thu May 14, 2020 17:50 ]
Post subject:  Re: SQL Help
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.
Author:  Joans [ Fri May 15, 2020 15:42 ]
Post subject:  Re: SQL Help
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.
Author:  Cras [ Fri May 15, 2020 16:18 ]
Post subject:  Re: SQL Help
It's still a fugly hack, mind - but so is the majority of my output.
Author:  Trooper [ Fri May 15, 2020 16:31 ]
Post subject:  Re: SQL Help
Fugly Hack is the name of my github account
Author:  Cras [ Fri May 15, 2020 16:32 ]
Post subject:  Re: SQL Help
It's the title of my résumé.
Author:  Joans [ Fri May 15, 2020 18:41 ]
Post subject:  Re: SQL Help
If it works...
Author:  GazChap [ Thu Jul 23, 2020 9:33 ]
Post subject:  Re: SQL Help
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.
Author:  Joans [ Thu Jul 23, 2020 10:00 ]
Post subject:  Re: SQL Help
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.
Author:  GazChap [ Thu Jul 23, 2020 10:19 ]
Post subject:  Re: SQL Help
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??
Author:  Joans [ Thu Jul 23, 2020 10:31 ]
Post subject:  Re: SQL Help
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."
Author:  GazChap [ Thu Jul 23, 2020 10:33 ]
Post subject:  Re: SQL Help
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 :)
Author:  Grim... [ Thu Jul 23, 2020 10:41 ]
Post subject:  Re: SQL Help
SQL will build a temporary table by itself. If you watch the processlist really carefully you might see it happening.
Author:  GazChap [ Thu Jul 23, 2020 10:51 ]
Post subject:  Re: SQL Help
Ain't nobody got time for that ;)
Author:  Grim... [ Thu Jul 23, 2020 11:35 ]
Post subject:  Re: SQL Help
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.
Author:  Cras [ Thu Jul 23, 2020 12:07 ]
Post subject:  Re: SQL Help
Hell yes. Database servers are smarter than you are.
Author:  GazChap [ Thu Jul 23, 2020 13:21 ]
Post subject:  Re: SQL Help
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.
Author:  Joans [ Tue May 17, 2022 18:44 ]
Post subject:  Re: SQL Help
Apparently, I'm bad at SQL in May (I must have been off last year).
As usual, this feels like a stupid question, but my brain has gone blank.
Stored procedure, TSql, I want to compare two variables and if they don't match, do "something".
Ah, but either of them could be null.
I feel like I would normally just do something like
Code:
If isnull(@a, 0) != isnull(@b, 0)

Where 0 is something that a and b would never be if they had a value, but that seems a bit dangerous, as maybe something will change in the future that means they could be 0.

So then I end up with:
Code:
If @a != @b or (@a is null and @b is not null) or (@a is not null and @b is null)

And that just seems like a really long way to do it, so there must be a better way, but my brain is mush, so I'm delegating to the internet.
Author:  Mr Russell [ Tue May 17, 2022 18:59 ]
Post subject:  Re: SQL Help
https://stackoverflow.com/questions/661 ... sible-null seems to think coalesce could help, or just @a is not null and @b is not null and @a <> @b
Author:  Mr Russell [ Tue May 17, 2022 19:00 ]
Post subject:  Re: SQL Help
I guess it depends if both a and b are null equals a match or a no match
Author:  Joans [ Tue May 17, 2022 19:45 ]
Post subject:  Re: SQL Help
Thank you, this is definitely the right direction.
I think the not(coalesce (@a,@b) or @a=@b) should do the trick, but it's not quite working, so I'll try and work that out.
Author:  Mr Russell [ Tue May 17, 2022 21:32 ]
Post subject:  Re: SQL Help
Thing is though, if you need to make the statement longer and clunkier to make it work then go for it.

If it's stupid and it works, it's not stupid.
Author:  Cras [ Tue May 17, 2022 21:37 ]
Post subject:  Re: SQL Help
Yeah, it's clunkier to write out but it's not inefficient in execution
Author:  Joans [ Wed May 18, 2022 9:03 ]
Post subject:  Re: SQL Help
Just to save me chasing this round in circles

Code:
If not (coalesce(@a, @b) is null or @a = @b)


Won't work if only one of @a or @b is null, right? Because you get not (false or unknown) = not unknown = unknown.

I could've just written this out the long way by now...
Page 3 of 3 All times are UTC [ DST ]
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/