prabhu.st (7/10/2014)

---

@halifaxdal

May I know from which DB have you executed this statement, because if its got executed from the system DBs then propably you could get such number of results...

prabhu.st (7/10/2014)

---

@halifaxdal

May I know from which DB have you executed this statement, because if its got executed from the system DBs then propably you could get such number of results...

Just ran the second query on a server and it surprised me that a non-used db consumes 93.29% CPU time!

Does it calculate CPU time based on the whole server life...

What SQL edition the script applies to? Thanks.

Ed Pollack (6/2/2014)

---

The scripts here were tested with Adventureworks2008 as well as AdventureWorks2012---you...

Where can I download the database? All I found are for newer version like 2008R2 or DW2008R2

Thanks.

Thank you Lynn and Jeff for your time, it's much appreciated.

As WITH is not allowed in this case, I did some replacement:

FYStart = DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))

FYPrevStart = DateAdd(yy,-1,FYStart)

FYPrevStart = DateAdd(yy,-1,DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)))

FYNextStart...

Lynn Pettis (4/13/2014)

---

As much as I hate saying this, since semicolons are statement terminators not statement begininators, try putting a semicolon in front of the WITH of the CTE declaration....

Jeff Moden (4/13/2014)

There's a reason why it takes 2 weeks to get something into production. I suspect it's because people need time to test your code. So now...

Jeff Moden (4/12/2014)

---

Pesonally, I think that option 3 is the worst (it sounds like you're setting up to write embedded code, which is a huge mistake for maintenance, IMHO). ...

Jeff Moden (4/12/2014)

---

halifaxdal (4/12/2014)

---

Thank you guys so much and especially to Jeff for the analysis.

The one with function was accepted by the Dundas and the other one with DECLARE was...

Thank you guys so much and especially to Jeff for the analysis.

The one with function was accepted by the Dundas and the other one with DECLARE was rejected as DECLARE...

Thank you Jeff, I appreciate your enlightening, here is what I got so far:

SELECTB = Sum(Case When

DocStatus = 'Active'

Then 1 Else 0 End),

C = Sum(Case When

DocStatus =...

Jeff Moden (4/11/2014)

---

halifaxdal (4/11/2014)

---

This is driving people crazy!

There is another sp to be converted and this sp contains loop and lots of calculation. The original sp is working fine and...