Thanks for explaining the concept in such an easy way, However i wanna ask a question: Suppose the orderdate is on saturday or sunday and the delivery day is also same. What do we do in this scenario?
Hello Ma'am, I really appreciate your work, it's like free videos, and I will learn these things but the biggest problem with me is I'm unable to do it, whether the intermediate course or advanced course. I'm so frustrated that in the end, I will see the answer. I've seen so many videos but I'm unable to do it myself. what to do? is it like for others or it's just with me?
Thank you. Try to keep practicing with the datasets provided in the videos. Even after seeing the result, try to write the same query yourself again. If you keep practicing, you will soon learn the tricks to solve these queries. But the key is to write the query yourself every time you watch a video. Hope it helps 👍
Thank you soo much very helpful! Just one question where i am stuck from couple of days and not getting a solution. Here by default sql server considers weekend as saturday and sunday, what if i need to calculate no. Of days between 2 dates considering weekend as friday? Request you to kindly help me with the code. Thanks :)
Thank you. In this case, you would need to use a Calendar table that identifies the Friday as a holiday. You can then identify the working days and calculate accordingly.
Having a date table that marks weekends and holidays makes it much easier to implement this requirement as a simple join can determine whether the day is a work day or not.
You will need to write a function that identifies the holidays.either by constant dates for ex Christmas always falls on 25th Dec or holidays that fall on a particular day of a week..for ex every 1st Monday of August etc.
I have a doubt, in your final querie's 3rd line of code, if we could have added +1 , would it work? seelct orderdate, shipdate, (datediff(dd, order, shipdate) +1) - ((datediff (ww, orderdate, shipdate) * 2 ) + 1) from sales would it work ? Please do answer
ALTER TABLE mytable ADD difference_dt as DATEDIFF(DAY, start_dt, end_DT ) ; gives an error "Syntax error, expected something like a 'TRANSACTIONTIME' keyword between the 'as' keyword and the word 'DATEDIFF'." or SELECT DATEDIFF(day, start_dt, end_DT) as difference FROM mytable gives error "Syntax error: expected something between '(' and the 'day' keyword." kindly let me know
Datediff function is not a valid teradata function. You will need to subtract two days to get the difference. You can refer here docs.teradata.com/r/1DcoER_KpnGTfgPinRAFUw/WdO2taOuHi5IaUk0dEsJTg
alter function ufn_getweekdaysbetweentwodate(@StartDate Datetime, @EndDate datetime) returns int as begin declare @Cnt int select @Cnt = count(1) from generate_series(0,datediff(dd,@StartDate, @EndDate) -1,1) where Datename(dw, @StartDate +Value) not in ('Saturday','Sunday')
return @Cnt end declare @StartDate datetime = getdate(), @EndDate datetime = getdate() +10 select @StartDate as StartDate, @EndDate as EndDate,dbo.ufn_getweekdaysbetweentwodate (@StartDate,@EndDate) as WeekDaysbwtwodates
Very helpful. Best compilation for sql queries.
Thank you
Thank you very much! The video was extremly helpful. Bravo. I was exactly looking for the same material as explained. Hands down. 🙌
Glad it was helpful 🙏
Thank you so much for videos it was simply superb
Thank you
Thank you. Very helpful to prepare for the interviews.
Thank you
thank you.. this video solved my problem in code :)
Thank you
Thanks a lot, Really helpful my life today.
Thanks so much!
Hi, could you explain why not consider both order date on Saturday and Sunday as 1, else 0? Same question on ship date
Thanks for explaining the concept in such an easy way, However i wanna ask a question: Suppose the orderdate is on saturday or sunday and the delivery day is also same. What do we do in this scenario?
This is really very helpful.
Thank you
Great , Many Thanks. 👍
Thank you
Great content
Thank You!
Thanks a lot ❤️, saved my day 👍
Thank you
Very useful!
Thank you
Very helpful. Thank you so much!
Thank you
Nice video mam.. please provide database to work
Thanks a lottttt!!!! Really helpful.
Thank you!
Hello Ma'am, I really appreciate your work, it's like free videos, and I will learn these things but the biggest problem with me is I'm unable to do it, whether the intermediate course or advanced course. I'm so frustrated that in the end, I will see the answer. I've seen so many videos but I'm unable to do it myself. what to do? is it like for others or it's just with me?
Thank you. Try to keep practicing with the datasets provided in the videos. Even after seeing the result, try to write the same query yourself again. If you keep practicing, you will soon learn the tricks to solve these queries. But the key is to write the query yourself every time you watch a video. Hope it helps 👍
Thank you for this
Super mam your are Great 👍 👌 😀
Glad it was helpful.
Thanks
Is it easier to use a case and count for this scenario?
Can I get this table create statement and select statement query
Thank you soo much very helpful!
Just one question where i am stuck from couple of days and not getting a solution. Here by default sql server considers weekend as saturday and sunday, what if i need to calculate no. Of days between 2 dates considering weekend as friday?
Request you to kindly help me with the code. Thanks :)
Thank you. In this case, you would need to use a Calendar table that identifies the Friday as a holiday. You can then identify the working days and calculate accordingly.
@@LearnatKnowstar thank you for the prompt response!
Was just wondering if you care to explain more about how to progress on it..
Is there a way to execute this in Impala/Hive?
Great 👍
Glad it was helpful!
well explained
Thank you
And ma'am can we get any place where we can interact with you
You can always post your queries on our TH-cam channel
Can you let us know from where we can download these databases for practice ?
You can download sample databases. You can refer this video tutorial -
th-cam.com/video/ncj0EDzy_rw/w-d-xo.html
I need training on data stage with SQL,please provide your contract information
We will be announcing the batches soon on our TH-cam channel. Stay tuned.
thank you. what about Holidays ? what if you have a Date table and also exclude Holidays
Having a date table that marks weekends and holidays makes it much easier to implement this requirement as a simple join can determine whether the day is a work day or not.
@@LearnatKnowstar Is it possible to do it without the date table?
It is possible though not the preferred way.
You will need to write a function that identifies the holidays.either by constant dates for ex Christmas always falls on 25th Dec or holidays that fall on a particular day of a week..for ex every 1st Monday of August etc.
Will work incorrectly if order date is 2015-01-11 and ship date is 2015-01-25
How can we find days between different dates in one
column based on user id?
I have a doubt,
in your final querie's 3rd line of code, if we could have added +1 , would it work?
seelct orderdate, shipdate,
(datediff(dd, order, shipdate) +1) -
((datediff (ww, orderdate, shipdate) * 2 ) + 1)
from sales
would it work ? Please do answer
Nice
Thanks
It is not working for TEradata (datediff function)
ALTER TABLE mytable
ADD difference_dt as DATEDIFF(DAY, start_dt, end_DT ) ; gives an error "Syntax error, expected something like a 'TRANSACTIONTIME' keyword between the 'as' keyword and the word 'DATEDIFF'." or
SELECT DATEDIFF(day, start_dt, end_DT) as difference FROM mytable gives error "Syntax error: expected something between '(' and the 'day' keyword." kindly let me know
Datediff function is not a valid teradata function. You will need to subtract two days to get the difference.
You can refer here
docs.teradata.com/r/1DcoER_KpnGTfgPinRAFUw/WdO2taOuHi5IaUk0dEsJTg
How calculate last week of year date - next year any date first week date . I tried
How to calculate if we only need to skip a Sunday and want to consider monday to saturday as weekdays
alter function ufn_getweekdaysbetweentwodate(@StartDate Datetime, @EndDate datetime)
returns int
as
begin
declare @Cnt int
select @Cnt = count(1) from generate_series(0,datediff(dd,@StartDate, @EndDate) -1,1)
where Datename(dw, @StartDate +Value) not in ('Saturday','Sunday')
return @Cnt
end
declare @StartDate datetime = getdate(), @EndDate datetime = getdate() +10
select @StartDate as StartDate, @EndDate as EndDate,dbo.ufn_getweekdaysbetweentwodate (@StartDate,@EndDate) as WeekDaysbwtwodates
But like from 11 to 24 you have 2 Sunday and 2 Saturday. So, you should take it as total 4 off days
Mam plz explain in hindi also
Thank you. Will try to do some Hindi videos in the future 👍
This is not working solution, works conditionally only.