Viewing 15 posts - 1 through 15 (of 4,079 total)
I ask because you can get a fairly efficient solution to the example above using the LAG() function instead of a join. But this assumes that there are no other...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 2, 2021 at 4:29 pm
Edited to rephrase my question:
What if there were two 'F' rows preceding the 'S' row?
Do you only want data from the most recent 'F' row, prior to the 'S' row?
Will...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 2, 2021 at 4:25 pm
Jeff, one of the best things I ever heard about comments is this:
Comments don't exist to describe what the code is doing. They exist to describe what the code...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 11, 2021 at 3:56 pm
Why go to all the work of coding a cursor? It's easy enough to do set-based if you CROSS APPLY the STRING_SPLIT() function.
SELECT [ID], [SubName] =...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 20, 2021 at 10:29 pm
Just avoiding single aliases as a rule doesn't keep developers from using aliases like a1, a2, etc. When there are just a few joins, I've never understood the panic...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 17, 2021 at 9:49 pm
A "band-aid" for complex multiline table-valued functions is to use them only to write the data out to a #temp table, which you can then use for subsequent queries with...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 17, 2021 at 9:34 pm
If you include the Vlookup amount in your second table, you won't even require a case statement.
declare @Table1 table (ID int identity(1,1) primary key, Amount int)
insert into...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 14, 2021 at 3:11 pm
Just to pile on, COUNT(expr) only counts non-null values, while Count(*) counts rows. See below.
declare @table table (RowID int, RowData varchar(20))
insert into @table
values (1,'Hi Mom'), (2,...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 13, 2021 at 9:08 pm
Couple of ways to solve this. First, you could do the following.
...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 13, 2021 at 8:47 pm
Perhaps. I was just going by his language that says the columns contain nulls. I don't think he ever used the words "all" or "only".
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 27, 2021 at 9:02 pm
Back to Jeff's point, the code as written will only return a hit if ALL rows are NULL for a particular column. If even one row has a non-null value,...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 27, 2021 at 8:40 pm
While I understand at least one reason why CTEs don't automatically generate a spool file for re-use, I have always wondered why there wasn't an object that you could re-use...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 27, 2021 at 8:21 pm
You can run the query and output the data as a CSV, which Excel can load as a spreadsheet.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 26, 2021 at 4:42 pm
EXCEPT is great. But to be clear, it doesn't require that the tables have identical structures, it compares the results of two queries. You can select a subset of...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 20, 2021 at 2:12 pm
The data still has to be read -- how else would SQL know that the proposed new value was the same as the value in the table itself?
It's...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 16, 2021 at 7:15 pm
Viewing 15 posts - 1 through 15 (of 4,079 total)