SQL Help

Mr Russell · **Joined:** 6th Apr, 2008 **Posts:** 12243

It seems like the ANDs should work.

If not, you could use the CASE WHEN Statement:
WHERE (datediff(mm,getdate(),date_of_marathon)> CASE WHEN Sex='m' THEN 6 ELSE 3 END

GazChap · **Posted:** Fri Oct 20, 2017 12:40

Yes, you can do that with just ANDs.

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Okay, now we're getting complicated. I'm going to take this to real world because turning it into an analogy is too much of a pain. Basically, we've got 50 odd mobile apps that staff use. We have a non-usage policy where we blow away the app after 60 days of the user not opening it. We've now got a requirement to modify that - most apps will have 60 days, some will have no expiry at all, and others will have a custom value that could be any number of days. What that means is I need to build a custom sql query on the fly that can handle all those scenarios. This is what I've come up with. It's ugly as piss but I think it will work. Please sanity check it for me!

Code:

select * from apps where (((age>x and app=1) or (age>y and app=2)) and ((app<>3) and  (app<>4))) or (age>z and app<>1 and app<>2 and app<>3 and app<>4))

Breaks down into ((age>x and app=1) or (age>y and app=2)) which handles apps with custom expiry, (((age>x and app=1) or (age>y and app=2)) and ((app<>3) and (app<>4))) which then excludes those that have no expiry, and or (age>z and app<>1 and app<>2 and app<>3 and app<>4)) which handles all the apps with the standard expiry. Doing it like that I can feed any number of clauses within each grouping to build the query. This doesn't have to be fast, it's a background job.

Thoughts?

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Actually I can dump the middle clause, I think.

Code:

select * from apps where ((age>x and app=1) or (age>y and app=2)) or (age>z and app<>1 and app<>2 and app<>3 and app<>4))

Bamba · **Joined:** 25th Jul, 2010 **Posts:** 11128

I don't think you need to bracket those first two clauses up together do you? Assuming that, and for some extra readability, how about:

select *
from apps
where age>x and app=1
or age>y and app=2
or age>z and app not in (1,2,3,4)

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

That should work. I tend to overly bracket because it helps my readability when I'm constructing long clauses

Bamba · **Joined:** 25th Jul, 2010 **Posts:** 11128

Speaking generally though, wouldn't you be better having a ref data table that's got an entry for each app and it's attendant expiry period and just doing a lookup on that at launch time rather than baking the logic into a specific query?

Doctor Glyndwr · **Joined:** 30th Mar, 2008 **Posts:** 32619

What Bamba said. Build another table with static data with expiry dates for the apps that aren't the default 60 days. It's going to be a lot better than expanding your nested WHERE clauses to 50+ tuples.

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Love to, but I'm running with read-only DB rights for this, so I have to be able to build the whole thing into a single select query. I've got that data, it's sat in a config file on the box I'm building the query from, but it's not in SQL, hence having to construct the long complicated query on the fly.

Doctor Glyndwr · **Joined:** 30th Mar, 2008 **Posts:** 32619

Can't pull CSV files in? (Disclaimer, it's been 4 years since I touched any database any of the rest of you have ever heard of, I'm rusty.)

DavPaz · **Joined:** 27th Jun, 2008 **Posts:** 38460

Doctor Glyndwr wrote:

Can't pull CSV files in? (Disclaimer, it's been 4 years since I touched any database any of the rest of you have ever heard of, I'm rusty.)

Those yanks gave you a new name did they, Rusty?

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Doctor Glyndwr wrote:

Can't pull CSV files in? (Disclaimer, it's been 4 years since I touched any database any of the rest of you have ever heard of, I'm rusty.)

Not into the DB, no - which is the only place it'd be useful in the sql query itself

Doctor Glyndwr · **Joined:** 30th Mar, 2008 **Posts:** 32619

Ok, so you need a code generator that'll read your CSV and spit out the query.

Code generators are great and definitely never a hacky solution you later regret. You can do the thing. I believe in you.

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Woohoo!

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

It's already 42 lines of code. This will definitely work and never go wrong.

Grim... · **Posted:** Fri Oct 20, 2017 15:12

Just generate the SQL query. Give a shit if it's big, SQL is good at that.

If it makes like easier, couldn't you do a separate query for each app? 50 is piss all.

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

I only want 100 results though, across all apps - so if I was doing it in separate queries I'd struggle with how many to pull for each query. It's not awful, I think I've done it in a mere 49 lines of horrible code.

Grim... · **Posted:** Fri Oct 20, 2017 16:14

Code:

(SELECT * FROM x WHERE y = z)
UNION
(SELECT * FROM x WHERE a = b)
LIMIT 100

Doctor Glyndwr · **Joined:** 30th Mar, 2008 **Posts:** 32619

Doctor Glyndwr wrote:

Ok, so you need a code generator that'll read your CSV and spit out the query.

Grim... wrote:

Just generate the SQL query.

Fuck you too.

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Doctor Glyndwr wrote:

Ok, so you need a code generator that'll read your CSV and spit out the query.

Grim... wrote:

Just generate the SQL query.

Fuck you too.

Doctor Glyndwr wrote:

It's going to be a lot better than expanding your nested WHERE clauses to 50+ tuples.

Ahem.

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Grim... wrote:

Code:

