Max data based on month

Question

Max data based on month

LearnSQL

SSC-Addicted

Points: 407
More actions
June 2, 2022 at 6:01 am

#4043041

Hi All
I have a requirement like below ,need to take max of the week data. Ref table + data table ---> Out data table.
How can we achieve this.

Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

Here is the tricky part done. All you need to do is JOIN from the final query to your Ref table to get the 'Month'.

DROP TABLE IF EXISTS #DataTab;

CREATE TABLE #DataTab
(
    EmpNo INT NOT NULL
   ,WeekNo CHAR(3) NOT NULL
   ,Rnge SMALLINT NOT NULL
);

INSERT #DataTab
(
    EmpNo
   ,WeekNo
   ,Rnge
)
VALUES
(111, 'W1', 12)
,(111, 'W4', 17)
,(210, 'W9', 15)
,(210, 'W13', 26);

WITH ordered
AS (SELECT dt.EmpNo
          ,dt.WeekNo
          ,dt.Rnge
          ,rn = ROW_NUMBER() OVER (PARTITION BY dt.EmpNo
                                   ORDER BY CAST(STUFF(dt.WeekNo, 1, 1, '') AS INT) DESC
                                  )
    FROM #DataTab dt)
SELECT ordered.EmpNo
      ,ordered.WeekNo
      ,ordered.Rnge
FROM ordered
WHERE ordered.rn = 1;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

LearnSQL SSC-Addicted Points: 407 More actions · Answer 2

Thanks Phils!

But i really forgot to add an important point here what if the same emp for month 1 and month 3. Here 210 in M1 and M3.

Range

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

Here's a revised version. Note that the CREATE TABLE and INSERTs should usually be done by the person posting the question – I'm helping you out because because you're new here. In future, please present your test data like that (in a way which can be pasted directly into SSMS by other users).

DROP TABLE IF EXISTS #DataTab;

CREATE TABLE #DataTab
(
    EmpNo INT NOT NULL
   ,WeekNo CHAR(3) NOT NULL
   ,Rnge SMALLINT NOT NULL
);

INSERT #DataTab
(
    EmpNo
   ,WeekNo
   ,Rnge
)
VALUES
(111, 'W1', 12)
,(111, 'W4', 17)
,(210, 'W1', 22)
,(210, 'W4', 55)
,(210, 'W9', 15)
,(210, 'W13', 26);

DROP TABLE IF EXISTS #RefTab;

CREATE TABLE #RefTab
(
    WeekNo CHAR(3) NOT NULL PRIMARY KEY CLUSTERED
   ,Mth CHAR(2) NOT NULL
);

INSERT #RefTab
(
    WeekNo
   ,Mth
)
VALUES
('W1', 'M1')
,('W2', 'M1')
,('W3', 'M1')
,('W4', 'M1')
,('W5', 'M2')
,('W6', 'M2')
,('W7', 'M2')
,('W8', 'M2')
,('W9', 'M3')
,('W10', 'M3')
,('W11', 'M3')
,('W12', 'M3')
,('W13', 'M3');

WITH ordered
AS (SELECT dt.EmpNo
          ,dt.Rnge
          ,rt.Mth
          ,rn = ROW_NUMBER() OVER (PARTITION BY dt.EmpNo
                                               ,rt.Mth
                                   ORDER BY CAST(STUFF(dt.WeekNo, 1, 1, '') AS INT) DESC
                                  )
    FROM #DataTab dt
        JOIN #RefTab rt
            ON rt.WeekNo = dt.WeekNo)
SELECT ordered.EmpNo
      ,ordered.Mth
      ,ordered.Rnge
FROM ordered
WHERE ordered.rn = 1;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

LearnSQL wrote:

Thanks Phils! But i really forgot to add an important point here what if the same emp for month 1 and month 3. Here 210 in M1 and M3.

To Phil's good point, put yourself in our position. If we want to test our code, we can't copy test data from a graphic. Please see the article at the first link in my signature line below to help us help you and for you to get good answers more quickly. Phil has also provided a good alternative that use the VALUES Table Value Constructor.

And, yes... the CREATE TABLE statements are essential because they answer so many questions that never need to be asked.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)