If the number of days to ship is 6 days, then this query will give 6-(2*1)=4 days as working days. But it can be possible that there is only one weekend in these 6 days then correct answer will be 6-1=5 days. So, I feel there should another way to reduce weekends.
Create both tables shown in video by using these two codes /*table 1*/ DROP TABLE IF EXISTS customer_orders; create table customer_orders ( order_id integer, customer_id integer, order_date date, ship_date date); insert into customer_orders values(1000,1,cast('2022-01-05' as date),cast('2022-01-11' as date)) ,(1001,2,cast('2022-02-04' as date),cast('2022-02-16' as date)) ,(1002,3,cast('2022-01-01' as date),cast('2022-01-19' as date)) ,(1003,4,cast('2022-01-06' as date),cast('2022-01-30' as date)) ,(1004,1,cast('2022-02-07' as date),cast('2022-02-13' as date)) ,(1005,4,cast('2022-01-07' as date),cast('2022-01-31' as date)) ,(1006,3,cast('2022-02-08' as date),cast('2022-02-26' as date)) ,(1007,2,cast('2022-02-09' as date),cast('2022-02-21' as date)) ,(1008,4,cast('2022-02-10' as date),cast('2022-03-06' as date)) ; SELECT * FROM customer_orders; /*table 2*/ DROP TABLE IF EXISTS customer; create table customer ( customer_id integer, customer_name VARCHAR(10), gender VARCHAR(1), dob date); insert into customer values (1,'Rahul','M',cast('2000-01-05' as date)) ,(2,'Shilpa','F',cast('2004-04-05' as date)) ,(3,'Ramesh','M',cast('2003-07-07' as date)) ,(4,'Katrina','F',cast('2005-02-05' as date)) ,(5,'Alia','F',cast('1992-01-01' as date)) ; SELECT * FROM customer;
Appreciated your efforts. I really liked the way of explanation. Tried to solve the question asked: select current_date(), dateadd(days,6,current_date()) AS POST_DATE /*Date After adding 6 days*/ ,datediff(week,current_date(),POST_DATE) AS WEEK /* Week differnece between postdate and current_Date */ ,dateadd(days,2*week,POST_DATE) AS POST_DATE_WITH_WEEKDAYS_ONLY /* Add additional days based on no weeks difference */ ;
Found your channel today on LinkedIn and checked out. Just wanna say what an amazing initiative you have taken Ankit to get us introduced with Industry Interview questions. Want more such Interview Question videos along with Leetcode problems.
Solution for adding the days in respect to customerorders table with cte as ( Select orderid as "orderid",customerid as "customerid",order_date as "order_date" ,ship_date as "ship_date",DATEDIFF(day,order_date,ship_date) as days_to_ship, DATEDIFF(week,order_date,ship_date) as week_between, DATEDIFF(day,order_date,ship_date) - 2*DATEDIFF(week,order_date,ship_date) as business_days from customerorders) Select DATEADD(day,cast(c.business_days as int),c.ship_date) as order_date_addition_to_businessdays from cte c
Inspired From Karan Gupta's Post ``` SELECT *, CASE WHEN WEEKDAY(ship_date)=5 THEN DATE_ADD(ship_date, INTERVAL 2 DAY) WHEN WEEKDAY(ship_date)=6 THEN DATE_ADD(ship_date, INTERVAL 1 DAY) ELSE ship_date END AS newShipDate FROM customer_order;
Concern on the logic to get business days Case : If order was placed on monday and delivered on Wednesday then by your logic business days would be 0 Because different between dates : 2 Difference in weeks : 1
sir if a product is ordered on 3rd jan 2022 but got shipped on 13th jan 2022 i.e. 10 days it will be considered 2 weeks and we will subtract 2 X 2 = 4 as weekends by there is only one sat sun between 3rd jan 2022 to 13th jan 2022
Assuming the business days to add is stored in a column named business_days Select case when datediff(week,order_date,dateadd(day,business_days,order_date) ) > 0 Then dateadd(day,business_days+2*datediff(week,order_date,dateadd(day,business_days,order_date) ) ,order_date) else dateadd(day,business_days,order_date) End as ship_date
The query is correct, but it will not work for one of the corner cases where: order_date(1st, Wednesday) and we are adding 7 business dates to it. Initially, it looks like it has one weekend so the shift will be by 2 days but originally this 2 day shift will lead to another 2-day shift, in total 4 days. 1st(wed) + 7 = 8th(Thrusday) -> 1 weekend in between so, 1st(wed) + 2 + 7 = 10th(Saturday) -> 1 more weekend so, 10th(Sat) + 2 = 12th(Monday) Python: stackoverflow.com/questions/12691551/add-n-business-days-to-a-given-date-ignoring-holidays-and-weekends-in-python add_business_date(datetime.date(2022,6,2), 7) Result: datetime.date(2022, 6, 13)
9:30 For the first row, how are we sure that there were 2 weekend days in the 6 days it took to ship? It could've been Sunday to Friday, therefore only one weekend day. Please clarify, thanks
thank you very much, sir.. please create a separate playlist that consists of all your videos that are not in your previous playlists.....thank you in advance, really helpful.
Here is my solution my MYSQL: adding 7 day = Order_date + 7 Days of delivery date select *, case when dayname(order_date) ='Saturday' then date_add(order_date,interval 10 DAY) when dayname(order_date) in ('Sunday','Monday','Tuesday','Wednesday') then date_add(order_date,interval 9 DAY) when dayname(order_date) in ('Thursday','Friday') then date_add(order_date,interval 11 DAY) else order_date END business_day from customer_orders;
The Same logic I've implemented: declare @today_date date ,@start_date date set @today_date = '2022-01-23' Select @today_date as 'todayDate', dateadd(day,2*datediff(week,@today_date,dateadd(day,7,@today_date)) ,dateadd(day,7,@today_date)) as '7days_ahead_without_weekend'
--Assignment Question select case when DATEPART(weekday,'2022-12-26')IN(7) then DATEADD(day,10,'2022-12-26') when DATEPART(weekday,'2022-12-26')IN(5,6) then DATEADD(day,11,'2022-12-26') ELSE DATEADD(day,9,'2022-12-26') END as date_add_7
ok @@ankitbansal6 sir,,,, Just one more thing...... I used a lag function (in Y-O-Y growth rate question in DataLemur website) and didn't use an order by inside the window. But to my surprise when I used the ORDER BY clause outside, it affected the window function. Is it possible or it happened due to something else?
with cte as ( SELECT *, datename(DW,ship_date) as nameofthe_day FROM customer_orderss ) select order_id,customer_id,order_date,ship_date,nameofthe_day, case when nameofthe_day= 'Sunday' then datename(dw,dateadd(day,1,ship_date)) when nameofthe_day= 'Saturday' then datename(dw,dateadd(day,2,ship_date)) else nameofthe_day end as busi_day from cte
I find it wrong please correct me if not-- In days_to_ship column all days are divisible of 6 in that case it is a 6days week and sundays are not included then our assumtion wont work , what if days are less then week days, is it possible to find day of week is sunday and then removing the day
Here is my assignment. I have added 10 days in order_date column and calculated the actual business days to ship. select *, DATEADD(day, 10, order_date) as Added_ten_days, DATEDIFF(week, order_date, DATEADD(day, 10, order_date)) as Difference_of_week, DATEDIFF(day, order_date, DATEADD(day, 10, order_date)) as number_of_days_to_ship, DATEDIFF(day, order_date, DATEADD(day, 10, order_date)) - 2*DATEDIFF(week, order_date, DATEADD(day, 10, order_date)) as Actual_business_days_to_ship from customer_orders_five
How can I extract the current year from lets say a coulmn named order_date? So basically, like I want to fetch the product names which are ordered in the current year.
declare @n int; set @n=3; with leaddate as (select mydate, lead(mydate,@n) over(order by mydate) as three_later_date from dates where datepart(weekday,mydate) not in(1,7) ) select mydate,three_later_date from leaddate where mydate='2024-03-26'
Hello Sir , Can you please make a video as to how to approch the problem . Like how to break it in parts and then solve . Some guidance on this would be really helpful
if you want to see how to break down into smaller parts and approach then you should go through this series th-cam.com/video/jDx3BqrLmjk/w-d-xo.html I am also going through the above series apart from this ..
I have a Q For Example in the table there is date filed and i want to fetch quarter from date filed it should start from financial quarter as Q1 like this how to get this ?
For the Age problem it shows years without considering the months are completed or not. Suppose if someone is going to be 27 by Dec but it will show show 27 years even in August also. That's not right
but that is not a correct approach to find business days, lets say the we have 12 days gap, and it is starting from Saturday, then in 12 days of gap there will be 2 Saturdays and 2 sundays, that way we need to subs 4.
How about this query where we consider order date can be a weekend and we use a temp_date and update orderdate to temp date when saturday 2 days added and when sunday 1 day is added now we follow the same thing; WITH cte AS ( SELECT *, CASE WHEN DATEPART(WEEKDAY, order_date) = 7 THEN DATEADD(DAY, 2, order_date) WHEN DATEPART(WEEKDAY, order_date) = 1 THEN DATEADD(DAY, 1, order_date) ELSE order_date END AS Temp_date FROM customer_orders ), cte2 AS ( SELECT *, DATEDIFF(DAY, Temp_date, ship_date) AS days_difference, DATEDIFF(WEEK, Temp_date, ship_date) AS weeks_difference FROM cte ) select *,days_difference-2*weeks_difference as Business_days from cte2
MYSQL- Logic for adding given number of business days to the provided date .........select case when dayname(date_add(date_add('2022-12-29', INTERVAL 7 DAY),INTERVAL cast(FLOOR((datediff(date_add('2022-12-29', INTERVAL 7 DAY),'2022-12-29')/7)) as signed)*2 DAY)) IN( 'Saturday','Sunday') then date_add(date_add(date_add('2022-12-29', INTERVAL 7 DAY),INTERVAL cast(FLOOR((datediff(date_add('2022-12-29', INTERVAL 7 DAY),'2022-12-29')/7)) as signed)*2 DAY),Interval 2 DAY) else date_add(date_add('2022-12-29', INTERVAL 7 DAY),INTERVAL cast(FLOOR((datediff(date_add('2022-12-29', INTERVAL 7 DAY),'2022-12-29')/7)) as signed)*2 DAY) end Business_days
7/141 Hi sir, How to count age in MYSQL coz In SQL Server , datediff( ) takes three parameters but in MYSQL it only takes two parameter that are date , so when I am using datediff( ) it is giving me no. of days and then I have to divide it with 365 , to get the date Is their any other alternative? Thanks.
I did this as : declare @whatsdate date; set @whatsdate='2022-02-07' select case when datepart(weekday,@whatsdate) in (1,7) then dateadd(day,6,@whatsdate) else dateadd(day,7,@whatsdate) end
Hello @ankit bansal sir I am working on a project and imported data which contains datetime col(which has date and time)in text format I have tried everything to convert it to datetime format but its showing error everytime. Please enlighten me how to cast in correct way PS I am using mysql
@@ankitbansal6 the format for date time is '3/17/2019 5:39' , I am trying to update with this query update `sales`.`sales_march_2019` set `order date` =STR_TO_DATE( `order date`, '%mm/%dd/%YYYY %hh:%mm:%ss') showing error-- Incorrect datetime value: '3/17/2019 5:39' for function str_to_date, also one of the query was showing null values in order date col
create table amazon_orders ( order_id integer, customer_id integer, order_date date, ship_date date ); insert into amazon_orders values (1000,1,cast('2022-01-05' as date),cast('2022-01-11' as date)),(1001,2,cast('2022-02-04' as date),cast('2022-02-16' as date)), (1002,3,cast('2022-01-01' as date),cast('2022-01-19' as date)),(1003,4,cast('2022-01-06' as date),cast('2022-01-30' as date)), (1004,1,cast('2022-02-07' as date),cast('2022-02-13' as date)),(1005,4,cast('2022-01-07' as date),cast('2022-01-31' as date)), (1006,3,cast('2022-02-08' as date),cast('2022-02-26' as date)),(1007,2,cast('2022-02-09' as date),cast('2022-02-21' as date)), (1008,4,cast('2022-02-10' as date),cast('2022-03-06' as date)); select * from amazon_orders;
MYSQL users can use the following query: SELECT *, datediff(ship_date,order_date) AS days_to_ship ,WEEK(ship_date)-WEEK(order_date) AS weeks , datediff(ship_date,order_date) - 2*(WEEK(ship_date)-WEEK(order_date)) AS business_days_to_ship FROM customer_orders;
Assignment question: Adding 5 business days from a given date: Select order_date,dateadd(day,5+(2*DATEDIFF(week,order_date,dateadd(day,5,order_date))),order_date) n_business_days from customers
Assignment Answer: Did it for 5 days, can we done for N number of days select getdate(),DATEADD(day,5,GETDATE()) as days_added,DATEDIFF(week,GETDATE(),DATEADD(day,5,GETDATE()))*2 as no_of_weekend_days,DATEADD(day,5 - DATEDIFF(week,GETDATE(),DATEADD(day,5,GETDATE()))*2,GETDATE()) as week_days
MySQL solution for the business days to ship problem SELECT *, DATEDIFF(ship_date,order_date) AS days_to_ship, DATEDIFF(ship_date,order_date) - 2*(week(ship_date)-week(order_date)) AS business_days_to_ship FROM amazon_orders ;
If the number of days to ship is 6 days, then this query will give 6-(2*1)=4 days as working days. But it can be possible that there is only one weekend in these 6 days then correct answer will be 6-1=5 days. So, I feel there should another way to reduce weekends.
Create both tables shown in video by using these two codes
/*table 1*/
DROP TABLE IF EXISTS customer_orders;
create table customer_orders (
order_id integer,
customer_id integer,
order_date date,
ship_date date);
insert into customer_orders values(1000,1,cast('2022-01-05' as date),cast('2022-01-11' as date))
,(1001,2,cast('2022-02-04' as date),cast('2022-02-16' as date))
,(1002,3,cast('2022-01-01' as date),cast('2022-01-19' as date))
,(1003,4,cast('2022-01-06' as date),cast('2022-01-30' as date))
,(1004,1,cast('2022-02-07' as date),cast('2022-02-13' as date))
,(1005,4,cast('2022-01-07' as date),cast('2022-01-31' as date))
,(1006,3,cast('2022-02-08' as date),cast('2022-02-26' as date))
,(1007,2,cast('2022-02-09' as date),cast('2022-02-21' as date))
,(1008,4,cast('2022-02-10' as date),cast('2022-03-06' as date))
;
SELECT * FROM customer_orders;
/*table 2*/
DROP TABLE IF EXISTS customer;
create table customer (
customer_id integer,
customer_name VARCHAR(10),
gender VARCHAR(1),
dob date);
insert into customer values
(1,'Rahul','M',cast('2000-01-05' as date))
,(2,'Shilpa','F',cast('2004-04-05' as date))
,(3,'Ramesh','M',cast('2003-07-07' as date))
,(4,'Katrina','F',cast('2005-02-05' as date))
,(5,'Alia','F',cast('1992-01-01' as date))
;
SELECT * FROM customer;
The way you solve interview questions is just mind blowing. I think you should keep making more videos on interview questions. It's a gem
Sure. Thank you 😊
Randomly found your channel super explanation.
You are The King 👑 in SQL.
Welcome aboard!
Appreciated your efforts. I really liked the way of explanation.
Tried to solve the question asked:
select current_date(),
dateadd(days,6,current_date()) AS POST_DATE /*Date After adding 6 days*/
,datediff(week,current_date(),POST_DATE) AS WEEK /* Week differnece between postdate and current_Date */
,dateadd(days,2*week,POST_DATE) AS POST_DATE_WITH_WEEKDAYS_ONLY /* Add additional days based on no weeks difference */
;
Found your channel today on LinkedIn and checked out. Just wanna say what an amazing initiative you have taken Ankit to get us introduced with Industry Interview questions. Want more such Interview Question videos along with Leetcode problems.
Same here. I also found his channel on linkedin. A big thanks to you bro for such a great work you are doing.
Thank you so much. You made my day 🙂
Solution for adding the days in respect to customerorders table
with cte as (
Select orderid as "orderid",customerid as "customerid",order_date as "order_date" ,ship_date as "ship_date",DATEDIFF(day,order_date,ship_date) as days_to_ship,
DATEDIFF(week,order_date,ship_date) as week_between,
DATEDIFF(day,order_date,ship_date) - 2*DATEDIFF(week,order_date,ship_date) as business_days
from customerorders)
Select DATEADD(day,cast(c.business_days as int),c.ship_date) as order_date_addition_to_businessdays
from cte c
Inspired From Karan Gupta's Post
```
SELECT
*,
CASE WHEN WEEKDAY(ship_date)=5 THEN DATE_ADD(ship_date, INTERVAL 2 DAY)
WHEN WEEKDAY(ship_date)=6 THEN DATE_ADD(ship_date, INTERVAL 1 DAY)
ELSE ship_date END AS newShipDate
FROM
customer_order;
perfect 🔥
Very useful videos with scenario, keep it up
Thank you 😊
You are doing great bro please post everyday one question answer
Thank you 😊
@@ankitbansal6please pin the create and insert statement. it will help others i got it from comments
Concern on the logic to get business days
Case : If order was placed on monday and delivered on Wednesday then by your logic business days would be 0
Because different between dates : 2
Difference in weeks : 1
week diff will be 0 if it is in same week
Loved every piece of it.
Crystal clear explanation!
🙏
sir if a product is ordered on 3rd jan 2022 but got shipped on 13th jan 2022 i.e. 10 days it will be considered 2 weeks and we will subtract 2 X 2 = 4 as weekends by there is only one sat sun between 3rd jan 2022 to 13th jan 2022
Assuming the business days to add is stored in a column named business_days
Select case when datediff(week,order_date,dateadd(day,business_days,order_date) ) > 0 Then dateadd(day,business_days+2*datediff(week,order_date,dateadd(day,business_days,order_date) ) ,order_date) else dateadd(day,business_days,order_date) End as ship_date
Looks good. did you try run it?
No Ankit haven't run it.
:)
Was looking at the video late in the night while going to bed.
The query is correct, but it will not work for one of the corner cases where: order_date(1st, Wednesday) and we are adding 7 business dates to it. Initially, it looks like it has one weekend so the shift will be by 2 days but originally this 2 day shift will lead to another 2-day shift, in total 4 days.
1st(wed) + 7 = 8th(Thrusday) -> 1 weekend in between
so, 1st(wed) + 2 + 7 = 10th(Saturday) -> 1 more weekend
so, 10th(Sat) + 2 = 12th(Monday)
Python:
stackoverflow.com/questions/12691551/add-n-business-days-to-a-given-date-ignoring-holidays-and-weekends-in-python
add_business_date(datetime.date(2022,6,2), 7)
Result: datetime.date(2022, 6, 13)
@@ankitbansal6 do you have a solution video for the same?
Bro you are a GEM. thank you for making these videos.
i solved some of the trickiest questions only after watching your videos.🤘🤘
Awesome 😊
9:30 For the first row, how are we sure that there were 2 weekend days in the 6 days it took to ship? It could've been Sunday to Friday, therefore only one weekend day. Please clarify, thanks
Good question. Assumption is order date and ship date will always be weekdays .
Hi @ankitbansal, these functions dont work in Oracle. It is only valid in mysql. But still Thanks for clearing the doubts. Really helpful
I am using SQL server
Helpful.. great explanation
Thank you 😊
thank you very much, sir.. please create a separate playlist that consists of all your videos that are not in your previous playlists.....thank you in advance, really helpful.
Thank you very much Ankit sir for the video , Really helpful.
Keep watching
Here is my solution my MYSQL: adding 7 day = Order_date + 7 Days of delivery date
select *,
case
when dayname(order_date) ='Saturday' then date_add(order_date,interval 10 DAY)
when dayname(order_date) in ('Sunday','Monday','Tuesday','Wednesday') then date_add(order_date,interval 9 DAY)
when dayname(order_date) in ('Thursday','Friday') then date_add(order_date,interval 11 DAY)
else order_date
END business_day
from customer_orders;
Dateadd(
day,
datediff(week,order_date,dateadd(day, business_day,ordered)*2+datediff(day,order_date,dateadd(day, business_day,order_date),
order_date
)
as ship_date
From customers
select case when date_part(dayofweek,sysdate-4) >=0 and date_part(dayofweek,sysdate-4)
Hi Ankit thanks for this video
may I know in which video you provided the solution
Have not provided the solution yet. Will plan to publish soon.
@@ankitbansal6 please provide the solution
with datedifft as(select DATEadd(day,7,'2022-01-23')as dateq)
,diff as(select DATEDIFF(week ,'2022-01-23',(select dateq from datedifft))as dater)
select (select dateq from datedifft)+2*(select dater from diff)
The Same logic I've implemented:
declare @today_date date ,@start_date date
set @today_date = '2022-01-23'
Select @today_date as 'todayDate',
dateadd(day,2*datediff(week,@today_date,dateadd(day,7,@today_date)) ,dateadd(day,7,@today_date)) as '7days_ahead_without_weekend'
--Assignment Question
select
case when DATEPART(weekday,'2022-12-26')IN(7) then DATEADD(day,10,'2022-12-26')
when DATEPART(weekday,'2022-12-26')IN(5,6) then DATEADD(day,11,'2022-12-26')
ELSE DATEADD(day,9,'2022-12-26') END as date_add_7
Sir, just a small Question.....
Do these functions work only if the dates are in YYYY-MM-DD format???
The only condition is the data type of the column should be date or datetime or timestamp .
@@ankitbansal6 ok
ok @@ankitbansal6 sir,,,, Just one more thing...... I used a lag function (in Y-O-Y growth rate question in DataLemur website) and didn't use an order by inside the window. But to my surprise when I used the ORDER BY clause outside, it affected the window function. Is it possible or it happened due to something else?
@@2412_Sujoy_Das not possible
with cte as (
SELECT *, datename(DW,ship_date) as nameofthe_day FROM customer_orderss
)
select order_id,customer_id,order_date,ship_date,nameofthe_day, case when nameofthe_day= 'Sunday' then datename(dw,dateadd(day,1,ship_date))
when nameofthe_day= 'Saturday' then datename(dw,dateadd(day,2,ship_date))
else nameofthe_day
end as busi_day
from cte
I find it wrong please correct me if not-- In days_to_ship column all days are divisible of 6 in that case it is a 6days week and sundays are not included then our assumtion wont work , what if days are less then week days, is it possible to find day of week is sunday and then removing the day
Here is my assignment. I have added 10 days in order_date column and calculated the actual business days to ship.
select *, DATEADD(day, 10, order_date) as Added_ten_days,
DATEDIFF(week, order_date, DATEADD(day, 10, order_date)) as Difference_of_week,
DATEDIFF(day, order_date, DATEADD(day, 10, order_date)) as number_of_days_to_ship,
DATEDIFF(day, order_date, DATEADD(day, 10, order_date)) - 2*DATEDIFF(week, order_date, DATEADD(day, 10, order_date)) as Actual_business_days_to_ship
from customer_orders_five
How can I extract the current year from lets say a coulmn named order_date?
So basically, like I want to fetch the product names which are ordered in the current year.
Based on current date function
HI Ankit, Can you please let us know if it is in weekend how to proceed further?
select *,
Datediff(day,order_date,ship_date) - 2*Datediff(week,order_date,ship_date)+2 business_days_added
Nice videos u make!🙏🏻 thank u
Thanks 🙏
declare @n int;
set @n=3;
with leaddate as (select mydate,
lead(mydate,@n) over(order by mydate) as three_later_date
from dates
where datepart(weekday,mydate) not in(1,7)
)
select mydate,three_later_date from leaddate
where mydate='2024-03-26'
Thank you sir ❤️❤️
sir can you please explain me in simple words the diffrence between count(*) and count(1) ?
Check it out
th-cam.com/video/IE8NueisxHY/w-d-xo.html
@@ankitbansal6 Thanks sir.. Now its clear..👍
Hello Sir , Can you please make a video as to how to approch the problem . Like how to break it in parts and then solve . Some guidance on this would be really helpful
if you want to see how to break down into smaller parts and approach then you should go through this series
th-cam.com/video/jDx3BqrLmjk/w-d-xo.html
I am also going through the above series apart from this ..
I have a Q For Example in the table there is date filed and i want to fetch quarter from date filed it should start from financial quarter as Q1 like this how to get this ?
Datepart function
Thanks sir
For the Age problem it shows years without considering the months are completed or not. Suppose if someone is going to be 27 by Dec but it will show show 27 years even in August also. That's not right
but that is not a correct approach to find business days, lets say the we have 12 days gap, and it is starting from Saturday, then in 12 days of gap there will be 2 Saturdays and 2 sundays, that way we need to subs 4.
Agreeeee
Useful
How about this query where we consider order date can be a weekend and we use a temp_date and update orderdate to temp date when saturday 2 days added and when sunday 1 day is added now we follow the same thing;
WITH cte AS (
SELECT
*,
CASE
WHEN DATEPART(WEEKDAY, order_date) = 7 THEN DATEADD(DAY, 2, order_date)
WHEN DATEPART(WEEKDAY, order_date) = 1 THEN DATEADD(DAY, 1, order_date)
ELSE order_date
END AS Temp_date
FROM
customer_orders
),
cte2 AS (
SELECT
*,
DATEDIFF(DAY, Temp_date, ship_date) AS days_difference,
DATEDIFF(WEEK, Temp_date, ship_date) AS weeks_difference
FROM
cte
)
select *,days_difference-2*weeks_difference as Business_days from cte2
what if the difference between order date and ship date are 3days? Then the business days calculation will fail
Why?
second way :
select
case when datepart(weekday,getdate()) in (1,7) then dateadd(day,6,getdate()) else dateadd(day,7,getdate()) end
how does it works .
MYSQL- Logic for adding given number of business days to the provided date .........select case when dayname(date_add(date_add('2022-12-29', INTERVAL 7 DAY),INTERVAL cast(FLOOR((datediff(date_add('2022-12-29', INTERVAL 7 DAY),'2022-12-29')/7)) as signed)*2 DAY)) IN( 'Saturday','Sunday') then
date_add(date_add(date_add('2022-12-29', INTERVAL 7 DAY),INTERVAL cast(FLOOR((datediff(date_add('2022-12-29', INTERVAL 7 DAY),'2022-12-29')/7)) as signed)*2 DAY),Interval 2 DAY) else
date_add(date_add('2022-12-29', INTERVAL 7 DAY),INTERVAL cast(FLOOR((datediff(date_add('2022-12-29', INTERVAL 7 DAY),'2022-12-29')/7)) as signed)*2 DAY) end Business_days
please provide the solution ankit for the adding business days am not getting it
7/141
Hi sir,
How to count age in MYSQL coz
In SQL Server , datediff( ) takes three parameters
but in MYSQL it only takes two parameter that are date , so when I am using datediff( ) it is giving me no. of days
and then I have to divide it with 365 , to get the date
Is their any other alternative?
Thanks.
use timestampdiff() date function.
I did this as :
declare @whatsdate date;
set @whatsdate='2022-02-07'
select
case when datepart(weekday,@whatsdate) in (1,7) then dateadd(day,6,@whatsdate) else dateadd(day,7,@whatsdate) end
hi in above solution if the day is in sunday then by adding 6 to sunday date this will result in saturday date. if worng please correct me
@Ankit_Bansal Sir please provide solution to the assignment, I am getting confused
Please provide a query by which we can get 1st date of every month.
Hello @ankit bansal sir I am working on a project and imported data which contains datetime col(which has date and time)in text format I have tried everything to convert it to datetime format but its showing error everytime. Please enlighten me how to cast in correct way
PS I am using mysql
Show me data and error
@@ankitbansal6 the format for date time is '3/17/2019 5:39' , I am trying to update with this query update `sales`.`sales_march_2019` set `order date` =STR_TO_DATE( `order date`, '%mm/%dd/%YYYY %hh:%mm:%ss') showing error-- Incorrect datetime value: '3/17/2019 5:39' for function str_to_date, also one of the query was showing null values in order date col
Where can i find the answer to that question ?
What if days_to_ship = 4 and these 4 days are not weekends
Hey Ankit, i am unable to calculate week in mysql using datediff. for week less than 7 days i am getting 0 if i divide datediff by 7. please help
Try this
select week('2010-11-18') - week ('2010-10-18');
@@ankitbansal6 wow it worked! thanks
Can you pls give us the code for creating this table?
create table amazon_orders (
order_id integer,
customer_id integer,
order_date date,
ship_date date
);
insert into amazon_orders values
(1000,1,cast('2022-01-05' as date),cast('2022-01-11' as date)),(1001,2,cast('2022-02-04' as date),cast('2022-02-16' as date)),
(1002,3,cast('2022-01-01' as date),cast('2022-01-19' as date)),(1003,4,cast('2022-01-06' as date),cast('2022-01-30' as date)),
(1004,1,cast('2022-02-07' as date),cast('2022-02-13' as date)),(1005,4,cast('2022-01-07' as date),cast('2022-01-31' as date)),
(1006,3,cast('2022-02-08' as date),cast('2022-02-26' as date)),(1007,2,cast('2022-02-09' as date),cast('2022-02-21' as date)),
(1008,4,cast('2022-02-10' as date),cast('2022-03-06' as date));
select * from amazon_orders;
Please what can i do when i get this message "'DATE_PART' is not a recognized built-in function name."
Which database?
@@ankitbansal6 I think it's 2023 SSMS
this video is not included in any playlist.
Will add
Adding days excluding Weekends
Can you give us solution for the last question?
Yes i will make a video on it
@@ankitbansal6 Can you share the link to verify
MYSQL users can use the following query:
SELECT *, datediff(ship_date,order_date) AS days_to_ship
,WEEK(ship_date)-WEEK(order_date) AS weeks
, datediff(ship_date,order_date) - 2*(WEEK(ship_date)-WEEK(order_date)) AS business_days_to_ship
FROM customer_orders;
Last like to 800 by me.😂😂
Thank you 🙏
Assignment question:
Adding 5 business days from a given date:
Select order_date,dateadd(day,5+(2*DATEDIFF(week,order_date,dateadd(day,5,order_date))),order_date) n_business_days from customers
Hi Ankit,
Table A
Col1 col2
A. 1
B. 2
C. 3
D. 4
E. 5
Output:
Col1 col2
B 1
A 2
D 3
E 4
E. 5
How to achieve this using SQL
What's the logic here ?
Select dateadd(day,7,'2022-01-23')+2
You can't add +2 simply . you may have 2 consider more than 1 weekend or no weekend..
Hello Ankit,
Please provide the solution to the question asked by you.
Sure
SELECT
DATEADD ( DAY, CAST(2*DATEDIFF(WEEK,order_date,ship_date) AS INT), ship_date)
FROM customer_orders;
Assignment Answer:
Did it for 5 days, can we done for N number of days
select getdate(),DATEADD(day,5,GETDATE()) as days_added,DATEDIFF(week,GETDATE(),DATEADD(day,5,GETDATE()))*2 as no_of_weekend_days,DATEADD(day,5 - DATEDIFF(week,GETDATE(),DATEADD(day,5,GETDATE()))*2,GETDATE()) as week_days
MySQL solution for the business days to ship problem
SELECT
*,
DATEDIFF(ship_date,order_date) AS days_to_ship,
DATEDIFF(ship_date,order_date) - 2*(week(ship_date)-week(order_date)) AS business_days_to_ship
FROM
amazon_orders
;