SQL Query for Last Month

Shailesh1

Hi There,

 

I have downloaded the report of Outage Duration Last month from content excahnge. When this report is run it gives the outage duration for the last 30 days and not exactly Last Month/Previous Month. I am not that good with writing sql queries and have check the date period parameter under various existing report with the last month tag...however it seems to different in each. So couldn't find a well defined repeated pattern in any of the reports. Hence posting for further advise. Below is the exisitng query of the Outage Duration Last month report....please can someone give me a query for Last/Previous Month ->

SELECT
    StartTime.EventTime,
    Nodes.Caption,
    Nodes.Location,
    StartTime.Message,
    DATEDIFF(Mi, StartTime.EventTime,
    (SELECT TOP 1
        EventTime
        FROM Events AS Endtime
        WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
            AND EndTime.NetObjectType = 'N'
            AND EndTime.NetworkNode = StartTime.NetworkNode
        ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes

FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND
eventtime between dateadd(month, -1, getdate()) and getdate()
ORDER BY Nodes.Caption ASC

Thanks

qle

Try this. Change the second to last line from this

eventtime between dateadd(month, -1, getdate()) and getdate()

to this

datepart(yy,eventtime) = datepart(yy,dateadd(m,-1,getdate())) and datepart(m,eventtime) = datepart(m,dateadd(m,-1,getdate()))

Let us know if this is what you're looking for.