quote:

---

Originally posted by boris37
So heres the problem. We need 1 entry per day per meterid. For some reason time is included in this date column and the entries might be doubled with a 1minute in between the timestamp. My solution was to remove the hours and just find per day per meterid.

---

WITH C AS 
(
SELECT DATEADD(day, DATEDIFF(day, 0, storeddatetime), 0) AS DateOnly, meterid
FROM NFLiquidsHist
)

SELECT DateOnly, meterid
FROM C
GROUP BY DateOnly, meterid;

SELECT DateOnly, meterid
FROM (SELECT DATEADD(day, DATEDIFF(day, 0, storeddatetime), 0) AS DateOnly, meterid
	  FROM NFLiquidsHist ) AS C
GROUP BY DateOnly, meterid;

DateOnly                meterid
----------------------- ----------
2009-06-20 00:00:00.000 8283
2009-06-20 00:00:00.000 8284
2009-06-23 00:00:00.000 8283
2009-06-23 00:00:00.000 8285
2009-06-23 00:00:00.000 8286

SELECT DateOnly, meterid
FROM (SELECT DATEADD(day, DATEDIFF(day, 0, storeddatetime), 0) AS DateOnly, meterid
	  FROM NFLiquidsHist ) AS C
GROUP BY DateOnly, meterid
HAVING COUNT(*) > 1;

quote:

---

Originally posted by nathans

You can find PK of table using: sp_help [NFLiquidsHist]

Regarding your aggregate error, you probably added the MeterId to your select statement as well as where. If that is the case you will need to add to your group by clause:

SELECT CONVERT(VARCHAR(10), StoredDateTime, 111) AS storeddatetime, meterid , COUNT(*) AS Expr1
FROM NFLiquidsHist
WHERE (meterid = 8283)
GROUP BY CONVERT(VARCHAR(10), StoredDateTime, 111), meterid
HAVING (COUNT(*) > 1)

edit: added where

---

SELECT bad_rows.*
FROM NFLiquidsHist AS bad_rows 
LEFT JOIN
(SELECT dateadd(dd,datediff(dd,0, StoredDateTime),0) AS storeddatetime, MeterID,MIN(PKCol) AS MinRec
FROM NFLiquidsHist
WHERE (MeterID = 8283)
GROUP BY dateadd(dd,datediff(dd,0, StoredDateTime),0), MeterID
HAVING (COUNT(*) > 1)) AS good_rows 
ON good_rows.storeddatetime = dateadd(dd,datediff(dd,0, bad_rows.StoredDateTime),0)
AND  good_rows.MeterID=bad_rows.MeterID
AND good_rows.MinRec=bad_rows.PKCol
WHERE good_rows.MinRec IS NULL

quote:

---

Originally posted by Peso

Something similar to this?

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY MeterID ORDER BY DATEDIFF(DAY, StoredDateTime, 0)) AS recID
FROM Table1
) AS f
WHERE recID > 1

---

N 56°04'39.26"
E 12°55'05.63"

---

quote:

---

Originally posted by Peso

DELETE f
-- SELECT *
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY MeterID ORDER BY StoredDateTime DESC) AS recID
FROM NFLiquidsHist
) AS f
WHERE recID > 1

---

N 56°04'39.26"
E 12°55'05.63"

---

quote:

---

Originally posted by boris37

quote:

---

Originally posted by Peso

DELETE f
-- SELECT *
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY MeterID ORDER BY StoredDateTime DESC) AS recID
FROM NFLiquidsHist
) AS f
WHERE recID > 1

---

N 56°04'39.26"
E 12°55'05.63"

---

Doesn't work if i add meterid like so:

SELECT *
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY MeterID ORDER BY StoredDateTime DESC) AS recID
FROM NFLiquidsHist where (MeterID = 8283)
) AS f
WHERE recID > 1

it gives me 2604 random rows that mean nothing. If i remove the meterid it crashes sql server... lol

---

SELECT *
FROM 
(
	SELECT 
		MeterID,
		StoredDateTime,
		ROW_NUMBER() OVER (PARTITION BY MeterID ORDER BY StoredDateTime DESC) AS recID
	FROM 
		NFLiquidsHist 
	where 
		MeterID = 8283
) AS f
WHERE recID > 1

quote:

---

Originally posted by visakh16

so as per your rule you want a single row out of above set? or one for each day?

---

SELECT *
FROM 
(
	SELECT 
		MeterID,
		StoredDateTime,
		ROW_NUMBER() OVER (PARTITION BY MeterID, StoredDateTime  ORDER BY StoredDateTime DESC) AS recID
	FROM 
		NFLiquidsHist 
	where 
		MeterID = 8283
) AS f
WHERE recID = 1