SQL | How to generate missing date records | How to create Reports showing data for each day

Подписаться 44 тыс.

Просмотров 25 тыс.

50% 1

In this video, we write a SQL query to generate missing date records.
Example in the video -
Generate a report showing order count for each day including days when no orders were placed.
How to install SQL Server for practice?
• How to install SQL Ser...
Check out the complete list of SQL Query Interview Questions -
• SQL Query Interview Qu...
Best Data Science / Analytics / SQL courses
Learn SQL Basics for Data Science Specialization
imp.i384100.net/qnXYk5
Beginners to Python Programming
skillshare.eqcm.net/GjMakm
Data Science and Business Analytics with Python
skillshare.eqcm.net/JrM1Aq
Get 40% OFF of Skillshare with code FLASH40 - October 2022
skillshare.eqcm.net/c/3299742...
Data Science Fundamentals with Python and SQL Specialization
imp.i384100.net/mgVYre
Python for Everybody Specialization
imp.i384100.net/DVz7Aj
Google Data Analytics Professional Certificate
imp.i384100.net/OR37oQ
Coursera Plus - Data Science Career Skills
imp.i384100.net/c/3299742/132...
Please do not forget to like, subscribe and share.
For enrolling and enquiries, please contact us at
Website - knowstar.org/
Facebook - / knowstartrainings
Linkedin - www.linkedin.com/company/know...
Email - learn@knowstar.org

Опубликовано:

6 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 46

@SP-fc9bx 2 года назад

You are so awesome! SQL came late for me in life but learning all these cool tricks is helping a LOT

@LearnatKnowstar 2 года назад

Glad it is helpful.

@agape13 2 года назад

Queen 👑 ! Excellent channel and teaching skills 🏆

@LearnatKnowstar 2 года назад

Thank you so much for your support!

@kingstonxavier 2 года назад

We can apply having clause count(OrderID) is Null to filter out the missing dates

@shaikhkalim956 Год назад

Every helpful video, nice way of explanation 👍

@LearnatKnowstar Год назад

Thank you 🙏

@sravankumar1767 2 года назад

Superb explanation 👌 👏

@LearnatKnowstar 2 года назад

Thank you

@bobgamble8204 2 года назад

A CTE is not the same as a temporary table. It just isn't.

@subhadarshisethy1680 2 года назад

Ty mam...

@LearnatKnowstar 2 года назад

Thank you

@sureshsugoor Год назад

Nice explained 💐

@LearnatKnowstar Год назад

Thank you

@ramyagopinath8320 11 месяцев назад

Awesome content❤

@LearnatKnowstar 11 месяцев назад

Thank you

@Faisal1504 2 года назад

Thanks

@LearnatKnowstar 2 года назад

Thank you

@briandennehy6380 2 года назад

Super stuff

@LearnatKnowstar 2 года назад

Thank you

@pratz17 Год назад

nice approach. I have another doubt: How to use/fill the record for missing dates from the previous dates ? like 10th January is the missing date then how can we make use of values on 9th jan and copy for 10th jan and generate a new table with all the dates ?

@LearnatKnowstar Год назад

Thank you for your comment. The forward fill approach is the topic for our next video coming this week. Stay tuned and please Subscribe 🙏

@localmartian9047 Год назад

We can use lag window function

@Niteshkumar-ly2ip Год назад

Can you please share the scripts of this example?

@robertmaslo1348 2 года назад

Sequence generation is very easy in MySQL: SELECT seq FROM seq_1_to_31

@udayreddy7173 2 года назад

Hi, Please explain to create temp table with first 12 days in each month of a year

@judelaw12 2 месяца назад

Don't, simply create a dates table, every DB should have a dates tables created. Just link to that table. Get a script online and select first 12 days for each month from that table.

@muizvasaya8435 Год назад

Thanks for the video, but I have a small problem when I'm creating dates month wise, its not resulting proper, its adding 30 days to each date, and with this it results this way 2016-12-31 00:00:00.000 2017-01-31 00:00:00.000 2017-02-28 00:00:00.000 2017-03-28 00:00:00.000 Declare @startdate date='2016-12-31'; Declare @endate date ='2022-7-31'; With dates as ( select @startdate as transactiondate union all Select dateadd(mm,1,transactiondate) from dates where dateadd(mm,1,transactiondate)

@ankitdeswal2231 Год назад

This is because you're using month (mm) in the datepart for DATEADD fn this will increment the month by the number to add specified. Use dateadd(dd,1, transactiondate) and it will solve your problem.

@user-fi8iy6lh5z Год назад

How to find duplicate data without using Group by clause

@Ready_Set_Boom 2 года назад

Where was this video 3 months ago. I came to a similar solution but I was struggling as a newbie.

@LearnatKnowstar 2 года назад

Glad it was helpful.

@whyapparel5438 Год назад

it is doesn't work in MySQL.

@nikolaybaranov2213 2 года назад

Maximum recursion = 100, a longer sequence can't be generated.

@nikolaybaranov2213 2 года назад

The value can be changed by specifying MAXRECURSION: SELECT OrderDate from Dates OPTION (MAXRECURSION NNN); MAXRECURSION >= 0 and

@adityaraopandrapagada1894 2 года назад

Great Solution... I have a requirement: I have a cities column with or without duplicates. I need count of cities start with each alphabet. If there is no city with any alphabet it should be shown as zero. Can you help me out @LearnAtKnowstar ?

@LearnatKnowstar 2 года назад

You can perform a count by extracting first letter of each city..ex - Left(Cityname,1)

@LearnatKnowstar 2 года назад

Create a CTE with the list of all alphabets A-Z and perform a left join with the CTE on the first alphabet of column city

@adityaraopandrapagada1894 2 года назад

@@LearnatKnowstar I wrote a query for firstname in Person.Person table, please lemme know whether my approach is correct or not. With t1 as (select row_number() over(order by object_id) as Alp from sys.columns), t2 as (select char(((Alp-1)%26)+65) alpha from t1 where Alp

@LearnatKnowstar 2 года назад

Looks perfect! Only not sure why there is group by on firstname for t3. Other than that, this should be giving the correct results.

@LearnatKnowstar 2 года назад

Thanks for mentioning this scenario. This is an interesting idea for us to cover in our video tutorials!