Need to alter my code to show limited Info

  • 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

  • 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
  • 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'.

  • 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[/url] 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 (;) 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
  • 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!

  • 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • 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?....

  • 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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.

  • And My Thanks is to Luis C he was very helpful without being Condescending! 🙂

  • I agree with Sean, even if I don't know Erland, his reputation and answers in a short time in this site say a lot about him and his expertise and willingness to help.

    The three of us suggested you the same thing to get better help (Sean and I in an article and Erland with a direct post).

    I'm glad that you considered my posts as helpful, but I couldn't help you any further without more information.

    I hope that you continue to visit this site and learn as we all do in here. 😉

    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
  • he wasn't being snarky or anything like that.

    he was just trying to point out that you need to provide enough information for the people here to understand your problem (your table structures and some data). Without that, all people here can do is guess at a solution. On the other hand, if you provide enough information, you can get some great (and quick) answers.

    Basically, make it as easy as possible to understand and recreate your scenario. Then other people can see what you see. And then they can help. Without that, they really can't.

  • lisa.ogle (9/6/2013)


    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.

    No one is blaming you for not knowing SQL. These forums exists exactly for people who need help with SQL.

    However, remember that people here help you in their free time. We are not paid to help you, nor do you pay us. And in order to help you, we are very much reliant on the information you give us.

    And, it is very frustrating when you want to help someone, but the person only says "I still get an error", but do not show us the query, nor the error message.

    As for asking data as INSERT statement and not in Excel book. Sure, I can construct INSERT statements myself. But that's a kind of boring job, and since I do this for free, I rather concentrating on the fun part. And, I will have to admit, I do appreciate when the person asking for help also makes an effort.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Again I did provide the code I did provide the error and I did attach a sample of the data that was returned. Please remove me from your forum. My point was I did attach all that was requested and needed. Sorry that you did not see that I still see it myself in this thread. I thought it was rude and condescending for you to assume I thought you were Psychics-- I did not think that at all I merely asked for assistance and was dealt with what I thought and still do think was a sarcastic return. I was able to figure it out myself. Therefore I consider the matter closed I know not to ask anything on this forum again and in fact tried to disable my account feel free to do that for me if you like.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply