How to use “Partition By” or “Max”? for SQL Server

Question

I have a very similar question to what was asked here for an Oracle DB (but I have an SQL Server 2012). The example I have used as a starter is based on the answer given here.

What I have is these four columns:

[L2] ,[DateofReporting],[L3] and [ServerName] more or less at a random day data is added to that table, but if it is, it will always be the same [L2],[DateofReporting],[L3] but with a different [ServerName]

Now I want to extract that data to give me all the servers [ServerName], which were added last for all months and years grouped by L2, L3 and the related month and year (coming from [DateofReporting]) .

SELECT [ID],[L2],[DateofReporting],[L3],[ServerName]
      FROM (
            select *, 
            max([DateofReporting]) 
                OVER (PARTITION BY YEAR([DateofReporting]), 
                      Month([DateofReporting])) maxdate
            from [EADATAGOV].[Governance].[ToDos] 
        )max_date
      where [DateofReporting] = maxdate

The problem I am phasing is, that the data is incomplete and their is obviously a bug in my statement. By now I don't see the tree for the forest, could you please help me clean up that SQL statement, or if there is a smarter way of doing it, I am open to suggestions.

I was thinking about utilizing ROW_NUMBER() to mark the relevant entries and than do a select on them, but I have never worked with that before. thx Jan

example of output:

ID      L2   DateofReporting     L3                  name
18214   Summer  2017-09-20       cloud              BINHAS01105 <-- 
18215   Summer  2017-09-20       lightbulb          BINHAS60276 <-- 
18217   Summer  2017-09-20       lightbulb          CNAHAS62003 <-- 
15297   Summer  2017-09-15       cloud              CINHAS01105
15298   Summer  2017-09-15       boat               CINHAS60277  
15300   Summer  2017-09-15       lightbulb          DNAHAS62003
10512   Summer  2017-08-20       lightbulb          DNAHAS62003 <--

the ones pointed out, are the ones I would expect to see in the result. As eg. boat does not have a newer entry than that of the 09-15.

new approach:

Select [L2],
MAX([DateofReporting]) LDateOfTest
from [EADATAGOV].[Governance].[ToDos]
group by [L2], YEAR([DateofReporting]), Month([DateofReporting]) ,[DType] 
having DType= 'test'
order by LDateOfTest desc, L2 desc

This provides me (correctly) the latest date for each L2 for every month. Now in theory I should be able to use another query on the very same table where L2 and the LDateOfTest match. My idea of a subselect does not work, as I can only pass one criteria, not two. But I don't know how that works, can you help me with the join(?) ?

Sample output for an input would help to get more meaningful responses. — Radim Bača, Sep 21 '17 at 06:21
I have added an example of an output to my original question. — Janbro, Sep 21 '17 at 06:28
include the sample data and then the result you got vs the result expected. — xGeo, Sep 21 '17 at 06:51

etsa · Answer 1 · 2017-09-21T06:53:12.947

0

It's difficult understand your request, since you didn't post any sample data (input). As far as I understood, may be we can start from this query. Can you try it and pls let me know?

SELECT ID
    ,L2
    ,DATEOFREPORTING
    ,L3
    ,SERVERNAME
FROM(
    SELECT ID
        ,L2
        ,DATEOFREPORTING
        ,L3
        ,SERVERNAME
        ,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY DATEOFREPORTING DESC) RN
    FROM TODOS
    ) A
WHERE RN = 1;

edited Sep 21 '17 at 06:53

answered Sep 21 '17 at 06:44

etsa

5,020
1
7
18

Added DESC in ORDER BY – etsa Sep 21 '17 at 06:53

score 0 · Answer 2 · answered Sep 21 '17 at 07:11

0

Select [ID],[L2],[DateofReporting],[L3],[ServerName] From( Select [ID],[L2],[DateofReporting],[L3],[ServerName], Row_NUmber() Over(Partition BY [ServerName],[L3] Order BY [DateofReporting] Desc) as Row_Num from [EADATAGOV].[Governance].[ToDos] ) Temp Where Row_Num = 1

answered Sep 21 '17 at 07:11

Sumeet

61
1
2

thx, this brings it real close, but it lists me also the CINHAS60277 servername. That is most likely for me being inprecise in what I am trying to achive. I want to extract all those servers which were entered at the last date, regardless whether they were entered at a previous day in that month.
So in other words, show me for each L2, L3 those servernames which were entered at the last date in that month for that L2, L3 combination. And show that for all months and years before. – Janbro Sep 21 '17 at 07:34

Janbro · Accepted Answer · 2017-09-22T06:18:42.813

That's the solution I have come up with after some hours of struggling. I had to completely reset my approach.

IF OBJECT_ID('tempdb..#tmp_table') IS NOT NULL DROP TABLE #tmp_table

Select [L2],
MAX([DateofReporting]) LDateOfTest
into  #tmp_table --(L2t, LDateOfTest)
from [EADATAGOV].[Governance].[ToDos]
group by [L2], YEAR([DateofReporting]), Month([DateofReporting]) ,[DType] 
having DType = 'test'
order by LDateOfTest desc, L2 desc

SELECT [ID]
      ,[EADATAGOV].[Governance].[ToDos].[L2] L2f
      ,YEAR([DateofReporting]) YoT, Month([DateofReporting]) MoT
      ,[L3]
      ,[ServerName]
  FROM [EADATAGOV].[Governance].[ToDos] 
  right join #tmp_table tt on tt.L2 = [EADATAGOV].[Governance].[ToDos].[L2] and   tt.LDateOfTest = ToDos.DateofReporting
  where DType = 'test' 
  order by DateofReporting desc, L3 asc
  DROP TABLE #tmp_table

It probably isn't the prettiest solution, but it get's me the results I was hoping for.

How to use “Partition By” or “Max”? for SQL Server

3 Answers3