SQL- last 6 days but only last record for each day

Question

User1238961430 posted

Hello Community. 

I have this query

WITH X AS (SELECT        EwonId, Name, CAST(TagDate AS DATE) AS Datum, MAX(TagDate) AS TidPunkt
                          FROM            Talk2M.FlatTag WITH (NOLOCK)
                          WHERE        (Name IN ('TankConductivity')) AND (TagDate >= CAST(DATEADD(dd, - 6, CAST(GETDATE() AS DATE)) AS VARCHAR(50))) AND (TagDate < CAST(CAST(GETDATE() AS DATE) AS VARCHAR(50)))
                          GROUP BY EwonId, Name, CAST(TagDate AS DATE)), Y AS
    (SELECT        EwonId, Name, CAST(TagDate AS DATE) AS Datum, MAX(TagDate) AS TidPunkt
      FROM            Talk2M.FlatTag AS FlatTag_1 WITH (NOLOCK)
      WHERE        (Name IN ('TotalPermeateVolume')) AND (TagDate >= CAST(DATEADD(dd, - 6, CAST(GETDATE() AS DATE)) AS VARCHAR(50))) AND (TagDate < CAST(CAST(GETDATE() AS DATE) AS VARCHAR(50)))
      GROUP BY EwonId, Name, CAST(TagDate AS DATE))
    SELECT        FE.EwonName, CAST(Con.TidPunkt AS DATE) AS TidPunkt, Con.TankConductivity, Vol.TotalPermeateVolume, CASE WHEN CAST(REPLACE(Con.TankConductivity, ',', '.') AS FLOAT) > 2.5 THEN 'YES' ELSE '' END AS Alarm
     FROM            (SELECT        EwonId
                               FROM            X AS X_2
                               UNION
                               SELECT        EwonId
                               FROM            Y AS Y_2) AS DistRow INNER JOIN
                                  (SELECT DISTINCT EwonId, EwonName
                                    FROM            Talk2M.FlatEwon WITH (NOLOCK)) AS FE ON DistRow.EwonId = FE.EwonId LEFT OUTER JOIN
                                  (SELECT        X_1.EwonId, X_1.TidPunkt, FT.Value AS TankConductivity
                                    FROM            Talk2M.FlatTag AS FT INNER JOIN
                                                              X AS X_1 ON FT.EwonId = X_1.EwonId AND X_1.Name = FT.Name AND FT.TagDate = X_1.TidPunkt) AS Con ON DistRow.EwonId = Con.EwonId LEFT OUTER JOIN
                                  (SELECT        Y_1.EwonId, Y_1.TidPunkt, FT.Value AS TotalPermeateVolume
                                    FROM            Talk2M.FlatTag AS FT INNER JOIN
                                                              Y AS Y_1 ON FT.EwonId = Y_1.EwonId AND Y_1.Name = FT.Name AND FT.TagDate = Y_1.TidPunkt) AS Vol ON DistRow.EwonId = Vol.EwonId

It gives me the last 6 days of data. However each ewonid has several data each day and i am only interested in the last data for that day so i only get 1 record for each day, and this can be the first or the last data, it do not matter. How can i accomplish this? 

all the best 

Answer 1

User753101303 posted

Hi,

For now it seems you are trying to do too much at the same level ie you group on the date which is a good start but then also by id (which I assume unique) and name tio include that in the fields list but loosing sight of your actual need.

Assuming EwordId is an identity column and you want the lastest inserted lne for each day I would start with something such as :

SELECT MAX(EwonId) FROM Tallk2
GROUP BY CAST(TagDate AS DATE)

Then don't change this query but reuse it with IN or a JOIN to get the full details for the rows you want.

Answer 2

User1238961430 posted

Sorry to say but that was above my skill set. Im a novice. should i do this in a new view? 

Answer 3

User753101303 posted

For example something such as:

CREATE TABLE MyTable(Id INT IDENTITY,Name VARCHAR(10),TagDate SMALLDATETIME)
GO
INSERT INTO MyTable(Name,TagDate) VALUES ('A','20200101 8:00')
INSERT INTO MyTable(Name,TagDate) VALUES ('B','20200101 9:00')
INSERT INTO MyTable(Name,TagDate) VALUES ('C','20200201 8:00')
INSERT INTO MyTable(Name,TagDate) VALUES ('D','20200301 8:00')
INSERT INTO MyTable(Name,TagDate) VALUES ('E','20200401 8:00')
INSERT INTO MyTable(Name,TagDate) VALUES ('F','20200401 9:00')

SELECT MyTable.* FROM
(
	SELECT MAX(Id) AS Id
	FROM MyTable
	GROUP BY CAST(TagDate AS DATE)
) g JOIN MyTable ON MyTable.Id=g.Id

g is to select the last id for each day and then it is joined with the same table (without grouping by the id or name) to select those rows ie each part of the query have it own purpose.

For now it seems yyou tried to stuff both steps in the same query and you end up grouping on other column as well to be able to select them but it destroys the purpose of grouping on each day only.