SQL Server Add Opening Monthly value, Closing Monthly value and Average of these two values as three separate columns

2fjabf4q  于 2023-04-04  发布在  其他
关注(0)|答案(2)|浏览(91)

I have a single table that looks like this:
| DATE | COUNTRY | CUSTOMER_TYPE |
| ------------ | ------------ | ------------ |
| 01/03/23 | Germany | 1 |
| 01/03/23 | Germany | 1 |
| 02/03/23 | France | 3 |
| 05/03/23 | UK | 1 |
| 10/03/23 | Spain | 5 |
| 31/03/23 | Germany | 2 |

My requirement is as follows:

  1. Group the table by month + year, country, customer type
  2. Add a column [OPENING_VALUE] counting all rows that have a date of the first of the month (grouped by the month, country and customer type)
  3. Add a column [CLOSING_VALUE] counting all rows that have a date of the last of the month (grouped by the month, country and customer type)
  4. Add a column [AVERAGE_VALUE] that averages the [OPENING_VALUE] and [CLOSING_VALUE] for each month.

It should look something like this:
| DATE | COUNTRY | CUSTOMER_TYPE | OPENING_VALUE | CLOSING_VALUE | AVERAGE_VALUE |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 03/23 | Germany | 1 | 2 | 1 | 1.5 |
| 03/23 | France | 1 | 17 | 25 | 21 |
| 03/23 | UK | 1 | 100 | 150 | 125 |
| 03/23 | Spain | 1 | 5 | 10 | 7.5 |
| 04/23 | Germany | 2 | 15 | 20 | 17.5 |
| 04/23 | France | 2 | 50 | 100 | 75 |

...

Thanks!

ztigrdn8

ztigrdn81#

Here is how you find the "first" and "last" day of the month (assuming these are the first and last referenced days since day 1 would always be the first day but isn't in your example data.)

WITH numbered AS (
  SELECT *,
      ROW_NUMBER() OVER (PARTITION BY Customer, YEAR(date), MONTH(date) ORDER BY DAY(date) ASC as RN_FIRST,
      ROW_NUMBER() OVER (PARTITION BY Customer, YEAR(date), MONTH(date) ORDER BY DAY(date) DESC as RN_LAST
  FROM sometableyoudidntname
)
SELECT *, F.Date as FIRST_DATE, L.Date as LAST_DATE
FROM sometableyoudidntname X
LEFT JOIN numbered F ON F.Customer = X.Customer AND F.date = X.date AND RN_FIRST = 1
LEFT JOIN numbered L ON L.Customer = X.Customer AND L.date = X.date AND RN_LAST = 1

Now that you know the date of the first and the last items you should be able to do the group by

NOTE: It is not exactly clear to me if I am using the right partition and joins. This depends on what drives the uniqueness of your data model.

qkf9rpyu

qkf9rpyu2#

It's really helpful if you provide DDL/DML that's easy to reproduce when you ask questions like this. Your result set doesn't seem to match the example data you provided.

Consider:

DECLARE @Table TABLE (Date DATE, Country NVARCHAR(50), Customer_type INT);
INSERT INTO @Table (Date, Country, Customer_type) VALUES 
('2023-03-01', 'Germany', 1),
('2023-03-01', 'Germany', 1),
('2023-03-02', 'France', 3),
('2023-03-05', 'UK', 1),
('2023-03-10', 'Spain', 5),
('2023-03-31', 'Germany', 2);

Using this we just need to be able to flag the first and last day values, and then aggregate on that:

SELECT RIGHT('0'+CAST(DATEPART(MONTH,Date) AS VARCHAR),2)+'/'+RIGHT(CAST(DATEPART(YEAR,Date) AS VARCHAR),2) AS Date, Country, Customer_type, COUNT(a.Opening_Value) AS Opening_Value, AVG(.0+a.Average_Value) AS Average_Value, COUNT(a.Closing_Value) AS Closing_Value
  FROM (
SELECT Date, Country, Customer_type, 
       CASE WHEN DATEPART(DAY,Date) = 1 THEN 1 END AS Opening_Value, 
       1 AS Average_Value, 
       CASE WHEN DATEPART(DAY,DATEADD(DAY,1,Date)) = 1 THEN 1 END AS Closing_Value
  FROM @Table
       ) a
 GROUP BY DATEPART(MONTH,Date), DATEPART(YEAR,Date), Country, Customer_type
DateCountryCustomer_typeOpening_ValueAverage_ValueClosing_Value
03/23France301.0000000
03/23Germany121.0000000
03/23Germany201.0000001
03/23Spain501.0000000
03/23UK101.0000000

As a side note, when using dates it's a good idea to use the ISO format of yyyy-mm-dd as it removes any ambiguity.

相关问题