October 9, 2013 at 9:42 am
vigneshlagoons (10/9/2013)
Hi Sean,Can you please help me sort this.
Sure as soon as you post everything in a consumable format. As I said previously, I am not going to spend an hour setting up the problem. I suspect that the query that David posted is almost exactly what you will end up with though.
_______________________________________________________________
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 11:42 pm
Hi Sean,
Thanks! Will get back to you with all sufficient datas.
October 9, 2013 at 11:47 pm
Hi David,
Thanks a lot for your timely help on this.
October 10, 2013 at 4:35 am
Hi Sean,
I added information in the attachment. Hope this helps to sort out. Thanking you in anticipation.
Query for Scenario1:
SELECT *
FROM USER_PRFL up
INNER JOIN BSP_LOB_Grp_Lookup bl
ON bl.BSP_LOB_GRP = up.User_Group
INNER JOIN IMPACTED_LOB il
ON il.BSP_LOB_CD = bl.BSP_LOB_CD
WHERE BSP_LOB_GRP = 'CCSG Group'
This query fetches value of CCSG Group, but there are some more groups present in BSP_LOB_GRP column like (Site Group, Sales Group, LOB Group) and these group should belong to CCSG group. So you can find more in the attachment.
October 10, 2013 at 8:13 am
Wow it still pretty tough to figure out what you want as output. I took the liberty of extracting your ddl and sample data here so others can find it without having to open a word doc.
CREATE TABLE [dbo].[USER_PRFL](
[User_Id] [varchar](30) NOT NULL,
[User_FullName] [varchar](50) NULL,
[Email_Address] [varchar](30) NULL,
[User_Group] [varchar](30) NULL,
CONSTRAINT [PK_USER] PRIMARY KEY CLUSTERED
(
[User_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into USER_PRFL values('Mike', 'Michael Vaughan', 'mike@ex.com','CCSG Group');
insert into USER_PRFL values('Adams', 'Paul Adams', 'adams@ex.com','Service Group');
insert into USER_PRFL values('Jim', 'Jimmy Corner', 'jim@ex.com','LOB Group');
insert into USER_PRFL values('Kate', 'Kate Larry', 'kate@ex.com','Site Group');
insert into USER_PRFL values('Russell', 'Russell Westbrook', 'russell@ex.com','Sales Group');
CREATE TABLE [dbo].[BSP_LOB_Grp_Lookup](
[BSP_LOB_GRP] [varchar](20) NOT NULL,
[BSP_LOB_CD] [char](3) NULL,
[BSP_LOB_GRP_TYPE_CD] [char](3) NULL
) ON [PRIMARY]
GO
insert into BSP_LOB_Grp_Lookup values ('CCSG Group', 'CG','CGP')
insert into BSP_LOB_Grp_Lookup values ('Service Group', 'SG','SGP')
insert into BSP_LOB_Grp_Lookup values ('LOB Group', 'LG','LGP')
insert into BSP_LOB_Grp_Lookup values ('Site Group', 'SIG','LGP')
insert into BSP_LOB_Grp_Lookup values ('Sales Group', 'SAG','LGP')
CREATE TABLE [dbo].[IMPACTED_LOB](
[Event_ID] [int] NOT NULL,
[BSP_LOB_CD] [char](3) NOT NULL,
CONSTRAINT [PK_IMPACTED_LOB] PRIMARY KEY CLUSTERED
(
[Event_ID] ASC,
[BSP_LOB_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into IMPACTED_LOB values('1', 'CG')
insert into IMPACTED_LOB values('2', 'SG')
insert into IMPACTED_LOB values('3', 'SG')
insert into IMPACTED_LOB values('4', 'LG')
insert into IMPACTED_LOB values('5', 'CG')
insert into IMPACTED_LOB values('6', 'LG')
insert into IMPACTED_LOB values('7', 'SG')
insert into IMPACTED_LOB values('8', 'LG')
insert into IMPACTED_LOB values('9', 'CG')
insert into IMPACTED_LOB values('10', 'SIG')
insert into IMPACTED_LOB values('11', 'SAG')
insert into IMPACTED_LOB values('12', 'SAG')
insert into IMPACTED_LOB values('13', 'SIG')
As I stated I really don't know what you want for output but something like this might get you started?
select *
from USER_PRFL p
join BSP_LOB_Grp_Lookup gl on gl.BSP_LOB_GRP = p.User_Group
join IMPACTED_LOB l on l.BSP_LOB_CD = gl.BSP_LOB_CD
_______________________________________________________________
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 10, 2013 at 8:22 am
This query fetches value of CCSG Group, but there are some more groups present in BSP_LOB_GRP column like (Site Group, Sales Group, LOB Group) and these group should belong to CCSG group. So you can find more in the attachment.
Based on this, it looks like your current tables are insufficient to do the query without hardcoding some business logic.
You say that (Site Group, Sales Group, LOB Group) BELONG to CCSG group. How could we tell that from the data? By BSP_LOB_GRP_TYPE_CD? Is there a table that describes the hierarchy?
October 10, 2013 at 9:23 am
Hi Nevyn,
Site Group, Sales Group, LOB Group and CCSG Group are values present in BSP_LOB_GRP column.
If a user is logged in as a CCSG Group members he should also be able to see(LOB Group, Site Group, Service Group)values too.
October 10, 2013 at 9:24 am
Hi Sean,
This is just fine. Now on top of this. I just want to display LOB Group, Site Group, Service Group for CCSG Group.
EX: If Mike log in, he belongs to CCSG Group and we fetch the respective BSP_LOB_CD from BSP_LOB_Grp_Lookup and IMPACTED_LOB table.
Now mike who belong to CCSG Group should be able to see these group(LOB Group, Site Group, Service Group)values too. This is the one I am looking for.
October 10, 2013 at 9:31 am
vigneshlagoons 51204 (10/10/2013)
Hi Sean,This is just fine. Now on top of this. I just want to display LOB Group, Site Group, Service Group for CCSG Group.
EX: If Mike log in, he belongs to CCSG Group and we fetch the respective BSP_LOB_CD from BSP_LOB_Grp_Lookup and IMPACTED_LOB table.
Now mike who belong to CCSG Group should be able to see these group(LOB Group, Site Group, Service Group)values too. This is the one I am looking for.
Why should he see those other groups? There is nothing in your data that suggests this person should see all the groups?
_______________________________________________________________
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 10, 2013 at 9:46 am
Hi Sean,
I am doing this for SSRS reports. I mean its like when user logs in as CCSG Group these group values should also show up(Site Group, Sales Group, LOB Group) in the report generated. Let me know if you need more info.
October 10, 2013 at 9:58 am
vigneshlagoons 51204 (10/10/2013)
Hi Sean,I am doing this for SSRS reports. I mean its like when user logs in as CCSG Group these group values should also show up(Site Group, Sales Group, LOB Group) in the report generated. Let me know if you need more info.
Not totally sure what you mean but maybe something like this helps?
select *
from USER_PRFL p
join BSP_LOB_Grp_Lookup gl on gl.BSP_LOB_GRP = p.User_Group OR p.User_Group = 'CCSG Group'
join IMPACTED_LOB l on l.BSP_LOB_CD = gl.BSP_LOB_CD
where p.User_Id = 'Mike'
_______________________________________________________________
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 10, 2013 at 10:09 am
Hi Sean,
I need to display values of Site Group, LOB Group, Sales group like we displayed for CCSG Group in the same query. May be a sub-query?
Mike logs in as CCSG Group and we display CCGS Group values, just on top of this we need to display the mentioned group values too. Hope you got it.
October 10, 2013 at 11:05 am
vigneshlagoons 51204 (10/10/2013)
Hi Nevyn,Site Group, Sales Group, LOB Group and CCSG Group are values present in BSP_LOB_GRP column.
If a user is logged in as a CCSG Group members he should also be able to see(LOB Group, Site Group, Service Group)values too.
Is there anything in the tables to tell you that a CCSG Group user should be able to see those other groups?
October 10, 2013 at 11:24 am
Try:
WITH CCSGLinks AS (
SELECT [BSP_LOB_GRP],
[BSP_LOB_CD],
[BSP_LOB_GRP_TYPE_CD]
CASE WHEN BSP_LOB_GRP IN ('LOB Group','Site Group','Sales Group') THEN 'CCSG Group' ELSE BSP_LOB_GRP END LinkedGroup
)
select *
from USER_PRFL p
join CCSGLinks gl on gl.LinkedGroup = p.[User_Group] or gl.[BSP_LOB_GRP] = p.[User_Group]
join IMPACTED_LOB l on l.BSP_LOB_CD = gl.BSP_LOB_CD
And let me know if its what you want
Note: not tested with DDL as I don't have access to sql server until later tonight.
Other Note: if this is what's required, you've got a table design issue
October 10, 2013 at 8:42 pm
So table design issue because of bringing the group values on top of CCSG Group?
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply