ramrajan (7/18/2014)

---

If we have multiple readings for a ID we are picking the latest record date as current reading and then we need to show the prevoius reading data which...

ramrajan (7/18/2014)

---

And her is how i want to get the output. Based on the sample data mentioned in my POst

ID ReadingDate SeqID

1 2014-07-18 07:48:30.200 1

1 2014-07-17 07:48:30.200 2

2 2014-07-17 07:48:30.200...

<<cough>>

----------------------------------------------------------------------------

JLS

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 8...

Can you provide Ed with the information he needs to complete your query for you?

Use ROW_NUMBER(). Partition by ID, order by date descending, pick the first two rows.

Then check the date difference between row 2 and row 1 of each partition, using a CTE...

Latest reading date

Current reading

Are they the same thing?

Try this for a performance lift:

;WITH Aggregates AS (

SELECT

caseid,

[total]= SUM(benefitpaid),

[unprocessed]= SUM(CASE WHEN unprocessed = 1 THEN benefitpaid ELSE 0 END),

[sentout]= SUM(CASE WHEN statusid = 2 THEN benefitpaid ELSE 0...

This is a truly wild guess but seems to fit the sketchy description:

;WITH DedupedData AS (

SELECT tbl_id, cs_id, USERID, assgn_dtm, complet_dtm,

rn = ROW_NUMBER () OVER (PARTITION BY cs_id, USERID,...

DROP TABLE #Temp

CREATE TABLE #Temp (KEY1 INT, Key2 INT, [Type] VARCHAR(5))

INSERT INTO #Temp VALUES

(1,NULL, 'TypeA'),

(2,5, 'TypeA'),

(3,1, 'TypeB'),

(4,NULL, 'TypeA'),

(5,NULL, 'TypeB'),

(6,26, 'TypeC'),

(7,NULL, 'TypeD'),

(8,NULL, 'TypeD')

SELECT *

FROM #Temp ot

WHERE NOT EXISTS (

SELECT 1...

Yep, but haven't heard anything since.

Here's another response: read the article then...

pilla.sree85 (7/17/2014)

---

Till now i am good.then how would i check the condition duplicate count >1 then i need to execute query for further processing in order to display output ...

DROP TABLE #Temp

CREATE TABLE #Temp (ID int, [RowCount] int)

INSERT INTO #Temp (ID, [RowCount]) VALUES

(1, 448),(2, 267),(3, 297),(4, 216),(5, 405),(6, 254),(7, 107),(8, 102),(9,...

-- Next, check to see if table ReceivedFiles is required in the query,

-- perhaps you can get away with checking to see if there is a value or not...

-- Next, a little reformatting. Note that I've changed the expression for totalcount,

-- to indicate that you are counting rows. Aggregate functions COUNT, SUM, AVG etc ignore

-- rows where...