08-05-2022 10:17 AM
Code is working good if data greater than target date (>) is selected :
SELECT
xyz.ID,
xyz.Gender,
xyz.geography,
xyz.code,
xyz.delivery_status,
abc.department_code
FROM v.table1 as xyz
left join y.table2 as abc
on
xyz.ID = abc.ID AND
xyz.code = abc.code
where xyz.partition_date < '2021-01-01'
group by 1,2,3,4,5,6
-- It's working when using xyz.partition_date >= 2020-01-31
@Aman Sehgal @Stephan Lawson @Darryll Petrancuri @sql-thetan
08-07-2022 06:26 AM
The fact that you get records for >= 2020-01-31 doesn't mean you should get something for < 2021-01-01 as well.
Let's say the set contains the date 2099-01-01. Then, when applying the first condition, you will see the corresponding row, but applying the second one you will get "Query returned no results" which makes sense.
08-07-2022 11:09 AM
Hi @Artem Sheiko
Thanks for your answer. The problem is data is available from 2017-01-01 to current date. And if I search for records > 2017-01-01 it will work but if I do same for < Any Date (Let' say 2022-01-01) It will give no results. "Between" any date range is also not working.
08-07-2022 09:56 PM
When comparing date fields, results can sometimes be erroneous.
From what your explanation above, I can recommend following for you:
select partition_date, count(*) from v.table1 group by partition_date having count(*)>0
select to_date(partition_date, 'yyyy-MM-dd') as partition_dt from v.table1
09-07-2022 05:15 AM
Hi @Rishabh Shankar
Hope all is well! Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help.
We'd love to hear from you.
Thanks!
Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections.
Click here to register and join today!
Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.