how to retrieve the difference between the two given dates in SQL
up vote
-1
down vote
favorite
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 at 6:47
AVINASH M
349
add a comment |
up vote
-1
down vote
favorite
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 at 6:47
AVINASH M
349
3
what you have tried?
– Devsi Odedra
Nov 22 at 6:48
1
dont use image give it as text
– nikhil sugandh
Nov 22 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 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 at 7:06
What is the datatype for StartDate and EndDate columns ?
– Madhur Bhaiya
Nov 22 at 7:08
add a comment |
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
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 at 6:47
AVINASH 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 at 6:47
AVINASH M
349
asked Nov 22 at 6:47
AVINASH M
349
asked Nov 22 at 6:47
AVINASH M
349
asked Nov 22 at 6:47
AVINASH M
349
asked Nov 22 at 6:47
AVINASH M
349
349
3
what you have tried?
– Devsi Odedra
Nov 22 at 6:48
1
dont use image give it as text
– nikhil sugandh
Nov 22 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 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 at 7:06
What is the datatype for StartDate and EndDate columns ?
– Madhur Bhaiya
Nov 22 at 7:08
add a comment |
3
what you have tried?
– Devsi Odedra
Nov 22 at 6:48
1
dont use image give it as text
– nikhil sugandh
Nov 22 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 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 at 7:06
What is the datatype for StartDate and EndDate columns ?
– Madhur Bhaiya
Nov 22 at 7:08
3
3
what you have tried?
– Devsi Odedra
Nov 22 at 6:48
what you have tried?
– Devsi Odedra
Nov 22 at 6:48
1
1
dont use image give it as text
– nikhil sugandh
Nov 22 at 6:48
dont use image give it as text
– nikhil sugandh
Nov 22 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 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 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 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 at 7:06
What is the datatype for StartDate and EndDate columns ?
– Madhur Bhaiya
Nov 22 at 7:08
What is the datatype for StartDate and EndDate columns ?
– Madhur Bhaiya
Nov 22 at 7:08
add a comment |
3 Answers
3
active
oldest
votes
up vote
1
down vote
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 at 6:50
nikhil sugandh
1,2242619
add a comment |
up vote
0
down vote
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 at 7:00
George Joseph
1,26749
ROW_NUMBER requires a recent MySQL version, you must clarify what OP is using.
– Salman A
Nov 22 at 7:02
add a comment |
up vote
0
down vote
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 at 7:15
Salman A
174k66334421
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',
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
up vote
1
down vote
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 at 6:50
nikhil sugandh
1,2242619
add a comment |
up vote
1
down vote
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 at 6:50
nikhil sugandh
1,2242619
add a comment |
up vote
1
down vote
up vote
1
down vote
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 at 6:50
nikhil sugandh
1,2242619
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 at 6:50
nikhil sugandh
1,2242619
edited Nov 22 at 7:05
answered Nov 22 at 6:50
nikhil sugandh
1,2242619
answered Nov 22 at 6:50
nikhil sugandh
1,2242619
answered Nov 22 at 6:50
nikhil sugandh
1,2242619
1,2242619
add a comment |
add a comment |
up vote
0
down vote
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 at 7:00
George Joseph
1,26749
ROW_NUMBER requires a recent MySQL version, you must clarify what OP is using.
– Salman A
Nov 22 at 7:02
add a comment |
up vote
0
down vote
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 at 7:00
George Joseph
1,26749
ROW_NUMBER requires a recent MySQL version, you must clarify what OP is using.
– Salman A
Nov 22 at 7:02
add a comment |
up vote
0
down vote
up vote
0
down vote
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 at 7:00
George Joseph
1,26749
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 at 7:00
George Joseph
1,26749
answered Nov 22 at 7:00
George Joseph
1,26749
answered Nov 22 at 7:00
George Joseph
1,26749
answered Nov 22 at 7:00
George Joseph
1,26749
1,26749
ROW_NUMBER requires a recent MySQL version, you must clarify what OP is using.
– Salman A
Nov 22 at 7:02
add a comment |
ROW_NUMBER requires a recent MySQL version, you must clarify what OP is using.
– Salman A
Nov 22 at 7:02
ROW_NUMBER requires a recent MySQL version, you must clarify what OP is using.
– Salman A
Nov 22 at 7:02
ROW_NUMBER requires a recent MySQL version, you must clarify what OP is using.
– Salman A
Nov 22 at 7:02
add a comment |
up vote
0
down vote
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 at 7:15
Salman A
174k66334421
add a comment |
up vote
0
down vote
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 at 7:15
Salman A
174k66334421
add a comment |
up vote
0
down vote
up vote
0
down vote
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 at 7:15
Salman A
174k66334421
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 at 7:15
Salman A
174k66334421
answered Nov 22 at 7:15
Salman A
174k66334421
answered Nov 22 at 7:15
Salman A
174k66334421
answered Nov 22 at 7:15
Salman A
174k66334421
174k66334421
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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 at 6:48
1
dont use image give it as text
– nikhil sugandh
Nov 22 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 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 at 7:06
What is the datatype for StartDate and EndDate columns ?
– Madhur Bhaiya
Nov 22 at 7:08