Max data based on month
-
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!
-
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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.
-
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - 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)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply