how to Get my result according to picture

I have below query I want to show data fromCityId AND ToCityId . Suppose passenger travel fromCity London toCity Manchester. How do I write query like this, when I use where clause and in, its show me same values in fromcity and tocity
Expected results are in below Picture

      Select vh.VoucharId,fCity.CityName as FromCity, tCity.CityName as ToCity, InDate 

from VoucharHotel vh  

inner join City fCity on   vh.CityId = fCity.CityId  inner join City tCity on

 vh.CityId = tCity.CityId 

 where vh.InDate  between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM'   AND vh.CityId in (1,2)

enter image description here

  CREATE TABLE VoucharHotel (

        ID int IDENTITY(1,1) PRIMARY KEY,

        VoucharId Int ,

        CityId int,

        HotelId  int,

        InDate Datetime,

        OutDate Datetime

    );



    CREATE TABLE City (

        CityId int IDENTITY(1,1) PRIMARY KEY,

        CityName varchar(200),

    );

    insert into City Values('London')

    insert into City Values('Manchester')

    insert into City Values('Birmingham')

    insert into City Values('Leeds')



    CREATE TABLE HotelMaster (

        HotelId int IDENTITY(1,1) PRIMARY KEY,

        HotelName varchar(200),

    );

    insert into HotelMaster Values('London Hotel')

    insert into HotelMaster Values('Manchester Hotel')

    insert into HotelMaster Values('Birmingham Hotel')

    insert into HotelMaster Values('Leeds Hotel')



    Insert into VoucharHotel Values(22,1,1,'11/15/2018', '11/16/2018')

    Insert into VoucharHotel Values(22,2,2,'11/16/2018', '11/18/2018')

    Insert into VoucharHotel Values(22,1,1,'11/18/2018', '11/20/2018')



    Insert into VoucharHotel Values(23,2,2,'11/16/2018', '11/17/2018')

    Insert into VoucharHotel Values(23,4,4,'11/17/2018', '11/20/2018')

    Insert into VoucharHotel Values(23,2,2,'11/20/2018', '11/26/2018')

edited Nov 21 '18 at 8:31

asked Nov 21 '18 at 8:09

JOhns

529

I didn't see any VoucharId 24 in your sample data

– Squirrel
Nov 21 '18 at 8:12

its example picture

– JOhns
Nov 21 '18 at 8:15

how to get data like above picture what i want

– JOhns
Nov 21 '18 at 8:15

do you need max voucher id for same fromCity,toCity and inDate?

– Kiran
Nov 21 '18 at 8:34

i want user from to city user travel

– JOhns
Nov 21 '18 at 8:43

add a comment |

      Select vh.VoucharId,fCity.CityName as FromCity, tCity.CityName as ToCity, InDate 

from VoucharHotel vh  

inner join City fCity on   vh.CityId = fCity.CityId  inner join City tCity on

 vh.CityId = tCity.CityId 

 where vh.InDate  between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM'   AND vh.CityId in (1,2)

enter image description here

  CREATE TABLE VoucharHotel (

        ID int IDENTITY(1,1) PRIMARY KEY,

        VoucharId Int ,

        CityId int,

        HotelId  int,

        InDate Datetime,

        OutDate Datetime

    );



    CREATE TABLE City (

        CityId int IDENTITY(1,1) PRIMARY KEY,

        CityName varchar(200),

    );

    insert into City Values('London')

    insert into City Values('Manchester')

    insert into City Values('Birmingham')

    insert into City Values('Leeds')



    CREATE TABLE HotelMaster (

        HotelId int IDENTITY(1,1) PRIMARY KEY,

        HotelName varchar(200),

    );

    insert into HotelMaster Values('London Hotel')

    insert into HotelMaster Values('Manchester Hotel')

    insert into HotelMaster Values('Birmingham Hotel')

    insert into HotelMaster Values('Leeds Hotel')



    Insert into VoucharHotel Values(22,1,1,'11/15/2018', '11/16/2018')

    Insert into VoucharHotel Values(22,2,2,'11/16/2018', '11/18/2018')

    Insert into VoucharHotel Values(22,1,1,'11/18/2018', '11/20/2018')



    Insert into VoucharHotel Values(23,2,2,'11/16/2018', '11/17/2018')

    Insert into VoucharHotel Values(23,4,4,'11/17/2018', '11/20/2018')

    Insert into VoucharHotel Values(23,2,2,'11/20/2018', '11/26/2018')

edited Nov 21 '18 at 8:31

asked Nov 21 '18 at 8:09

JOhns

529

I didn't see any VoucharId 24 in your sample data

– Squirrel
Nov 21 '18 at 8:12

its example picture

– JOhns
Nov 21 '18 at 8:15

how to get data like above picture what i want

– JOhns
Nov 21 '18 at 8:15

do you need max voucher id for same fromCity,toCity and inDate?

– Kiran
Nov 21 '18 at 8:34

i want user from to city user travel

– JOhns
Nov 21 '18 at 8:43

add a comment |

      Select vh.VoucharId,fCity.CityName as FromCity, tCity.CityName as ToCity, InDate 

from VoucharHotel vh  

inner join City fCity on   vh.CityId = fCity.CityId  inner join City tCity on

 vh.CityId = tCity.CityId 

 where vh.InDate  between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM'   AND vh.CityId in (1,2)

enter image description here

  CREATE TABLE VoucharHotel (

        ID int IDENTITY(1,1) PRIMARY KEY,

        VoucharId Int ,

        CityId int,

        HotelId  int,

        InDate Datetime,

        OutDate Datetime

    );



    CREATE TABLE City (

        CityId int IDENTITY(1,1) PRIMARY KEY,

        CityName varchar(200),

    );

    insert into City Values('London')

    insert into City Values('Manchester')

    insert into City Values('Birmingham')

    insert into City Values('Leeds')



    CREATE TABLE HotelMaster (

        HotelId int IDENTITY(1,1) PRIMARY KEY,

        HotelName varchar(200),

    );

    insert into HotelMaster Values('London Hotel')

    insert into HotelMaster Values('Manchester Hotel')

    insert into HotelMaster Values('Birmingham Hotel')

    insert into HotelMaster Values('Leeds Hotel')



    Insert into VoucharHotel Values(22,1,1,'11/15/2018', '11/16/2018')

    Insert into VoucharHotel Values(22,2,2,'11/16/2018', '11/18/2018')

    Insert into VoucharHotel Values(22,1,1,'11/18/2018', '11/20/2018')



    Insert into VoucharHotel Values(23,2,2,'11/16/2018', '11/17/2018')

    Insert into VoucharHotel Values(23,4,4,'11/17/2018', '11/20/2018')

    Insert into VoucharHotel Values(23,2,2,'11/20/2018', '11/26/2018')

edited Nov 21 '18 at 8:31

asked Nov 21 '18 at 8:09

JOhns

529

      Select vh.VoucharId,fCity.CityName as FromCity, tCity.CityName as ToCity, InDate 

from VoucharHotel vh  

inner join City fCity on   vh.CityId = fCity.CityId  inner join City tCity on

 vh.CityId = tCity.CityId 

 where vh.InDate  between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM'   AND vh.CityId in (1,2)

enter image description here

  CREATE TABLE VoucharHotel (

        ID int IDENTITY(1,1) PRIMARY KEY,

        VoucharId Int ,

        CityId int,

        HotelId  int,

        InDate Datetime,

        OutDate Datetime

    );



    CREATE TABLE City (

        CityId int IDENTITY(1,1) PRIMARY KEY,

        CityName varchar(200),

    );

    insert into City Values('London')

    insert into City Values('Manchester')

    insert into City Values('Birmingham')

    insert into City Values('Leeds')



    CREATE TABLE HotelMaster (

        HotelId int IDENTITY(1,1) PRIMARY KEY,

        HotelName varchar(200),

    );

    insert into HotelMaster Values('London Hotel')

    insert into HotelMaster Values('Manchester Hotel')

    insert into HotelMaster Values('Birmingham Hotel')

    insert into HotelMaster Values('Leeds Hotel')



    Insert into VoucharHotel Values(22,1,1,'11/15/2018', '11/16/2018')

    Insert into VoucharHotel Values(22,2,2,'11/16/2018', '11/18/2018')

    Insert into VoucharHotel Values(22,1,1,'11/18/2018', '11/20/2018')



    Insert into VoucharHotel Values(23,2,2,'11/16/2018', '11/17/2018')

    Insert into VoucharHotel Values(23,4,4,'11/17/2018', '11/20/2018')

    Insert into VoucharHotel Values(23,2,2,'11/20/2018', '11/26/2018')

sql-server sql-server-2008 sql-server-2012 sql-server-2014

edited Nov 21 '18 at 8:31

asked Nov 21 '18 at 8:09

JOhns

529

edited Nov 21 '18 at 8:31

asked Nov 21 '18 at 8:09

JOhns

529

edited Nov 21 '18 at 8:31

asked Nov 21 '18 at 8:09

JOhns

529

asked Nov 21 '18 at 8:09

JOhns

529

asked Nov 21 '18 at 8:09

