Quick suggestion, use a case statement

😎

USE tempdb;

GO

create table dbo.fun_test(

packtype nvarchar(50),

price money)

insert into dbo.fun_test

values ('Single','2'),('Monthly','25.00'),('Monthly','27.00'),('Monthly','23.50'),('Single','2'),('Deposit','1'),('Deposit','2')

select

PackType

,count(*) as Records

,sum(CASE ...

Little clean up in the logic, now it looks pretty good

😎

/********************************************************************

-- Stripping out any non-numerical characters

-- EE 2014-10-28 Inital coding

-- EE ...

Looks like this is coming from sqlmap (sqlmap.org), automated injection tool.

😎

SQL Server's implementation of the window functions does not allow for sharing window specification (over clause specs) between functions.

😎

Here is my attempt from the train journey to work this morning, looks like it will give the while loop a real run for the money.

😎

/********************************************************************

-- Stripping out any...

Nice problem, just what's needed to get the brain started in the morning:-D

😎

/* BASE_DATA brings together everything that is needed, adds

a group identifier for...

Some test results, looks like the computed column method is roughly 4 times faster than the next one

😎

Beginning execution loop

--============================================================================================================

-- Method 1 by sqlslacker

-- ...

Chipping in a little more, another method is a computed column and an index on that column, here is a script adapted to fit Jeff's test harness, append it at...

Quick thought, embed the case statement in a CTE and then do the aggregations on the output.

😎

Quick note, if the ROW_NUMBER function has a satisfying index to work on it is not expensive at all, consider the code below which only does one index scan and...

Jeff Moden (10/25/2014)

---

Eirikur Eiriksson (10/25/2014)

---

Keep in mind, there's no such thing as a bad code (if it works), your efforts are as worthy as mine any given day, keep up...

Either to an explicit type cast to numeric or float using cast or convert or multiply the column value with 1.0,...

Sorry about the error, quick copy/paste without checking...

SQL Server performs an Integer division on whole...

Good job, the scripts do explain what is happening

😎

The table dbo.MMA_KPIVAR_DAT can have many instances of the same VARIABLE_ID, CLIENT_ID and SITE_ID. The table dbo.MMA_KPIVAR_MST can have many instances of...