AFAIK, [L|R]TRIM() only removes blank spaces at either end of the string.
User REPLACE() to replace the characters you want to remove with an empty string.
REPLACE(FIELD,X,'')
But be aware that this may (I can’t find anything definitive) reduce the SARGability of your query, hurting performance.
Good to know. That means I probably get to tell the user requesting the report that we’re not going to display those names the way she wants.
Test it, see what it does performance-wise. If it’s not an indexed field in the first place, it probably won’t have much impact.
If it does hurt query performance, you can do it at the report layer instead of in the query.
So, I ended up doing this to satisfy the requestor.
REPLACE (REPLACE (REPLACE(Procedure_Name, ‘X ‘,’’),‘Y ‘,’’),‘Z’, ‘’) AS Procedure_Name
It didn’t slow anything down that I noticed, so now she’s happy.
Okay, why does
(COUNT(field1)*100/COUNT(field2)) AS ‘%’ work, but
(COUNT(field1)/COUNT(field2))*100 AS ‘%’ gives me all zeros?
Edit: Imagine an asterisk before each of the 100s
To make your code show properly in Markdown, either wrap it in backticks (`) or put it on a line starting with 4 spaces. If you have to use a character that’s used by Markdown, precede it with a backslash (like so: 10\*10\*10)
You’re missing a closing paren in the second example (assuming that count(field2) always returns non-zero).
((COUNT(field1)/COUNT(field2)))*100 AS '%'
Sometimes it helps me if I indent each layer of parens to make it clearer what my grouping is (it also helps find the missing ones).
(
(
COUNT(field1) / COUNT(field2)
)
) * 100 AS '%'Hey, it’ll let me post in this one again!
Count() returns an integer which means you’re dividing an integer by an integer and you’ll get an integer back, in this case 0. If you’ll convert field1 to something with a decimal point, it will return a decimal:
select (convert(float, count(field1)) / count(field2)) * 100
BTW, in a previous post where we were discussing the “%” mask, it should be noted that TechnoMistress gave the correct answer before I did, but I didn’t see it until later.
I’ve come to realize this is the perfect thread to read if I need a reality check…it quickly reminds me how much I don’t know.
Me too. Or at least I’m reminded of all the little details that keep slipping my mind.
I moved 40 posts to a new topic: SQL and Database: Splitting a column into individual words?
I moved a post to an existing topic: SQL and Database Learning Resources
Holy Threadus Ressurectus, Batman! (What’s the record for the longest time between posts?)
I have, as the topic title suggests, an SQL question, and remembered we’d had a few threads on SQL before. @dakboy, if I remember correctly, you’re our resident SQL genius, so hoping you can tell me if this is possible.
I have two tables with overlapping date ranges - one stores employees’ status, the other stores their commission group:
| PayeeID | Start Date | End Date | Status |
|---|---|---|---|
| 12345 | 08/27/2018 | 01/24/2022 | 1 |
| 12345 | 01/25/2022 | 03/21/2023 | 5 |
| 12345 | 03/22/2023 | 12/31/2999 | 4 |
| PayeeID | Start Date | End Date | CommGrp |
|---|---|---|---|
| 12345 | 04/07/2019 | 04/02/2022 | SA |
| 12345 | 04/03/2022 | 12/31/2999 | FTWR |
I need to combine the two tables so that the status and commission group are on the same line with the appropriate start and end dates (although I might be able to go with only Start Date; I’ll have to check something):
| PayeeID | Start Date | End Date | Status | CommGrp |
|---|---|---|---|---|
| 12345 | 08/27/2018 | 01/24/2022 | 1 | SA |
| 12345 | 01/25/2022 | 04/02/2022 | 5 | SA |
| 12345 | 04/03/2022 | 03/21/2022 | 5 | FTWR |
| 12345 | 03/22/2023 | 12/31/2999 | 4 | FTWR |
I’ve been trying to figure this out for way too long, using unions, case statements, nested select statements, and I just can’t get it. Is this possible using SQL, or am I screwed?
Dak will probably have a better answer, but I think what you need is a JOIN. If the first set comes from TableA and the second comes from TableB, you’d have something like this:
select a.payeeid, a.[start date], a.[end date], a.[status], b.commgrp
from TableA a
join TableB b on b.payeeid = a.payeeid
The “a” and “b” are just aliases for the tables and just make for less typing.
Yeah, tried that. Unfortunately it just repeats the start date and end date from table A for the table B data; it doesn’t interleave the data with the date ranges.
Oy this is a tough one.
I’m messing around with it in SQL Fiddle
Thanks @dakboy, I appreciate it.
Yeah, I didn’t pay any attention to the date ranges. I was tired. This is messy and it’s set up for multiple PayeeIDs, but I suspect if you run against them, it’ll blow up. Give it a try:
declare @things table (payeeid int not null, startdate date not null, enddate date not null, [status] int null, commgrp varchar(10) null)
;with startdates as (
select payeeid, [start date] startdate from TableA
union
select payeeid, [start date] startdate from TableB
), enddates as (
select payeeid, [end date] enddate from TableA
union
select payeeid, [end date] enddate from TableB
), mins as (
select min(s.startdate) startdate, min(e.enddate) enddate
from startdates s, enddates e
where e.enddate > s.startdate
), ordered_ranges as (
select startdate, enddate
from mins
union all
select s.startdate, e.enddate
from startdates s
join ordered_ranges ord on ord.enddate < s.startdate
join enddates e on e.enddate > s.startdate
)
insert @things (payeeid, startdate, enddate)
select distinct s.payeeid, o.startdate, min(o.enddate) enddate
from ordered_ranges o
join startdates s on s.startdate = o.startdate
join enddates e on e.enddate = o.enddate
group by o.startdate, s.payeeid
update @things set [status] = a.[status]
from @things t
join TableA a on a.payeeid = t.payeeid and t.startdate between a.[start date] and a.[end date]
update @things set commgrp = b.commgrp
from @things t
join TableB b on b.payeeid = t.payeeid and t.enddate between b.[start date] and b.[end date]
select * from @things