dwain.c (6/17/2013)

---

ChrisM@Work (6/17/2013)

---

Hi Amy

This runs about 4 times faster than the original against the sample data set.

SELECT

w.ID,

Wage = MIN(w.Wage),

Sector = MIN(w.Sector),

tw = MIN(a.tw)

FROM #WageData w

INNER...

Ramp up the output row count to about a quarter million and blackhole the output (an attempt to eliminate io from the execution time), and a tally table version runs...

Chrissy321 (6/17/2013)

---

I actually need to pass multiple parameters so CASE logic would probably get really dense. I am going to look at a multi-statement table-valued function.

I will be joining multiple...

dwain.c (6/17/2013)

---

Well I'll be darned...nice one, Dwain. Must have...

Thanks for posting.

I'd recommend you implement the indexes I posted earlier and repost the actual plan from prod with the indexes...

There's another element to this issue here. The OP has a calendar table but is unsure how to plug it into the query above.

TheSQLGuru (6/17/2013)

---

T.Ashish (6/17/2013)

---

Provided test data is from my local, so it will not give any issue. Getting data from prod server is not possible for me.

...

A randomly-selected work from a number of randomly-selected authors:

SELECT

WORKID,

AUTHORID

FROM (

SELECT TOP 10

AUTHORID

FROM dbo.LITERARYWORKS

GROUP BY MASTNUM

ORDER BY NEWID()

) at

CROSS APPLY (

SELECT TOP 1

WORKID

FROM...

Hi Amy

This runs about 4 times faster than the original against the sample data set.

SELECT

w.ID,

Wage = MIN(w.Wage),

Sector = MIN(w.Sector),

tw = MIN(a.tw)

FROM #WageData w

INNER JOIN (

SELECT...

T.Ashish (6/17/2013)

---

Provided test data is from my local, so it will not give any issue. Getting data from prod server is not possible for me.

I...

-- try changing this

OUTER APPLY (

SELECT

active_users = SUM(CASE WHEN activity_flag = 1 AND reg_flag = 0 THEN 1 ELSE 0 END),

new_users = SUM(CASE WHEN activity_flag = 1 AND reg_flag...

The result set from your second query already contains all of the columns in your requested output. What do you want to change?

Your first query is quite inefficient. The table...

Why read the persons table three times when you only have to read it once?

SELECT

activity_flag = case when comp.activity_flag = 1 then 'Active' else 'Inactive' end,

comp.name,

comp.comp_code,

x1.active_users,

x1.new_users,

x1.inactive_users,

worker = (select count(1)

from...

Hands up if you find this easier to scan:

SELECT

o.ID,

PAYREAL = ISNULL(SUM(o.VALPAYREAL),0),

PAYPLAN = ISNULL(SUM(o.VALPAYPLAN),0),

x.PAYPLANTODATE,

PAYPROCESS = ISNULL(SUM(o.VALPAYPROCESS),0)

FROM ABC o

CROSS APPLY (

SELECT

...

You're welcome, and thanks for the feedback. Sorry I didn't get back to you on your second question, I'm on UK time.