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

No video

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

Рет қаралды 25,846

Learn at Knowstar

Күн бұрын

Пікірлер: 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

@bobgamble8204 2 жыл бұрын

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

@sravankumar1767 2 жыл бұрын

Superb explanation 👌 👏

@LearnatKnowstar 2 жыл бұрын

Thank you

@shaikhkalim956 2 жыл бұрын

Every helpful video, nice way of explanation 👍

@LearnatKnowstar 2 жыл бұрын

Thank you 🙏

@aadarshchaudhari3957 Жыл бұрын

Thanks

@LearnatKnowstar 11 ай бұрын

Thank You!

@ramyagopinath8320 Жыл бұрын

Awesome content❤

@LearnatKnowstar Жыл бұрын

Thank you

@sureshsugoor Жыл бұрын

Nice explained 💐

@LearnatKnowstar Жыл бұрын

Thank you

@subhadarshisethy1680 2 жыл бұрын

Ty mam...

@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

@robertmaslo1348 2 жыл бұрын

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

@briandennehy6380 2 жыл бұрын

Super stuff

@LearnatKnowstar 2 жыл бұрын

Thank you

@user-fi8iy6lh5z Жыл бұрын

How to find duplicate data without using Group by clause

@udayreddy7173 2 жыл бұрын

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

@judelaw12 4 ай бұрын

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.

@Niteshkumar-ly2ip Жыл бұрын

Can you please share the scripts of this example?

@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

@muizvasaya8435 2 жыл бұрын

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.

@whyapparel5438 Жыл бұрын

it is doesn't work in MySQL.

@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!