Can you post the query you are using?

T.Ashish (8/25/2013)

---

Dear All,

I have a query that takes 22 seconds.

If I create below index then query finishes in 5 seconds, but I'm told that this type of index can't be...

SELECT SUM(Boiler1)

FROM Energy_Centre_Availability eca

WHERE NOT EXISTS (

SELECT 1

FROM Energy_Centre_Boiler_Maintenance_Schedule s

WHERE eca.t_stamp > s.startDate

AND eca.t_stamp < s.endDate

)

SELECT SUM(Boiler1)

FROM Energy_Centre_Availability eca

INNER JOIN Energy_Centre_Boiler_Maintenance_Schedule s

ON eca.t_stamp > s.startDate

AND eca.t_stamp < s.endDate

;WITH SampleData AS (

SELECT * FROM (values

('8009874321', 'cars'),

('8009874321', 'Newsales'),

('8009874321', 'Newsales'),

('8009870000', 'Newsales'),

('8009870000', 'BenaSales'),

('8009870000', 'BenaSales'),

('8009870000', 'BenaSales2'),

('8009870000', 'BenaSales2')

) sampleData ([800num], CompanyName))

SELECT [800num], CompanyName

FROM (

SELECT [800num], CompanyName,

dr = DENSE_RANK() OVER(PARTITION BY...

Jan - it was...

Why restrict yourself to a view or a function? Why not just write the query?

Four years on...he's your tutor now.

Why are you using IN and not = for these comparisons?

SV00300.CUSTNMBR in (@CUSTNMBR)

or SV00300.LOCATNNM in (@LOCATNNM)

or SV00300.Contract_Number in (@Contract_Number)

If any of these variables are a comma-delimited list, they won't be...

Sean Lange (8/23/2013)

---

SQLRNNR (8/23/2013)

---

Sean Lange (8/23/2013)

---

SQLRNNR (8/23/2013)

---

Koen Verbeeck (8/23/2013)

---

ChrisM@Work (8/23/2013)

---

GilaMonster (8/23/2013)

---

Fal (8/22/2013)

---

Speaking of fruit. The missus and I are getting a new kitchen in and are trying to finalise...

I wouldn't want to bet on that "cannot", HanShi. Perhaps "cannot easily"...

From the sample data??

Thanks for posting sample data, Kelvin. What do you want the output to look like?

If your sample data doesn't look anything like your real data, then you are likely to receive queries which don't work when run against your real data.

How about an...

-- try this

SELECT n.*, d.category, d.n

FROM newtable n

INNER JOIN (

SELECT

category,

amended_sw_manufacturer,

amended_product_name,

amended_product_version,

n = COUNT(*)

FROM datalookuptable

WHERE category IN ('Licensable','Non Licensable')

GROUP BY category, amended_sw_manufacturer, amended_product_name, amended_product_version

) d

ON n.softwaremanufacturer...