SELECT

DENSE_RANK() OVER(ORDER BY reference, collection_day, (rnum-1)/6 ),

*

FROM (

select

row_number() OVER (PARTITION BY reference, collection_day ORDER BY sort_date) AS rnum

,collection_day, reference, sort_date

from #temp

) d

order by reference, sort_date

-- force usage of the view

SELECT Column1, Column2, ... FROM Table1, View1

WITH (NOEXPAND) WHERE ...

-- force usage of the base tables

SELECT Column1, Column2, ... FROM Table1, View1

WHERE...

David, you might want to read the article again. The MONTH() function is covered...

That's because the subquery in Kapil's...

kapil_kk (8/2/2013)

---

...

Try this now-

Select

jc.intJobCardId

,b.vchBarcode as [Barcode]

,i.dteIncidentDate as [IncidentDate]

,ISNULL(ISNULL(p.nvcDisplayName, p2.nvcDisplayName), '') as [ServiceProvider]

,dteAssignedDate as [AssignedDate]

,dteCompletedDate as [CompletedDate]

,DATEDIFF(Hour, dteAssignedDate,dteCompletedDate) as [TimeToComplete]

,ISNULL(lb.TotalHours, 0) as [TotalHours]

,lb.TotalHours/DATEDIFF(Hour, dteAssignedDate,dteCompletedDate) as [Efficiency]

,'' as [Productivity]

,l.vchLocation

,typ.vchType

From dtlIncident...

R. Brush (8/1/2013)

---

ChrisM@Work,

I know how to do it. I didn't say it could not be accomplished some other way.

My original post said that an alias cannot be referred to...

This works but it's quite expensive to run:

WITH Pass1 AS (

SELECT

PoolId = DENSE_RANK() OVER(ORDER BY ResidentId),

HouseId, ResidentId

FROM #HouseResident

),

Pass2 AS (

SELECT

PoolId = MIN(PoolId) OVER (PARTITION BY HouseId),

HouseId,...

R. Brush (7/31/2013)

---

Just a clarificaton:

An alias for a calcuated column can be used in GROUP BY or ORDER BY clauses, but not in the SELECT column list as vega805 was...

Like this:

SELECT

c.companyName,

r.hoops,

r.ranchNumber,

tbNHC.[00002],

tbNHC.[30],

tbNHC.[40],

tbNHC.[60],

p.SumOfqtyReceived,

([30]+[40]+(0.6*[60]))/(p.[SumOfqtyReceived]/x.[TotalAc]) AS crateHC,

x.TotalAC,

tbNHC.Date

FROM tlkpCompany c

INNER JOIN tblCycleRanch r ON c.[companyNum] = r.[companyNum]

CROSS APPLY ( -- perform aggregate...

R. Brush (7/31/2013)

---

Access lets you define a calculated column in a query and then use the calculated column name in other calculated columns. SQL Server will not allow this....

Stefan_G (7/31/2013)

---

Since you are on SQL2008, you can forget about TRY_CAST since this is a SQL2012 feature only.

On SQL2008 you could use a CASE expression to guard the expression evaluation...

Ok - try these:

SELECT

TTT_Factureren = 0,

TTT_FactuurStatus = 0,

TTT_FactuurNummer = 0

FROM tblInvoicesProject

WHERE TTT_ProjectID = 26

AND TTT_FactuurStatus = 1

AND TTT_ID NOT IN (

SELECT SUBSTRING(CAST(X.TextDescription AS VARCHAR(100)),13,10)

FROM [500].dbo.frstx X

WHERE (LEFT(CAST(X.TextDescription AS VARCHAR(100)),...

How many rows are returned?

Have you tried both subqueries against your actual data, Marc?

Are you processing mailing (DM) extracts?