MS SQL store multiple result in variables
|
|
 Jul 9 2014, 03:08 AM
Return to original view | Post
#1
|
 
Senior Member
868 posts Joined: Sep 2009 From: douchistan, pekopon  
|
QUOTE(Xaxas @ Jul 9 2014, 02:48 AM) Once I've gotten the country name, I'm planning to filter the location based on the most selected. How bout this ? CODE select country,location,attraction from Itinerary where schedule between '2014-08-01' and '2014-08-20' group by country,location,attraction having sum(participant) > 20 |
|
|
|
|
|
Jul 9 2014, 03:37 AM
Return to original view | Post
#2
|
|
Senior Member
868 posts Joined: Sep 2009 From: douchistan, pekopon
|
QUOTE(Xaxas @ Jul 9 2014, 03:19 AM) Hmmm it doesn't work as location and attraction has different values in it. I'll probably have to make my scope smaller by letting the user selects only one location. can try the following query ?not the most efficient one. what's the dataset that you have ? CODE SET ROWCOUNT 1 select country,location,attraction from Itinerary where schedule between '2014-08-01' and '2014-08-20' where country = <the country you wanna select> group by country,location,attraction having sum(participant) > 20 order by sum(participant) desc |
|
|
Jul 9 2014, 04:30 AM
Return to original view | Post
#3
|
|
Senior Member
868 posts Joined: Sep 2009 From: douchistan, pekopon
|
QUOTE(Xaxas @ Jul 9 2014, 04:20 AM) The data from the table in the first post. Was planning to create a procedure for it to run in asp.net when a page load or at a given time without having to select a country. what is your intended output ? can you draft a sample output that you expect ? |
|
|
Jul 9 2014, 05:00 AM
Return to original view | Post
#4
|
|
Senior Member
868 posts Joined: Sep 2009 From: douchistan, pekopon
|
QUOTE(Xaxas @ Jul 9 2014, 04:45 AM) I would like the result to show up as Location Perak after checking for the country, schedule and participant. In this case, it should be itineraryID 1/3/4 with location showing only Perak. Basically it suppose to produce an output of a similar result where all three id have it, which is Perak. Depending on your dataset, you will have to use wildcard to get the location. Just need to be wary of sql injection though.Try this out: CODE DECLARE @country VARCHAR(50) DECLARE @location VARCHAR(50) SET @country = 'Malaysia' SET @location = '%Perak%' SELECT itineraryID,,country,location,schedule,participant FROM Itinerary WHERE Country = @Country AND Location LIKE @Location |
|
|
Jul 9 2014, 05:46 AM
Return to original view | Post
#5
|
|
Senior Member
868 posts Joined: Sep 2009 From: douchistan, pekopon
|
QUOTE(Xaxas @ Jul 9 2014, 05:20 AM) Is it possible to like use a while loop to check if all the result shown have the same location without having to specify it? Don't get what you are trying to do here ?Perhaps you can do it from the application end. The only loop I know in SQL is thru the use of CURSOR, but again, I am not sure what you are actually trying to achieve here  |
|
|
Jul 9 2014, 07:02 AM
Return to original view | Post
#6
|
|
Senior Member
868 posts Joined: Sep 2009 From: douchistan, pekopon
|
QUOTE(Xaxas @ Jul 9 2014, 06:29 AM) Well it's okay, thanks for the help though. I'll probably change the data to all have only one single location. I was actually trying to generate from the database the most chosen location based on the country selected, the schedule time and the no. participant. That can be done as long as you are clear on what you wanted to achieve.I try to do most of it in SQL if possible. Anyway, the choice is yours. |
|
|
Jul 9 2014, 10:30 PM
Return to original view | Post
#7
|
|
Senior Member
868 posts Joined: Sep 2009 From: douchistan, pekopon
|
QUOTE(Xaxas @ Jul 9 2014, 12:42 PM) Haha okay, but partly because of time constraint, I'll have to make it a bit simpler but still show what it is suppose to do. Although it isn't a complete product of what I want to do wookiee ... all the best ! |
| Change to: |  0.0153sec
 0.56
 6 queries
 GZIP Disabled
Time is now: 18th December 2025 - 04:04 AM |
Quote