My calendar year runs from 07-01-(of one year) to 06-30-(of the next year).

My SQLITE DB has a Timestamp column and it's data type is datetime and stores the timestamp as 2023-09-01 00:00:00.

What I'm trying to do is get the MAX date in my fiscal years of the latest snowfall of the season over all years in the db.

I've been trying many variations of the below query. This query says it runs with no mistakes and returns "null" values. I'm using SQLITE DB Browser to write and test the query.

SELECT Timestamp, MAX(strftime('%m-%d-%Y', Timestamp)) AS lastDate,
snowDepth AS lastDepth FROM DiaryData
WHERE lastDepth <> 0 BETWEEN strftime('%Y-%m-%d', Timestamp,'start of year', ' 7 months')
AND strftime('%Y-%m-%d', Timestamp, 'start of year', ' 1 year', ' 7 months', '- 1 day')
ORDER BY lastDate LIMIT 1 

and this is what's in my test database:

Timestamp          snowFalling  snowLaying  snowDepth
2021-11-10 00:00:00     0            0         7.2
2022-09-15 00:00:00     0            0         9.5
2022-12-01 00:00:00     1            0         2.15
2022-10-13 00:00:00     1            0         0.0
2022-05-19 00:00:00     0            0         8.82
2023-01-11 00:00:00     0            0         3.77

If it's running properly I should expect:

What am I missing or is this not possible in SQLITE? Any help would be appreciative.

CodePudding user response：

Use aggregation by fiscal year utilizing SQLite's feature of bare columns:

SELECT Timestamp, 
       strftime('%m-%d-%Y', MAX(Timestamp)) AS lastDate, 
       snowDepth AS lastDepth 
FROM DiaryData
WHERE snowDepth <> 0
GROUP BY strftime('%Y', Timestamp, ' 6 months');

See the demo.

CodePudding user response：

You can use the ROW_NUMBER window function to address this problem, yet need to apply a subtle tweak. In order to account for fiscal years, you can partition on the year for timestamps slided 6 months further. In this way, ranges like [2021-01-01, 2021-12-31] will instead be slided to [2021-06-01, 2022-05-31].

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER(
                  PARTITION BY STRFTIME('%Y', DATE(Timestamp_, ' 6 months')) 
                  ORDER     BY Timestamp_ DESC           ) AS rn
    FROM tab
)
SELECT Timestamp_, 
       STRFTIME('%d-%m-%Y', Timestamp_) AS lastDate,
       snowDepth                        AS lastDepth
FROM cte
WHERE rn = 1 

Check the demo here.

CodePudding user response：

I'd get season for each record first, snowfall date relative to record's season start date after this, and largest snowfall date relative to record's season start date finally:

with
  data as (
    select
      *
    , case
        when cast(strftime('%m', "Timestamp") as int) <= 7
          then strftime('%Y-%m-%d', "Timestamp", 'start of year', '-1 year', ' 6 months')
        else strftime('%Y-%m-%d', "Timestamp", 'start of year', ' 6 months')
      end as "Season start date"
    from DiaryData

    where 1==1
    and "snowDepth" <> 0.0
  )

, data2 as (
    select
      *
    , julianday("Timestamp") - julianday("Season start date")
      as "Showfall date relative to season start date"
    from data
  )

, data3 as (
    select
      "Timestamp"
    , "snowFalling"
    , "snowLaying"
    , "snowDepth"
    from data2

    group by null
    having max("Showfall date relative to season start date")
  )
select
  *
from data3

demo