Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Need help in SQL Query Expand / Collapse
Author
Message
Posted Wednesday, October 9, 2013 7:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 13,325, Visits: 12,811
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 Moden's 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)
Post #1503117
Posted Wednesday, October 9, 2013 8:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 8, 2013 10:34 PM
Points: 33, Visits: 151
Hi Sean,

So for this time are these dats enough.
Post #1503125
Posted Wednesday, October 9, 2013 8:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 13,325, Visits: 12,811
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 Moden's 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)
Post #1503135
Posted Wednesday, October 9, 2013 8:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:41 AM
Points: 7,056, Visits: 7,280
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.

Post #1503145
Posted Wednesday, October 9, 2013 9:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 8, 2013 10:34 PM
Points: 33, Visits: 151
Hi Sean,

Can you please help me sort this.
Post #1503165
Posted Wednesday, October 9, 2013 9:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 13,325, Visits: 12,811
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 Moden's 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)
Post #1503175
Posted Wednesday, October 9, 2013 11:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 8, 2013 10:34 PM
Points: 33, Visits: 151
Hi Sean,

Thanks! Will get back to you with all sufficient datas.
Post #1503373
Posted Wednesday, October 9, 2013 11:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 8, 2013 10:34 PM
Points: 33, Visits: 151
Hi David,

Thanks a lot for your timely help on this.
Post #1503374
Posted Thursday, October 10, 2013 4:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 8, 2013 10:34 PM
Points: 33, Visits: 151
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.


  Post Attachments 
Table Query.docx (13 views, 40.74 KB)
Post #1503454
Posted Thursday, October 10, 2013 8:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 13,325, Visits: 12,811
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 Moden's 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)
Post #1503604
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse