Davin21 (6/9/2011)

---

Probably not very performant, but you could always use:

;with customer_name as

(

SELECT *,SUM(rendered) OVER(PARTITION BY customername) as totalrendered from #customer

)

SELECT

customer.customername

,customer.serviceid

,serv.servicename

FROM customer_name customer

INNER JOIN #services serv on serv.serviceid = customer.serviceid

WHERE totalrendered...

if you need to avoid the having you can do this... not sure if it's the most efficient... (using the limited data it's 2 ms and 6 scans and 6...

Michael Valentine Jones (6/8/2011)

---

mtillman-921105 (6/8/2011)

---

Speaking of nonsense, an instructor of mine said that people used...

so the definition that allows 0.999... to be equal to 1 is that there is not a number between the two. And I also believe that this is what's...

Lee Sloan (1/14/2009)

---

SQLBOT with respect, I think you miss my point.

What I'm trying (and obviously failing) to say is that the advice points toward setting the processes at...

Add a HAVING clause to each of your selects... you have to sum the PCM and make sure it is >0 to include it, as well as using the WHERE...

To be more exact, these statements... notice the where clause...

select @Cols = stuff((select ', ' + quotename(A.AllergenName) from

Allergens A JOIN PCM P on A.AllergyID =

P.AllergyID

WHERE p.Record_Date BETWEEN '04/01/2011'...

in your query to select the column names... you will want to filter out the ones that have no values during that date period otherwise they'll be present in your...

could you post table schema's as well as estimated number of rows in each table... also indexes if there are any...

um... why not force the insert script into a known data type? I'm assuming you are using the insert script to create the table #TOTAUX...

Something like:

SELECT ALIMENTER_ID,CAST(SUM(BILAN) ...

I just had an interesting occurance.

Server Version:10.0, 64-bit

Database Compatibility: 80

the snippet in the question copied verbatim into SSMS and worked. The result was 255.

I would have thought database compatibility...

well... I'm not sure why either... but some simple debugging/troubleshooting things can narrow it down.

1. Ensure you are connecting to the same database (be it master or defined) in both...

Alternatively you can do (I have this in a snippet)

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE [COLUMN_NAME] = @ColumnName

Great post Jeff!

I had a thought... this could be extended to account for Times in addition to dates... just move the calculation to the lowest significant field (from day to...