How to get earliest of one column and get latest of another column in MSSQL

up vote
-1
down vote

favorite

When I run this query :

select * from customertravel where PersonID in (1,2,7)

I get this :

enter image description here

But I want to filter results to be like this :

enter image description here

How should I modify that query to get first arrive date and last leave date? I know I need to do some group by but I couldn't figure how to use it.. Thnkas in advance..

edited Nov 10 at 15:35

Salman A

171k65328413

asked Nov 6 at 14:10

jason

1,5352168120

add a comment |

up vote
-1
down vote

favorite

When I run this query :

select * from customertravel where PersonID in (1,2,7)

I get this :

enter image description here

But I want to filter results to be like this :

enter image description here

How should I modify that query to get first arrive date and last leave date? I know I need to do some group by but I couldn't figure how to use it.. Thnkas in advance..

edited Nov 10 at 15:35

Salman A

171k65328413

asked Nov 6 at 14:10

jason

1,5352168120

add a comment |

up vote
-1
down vote

favorite

When I run this query :

select * from customertravel where PersonID in (1,2,7)

I get this :

enter image description here

But I want to filter results to be like this :

enter image description here

How should I modify that query to get first arrive date and last leave date? I know I need to do some group by but I couldn't figure how to use it.. Thnkas in advance..

edited Nov 10 at 15:35

Salman A

171k65328413

asked Nov 6 at 14:10

jason

1,5352168120

When I run this query :

select * from customertravel where PersonID in (1,2,7)

I get this :

enter image description here

But I want to filter results to be like this :

enter image description here

How should I modify that query to get first arrive date and last leave date? I know I need to do some group by but I couldn't figure how to use it.. Thnkas in advance..

sql sql-server datetime group-by

edited Nov 10 at 15:35

Salman A

171k65328413

asked Nov 6 at 14:10

jason

1,5352168120

edited Nov 10 at 15:35

Salman A

171k65328413

asked Nov 6 at 14:10

jason

1,5352168120

edited Nov 10 at 15:35

Salman A

171k65328413

edited Nov 10 at 15:35

Salman A

171k65328413

edited Nov 10 at 15:35

Salman A

171k65328413

asked Nov 6 at 14:10

jason

1,5352168120

asked Nov 6 at 14:10

jason

1,5352168120

asked Nov 6 at 14:10

jason

1,5352168120

add a comment |

2 Answers
2

active

oldest

votes

up vote
4
down vote

accepted

Supposing your ArriveDate and LeaveDate are dates and not strings or so, you could try the following:

SELECT PersonID,
 Name,
 City,
 MIN(ArriveDate),
 MAX(LeaveDate)
FROM customertravel
WHERE PersonID IN (1, 2, 7)
GROUP BY PersonID,
 Name,
 City;

answered Nov 6 at 14:24

Robert Kock

3,6171616

add a comment |

up vote
2
down vote

You need to group by all columns except the dates, and find MIN and MAX. Just in case your dates are in German format you need to convert them before calculating aggregate:

SELECT
 PersonID,
 Name,
 City,
 MIN(CONVERT(DATE, ArriveDate, 104)) AS FirstArriveDate,
 MAX(CONVERT(DATE, LeaveDate, 104)) AS LastLeaveDate
FROM customertravel
WHERE PersonID IN (1, 2, 7)
GROUP BY PersonID, Name, City

answered Nov 6 at 14:37

Salman A

171k65328413

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

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53173606%2fhow-to-get-earliest-of-one-column-and-get-latest-of-another-column-in-mssql%23new-answer', 'question_page');

);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
4
down vote

accepted

Supposing your ArriveDate and LeaveDate are dates and not strings or so, you could try the following:

SELECT PersonID,
 Name,
 City,
 MIN(ArriveDate),
 MAX(LeaveDate)
FROM customertravel
WHERE PersonID IN (1, 2, 7)
GROUP BY PersonID,
 Name,
 City;

answered Nov 6 at 14:24

Robert Kock

3,6171616

add a comment |

up vote
4
down vote

accepted

Supposing your ArriveDate and LeaveDate are dates and not strings or so, you could try the following:

SELECT PersonID,
 Name,
 City,
 MIN(ArriveDate),
 MAX(LeaveDate)
FROM customertravel
WHERE PersonID IN (1, 2, 7)
GROUP BY PersonID,
 Name,
 City;

answered Nov 6 at 14:24

Robert Kock

3,6171616

add a comment |

up vote
4
down vote

accepted

Supposing your ArriveDate and LeaveDate are dates and not strings or so, you could try the following:

SELECT PersonID,
 Name,
 City,
 MIN(ArriveDate),
 MAX(LeaveDate)
FROM customertravel
WHERE PersonID IN (1, 2, 7)
GROUP BY PersonID,
 Name,
 City;

answered Nov 6 at 14:24

Robert Kock

3,6171616

Supposing your ArriveDate and LeaveDate are dates and not strings or so, you could try the following:

SELECT PersonID,
 Name,
 City,
 MIN(ArriveDate),
 MAX(LeaveDate)
FROM customertravel
WHERE PersonID IN (1, 2, 7)
GROUP BY PersonID,
 Name,
 City;

answered Nov 6 at 14:24

Robert Kock

3,6171616

answered Nov 6 at 14:24

Robert Kock

3,6171616

answered Nov 6 at 14:24

Robert Kock

3,6171616

answered Nov 6 at 14:24

Robert Kock

3,6171616

add a comment |

up vote
2
down vote

You need to group by all columns except the dates, and find MIN and MAX. Just in case your dates are in German format you need to convert them before calculating aggregate:

SELECT
 PersonID,
 Name,
 City,
 MIN(CONVERT(DATE, ArriveDate, 104)) AS FirstArriveDate,
 MAX(CONVERT(DATE, LeaveDate, 104)) AS LastLeaveDate
FROM customertravel
WHERE PersonID IN (1, 2, 7)
GROUP BY PersonID, Name, City

answered Nov 6 at 14:37

Salman A

171k65328413

add a comment |

up vote
2
down vote

You need to group by all columns except the dates, and find MIN and MAX. Just in case your dates are in German format you need to convert them before calculating aggregate:

SELECT
 PersonID,
 Name,
 City,
 MIN(CONVERT(DATE, ArriveDate, 104)) AS FirstArriveDate,
 MAX(CONVERT(DATE, LeaveDate, 104)) AS LastLeaveDate
FROM customertravel
WHERE PersonID IN (1, 2, 7)
GROUP BY PersonID, Name, City

answered Nov 6 at 14:37

Salman A

171k65328413

add a comment |

up vote
2
down vote

You need to group by all columns except the dates, and find MIN and MAX. Just in case your dates are in German format you need to convert them before calculating aggregate:

SELECT
 PersonID,
 Name,
 City,
 MIN(CONVERT(DATE, ArriveDate, 104)) AS FirstArriveDate,
 MAX(CONVERT(DATE, LeaveDate, 104)) AS LastLeaveDate
FROM customertravel
WHERE PersonID IN (1, 2, 7)
GROUP BY PersonID, Name, City

answered Nov 6 at 14:37

Salman A

171k65328413

You need to group by all columns except the dates, and find MIN and MAX. Just in case your dates are in German format you need to convert them before calculating aggregate:

SELECT
 PersonID,
 Name,
 City,
 MIN(CONVERT(DATE, ArriveDate, 104)) AS FirstArriveDate,
 MAX(CONVERT(DATE, LeaveDate, 104)) AS LastLeaveDate
FROM customertravel
WHERE PersonID IN (1, 2, 7)
GROUP BY PersonID, Name, City

answered Nov 6 at 14:37

Salman A

171k65328413

answered Nov 6 at 14:37

Salman A

171k65328413

answered Nov 6 at 14:37

Salman A

171k65328413

answered Nov 6 at 14:37

Salman A

171k65328413

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Myujth