I was able to stabilize my first solution up to 1M rows - 2000 ItemIDs in the BOM and 5000 dates for each! Talk about a counterintuitive way to...

Hi Serg,

Yeah data is really important to this query without a doubt. Try the originals with 100 ItemIDs and 200000 rows of warehouse data and things change drastically again...

I found this problem interesting so I decided to check into how it actually performs on a larger test harness, so I set up 100 ItemIDs and 100,000 rows of...

Why bother with a TRIGGER on a two character column to UPPER the value. Why not just use a computed column AS (UPPER(Country))?

If you need indexing on it, make...

I'll second that. Using the staggered rows approach is pretty well known when it comes to finding contiguous dates, but not everyone realizes how it...

Here's another approach using Serg's kindly provided setup data.

WITH InventoryDates AS

(

SELECT [Date]

FROM @WhareHouseMovement

WHERE Quantity > 0

...

saumil1987 (5/6/2015)

---

Hi Guys,

Can any one please help me with this?

I've this result from my 'case' query;

Jan Feb Mar April

1 2 3 ...

Here's a query that makes this suggestion pretty simple:

WITH SampleTimes (t)...

It is interesting to note that SQL MVP Erland Sommarskog has written a series of articles on error handling in T-SQL.

Error and Transaction Handling in SQL Server

That's just the first...

Not the first time I've seen LAG be LAME! But it is simple.

Too bad you're not working in SQL 2012:

WITH SampleData (WeekNr, Data) AS

(

SELECT 1, 18

UNION ALL SELECT 2, 33

UNION...

There are pros and cons to this but here's an article with some templates:

Using Stored Procedures to Provide an Application’s Business-Logic Layer [/url]

I hope it works for you. I failed to mention,...

I forgot that I wanted to point out a few problems with the requirements:

- You have no primary keys on your tables.

- Your payments table has the date...

This might do it for you.

create table #tbl1

(

saleNo int

,saleDate date

,saleAmt float

)

insert into #tbl1 (saleNo, saleDate, saleAmt)

values (1, '2014-06-01',1200)

insert into #tbl1 (saleNo, saleDate, saleAmt)

values (2, '2014-10-01',2400)

insert into #tbl1 (saleNo, saleDate, saleAmt)

values...