So, if I'm correct, you want the output from this:

SELECT

t.yrm,

s.sales,

s.part

FROM

testtally AS t

LEFT JOIN

testsales AS s

ONt.yrm = s.yrm

order by

t.yrm,

s.part

which looks like this:

yrm sales...

jayoub1 (12/7/2010)

---

Thanks for your reply

So you are saying that i would create an SQL agent job to run on the first of every month and put the query in the...

drew.allen (12/7/2010)

---

Craig Farrell (12/7/2010)

---

drew.allen (12/7/2010)

---

I was trying to do the same thing and ran into the same problem. The cause was a GO statement that I included in my...

I'm not entirely sure what a Tally Table has to do with this, but can you post some sample data from the two tables to help illustrate the exact data...

That I'm...

loki1049 (12/7/2010)

---

Ah great thanks, thats just want I was looking for. Cuts the run time down from 24 sec to 8 sec. Thats a good start.

And what do you mean...

Add this line to your where clause:

AND cteTally.N <= DATEDIFF( dd, Temp.Date1NoTime, Temp.Date2NoTime)

This will restrict the crossjoin to just the # of Tally records you require.

FYI: the code above loses...

RStarr, while your code seems functional (I didn't extensively test it), take a look at the million row test.

If you look near the bottom of the article, Jeff gives a...

What you're looking for is:

Insert Into #table EXEC sp_proc

Don't think we'll need the full query for this Jay.

You've got a two step process here that you're dealing with. First, you want to get rid of the need...

SQL33 (12/7/2010)

---

When setting up indexing, is it limited by the selected field size property settings? If so, can I downsize the fieldsize properties to enable more fields being included...

27 million rows on what appears to be a single drive spindle desktop server? 1 Gig is barely enough to hold Windows in memory these days. 🙂

At a guess,...

drew.allen (12/7/2010)

---

I was trying to do the same thing and ran into the same problem. The cause was a GO statement that I included in my dynamic SQL. ...

sharath.chalamgari (12/7/2010)

---

My Execute sql task is having one store procedure along with some sql statemetns in the sql query. when we run the package it is taking more time...

This isn't too bad, but you just need to wrap an override on the first column with a case statement so that when it's over 40 hours you simply show...