The country table with area codes and the collected population number.
I want to return the population registered on the month's last day. Some areas have the record till the last day of the month, and certain areas have no records on all days of the month, so in that case, I have to consider the last recorded date as the end of the month example, 16-09-2022. I don't know how to write the case statement to pick areas that have the end of the month and the date for areas that have the last registration date of the month. If area 06 has population 120 registered on 30-09-2022 then DATE will be 30-09-2022 for Aread 06 and if area 05 has population 30 registered on 18-09-2022 then DATE will be 18-09-2022 for AREA 05
SELECT
[Date]
,Country
,[Area Code]
,[From date]
,CASE WHEN [End date] = '1900-01-01' THEN Null ELSE [End date] END AS [End date]
,[Population]
,[Water Temperature (°C)]
FROM [dbo].[Population]
WHERE [Date] IN (
SELECT
CASE WHEN [Date]= EOMONTH([Date]) THEN EOMONTH([Date]) ELSE MAX([Date]) END AS [DATE]
FROM [dbo].[Population])
GROUP BY [Date]
Order BY [Date]
Data in the population table
INSERT INTO #Population (Date, Country, AreaCode, PopulationNo,[Temperature]) VALUES ('2022-06-08','B',6,118102,8.7), ('2022-06-09','B',6,118067,8.7), ('2022-06-10','B',6,118027,8.3), ('2022-06-11','B',6,117992,9.1), ('2022-06-12','B',6,117940,8.9), ('2022-06-13','B',6,117924,9.1), ('2022-06-14','B',6,117849,9.9), ('2022-06-15','B',6,117799,9.5), ('2022-06-16','B',6,117780,9.5), ('2022-06-17','B',6,117742,9.7), ('2022-06-18','B',6,117740,9.8), ('2022-06-19','B',6,117732,9.2), ('2022-06-20','B',6,117676,9.8), ('2022-06-21','B',6,117556,10.5), ('2022-06-22','B',6,117552,10.3), ('2022-06-23','B',6,117524,10.4), ('2022-06-24','B',6,117496,10.4), ('2022-06-25','B',6,117471,10.3), ('2022-06-26','B',6,117466,10.5), ('2022-06-27','B',6,117461,10.8), ('2022-06-28','B',6,117461,10.7), ('2022-06-29','B',6,117459,11), ('2022-06-30','B',6,117459,11), ('2022-07-01','B',6,117446,11.4), ('2022-07-02','B',6,117445,11.1), ('2022-07-03','B',6,117442,10.9), ('2022-07-04','B',6,117438,12.4), ('2022-07-05','B',6,117438,12.1), ('2022-07-06','B',6,117426,12.4), ('2022-07-07','B',6,117414,12.9), ('2022-07-08','B',6,117407,12.8), ('2022-07-09','B',6,117404,12.6), ('2022-07-10','B',6,117403,12.2), ('2022-07-11','B',6,117402,12.4), ('2022-07-12','B',6,117401,12.3), ('2022-07-13','B',6,117389,13.5), ('2022-07-14','B',6,117377,12.7), ('2022-07-15','B',6,117366,12.9), ('2022-07-16','B',6,117351,13), ('2022-07-17','B',6,117331,13), ('2022-07-18','B',6,117309,13.2), ('2022-07-19','B',6,117279,13), ('2022-07-20','B',6,117259,12.9), ('2022-07-21','B',6,117243,13), ('2022-07-22','B',6,117192,13), ('2022-07-23','B',6,117129,12.8), ('2022-07-24','B',6,117093,12.8), ('2022-07-25','B',6,117078,12.8), ('2022-07-26','B',6,117065,12.9), ('2022-07-27','B',6,116871,13), ('2022-07-28','B',6,116816,12.8), ('2022-07-29','B',6,116785,12.8), ('2022-07-30','B',6,116737,12.9), ('2022-07-31','B',6,116157,13.9), ('2022-08-01','B',6,116042,13.5), ('2022-08-02','B',6,115982,13.1), ('2022-08-03','B',6,115981,13.7), ('2022-08-04','B',6,115975,13.7), ('2022-08-05','B',6,115934,13.5), ('2022-08-06','B',6,115897,13.3), ('2022-08-07','B',6,115867,13.3), ('2022-08-08','B',6,115822,13.1), ('2022-08-09','B',6,115748,13.4), ('2022-08-10','B',6,115685,13.3), ('2022-08-11','B',6,115611,13.1), ('2022-08-12','B',6,115540,12.9), ('2022-08-13','B',6,115456,12.9), ('2022-08-14','B',6,115403,12.9), ('2022-08-15','B',6,115335,13), ('2022-08-16','B',6,115265,13.1), ('2022-08-17','B',6,115120,12.9), ('2022-08-18','B',6,114997,12.9), ('2022-08-19','B',6,114939,12.8), ('2022-08-20','B',6,114830,12.8), ('2022-08-21','B',6,114752,12.9), ('2022-08-22','B',6,114637,14), ('2022-08-23','B',6,114307,13.4), ('2022-08-24','B',6,114227,13.2), ('2022-08-25','B',6,114109,13.2), ('2022-08-26','B',6,114043,13.3), ('2022-08-27','B',6,113953,13.6), ('2022-08-28','B',6,113886,13.4), ('2022-08-29','B',6,113841,13.3), ('2022-08-30','B',6,113820,13.3), ('2022-08-31','B',6,113745,13.3), ('2022-09-01','B',6,113725,13.3), ('2022-09-02','B',6,113665,13), ('2022-09-03','B',6,113613,13.3), ('2022-09-04','B',6,113536,13.3), ('2022-09-05','B',6,113518,13.3), ('2022-09-06','B',6,113518,13.2), ('2022-09-07','B',6,113475,13.2), ('2022-09-08','B',6,113435,13.1), ('2022-09-09','B',6,113378,13.1), ('2022-09-10','B',6,113340,13.1), ('2022-09-11','B',6,113289,13), ('2022-09-12','B',6,113204,12.8), ('2022-09-13','B',6,113144,12.7), ('2022-09-14','B',6,113097,12.8), ('2022-09-15','B',6,113097,12.7), ('2022-09-16','B',5,112970,12.7), ('2022-09-16','B',6,0,0), ('2022-09-17','B',5,112353,12.6), ('2022-09-18','B',5,112034,12.7), ('2022-09-19','B',5,112024,12.4), ('2022-09-20','B',5,112007,12), ('2022-09-21','B',5,111988,12), ('2022-09-22','B',5,111980,11.9), ('2022-09-23','B',1,49950,11.9), ('2022-09-23','B',5,111970,11.9), ('2022-09-24','B',1,49936,11.9), ('2022-09-24','B',5,111963,11.9), ('2022-09-25','B',1,49930,11.9), ('2022-09-25','B',5,111938,11.9), ('2022-09-26','B',1,49924,11.8), ('2022-09-26','B',5,111901,11.8), ('2022-09-27','B',1,49919,11.6), ('2022-09-27','B',5,111897,11.6), ('2022-09-28','B',1,49918,11.4), ('2022-09-28','B',5,111882,11.4), ('2022-09-29','B',1,49918,11.4), ('2022-09-29','B',5,111878,11.4), ('2022-09-30','B',1,49916,11.3), ('2022-09-30','B',5,111858,11.3);
If I'm understanding you correctly the crux of what you really want is the population on the MAX(Date) for any given Country, Month and Year.
With the query you've written what could happen is you'll get more than 1 day per country.
Imagine Country A has data for 30-09-2022 AND 29-09-2022 whilst country B only has data on 29-09-2022. Now the list provided by your subquery would contain 30-09-2022 and 29-09-2022 which won't help you reduce your data set for country A.
I've set up a simplified table & populated it like this:
CREATE TABLE #Population ([Date] DATE, Country VARCHAR(1), [Population] INT);
INSERT INTO #Population (Date, Country, Population)
VALUES ('2022-09-30', 'A', 200),
('2022-09-29', 'A', 201),
('2022-08-31', 'A', 100),
('2022-08-30', 'A', 101),
('2022-09-29', 'B', 300),
('2022-09-28', 'B', 299),
('2022-08-30', 'B', 200),
('2022-08-29', 'B', 199);
This now gives me raw data for 2 countries, for 2 different months where the date isn't necessarily EOMONTH.
I then can write a CTE, or temp table, to get the appropriate dates per country, month & year using something like this:
;WITH CTEDates
AS (
SELECT MAX(P.Date) AS Date,
YEAR(P.Date) AS DateYear,
MONTH(P.Date) AS DateMonth,
P.Country
FROM #Population AS P
GROUP BY YEAR(P.Date),
MONTH(P.Date),
P.Country
)
Selecting all from this gives us a dataset something like this:
Date DateYear DateMonth Country
2022-08-31 2022 8 A
2022-08-30 2022 8 B
2022-09-30 2022 9 A
2022-09-29 2022 9 B
This can then be joined back to your population table to add on the Population column a bit like this:
SELECT CTEDates.Date, CTEDates.Country, P.Population
FROM CTEDates
INNER JOIN #Population AS P ON P.Country = CTEDates.Country AND P.Date = CTEDates.Date;