SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need to alter my code to show limited Info


Need to alter my code to show limited Info

Author
Message
lisa.ogle
lisa.ogle
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 22
I am trying to write a query to show by regions the top 4 languages spoken there. How can I write this?

Select Distinct
PD.ProviderId,
PD.FullName,
PL.LanguageDesc,
PR.RegionKey,
MD.OperationalMarket

From [dbo].[ProviderDim] PD
Join [dbo].[ProviderLanguagesXref]PL
On PD.ProviderID=PL.ProviderID
Join [dbo].[ProviderLobRegionXref] PR
On PD.ProviderKey=PR.ProviderKey
Join [dbo].[MarketLevelDim] MD
On PR.RegionKey=MD.OperationalRegionKey
Where PD.Active ='1'

Order by PD.ProviderId, MD.OperationalMarket
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16301 Visits: 19062
Without any more details on how the top 4 languages are defined or any sample data and expected results, this might give you a hint.
WITH Distinct_Data AS(
SELECT DISTINCT PD.ProviderId,
PD.FullName,
PL.LanguageDesc,
PR.RegionKey,
MD.OperationalMarket
FROM [dbo].[ProviderDim] PD
INNER JOIN [dbo].[ProviderLanguagesXref] PL ON PD.ProviderID = PL.ProviderID
INNER JOIN [dbo].[ProviderLobRegionXref] PR ON PD.ProviderKey = PR.ProviderKey
INNER JOIN [dbo].[MarketLevelDim] MD ON PR.RegionKey = MD.OperationalRegionKey
WHERE PD.Active = '1'
),
RowNo AS(
SELECT *, ROW_NUMBER() OVER( PARTITION BY PR.RegionKey ORDER BY MD.OperationalMarket) rn
FROM Distinct_Data
)
SELECT ProviderId,
FullName,
LanguageDesc,
RegionKey,
OperationalMarket
FROM RowNo
WHERE rn <= 4
ORDER BY ProviderId,
OperationalMarket



For better help, please follow the suggestions mentioned on the article linked in my signature. ;-)


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
lisa.ogle
lisa.ogle
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 22
thank you for the response I tried it and I get this Error Message:

Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '1'.
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16301 Visits: 19062
The first error message is giving you the solution to the problem.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.


This is a common table expression and it's a powerful tool that will help you in many scenarios. You can read more about them here and here. But feel free to explore for more information on the subject. And don't forget to ask any other specific questions you have.:-)

EDIT: I forgot to mention that you must terminate your previous statement with a semicolon (Wink and even try to terminate all of your statements with it to avoid future problems.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
lisa.ogle
lisa.ogle
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 22
I still cannot get it to work I used this I put the ; after my select statement before your with statement and still get an error. I have attached a sample of what the data looks like when pulled. Here is what I need to do. In the full data pull I have 8 Regions. I need to show the top 4 languages Doctors list as spoken for each Region. If my code is not correct and there is a better way to do this then please show me how I should be able to accomplish this.

Thank you!
Attachments
SAMPLE LANGUAGE DATA.xlsx (19 views, 9.00 KB)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25886 Visits: 17511
lisa.ogle (9/5/2013)
I still cannot get it to work I used this I put the ; after my select statement before your with statement and still get an error. I have attached a sample of what the data looks like when pulled. Here is what I need to do. In the full data pull I have 8 Regions. I need to show the top 4 languages Doctors list as spoken for each Region. If my code is not correct and there is a better way to do this then please show me how I should be able to accomplish this.

Thank you!


What version of SQL are you running?

I would suggest you take a few minutes and read the first article in my signature.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2097 Visits: 872
lisa.ogle (9/5/2013)
I still cannot get it to work I used this I put the ; after my select statement before your with statement and still get an error.


We don't see your query. We don't see your error messages. We are good at SQL here. But our psychic abilities are very poor.

Overall, if you want a tested query, you make yourself a great services if you post:

1) CREATE TABLE statements for all your tables.
2) INSERT statments with sample data, enough to illustrate the problem.
3) The desired result given the sample.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
lisa.ogle
lisa.ogle
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 22
If you looked at my earlier post- I posted my query I posted the error message and i attached a sample of my data on my last post to the person that was trying to help me Any good psychic would have noticed that. I have decided to use another option but thank you for your time. Good Day. Tell me can you tell what I am thinking?....
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25886 Visits: 17511
lisa.ogle (9/5/2013)
If you looked at my earlier post- I posted my query I posted the error message and i attached a sample of my data on my last post to the person that was trying to help me Any good psychic would have noticed that. I have decided to use another option but thank you for your time. Good Day. Tell me can you tell what I am thinking?....


I am guessing you have no idea who Erland is? Might want to google his name and read up about the ability of the person who was trying to help you. The list of things he stated that need to be posted to offer solid assistance is absolutely correct. I had suggested you look at the link in my signature would have told you the same thing.

I am glad you were able to figure out another way to solve your issue. Come back again if you need assistance.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
lisa.ogle
lisa.ogle
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 22
Thats fine but we all have to learn somewhere and Im guessing you all were not born with the ability to do SQL. I am thinking you too had to learn somewhere. Now that you are experts you seem to forget that. So when a person is asking a question of you on this site which I had assumed was a forum to do just that I don't think a sarcastic response is called for. I never assumed anyone was a psychic. But that's ok I now know this is not a site for helping someone that is not an expert and I will not ask another question on this site. Thank you for the assistance I did get tho.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search