CODEWITHSUNDEEP
sqlsql-serversql-server-2012
RussH
RussH

Reputation: 25

Retrieve last record from current month and previous month


I need to get the last recorded value from the current month and the previous month. There are roughly 4,600 records per month.

The following is the code I have tried, however it returns '0' for the two months and not the value:

SELECT a.LogPoint as [Meter]
,max(CASE WHEN c.DateTimeStamp = dateadd(MM,-1,getdate()) THEN c.FloatVALUE ELSE 0 END) as [Total LAST Month]
,max(CASE WHEN c.DateTimeStamp = getdate() THEN c.FloatVALUE ELSE 0 END) as [Total This Month]
FROM 
   SWR.dbo.LoggedEntities a
  ,SWR.dbo.TrendLogRelation b
  ,SWR.dbo.LogTimeValues c
WHERE
      a.GUID = b .GUID
  AND a.Type LIKE 'trend.ETLog'
  AND a.LogPoint = 'WsumOut_Trnd'
  AND b.EntityID = c.ParentID
GROUP BY a.LogPoint

Any help would be greatly appreciated.
Cheers.

Upvotes: 0

Views: 114

Answers (3)

ViKiNG
ViKiNG

Reputation: 1334

I assume the LogPoint is the primary key. correct? In that case check following:

    SELECT mainA.LogPoint AS [Meter],
       lastMonth.FloatValue AS [Total LAST Month],
       thisMonth.FloatValue AS [Total This Month]
FROM SWR.dbo.LoggedEntities mainA
     CROSS APPLY
     (
       SELECT TOP 1 c.FloatVALUE
       FROM SWR.dbo.LoggedEntities a
           JOIN SWR.dbo.TrendLogRelation b ON a.GUID = b.GUID
           JOIN SWR.dbo.LogTimeValues c ON b.EntityID = c.ParentID
       WHERE a.LogPoint = mainA.LogPoint 
       ORDER BY c.DateTimeStamp DESC
     ) thisMonth
    CROSS APPLY
     (
       SELECT TOP 1 c.FloatVALUE
       FROM SWR.dbo.LoggedEntities a
           JOIN SWR.dbo.TrendLogRelation b ON a.GUID = b.GUID
           JOIN SWR.dbo.LogTimeValues c ON b.EntityID = c.ParentID
       WHERE a.LogPoint = mainA.LogPoint AND c.DateTimeStamp <= DATEADD(MM,-1,GETDATE()) 
       ORDER BY c.DateTimeStamp DESC
     ) lastMonth
WHERE a.Type LIKE 'trend.ETLog'
      AND a.LogPoint = 'WsumOut_Trnd';

Just realized that I missed the last month date check. added now. try that :)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Two important points before I start:

  1. Never use commas in the FROM clause. Always use explicit JOIN syntax.
  2. Table aliases should be abbreviations for the table.

Then, you want to use row_number():

SELECT LogPoint as [Meter],
       max(CASE WHEN seqnum = 1 AND
                     DATEDIFF(month, DateTimeStamp, getdate()) = 1
                THEN cltv.FloatVALUE
           END) as [Total LAST Month],
       max(CASE WHEN seqnum = 1 AND
                     DATEDIFF(month, DateTimeStamp, getdate()) = 0
                THEN ltv.FloatVALUE 
           END) as [Total This Month]
FROM (SELECT le.LogPoint, ltv.DateTimeStamp,
             ROW_NUMBER() OVER (PARTITION BY YEAR(DateTimeStamp), MONTH(DateTimeStamp)
                                ORDER BY DateTimeStamp DESC
                               ) as seqnum
      FROM SWR.dbo.LoggedEntities le JOIN
           SWR.dbo.TrendLogRelation tlr
           ON le.GUID = tlr.GUID JOIN
           SWR.dbo.LogTimeValues ltv
           ON ltr.EntityID = ltv.ParentID
      WHERE le.Type LIKE 'trend.ETLog' AND
            le.LogPoint = 'WsumOut_Trnd' AND
            DATEDIFF(month, ltv.DateTimeStamp, getdate()) IN (0, 1)
     ) x
WHERE seqnum = 1;

Upvotes: 0

David Betteridge
David Betteridge

Reputation: 218

getdate() includes both the time and the date, which is why you aren't getting any matches.

One option is to cast both values to dates and then do the comparison.

Upvotes: 0

Related Questions