JOhns

529

I didn't see any VoucharId 24 in your sample data

– Squirrel
Nov 21 '18 at 8:12

its example picture

– JOhns
Nov 21 '18 at 8:15

how to get data like above picture what i want

– JOhns
Nov 21 '18 at 8:15

do you need max voucher id for same fromCity,toCity and inDate?

– Kiran
Nov 21 '18 at 8:34

i want user from to city user travel

– JOhns
Nov 21 '18 at 8:43

add a comment |

I didn't see any VoucharId 24 in your sample data

– Squirrel
Nov 21 '18 at 8:12

its example picture

– JOhns
Nov 21 '18 at 8:15

how to get data like above picture what i want

– JOhns
Nov 21 '18 at 8:15

do you need max voucher id for same fromCity,toCity and inDate?

– Kiran
Nov 21 '18 at 8:34

i want user from to city user travel

– JOhns
Nov 21 '18 at 8:43

I didn't see any VoucharId 24 in your sample data

– Squirrel
Nov 21 '18 at 8:12

its example picture

– JOhns
Nov 21 '18 at 8:15

how to get data like above picture what i want

– JOhns
Nov 21 '18 at 8:15

do you need max voucher id for same fromCity,toCity and inDate?

– Kiran
Nov 21 '18 at 8:34

i want user from to city user travel

– JOhns
Nov 21 '18 at 8:43

add a comment |

1 Answer
1

active

oldest

votes

your starting table should be a join on the table VoucharHotel like:

Select V1.VoucharId, V1.CityId, V2.HotelId  ,V1.InDate

from  VoucharHotel  V1

join VoucharHotel V2 on V1.VoucharId  = V2.VoucharId  and convert(date,V1.OutDate) = dateadd(day,-1,convert(date,V2.InDate ) )

From here just add joins to City Table and filter on the date you want

answered Nov 21 '18 at 8:20

picklerick

32918

@JOhns did you solve it so?

– picklerick
Nov 22 '18 at 11:51

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');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53407655%2fhow-to-get-my-result-according-to-picture%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

your starting table should be a join on the table VoucharHotel like:

Select V1.VoucharId, V1.CityId, V2.HotelId  ,V1.InDate

from  VoucharHotel  V1

join VoucharHotel V2 on V1.VoucharId  = V2.VoucharId  and convert(date,V1.OutDate) = dateadd(day,-1,convert(date,V2.InDate ) )

From here just add joins to City Table and filter on the date you want

answered Nov 21 '18 at 8:20

picklerick

32918

@JOhns did you solve it so?

– picklerick
Nov 22 '18 at 11:51

add a comment |

your starting table should be a join on the table VoucharHotel like:

Select V1.VoucharId, V1.CityId, V2.HotelId  ,V1.InDate

from  VoucharHotel  V1

join VoucharHotel V2 on V1.VoucharId  = V2.VoucharId  and convert(date,V1.OutDate) = dateadd(day,-1,convert(date,V2.InDate ) )

From here just add joins to City Table and filter on the date you want

answered Nov 21 '18 at 8:20

picklerick

32918

@JOhns did you solve it so?

– picklerick
Nov 22 '18 at 11:51

add a comment |

your starting table should be a join on the table VoucharHotel like:

Select V1.VoucharId, V1.CityId, V2.HotelId  ,V1.InDate

from  VoucharHotel  V1

join VoucharHotel V2 on V1.VoucharId  = V2.VoucharId  and convert(date,V1.OutDate) = dateadd(day,-1,convert(date,V2.InDate ) )

From here just add joins to City Table and filter on the date you want

answered Nov 21 '18 at 8:20

picklerick

32918

your starting table should be a join on the table VoucharHotel like:

Select V1.VoucharId, V1.CityId, V2.HotelId  ,V1.InDate

from  VoucharHotel  V1

join VoucharHotel V2 on V1.VoucharId  = V2.VoucharId  and convert(date,V1.OutDate) = dateadd(day,-1,convert(date,V2.InDate ) )

From here just add joins to City Table and filter on the date you want

answered Nov 21 '18 at 8:20

picklerick

32918

answered Nov 21 '18 at 8:20

picklerick

32918

answered Nov 21 '18 at 8:20

picklerick

32918

answered Nov 21 '18 at 8:20

picklerick

32918

@JOhns did you solve it so?

– picklerick
Nov 22 '18 at 11:51

add a comment |

@JOhns did you solve it so?

– picklerick
Nov 22 '18 at 11:51

@JOhns did you solve it so?

– picklerick
Nov 22 '18 at 11:51

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

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

Search This Blog

Ufyukyu