CASE STATEMENT last date of the month or max date of the month EOMONTH and MAXDate-CodePudding

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);

CodePudding user response：

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;