SQL tutorial | How to find n consecutive date records | Sales for at least n consecutive days.

Hello, I am getting this error.
Msg 517, Level 16, State 1, Line 46
Adding a value to a 'datetime' column caused an overflow.

This video is life saver. Thank you very much!

Hi maam.. I am fan of your teachings.. You are doing great work.. It's a request maam can you please make video on data analytics project using sql... The way we do in our analytics job.. Please reply maam when can we expect the same.. Thanku

Hello
DATEADD is not working in my Orcale. please, help me to get same output(no of Consecutive days) without DATEADD function
Thanks

But suppose we want to list all the consecutive dates in the same column, then in that case how can we do it.

Given 3 columns: date|customer ID| product usage; find out customers who used the product for 5 consecutive days. Can you please help me with this question?

Very easy to understand :)

Hi, great video, how to exclude weekends while keeping the continuous date?
Thank you

--ORACLE

with cte as(
select start_date, end_date,
start_date - row_number() over (order by start_date) as GRN
from projects)
select min(start_date), max(end_date) from cte group by grn order by grn;

Why don't use lead()?

Will this query work if the dates are 1st may, 3rd may. 5th may so on.... It won't.

Hi Great Video. But how do I change this to show consecutive days per product if I have a listing of various products??

Very importan 'islands and gaps' question, thanks for sharing

LEAD/LAG function is the most appropriate for this kind of scenario, instead of using multiple functions like row_number, dateadd

For the 'filter' you could have used Having CNT > =3 after the Group

What if there repeated order date values
Then row number will be
1 4-1-21
2 4-1-21

pls. show how to display last 30 days record of items (including dates with no particular items (just set its value to 0) and also show how many times each item exists each day.

hi can you pls. make a video on making a query, for example: I want to display the total jobtitle_A, jobtitle_B... so on and so forth in the database registered daily by the encoder, displaying value zero for a particular day if there was no entry made.

Need Help -
Scenario - I have a table testdata with columns
memberid (varchar) ,codetype(varchar),effectivedate(datetime)
this table having 20k records - from year 2015 to 2021
I need to find out the records - first two date records of each member of every year [ only memberid is unique)
eg.
member id codetype effectivedate
123 ABC 1/2/2015
123 ABC 1/2/2015
123 ABC 8/15/2015
123 EFG 9/15/2015
123 EFG 2/15/2018
345 EFG 3/14/2018
345 EFG 3/17/2018
345 ABC 9/19/2020
456 EFG 12/20/2021
result should be like below
member id codetype effectivedate
123 ABC 1/2/2015
123 ABC 1/2/2015
123 ABC 2/15/2018
345 EFG 3/14/2018
345 EFG 3/17/2018
345 ABC 9/19/2020
456 EFG 12/20/2021
tried lot of ways but no luck so far

--Postgresql solution

select count(*), min(created_at), max(created_at) from
(select created_at,
created_at - row_number() over(order by created_at) * interval '1 day' as constant_date
from posts) a
group by created_at
order by 1 desc;

Thank you..

It has a minor issue, for MySQL, it was not working if the records had the same dates. Try the below version

WITH CTE as
(SELECT DATE(created_at) dt, DATE_ADD(DATE(created_at), INTERVAL - ROW_NUMBER() OVER (ORDER BY created_at) DAY) as col
FROM statistics
GROUP BY dt
ORDER BY dt)

SELECT COUNT(*) as CNT
FROM CTE
GROUP BY col

Thank you so much. I was struggling with this.

brilliant

This was helpful, thank you.

If you get a chance can you please make a video to find next month first Monday
2.find which day the year start
3.write a query to find everymonth 1st dat Jan 1 Feb 1 marc1....dec1

Thanks for the tutorial Maam. Please create a video for complex flat file column

Excellent

hello mam, your videos are quite wonderful for newbies, i just want to know more in detail about
real-time working scenario. what is the project team size.? how many are they? how to integrate SVN or git into datastage and maintain version.?
how to deploy the code into production environment.? what exactly be the transformations you do in your enhancement or support project?
what could be the exact enhancements to the project.? what are SLA breaches and tickets?
how to install autosys or cron scheduler and integrate with datastage to schedule jobs? Sorry for the list. But, i didnt find
any blogs for these. if you have time, please do a session on these. Thanks a lot.

I like your videos and thank you for sharing all. 😊
Can you please create a video on how to get average sales value for Last 10 same day week on date level? For example for order date 27-05-2021 (i.e Thursday), last week is 20-05-2021, last to last week is 13-05-2021 and so on till last 10 weeks.
So there like 27-05-2021 as one of the order date, there will be different other order dates.
I can do this by creating 10 different column in subquery and do an average using those. But is that possible using some other logic or function?