Inconsistency in QUALIFY clause?

helli
Contributor

We have a table with the relation of users and teams with the duration.

-- given data
|USR |TEAM |BEG_DATE |END_DATE |
|daniel|team_A|2021-09-01|2021-09-30|
|daniel|team_B|2021-10-01|2021-10-31|
|daniel|team_C|2021-11-01|2021-12-31|

I want to create an additional row with a time slot from 1900-01-01 to the start of the first known association of this user to a given team.
-- desired result
|USR |TEAM|BEG_DATE |END_DATE |
|daniel|- |1900-01-01|2021-08-31|

In the SQL below I have two different QUALIFY clauses. Both aim to reduce the given data to one row. I would expect that both return the same result set. If you comment one, you get once 1 and once 3 rows.
Despite the fact that there are several other ways to solve my problem (distinct, group by, ...). What I have overlooked in my first approach?

WITH usr_to_team AS
(
select 'daniel' as usr, 'team_A' as team, date'2021-09-01' as beg_date, date'2021-09-30' as end_date UNION ALL
select 'daniel' as usr, 'team_B' as team, date'2021-10-01' as beg_date, date'2021-10-31' as end_date UNION ALL
select 'daniel' as usr, 'team_C' as team, date'2021-11-01' as beg_date, date'2021-12-31' as end_date
)
SELECT usr, '-' AS team, date'1900-01-01' AS beg_date, min(beg_date) OVER (PARTITION BY usr) - 1 AS end_date /* remove 1 day from the lowest beg_date as end_date for the "previous"*/
FROM usr_to_team
--QUALIFY beg_date = min(beg_date) OVER (PARTITION BY usr) -- three rows
QUALIFY 1 = row_number() OVER (PARTITION BY usr ORDER BY beg_date) -- one row as expected - correct but not so elegant to my mind
;

 

Kind regards
Daniel

1 ACCEPTED SOLUTION

helli
Contributor

Colleagues of me found the solution. You need to qualify the column beg_date. There is a column with that name in the source and as fixvalue in the select. See correct version below. 

One can complain that the parser does not struggle with the ambiguous name. 

WITH usr_to_team AS
(
select 'daniel' as usr, 'team_A' as team, date'2021-09-01' as beg_date, date'2021-09-30' as end_date UNION ALL
select 'daniel' as usr, 'team_B' as team, date'2021-10-01' as beg_date, date'2021-10-31' as end_date UNION ALL
select 'daniel' as usr, 'team_C' as team, date'2021-11-01' as beg_date, date'2021-12-31' as end_date
)
SELECT usr, '-' AS team, date'1900-01-01' AS beg_date, min(beg_date) OVER (PARTITION BY usr) - 1 AS end_date /* remove 1 day from the lowest beg_date as end_date for the "previous"*/
FROM usr_to_team
--QUALIFY beg_date = min(beg_date) OVER (PARTITION BY usr) -- three rows
QUALIFY usr_to_team.beg_date = min(usr_to_team.beg_date) OVER (PARTITION BY usr) -- with the qualified name it works
--QUALIFY 1 = row_number() OVER (PARTITION BY usr ORDER BY beg_date) -- one row as expected - correct but not so elegant to my mind
;

View solution in original post

1 REPLY 1

helli
Contributor

Colleagues of me found the solution. You need to qualify the column beg_date. There is a column with that name in the source and as fixvalue in the select. See correct version below. 

One can complain that the parser does not struggle with the ambiguous name. 

WITH usr_to_team AS
(
select 'daniel' as usr, 'team_A' as team, date'2021-09-01' as beg_date, date'2021-09-30' as end_date UNION ALL
select 'daniel' as usr, 'team_B' as team, date'2021-10-01' as beg_date, date'2021-10-31' as end_date UNION ALL
select 'daniel' as usr, 'team_C' as team, date'2021-11-01' as beg_date, date'2021-12-31' as end_date
)
SELECT usr, '-' AS team, date'1900-01-01' AS beg_date, min(beg_date) OVER (PARTITION BY usr) - 1 AS end_date /* remove 1 day from the lowest beg_date as end_date for the "previous"*/
FROM usr_to_team
--QUALIFY beg_date = min(beg_date) OVER (PARTITION BY usr) -- three rows
QUALIFY usr_to_team.beg_date = min(usr_to_team.beg_date) OVER (PARTITION BY usr) -- with the qualified name it works
--QUALIFY 1 = row_number() OVER (PARTITION BY usr ORDER BY beg_date) -- one row as expected - correct but not so elegant to my mind
;

View solution in original post