January 20, 2015 at 1:06 am
I Have 2 Different Table As Follows
Table 1 - Person
With Columns PersonID,PersonGrp,PersonName
1,'',Person1
2,Grp1,Person2
3,Grp1,Person3
4,Grp2,Person4
5,Grp2,Person5
6,'',Person6
7,'',Person7
Table 2 - PersonMap
With Columns MapID,MapDepartment,MapGrp,MapPerID,DateFrom,DateTo
1,'ADMIN',,1,'01-JAN-2007',''
2,'MGMT',Grp1,,'01-JAN-2007','31-DEC-2008'
3,'MGMT',Grp2,,'01-JAN-2009',''
4,'OTHER',,,'01-JAN-2007',''
Required Output
PersonID,MapID,MapDepartment,DateFrom,DateTo
1,1,'ADMIN','01-JAN-2007',''
2,2,'MGMT','01-JAN-2007','31-DEC-2008'
2,4,'OTHER','01-JAN-2009',''
3,2,'MGMT','01-JAN-2007','31-DEC-2008'
3,4,'OTHER','01-JAN-2009',''
4,2,'MGMT','01-JAN-2009',''
5,2,'MGMT','01-JAN-2009',''
6,4,'OTHER','01-JAN-2007',''
7,4,'OTHER','01-JAN-2007',''
Please Help With Query To Achieve Them Same
WithOut Temp Shifting and With Temp Shifting
Although I Have Done The Above Result With Temp Shifting
But Would Require Better Ways For Same
January 20, 2015 at 1:13 am
Jay Sapani (1/20/2015)
I Have 2 Different Table As FollowsTable 1 - Person
With Columns PersonID,PersonGrp,PersonName
1,'',Person1
2,Grp1,Person2
3,Grp1,Person3
4,Grp2,Person4
5,Grp2,Person5
6,'',Person6
7,'',Person7
Table 2 - PersonMap
With Columns MapID,MapDepartment,MapGrp,MapPerID,DateFrom,DateTo
1,'ADMIN',,1,'01-JAN-2007',''
2,'MGMT',Grp1,,'01-JAN-2007','31-DEC-2008'
3,'MGMT',Grp2,,'01-JAN-2009',''
4,'OTHER',,,'01-JAN-2007',''
Required Output
PersonID,MapID,MapDepartment,DateFrom,DateTo
1,1,'ADMIN','01-JAN-2007',''
2,2,'MGMT','01-JAN-2007','31-DEC-2008'
2,4,'OTHER','01-JAN-2009',''
3,2,'MGMT','01-JAN-2007','31-DEC-2008'
3,4,'OTHER','01-JAN-2009',''
4,2,'MGMT','01-JAN-2009',''
5,2,'MGMT','01-JAN-2009',''
6,4,'OTHER','01-JAN-2007',''
7,4,'OTHER','01-JAN-2007',''
Please Help With Query To Achieve Them Same
WithOut Temp Shifting and With Temp Shifting
Although I Have Done The Above Result With Temp Shifting
But Would Require Better Ways For Same
Please define how the tables are related.
Can you also explain what you mean by 'temp shifting'?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 20, 2015 at 4:26 am
The Tables Are Related To Each Other As 1st Table Has List Of Employee And Other The Department In Which They Work.MapPerID = PersonID Of First Table.MapGrp = PersonGrp Of 1st Table.
The Output Required Is Employee And List Of Departments In Which He Has Worked Or Is Working
By Temp Shifting I Mean I Created A Temp Table In Between To Get The Desired Results.
January 20, 2015 at 4:35 am
It's just a simple join you need, isn't it? Please will you show us what you've already tried, so that we can show you where you're going wrong?
John
January 20, 2015 at 4:54 am
Jay Sapani (1/20/2015)
The Tables Are Related To Each Other As 1st Table Has List Of Employee And Other The Department In Which They Work.MapPerID = PersonID Of First Table.MapGrp = PersonGrp Of 1st Table.The Output Required Is Employee And List Of Departments In Which He Has Worked Or Is Working
By Temp Shifting I Mean I Created A Temp Table In Between To Get The Desired Results.
Is there any need to capitalise all of your words?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 20, 2015 at 4:54 am
I Have Used Multiple Unions To Get Desired Output.
1. Join On MapPersonID = PersonID
2. Join On MapGrp = PersonGrp
3. Join On For Others Memebers But It Gives Me Dulpicate Records Which I Want To Avoid
January 20, 2015 at 4:55 am
sorry phil i am used to using that case for my office work. will keep it in mind in further posts
January 20, 2015 at 5:01 am
this is the actual query i have used to get desired result,but i fell its not proper method to write the query
Select PersonID,PersonGrp,PersonName,MapID,MapDepartment,DateFrom,DateTo
From Person Join PersonMap On MapPersonID = PersonID
Union
Select PersonID,PersonGrp,PersonName,MapID,MapDepartment,DateFrom,DateTo
From Person Join PersonMap On MapGrp = PersonGrp
Union
Select PersonID,PersonGrp,PersonName,MapID,MapDepartment,DateFrom,DateTo
From Person Join PersonMap On 1 = 1
Where PersonID Not In (
Select PersonID From Person Join PersonMap On MapPersonID = PersonID
Union
Select PersonID From Person Join PersonMap On MapGrp = PersonGrp
)
January 20, 2015 at 5:01 am
No, I mean please show us the actual query you've tried. And if you also supply the table DDL and sample data in the form of CREATE TABLE and INSERT statements respectively, please, we'll be able to test whether whatever we suggest actually works.
John
January 20, 2015 at 5:19 am
CREATE TABLE [dbo].[MstrPerson](
[PrsnCode] [char](8) NOT NULL,
[PrsnName] [varchar](30) NULL,
[PrsnGrpCode] [varchar](12) NULL,
[PrsnMdfdBy] [smallint] NULL,
[PrsnMdfdOn] [datetime] NULL,
CONSTRAINT [PK_MstrPerson] PRIMARY KEY CLUSTERED
(
[PrsnCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT [dbo].[MstrPerson] ([PrsnCode], [PrsnName],[PrsnGrpCode], [PrsnMdfdBy], [PrsnMdfdOn]) VALUES (N'2001 ', N'Person 1',N'Grp1', 1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
INSERT [dbo].[MstrPerson] ([PrsnCode], [PrsnName],[PrsnGrpCode], [PrsnMdfdBy], [PrsnMdfdOn]) VALUES (N'2002 ', N'Person 2',N'Grp1', 1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
INSERT [dbo].[MstrPerson] ([PrsnCode], [PrsnName],[PrsnGrpCode], [PrsnMdfdBy], [PrsnMdfdOn]) VALUES (N'2003 ', N'Person 3',N'Grp1', 1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
INSERT [dbo].[MstrPerson] ([PrsnCode], [PrsnName],[PrsnGrpCode], [PrsnMdfdBy], [PrsnMdfdOn]) VALUES (N'2004 ', N'Person 4',N'Grp2', 1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
INSERT [dbo].[MstrPerson] ([PrsnCode], [PrsnName],[PrsnGrpCode], [PrsnMdfdBy], [PrsnMdfdOn]) VALUES (N'2005 ', N'Person 5',N'Grp2', 1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
INSERT [dbo].[MstrPerson] ([PrsnCode], [PrsnName],[PrsnGrpCode], [PrsnMdfdBy], [PrsnMdfdOn]) VALUES (N'2006 ', N'Person 6',N'Grp3', 1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
January 20, 2015 at 5:27 am
Besides providing DDL and test data script you should explain yor task in more details.
Looking at the desired output i see no reason why Person.PersonGrp ='grp1' matches both 'grp1' and 'OTHERS' in PersonMap while Person.PersonGrp ='grp2'
matches 'grp2' only.
January 20, 2015 at 5:29 am
CREATE TABLE [dbo].[MstrMap](
[MapKey] [bigint] IDENTITY(1,1) NOT NULL,
[MapDepartment] [varchar](8) NULL,
[MapPerson] [char](8) NULL,
[MapGroup] [varchar](12) NULL,
[MapValidFrom] [int] NULL,
[MapValidUpto] [int] NULL,
[MapMdfdBy] [smallint] NULL,
[MapMdfdOn] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[MstrMap] ([MapDepartment], [MapPerson],[MapGroup],[MapValidFrom],[MapValidUpto],[MapMdfdBy], [MapMdfdOn])
VALUES (N'ADMIN ', N'2001 ',N'', 40000, NULL,1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
INSERT [dbo].[MstrMap] ([MapDepartment], [MapPerson],[MapGroup],[MapValidFrom],[MapValidUpto],[MapMdfdBy], [MapMdfdOn])
VALUES (N'MGMT ', N'',N'Grp1', 40000, 40100,1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
INSERT [dbo].[MstrMap] ([MapDepartment], [MapPerson],[MapGroup],[MapValidFrom],[MapValidUpto],[MapMdfdBy], [MapMdfdOn])
VALUES (N'MGMT ', N'',N'Grp2', 40101,NULL,1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
INSERT [dbo].[MstrMap] ([MapDepartment], [MapPerson],[MapGroup],[MapValidFrom],[MapValidUpto],[MapMdfdBy], [MapMdfdOn])
VALUES (N'OTHER ', N'',N'', 40000, NULL,1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
January 20, 2015 at 5:35 am
if you look at desired output you will come to know my difficulty.
sorry if i am not able to explain my problem properly.
my required output being code 2002,2003 for specific dates in management
and then in others group after that date
2001,admin,40000,
2002,mgmt,40000,40100
2002,other,40101,
2003,mgmt,40000,40100
2003,other,40101,
January 20, 2015 at 6:57 am
If i got it right.
CREATE TABLE [dbo].[MstrPerson](
[PrsnCode] [char](8) NOT NULL,
[PrsnName] [varchar](30) NULL,
[PrsnGrpCode] [varchar](12) NULL,
[PrsnMdfdBy] [smallint] NULL,
[PrsnMdfdOn] [datetime] NULL,
CONSTRAINT [PK_MstrPerson] PRIMARY KEY CLUSTERED
(
[PrsnCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- [PrsnGrpCode] set NULL
INSERT [dbo].[MstrPerson] ([PrsnCode], [PrsnName],[PrsnGrpCode], [PrsnMdfdBy], [PrsnMdfdOn]) VALUES (N'2001 ', N'Person 1',null, 1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
INSERT [dbo].[MstrPerson] ([PrsnCode], [PrsnName],[PrsnGrpCode], [PrsnMdfdBy], [PrsnMdfdOn]) VALUES (N'2002 ', N'Person 2',N'Grp1', 1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
INSERT [dbo].[MstrPerson] ([PrsnCode], [PrsnName],[PrsnGrpCode], [PrsnMdfdBy], [PrsnMdfdOn]) VALUES (N'2003 ', N'Person 3',N'Grp1', 1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
INSERT [dbo].[MstrPerson] ([PrsnCode], [PrsnName],[PrsnGrpCode], [PrsnMdfdBy], [PrsnMdfdOn]) VALUES (N'2004 ', N'Person 4',N'Grp2', 1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
INSERT [dbo].[MstrPerson] ([PrsnCode], [PrsnName],[PrsnGrpCode], [PrsnMdfdBy], [PrsnMdfdOn]) VALUES (N'2005 ', N'Person 5',N'Grp2', 1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
INSERT [dbo].[MstrPerson] ([PrsnCode], [PrsnName],[PrsnGrpCode], [PrsnMdfdBy], [PrsnMdfdOn]) VALUES (N'2006 ', N'Person 6',N'Grp3', 1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
Select * from [dbo].[MstrPerson]
CREATE TABLE [dbo].[MstrMap](
[MapKey] [bigint] IDENTITY(1,1) NOT NULL,
[MapDepartment] [varchar](8) NULL,
[MapPerson] [char](8) NULL,
[MapGroup] [varchar](12) NULL,
[MapValidFrom] [int] NULL,
[MapValidUpto] [int] NULL,
[MapMdfdBy] [smallint] NULL,
[MapMdfdOn] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[MstrMap] ([MapDepartment], [MapPerson],[MapGroup],[MapValidFrom],[MapValidUpto],[MapMdfdBy], [MapMdfdOn])
VALUES (N'ADMIN ', N'2001 ',N'', 40000, NULL,1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
INSERT [dbo].[MstrMap] ([MapDepartment], [MapPerson],[MapGroup],[MapValidFrom],[MapValidUpto],[MapMdfdBy], [MapMdfdOn])
VALUES (N'MGMT ', N'',N'Grp1', 40000, 40100,1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
INSERT [dbo].[MstrMap] ([MapDepartment], [MapPerson],[MapGroup],[MapValidFrom],[MapValidUpto],[MapMdfdBy], [MapMdfdOn])
VALUES (N'MGMT ', N'',N'Grp2', 40101,NULL,1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
INSERT [dbo].[MstrMap] ([MapDepartment], [MapPerson],[MapGroup],[MapValidFrom],[MapValidUpto],[MapMdfdBy], [MapMdfdOn])
VALUES (N'OTHER ', N'',N'', 40000, NULL,1, CAST(N'2014-05-08 18:01:22.000' AS DateTime))
select * from [dbo].[MstrMap];
go
;
with others as (
select top(1) mp2.MapDepartment , mp2.MapValidFrom, mp2.MapValidUpto
from [dbo].[MstrMap] mp2
where mp2.MapPerson =N'' and mp2.MapGroup = N''
)
select
ruletype = case when (p.PrsnCode = mp.MapPerson) then 'matchig by person code'
when mp.MapDepartment is null then 'no matches found'
else 'matching by group' end
, p.PrsnCode, p.PrsnName, p.PrsnGrpCode
, intervals.MapDepartment, intervals.MapValidFrom, intervals.MapValidUpto
, mp.MapGroup, mp.MapPerson
from [dbo].[MstrPerson] p
left join [dbo].[MstrMap] mp on p.PrsnCode = mp.MapPerson
or (mp.MapPerson = N'' and p.PrsnGrpCode = mp.MapGroup)
outer apply (
select mp.MapDepartment, mp.MapValidFrom, mp.MapValidUpto
where mp.MapDepartment is not null
union all
select others.MapDepartment, mp.MapValidUpto, others.MapValidUpto
from others
where mp.MapValidUpto is not null
union all
select others.MapDepartment, others.MapValidFrom, others.MapValidUpto
from others
where mp.MapDepartment is null) intervals
January 20, 2015 at 9:58 pm
thanks old hand for the perfect solution required by me 🙂
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply