Viewing 15 posts - 4,231 through 4,245 (of 10,144 total)
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
August 2, 2013 at 4:40 am
-- 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...
August 2, 2013 at 4:28 am
David, you might want to read the article again. The MONTH() function is covered...
August 2, 2013 at 4:17 am
Teee (8/2/2013)
Lol! it actually worked when I used IN instead of = but the data that was returned wasn't correct, the second bit works fine.Thanks
That's because the subquery in Kapil's...
August 2, 2013 at 1:32 am
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...
August 2, 2013 at 1:14 am
R. Brush (8/1/2013)
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...
August 1, 2013 at 7:46 am
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,...
August 1, 2013 at 7:36 am
R. Brush (7/31/2013)
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...
August 1, 2013 at 1:01 am
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...
July 31, 2013 at 9:54 am
R. Brush (7/31/2013)
July 31, 2013 at 9:45 am
Stefan_G (7/31/2013)
On SQL2008 you could use a CASE expression to guard the expression evaluation...
July 31, 2013 at 6:20 am
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)),...
July 31, 2013 at 5:29 am
Have you tried both subqueries against your actual data, Marc?
July 31, 2013 at 5:13 am
Are you processing mailing (DM) extracts?
July 31, 2013 at 4:59 am
Viewing 15 posts - 4,231 through 4,245 (of 10,144 total)