I hope that the OP realizes that it is no longer necessary to pass multiple values within a comma-delimited list to a SP.

Use a Table-valued Parameter instead.

The only question of...

mickyT (11/20/2013)

---

dwain.c (11/20/2013)

---

Nice follow up Micky!

I have one question though, for the 4D case. Are both queries returning the same row counts?

I was a little unsure about what I...

Nice follow up Micky!

I have one question though, for the 4D case. Are both queries returning the same row counts?

I was a little unsure about what I did with...

I can second what Jeff said about performance of LAG/LEAD as I've done some testing myself. Frankly I was not impressed in the admittedly limited testing that I have...

As it turns out, I've been experimenting of late with product substitution tables and so far I have something that may work for you. Note that I have not...

You can try to see if this will work for you.

create procedure uspDataGet

as

select Code = 'aswe'

union

select Code = 'hgtr'

GO

create procedure uspDataTEST

@Audit bit = 0

as

declare @tblResult table

(

Code char(10)

)

--populate @tblResult with the...

It doesn't get much more elegant than that, but there are multiple ways to skin that same cat.

SELECT NewRow=b.ID

,Col1=MAX(CASE val WHEN 1 THEN Value ELSE ''...

Bangkok actually. 4-5 time zones removed from NZ.

Start with a Calendar table. For this example, I'll give you my utility FUNCTION to generate a calendar on the fly.

CREATE FUNCTION [dbo].[GenerateCalendar]

...

Jeff Moden (11/19/2013)

---

valeryk2000 (11/19/2013)

---

Very good article indeed. Learned a lot. I'm just taking on the "age" problem, it seems more complicated than I initially thought. I am working for a...

Laurence Neville (11/19/2013)

---

No, I was testing using my example data. mickey's query has a relatively simple plan with mostly index seeks. Running both mickey's and dwain's resulted in 19% for...

Luis Cazares (11/19/2013)

---

I'm just modifying Dwain's query to reduce the code by using dense_rank.

WITH SampleData (ID, Data) AS

(

SELECT 'A', 'P1' UNION ALL

SELECT 'A', 'P1' UNION ALL

SELECT 'A', 'P2' UNION ALL

SELECT...

Abu Dina (11/19/2013)

---

dwain.c (11/18/2013)

---

A couple of caveats to using this technique:

- All columns created will default to...

Here's a good intro article to dispel myths about Locking, Blocking and Deadlocking [/url]

Use a Tally table.

WITH results AS

(

SELECT 1 day, 100 amt UNION ALL

SELECT 2 day, 200 amt UNION ALL

...