October 9, 2013 at 3:40 am
Hi,
I have a group called Leaders group. Users belonging to this Leaders group should be able to see only people information belonging to Leaders group. The following people belong to Leaders group[a,,c,d,e,f]. I need to display this for a SSRS report. Can anyone help me with the query.
October 9, 2013 at 3:52 am
Please provide the Sample data and the result how you expect?
October 9, 2013 at 4:32 am
Here is the one
User_Profile
User_Id User_FullName Email User_Group
Group_Lookup
LOB_GRP LOB_CD LOB_TYPE_CD
Impacted_LOB
Event_ID LOB_CD
First user log-ins which is queried in User_Profile table. Then I try to relate User_Group and LOB_GRP.
Then I have a group called CCSG Group in LOB_GRP column in Group_Lookup table. So now this CCSG Group has its respective LOB_CD in same table. Now I need to compare LOB_CD column of Group_Lookup and Impacted_LOB and display the result for that particular group.
October 9, 2013 at 6:35 am
Hi Parul,
Can you help me with this query.
October 9, 2013 at 7:05 am
Best guess
SELECT up.User_Id, up.User_FullName, up.Email, up.User_Group ,i.Event_ID
FROM User_Profile a
JOIN User_Profile up ON up.User_Group = a.User_Group
JOIN Group_Lookup g ON g.LOB_GRP = up.User_Group
JOIN Impacted_LOB i ON i.LOB_CD = g.LOB_CD
Really need test data and expect results to form a better solution.
Far away is close at hand in the images of elsewhere.
Anon.
October 9, 2013 at 7:33 am
Hi David,
Here is my exact one
Here is the one
USER_PRFL
User_Id User_FullName Email User_Group
BSP_LOB_Grp_Lookup
BSP_LOB_GRP BSP_LOB_CD BSP_LOB_GRP_TYPE_CD
IMPACTED_LOB
Event_ID BSP_LOB_CD
Scenario1:
First user log-ins which is queried in USER_PRFL table. Then I try to relate User_Group and BSP_LOB_GRP.
Now I have a value called CCSG Group in BSP_LOB_GRP column in BSP_LOB_Grp_Lookup table. So now this CCSG Group has its respective BSP_LOB_CD in same table. Now I need to compare BSP LOB_CD column of BSP_LOB_Grp_Lookup and IMPACTED_LOB and display the result for that particular group.
On top of this the following values (Life, MLCT, GenAm, NEF, Annuity, Auto and Home, Annuity and Investments) present in BSP_LOB_GRP should belong to CCSG Group of the sane column.
October 9, 2013 at 7:41 am
As David suggested earlier, in order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
October 9, 2013 at 7:48 am
Hi Sean,
I really appreciate it. I am new to this forum and new to SQL too and will do the one you tod me going forward. As of now this is all I have right now. Is there any way you can help me on this.
Moreover this is my original data that I have furnished here.
October 9, 2013 at 7:57 am
Hi,
I am attaching my sql table structure for a best solution.
October 9, 2013 at 7:57 am
vigneshlagoons (10/9/2013)
Hi Sean,I really appreciate it. I am new to this forum and new to SQL too and will do the one you tod me going forward. As of now this is all I have right now. Is there any way you can help me on this.
Moreover this is my original data that I have furnished here.
We are all willing and able to help but you have to help us by providing details to work with. I understand you are a bit overwhelmed since you are new to sql. That is ok, we can work with that.
There is no data here. All you provided was a few table names and the column names for those tables. We don't know what datatypes you are using and we have no idea what the data might look like. We aren't even really sure what you are trying to do. Once you provide the details for your question we can help you formulate an answer.
_______________________________________________________________
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/
October 9, 2013 at 7:59 am
vigneshlagoons (10/9/2013)
Hi,I am attaching my sql table structure for a best solution.
OK now we have some data. However it is not consumable. It would help your cause greatly if you provided this as inserts instead of screenshots in a Word doc.
_______________________________________________________________
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/
October 9, 2013 at 8:15 am
Hi Sean,
So for this time are these dats enough.
October 9, 2013 at 8:32 am
vigneshlagoons (10/9/2013)
Hi Sean,So for this time are these dats enough.
Here is the challenge. You are looking for help to your problem. From what you have posted it will take me an hour to put this together into something so I have your tables and data on my system. That means an hour of effort just to setup the problem. Keep in mind that you are the one getting paid for this, not me. We are all volunteers around here. Yes I could cobble together enough to put this together but I don't have an extra hour in my day to do the leg work. Maybe somebody else is bored and will step up to do this for you.
_______________________________________________________________
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/
October 9, 2013 at 8:43 am
Users have User_Group of BSP INC but there is no BSP_LOB_GRP entry with this value
BSP_LOB_Grp_Lookup has NULL in BSP_LOB_CD column so it cannot be matched to BSP_LOB_CD in IMPACTED_LOB table
This basic query will join the three tables as you specified but may not work due to the above.
SELECT up.User_Id, up.User_FullName, up.Email, up.User_Group ,i.Event_ID
FROM User_Profile up
JOIN Group_Lookup g ON g.LOB_GRP = up.User_Group
JOIN Impacted_LOB i ON i.LOB_CD = g.LOB_CD
Far away is close at hand in the images of elsewhere.
Anon.
October 9, 2013 at 9:13 am
Hi Sean,
Can you please help me sort this.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply