how to retrieve the difference between the two given dates in SQL
-1
How to find the Longest Booking ID for the given two dates and Costliest Booking ID for the given cost.
Here we have the 13 days difference so we are getting the longest booking id as 1.
what are the approach to archive this using sql query.
mysql sql
asked Nov 22 '18 at 6:47
AVINASH MAVINASH M
349
add a comment |
-1
How to find the Longest Booking ID for the given two dates and Costliest Booking ID for the given cost.
Here we have the 13 days difference so we are getting the longest booking id as 1.
what are the approach to archive this using sql query.
mysql sql
asked Nov 22 '18 at 6:47
AVINASH MAVINASH M
349
3
what you have tried?
– Devsi Odedra
Nov 22 '18 at 6:48
1
dont use image give it as text
– nikhil sugandh
Nov 22 '18 at 6:48
i tried with : SELECT DATEDIFF(day, '2017/08/25', '2017/08/28') AS DateDiff; but i'm not able to get the max count of that
– AVINASH M
Nov 22 '18 at 6:53
@AVINASHM the costliest booking id will be 1 14*3000=42000 the data given is wrong i reckon
– nikhil sugandh
Nov 22 '18 at 7:06
What is the datatype for StartDate and EndDate columns ?
– Madhur Bhaiya
Nov 22 '18 at 7:08
add a comment |
-1
-1
-1
How to find the Longest Booking ID for the given two dates and Costliest Booking ID for the given cost.
Here we have the 13 days difference so we are getting the longest booking id as 1.
what are the approach to archive this using sql query.
mysql sql
asked Nov 22 '18 at 6:47
AVINASH MAVINASH M
349
How to find the Longest Booking ID for the given two dates and Costliest Booking ID for the given cost.
Here we have the 13 days difference so we are getting the longest booking id as 1.
what are the approach to archive this using sql query.
mysql sql
mysql sql
asked Nov 22 '18 at 6:47
AVINASH MAVINASH M
349
asked Nov 22 '18 at 6:47
AVINASH MAVINASH M
349
asked Nov 22 '18 at 6:47
AVINASH MAVINASH M
349
asked Nov 22 '18 at 6:47
AVINASH MAVINASH M
349
asked Nov 22 '18 at 6:47
AVINASH MAVINASH M
349
349
3
what you have tried?
– Devsi Odedra
Nov 22 '18 at 6:48
1
dont use image give it as text
– nikhil sugandh
Nov 22 '18 at 6:48
i tried with : SELECT DATEDIFF(day, '2017/08/25', '2017/08/28') AS DateDiff; but i'm not able to get the max count of that
– AVINASH M
Nov 22 '18 at 6:53
@AVINASHM the costliest booking id will be 1 14*3000=42000 the data given is wrong i reckon
– nikhil sugandh
Nov 22 '18 at 7:06
What is the datatype for StartDate and EndDate columns ?
– Madhur Bhaiya
Nov 22 '18 at 7:08
add a comment |
3
what you have tried?
– Devsi Odedra
Nov 22 '18 at 6:48
1
dont use image give it as text
– nikhil sugandh
Nov 22 '18 at 6:48
i tried with : SELECT DATEDIFF(day, '2017/08/25', '2017/08/28') AS DateDiff; but i'm not able to get the max count of that
– AVINASH M
Nov 22 '18 at 6:53
@AVINASHM the costliest booking id will be 1 14*3000=42000 the data given is wrong i reckon
– nikhil sugandh
Nov 22 '18 at 7:06
What is the datatype for StartDate and EndDate columns ?
– Madhur Bhaiya
Nov 22 '18 at 7:08
3
3
what you have tried?
– Devsi Odedra
Nov 22 '18 at 6:48
what you have tried?
– Devsi Odedra
Nov 22 '18 at 6:48
1
1
dont use image give it as text
– nikhil sugandh
Nov 22 '18 at 6:48
dont use image give it as text
– nikhil sugandh
Nov 22 '18 at 6:48
i tried with : SELECT DATEDIFF(day, '2017/08/25', '2017/08/28') AS DateDiff; but i'm not able to get the max count of that
– AVINASH M
Nov 22 '18 at 6:53
i tried with : SELECT DATEDIFF(day, '2017/08/25', '2017/08/28') AS DateDiff; but i'm not able to get the max count of that
– AVINASH M
Nov 22 '18 at 6:53
@AVINASHM the costliest booking id will be 1 14*3000=42000 the data given is wrong i reckon
– nikhil sugandh
Nov 22 '18 at 7:06
@AVINASHM the costliest booking id will be 1 14*3000=42000 the data given is wrong i reckon
– nikhil sugandh
Nov 22 '18 at 7:06
What is the datatype for StartDate and EndDate columns ?
– Madhur Bhaiya
Nov 22 '18 at 7:08
What is the datatype for StartDate and EndDate columns ?
– Madhur Bhaiya
Nov 22 '18 at 7:08
add a comment |
3 Answers
3
active
oldest
votes
1
this will work:
(SELECT 'Total Booking Count' AS Label, COUNT(*) AS Value FROM bookings)
UNION ALL
(SELECT 'Longest Booking Id', booking_id FROM bookings ORDER BY DATEDIFF(enddate, startdate) DESC LIMIT 1)
UNION ALL
(SELECT 'Costliest Booking Id', booking_id FROM bookings ORDER BY (tariff*DATEDIFF(enddate, startdate)) DESC LIMIT 1)
answered Nov 22 '18 at 6:50
nikhil sugandhnikhil sugandh
1,2612719
add a comment |
0
with data
as (select *
,row_number() over(order by datediff(dd,end_date,start_date) desc) as rnk_time
,row_number() over(order by tarrif desc) as rnk_cost
,count(*) over(partition by 1) as tot_cnt
from your_table
)
select 'Total Booking count',tot_cnt
from data
where rnk_time=1
union all
select 'Longest Booking id',booking_id
from data
where rnk_time=1
union all
select 'Costliest Booking id',booking_id
from data
where rnk_cost=1
answered Nov 22 '18 at 7:00
George JosephGeorge Joseph
1,44249
ROW_NUMBER requires a recent MySQL version, you must clarify what OP is using.
– Salman A
Nov 22 '18 at 7:02
add a comment |
0
A simple UNION ALL could be used to combine all unrelated results:
(SELECT 'Total Booking Count' AS Label, COUNT(*) AS Value FROM bookings)
UNION ALL
(SELECT 'Longest Booking Id', booking_id FROM bookings ORDER BY DATEDIFF(enddate, startdate) DESC LIMIT 1)
UNION ALL
(SELECT 'Costliest Booking Id', booking_id FROM bookings ORDER BY tariff DESC LIMIT 1)
However I believe costliest booking should be 1 (13 * 3000 = 39000) and calculation must be done using DATEDIFF(...) * tariff.
answered Nov 22 '18 at 7:15
Salman ASalman A
177k66338427
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53425274%2fhow-to-retrieve-the-difference-between-the-two-given-dates-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
this will work:
(SELECT 'Total Booking Count' AS Label, COUNT(*) AS Value FROM bookings)
UNION ALL
(SELECT 'Longest Booking Id', booking_id FROM bookings ORDER BY DATEDIFF(enddate, startdate) DESC LIMIT 1)
UNION ALL
(SELECT 'Costliest Booking Id', booking_id FROM bookings ORDER BY (tariff*DATEDIFF(enddate, startdate)) DESC LIMIT 1)
answered Nov 22 '18 at 6:50
nikhil sugandhnikhil sugandh
1,2612719
add a comment |
1
this will work:
(SELECT 'Total Booking Count' AS Label, COUNT(*) AS Value FROM bookings)
UNION ALL
(SELECT 'Longest Booking Id', booking_id FROM bookings ORDER BY DATEDIFF(enddate, startdate) DESC LIMIT 1)
UNION ALL
(SELECT 'Costliest Booking Id', booking_id FROM bookings ORDER BY (tariff*DATEDIFF(enddate, startdate)) DESC LIMIT 1)
answered Nov 22 '18 at 6:50
nikhil sugandhnikhil sugandh
1,2612719
add a comment |
1
1
1
this will work:
(SELECT 'Total Booking Count' AS Label, COUNT(*) AS Value FROM bookings)
UNION ALL
(SELECT 'Longest Booking Id', booking_id FROM bookings ORDER BY DATEDIFF(enddate, startdate) DESC LIMIT 1)
UNION ALL
(SELECT 'Costliest Booking Id', booking_id FROM bookings ORDER BY (tariff*DATEDIFF(enddate, startdate)) DESC LIMIT 1)
answered Nov 22 '18 at 6:50
nikhil sugandhnikhil sugandh
1,2612719
this will work:
(SELECT 'Total Booking Count' AS Label, COUNT(*) AS Value FROM bookings)
UNION ALL
(SELECT 'Longest Booking Id', booking_id FROM bookings ORDER BY DATEDIFF(enddate, startdate) DESC LIMIT 1)
UNION ALL
(SELECT 'Costliest Booking Id', booking_id FROM bookings ORDER BY (tariff*DATEDIFF(enddate, startdate)) DESC LIMIT 1)
answered Nov 22 '18 at 6:50
nikhil sugandhnikhil sugandh
1,2612719
edited Nov 22 '18 at 7:05
answered Nov 22 '18 at 6:50
nikhil sugandhnikhil sugandh
1,2612719
answered Nov 22 '18 at 6:50
nikhil sugandhnikhil sugandh
1,2612719
answered Nov 22 '18 at 6:50
nikhil sugandhnikhil sugandh
1,2612719
1,2612719
add a comment |
add a comment |
0
with data
as (select *
,row_number() over(order by datediff(dd,end_date,start_date) desc) as rnk_time
,row_number() over(order by tarrif desc) as rnk_cost
,count(*) over(partition by 1) as tot_cnt
from your_table
)
select 'Total Booking count',tot_cnt
from data
where rnk_time=1
union all
select 'Longest Booking id',booking_id
from data
where rnk_time=1
union all
select 'Costliest Booking id',booking_id
from data
where rnk_cost=1
answered Nov 22 '18 at 7:00
George JosephGeorge Joseph
1,44249
ROW_NUMBER requires a recent MySQL version, you must clarify what OP is using.
– Salman A
Nov 22 '18 at 7:02
add a comment |
0
with data
as (select *
,row_number() over(order by datediff(dd,end_date,start_date) desc) as rnk_time
,row_number() over(order by tarrif desc) as rnk_cost
,count(*) over(partition by 1) as tot_cnt
from your_table
)
select 'Total Booking count',tot_cnt
from data
where rnk_time=1
union all
select 'Longest Booking id',booking_id
from data
where rnk_time=1
union all
select 'Costliest Booking id',booking_id
from data
where rnk_cost=1
answered Nov 22 '18 at 7:00
George JosephGeorge Joseph
1,44249
ROW_NUMBER requires a recent MySQL version, you must clarify what OP is using.
– Salman A
Nov 22 '18 at 7:02
add a comment |
0
0
0
with data
as (select *
,row_number() over(order by datediff(dd,end_date,start_date) desc) as rnk_time
,row_number() over(order by tarrif desc) as rnk_cost
,count(*) over(partition by 1) as tot_cnt
from your_table
)
select 'Total Booking count',tot_cnt
from data
where rnk_time=1
union all
select 'Longest Booking id',booking_id
from data
where rnk_time=1
union all
select 'Costliest Booking id',booking_id
from data
where rnk_cost=1
answered Nov 22 '18 at 7:00
George JosephGeorge Joseph
1,44249
with data
as (select *
,row_number() over(order by datediff(dd,end_date,start_date) desc) as rnk_time
,row_number() over(order by tarrif desc) as rnk_cost
,count(*) over(partition by 1) as tot_cnt
from your_table
)
select 'Total Booking count',tot_cnt
from data
where rnk_time=1
union all
select 'Longest Booking id',booking_id
from data
where rnk_time=1
union all
select 'Costliest Booking id',booking_id
from data
where rnk_cost=1
answered Nov 22 '18 at 7:00
George JosephGeorge Joseph
1,44249
answered Nov 22 '18 at 7:00
George JosephGeorge Joseph
1,44249
answered Nov 22 '18 at 7:00
George JosephGeorge Joseph
1,44249
answered Nov 22 '18 at 7:00
George JosephGeorge Joseph
1,44249
1,44249
ROW_NUMBER requires a recent MySQL version, you must clarify what OP is using.
– Salman A
Nov 22 '18 at 7:02
add a comment |
ROW_NUMBER requires a recent MySQL version, you must clarify what OP is using.
– Salman A
Nov 22 '18 at 7:02
ROW_NUMBER requires a recent MySQL version, you must clarify what OP is using.
– Salman A
Nov 22 '18 at 7:02
ROW_NUMBER requires a recent MySQL version, you must clarify what OP is using.
– Salman A
Nov 22 '18 at 7:02
add a comment |
0
A simple UNION ALL could be used to combine all unrelated results:
(SELECT 'Total Booking Count' AS Label, COUNT(*) AS Value FROM bookings)
UNION ALL
(SELECT 'Longest Booking Id', booking_id FROM bookings ORDER BY DATEDIFF(enddate, startdate) DESC LIMIT 1)
UNION ALL
(SELECT 'Costliest Booking Id', booking_id FROM bookings ORDER BY tariff DESC LIMIT 1)
However I believe costliest booking should be 1 (13 * 3000 = 39000) and calculation must be done using DATEDIFF(...) * tariff.
answered Nov 22 '18 at 7:15
Salman ASalman A
177k66338427
add a comment |
0
A simple UNION ALL could be used to combine all unrelated results:
(SELECT 'Total Booking Count' AS Label, COUNT(*) AS Value FROM bookings)
UNION ALL
(SELECT 'Longest Booking Id', booking_id FROM bookings ORDER BY DATEDIFF(enddate, startdate) DESC LIMIT 1)
UNION ALL
(SELECT 'Costliest Booking Id', booking_id FROM bookings ORDER BY tariff DESC LIMIT 1)
However I believe costliest booking should be 1 (13 * 3000 = 39000) and calculation must be done using DATEDIFF(...) * tariff.
answered Nov 22 '18 at 7:15
Salman ASalman A
177k66338427
add a comment |
0
0
0
A simple UNION ALL could be used to combine all unrelated results:
(SELECT 'Total Booking Count' AS Label, COUNT(*) AS Value FROM bookings)
UNION ALL
(SELECT 'Longest Booking Id', booking_id FROM bookings ORDER BY DATEDIFF(enddate, startdate) DESC LIMIT 1)
UNION ALL
(SELECT 'Costliest Booking Id', booking_id FROM bookings ORDER BY tariff DESC LIMIT 1)
However I believe costliest booking should be 1 (13 * 3000 = 39000) and calculation must be done using DATEDIFF(...) * tariff.
answered Nov 22 '18 at 7:15
Salman ASalman A
177k66338427
A simple UNION ALL could be used to combine all unrelated results:
(SELECT 'Total Booking Count' AS Label, COUNT(*) AS Value FROM bookings)
UNION ALL
(SELECT 'Longest Booking Id', booking_id FROM bookings ORDER BY DATEDIFF(enddate, startdate) DESC LIMIT 1)
UNION ALL
(SELECT 'Costliest Booking Id', booking_id FROM bookings ORDER BY tariff DESC LIMIT 1)
However I believe costliest booking should be 1 (13 * 3000 = 39000) and calculation must be done using DATEDIFF(...) * tariff.
answered Nov 22 '18 at 7:15
Salman ASalman A
177k66338427
answered Nov 22 '18 at 7:15
Salman ASalman A
177k66338427
answered Nov 22 '18 at 7:15
Salman ASalman A
177k66338427
answered Nov 22 '18 at 7:15
Salman ASalman A
177k66338427
177k66338427
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53425274%2fhow-to-retrieve-the-difference-between-the-two-given-dates-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
what you have tried?
– Devsi Odedra
Nov 22 '18 at 6:48
1
dont use image give it as text
– nikhil sugandh
Nov 22 '18 at 6:48
i tried with : SELECT DATEDIFF(day, '2017/08/25', '2017/08/28') AS DateDiff; but i'm not able to get the max count of that
– AVINASH M
Nov 22 '18 at 6:53
@AVINASHM the costliest booking id will be 1 14*3000=42000 the data given is wrong i reckon
– nikhil sugandh
Nov 22 '18 at 7:06
What is the datatype for StartDate and EndDate columns ?
– Madhur Bhaiya
Nov 22 '18 at 7:08