If your table contained

col1 col2 col3

test1 10 OK

test1 6 DEL

test2 5 OK

the logic would produce

col1 col2 col3

test1 10 OK

test1 -6 DEL

test2 5 OK

and when summed

col1 col2

test1 4

test2 5

Well subject to indexes and performance testing I do this

;with cte (ID) AS (

SELECT ID

FROM Test1 T1

WHERE @p_flag = 1

ANDT1.moveFlag = 26

UNION ALL

SELECT ID

FROM Test1 T1

WHERE @p_flag = 0

ANDT1.moveFlag <>...

You are summing values so if there is a mix of DEL and non DEL rows (positive and negative values) then it is possible to have a positive value as...

Use

=SUM(IIF(Fields!col3.Value = "DEL",-CDec(Fields!col2.Value),CDec(Fields!col2.Value)))

or

=SUM(IIF(Fields!col3.Value = "DEL",CDec(Fields!col2.Value)*-1,CDec(Fields!col2.Value)))

I think putting -1 in brackets may be interpreted as a Boolean false

Well the first one works as well 😛

:hehe:

Not that I would normally disagree with a master 🙂 but my second query produces...

ChrisM@Work (5/7/2014)

---

Variation on David's;

DROP TABLE #Sample;

CREATE TABLE #Sample (a INT, b INT, c INT);

INSERT INTO #Sample (a, b, c)

SELECT * FROM (VALUES (12345, 10000, 1),

(12345, 10000, 2),

(12345, 10000, 3),

(12345, 10001,...

Or

;WITH cte ([Order#],ReceiptDate,MaxDate) AS (

SELECT [Order#],ReceiptDate,

MAX(ReceiptDate) OVER (PARTITION BY [Order#])

FROM )

SELECT [Order#],ReceiptDate

FROM cte

WHERE ReceiptDate >= DATEADD(month,-6,MaxDate)

;WITH cte ([Order#],ReceiptDate) AS (

SELECT [Order#],MAX(ReceiptDate)

FROM GROUP BY [Order#])

SELECT t.Order#,t.ReceiptDate

FROM cte

JOIN t ON t.[Order#] = cte.[Order#]

AND t.ReceiptDate >= DATEADD(month,-6,cte.ReceiptDate)

Or variation on Chris'

;WITH d (A,B,C,MAXB,MAXC) AS (

SELECT A,B,C,

MAX(B) OVER(PARTITION BY A),

MAX(C) OVER(PARTITION BY A,B)

FROM )

SELECT A,B,C

FROM d

WHERE B = MAXB

AND C = MAXC

*Edited* To fix

;WITH d (A, B, RowNo) AS (

SELECT A, B, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC)

FROM )

SELECT t.A, t.B, MAX(t.C) AS [C]

FROM d

JOIN t ON t.A = d.A...

Whether you are Domain Admin or Local Admin does not matter, it is role membership in SSRS that matters ie member of System Administrator role.

BUILTIN\Administrators is added to the System...

+1

The only thing I can deduce is this

;WITH cte (B)

AS (SELECT...

Not sure about that, my query took 3 secs with 80K rows on my test server.

Non Quirky Update solution (uses Tally table)

;WITH d (RowNo,PirateID,StartEvent,EndEvent,Duration) AS (

SELECT ROW_NUMBER() OVER (PARTITION BY PirateID ORDER BY StartEvent),

PirateID,StartEvent,EndEvent,DATEDIFF(second,StartEvent,EndEvent)

FROM timeSpent

),

r (RowNo,PirateID,N) AS (

SELECT d.RowNo,d.PirateID,t.N

FROM d

JOIN dbo.Tally t ON t.N BETWEEN...