(SELECT * FROM x WHERE y = z)
UNION
(SELECT * FROM x WHERE a = b)
LIMIT 100

Well I've written it now ain't I?

Doctor Glyndwr · **Joined:** 30th Mar, 2008 **Posts:** 32619

Cras wrote:

Ahem.

I'm confused. What is happening?

Bamba · **Joined:** 25th Jul, 2010 **Posts:** 11128

Doctor Glyndwr wrote:

Cras wrote:

Ahem.

I'm confused. What is happening?

Confusion is happening, so you're actually completely on top of the situation.

Grim... · **Posted:** Fri Oct 20, 2017 17:03

Got to say, I'd have just used Excel to write the SQL code for me.

COME AT ME, PEOPLE WHO DON'T WANT TO LOSE ALL THEIR DATA

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Doctor Glyndwr wrote:

Cras wrote:

Ahem.

I'm confused. What is happening?

You started off being anti-big where clauses, now you're claiming credit for being on team code factory.

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Grim... wrote:

Got to say, I'd have just used Excel to write the SQL code for me.

COME AT ME, PEOPLE WHO DON'T WANT TO LOSE ALL THEIR DATA

I'd be interested to know how.

Grim... · **Posted:** Fri Oct 20, 2017 17:10

Here's my .csv:

Code:

1,2000
2,1000
3,10000

So on C1 I bang in

Code:

=CONCATENATE("(SELECT * FROM mytable WHERE id = '",A1,"' AND age > '",A2,"') UNION ")

And then I make it go all the way down the rows and then I copy it into a text editor and fix the end.

BOSH

Doctor Glyndwr · **Joined:** 30th Mar, 2008 **Posts:** 32619

Cras wrote:

You started off being anti-big where clauses, now you're claiming credit for being on team code factory.

I'm against writing a big WHERE clause by hand, because it'll be confusing to read and hence hard to update without introducing hard-to-catch bugs. But codegenning it from a CSV should be a way of coping with that.

Alternatively...

Cras wrote:

Love to, but I'm running with read-only DB rights for this, so I have to be able to build the whole thing into a single select query.

...find whatever prick is responsible for this constraint and bellow at them to be less shite.

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Well for one it's a vendor product. Creating tables in vendor databases tends to come with feedback like 'removal of support'.

Also principle of least privilege, innit. If it can be done with minimal rights, do it with minimal rights.

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8648

Can you create temp tables in a read only db?

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Nope

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Not in SQL Server, anyway

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8648

Back to your mega code then, I guess.

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8648

This might be a stupid question, but here goes.
I have two databases (same server), let's call them a & b.
I have a stored procedure on database a which updates a record in a table on database b.
I want to call this procedure from an external app, which works, but the calling user then doesn't have permission to update the table in database b.
I don't really want to permanently change the permissions on database b (because it's not mine, so they could get changed back and break stuff).

I was trying to grant and revoke permissions as part of the procedure, but that doesn't work as they're in different databases. Thinking about it though, I'm guessing even if that worked the user calling the stored procedure wouldn't have permission to grant themselves permission on the table in database b.

My plan b is a terrible clunky C# thing involving impersonation, but the former seems the better option if I can do it?

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

I don't think there's really any way around the fact that if you're updating a table in b, you need to have rights to update a table in b.

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Do you 'own' the server they're running on? Give the account SQL-wide rights and it won't matter what they do at the DB level. Not exactly good practice, but...

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8648

I can dish out the rights to anyone I want to, so I suppose yeah, that's probably easier.

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

For the purposes of this question let's say I'm tracking race times. Each row in the table is the name of a racer, and their time. There are many entries for each racer, for example:

Steve, 3:30:00
Karen, 3:46:00
Steve, 3:26:00
Steve, 3:29:00
Karen, 3:52:00

What I want to pull out is each racer's best time. I'm currently doing this:

Select * from (select racer, racetime, row_number() over (partition by racer order by racetime asc) as time_rank from races) a where a.time_rank=1

The problem is I've got six million rows and that query is bastard expensive. It's taking about 30s to run which is far from ideal.

Any ideas how I could do this more efficiently?

Cheers.

Doctor Glyndwr · **Joined:** 30th Mar, 2008 **Posts:** 32619

why not

SELECT racer_name, min(time) FROM data GROUP BY 1

?

Grim... · **Posted:** Mon Mar 04, 2019 17:04

Code:

SELECT racer_name, MIN(time) FROM table GROUP BY racer_name

is safer.

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49232

Much better, cheers both!

myp · **Posted:** Mon Mar 04, 2019 22:03

Have you tried using Oracle instead?

GazChap · **Posted:** Thu Mar 21, 2019 17:52

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!

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8648

Can you left join on (SELECT clientid, max(datereceived) from accounts_payments GROUP BY clientid) p instead of on accounts_payments?

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8648

Oh, and alias max(datereceived) as something, else it won't work.

GazChap · **Posted:** Thu Mar 21, 2019 18:12

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

GazChap · **Posted:** Thu Mar 21, 2019 18:18

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.

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8648

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.

GazChap · **Posted:** Thu Mar 21, 2019 18:31

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!

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8648

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?

Be Excellent To Each Other

SQL Help
I don't think we have a thread

Who is online

Be Excellent To Each Other

SQL Help I don't think we have a thread

Who is online

SQL Help
I don't think we have a thread