Help With Select Query

  • 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

  • Jay Sapani (1/20/2015)


    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

    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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

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

  • 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

  • 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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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

  • sorry phil i am used to using that case for my office work. will keep it in mind in further posts

  • 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

    )

  • 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

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

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

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

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

  • 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

  • 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