CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformdbt
John Paul
John Paul

Reputation: 13

Previous month record calculation for a Year

I need to write a query in such a way that I need to pull both current month data and previous month data.

I have a table customers

Customer year mon spent
A 2021 01 200$
A 2020 12 100$

Similarly I have records for different customers.

since I don't have date column I will pass the substr(2023-01-31,1,4) to get the data

For one month: I can get the data using this query

select * 
from customers
Where year = substr(2021-01-31,1,4) 
    and month=substr(2021-01-31,6,2)
FULL OUTER JOIN
select * 
from customers
Where year = substr(add_months(2021-01-31,-1),1,4) 
    and month=substr(add_months(2021-01-31,-1),6,2)
Cust year month cspent pspent
A 2021 01 200$ 100$

My requirement is I need to pull the data for an entire year So if I pass the year alone in the where condition

I get only the current month data for customer.

Cust year month cspent pspent
A 2021 01 200$ null
B 2021 01 100$ null

How can I solve this problem in snowflake query processor.

Edit: can i calculate only the previous months data for a year from jan to december in a single query i used lag function as well but its not giving me the expected result.

Upvotes: 0

Views: 164

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26043

There are a couple of problems, the first is to define "for the year" do you mean the last 12 months, or the current calendar year?

so we can current a list of the 12 months of the last prior months (aka including the current month) or the last 12 months including the current month, or the current years worth of 12 months:

select column1 as rn
    ,CURRENT_DATE
    ,date_trunc('month', CURRENT_DATE) as cur_mon
    ,dateadd(month, -rn, cur_mon) as prior_months
    ,dateadd(month, -rn+1, cur_mon) as cur_months
    ,dateadd(month, rn-1, date_trunc('year', CURRENT_DATE)) as cur_year_months
from values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)

enter image description here

so now we have "12 months" the prior month, is just a DATEADD('month',-1, input_date) as output_date` away:

looking harder at your example, I agree with zip's guess you want to pass a year and get the full year.

if you take customer and join to the dates, then join to customer again, you get just the months that have data (per customer):

with customers(customer, year, mon, spent) as (
    select * from values
    ('A',2021, 01, 200),
    ('A',2020, 12, 100)
), years_worth_of_months as (
    select     
        dateadd(month, +column1-2, date_trunc('year', '2021-01-31'::date)) as prior_month
        ,dateadd(month, +column1-1, date_trunc('year', '2021-01-31'::date)) as cur_month
        ,year(cur_month) as c_year
        ,month(cur_month) as c_month 
        ,year(prior_month) as p_year
        ,month(prior_month) as p_month
    from values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
)
select a.*
    ,b.spent as pspent
from customers as a
join years_worth_of_months as y  
    on y.c_year = a.year 
        and y.c_month = a.mon
left join customers as b
    on a.customer = b.customer
        and y.p_year = b.year
        and y.p_month = b.mon

where-as if you want a row for the each customer for each month, that while require a cross join of customers (filtered to the year or not) and the time window.

then there is the option of using a lag function to get the prior data in the time window, but that also requires a larger filter to get "the prior month" and then a post filter via QUALIFY

Upvotes: 0

zip
zip

Reputation: 4061

Try this:

    WITH _Months AS (
  SELECT 2023 AS target_year,
         ARRAY_CONSTRUCT(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) AS months
),

TargetData AS (
  SELECT cust.Customer, cust.Year, cust.Month, cust.Spent AS CurrMonth,
    P.Spent AS PreviousMonth FROM Customers C LEFT JOIN Customers P
  ON
    cust.Customer = P.Customer
    AND cust.Year = P.Year
    AND cust.Month = P.Month + 1
    AND cust.Year = (SELECT target_year FROM _Months)
    AND cust.Month IN (SELECT * FROM _Months.months)
)

SELECT
  Customer, Year, Month,
  NVL(CurrMonth, 0) AS CurrMonth,
  NVL(PreviousMonth, 0) AS PreviousMonth
FROM TargetData WHERE Year = (SELECT target_year FROM _Months)
ORDER BY Customer, Year, Month;

Upvotes: 0

Related Questions