How will Get Data Like This

  • hello i have table tabl_period_log

    CREATE TABLE [dbo].[Tabl_Period_Log](

    [ID] [bigint] IDENTITY(1,1) NOT NULL,

    [PeriodID] [bigint] NULL,

    [Status1] [bigint] NULL,

    [userid] [bigint] NULL,

    [companyid] [bigint] NULL,

    [entrydate] [datetime] NULL,

    [flag] [varchar](50) NULL,

    CONSTRAINT [PK_Tabl_Period_Log] PRIMARY KEY CLUSTERED

    (

    [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

    SET ANSI_PADDING OFF

    GO

    SET IDENTITY_INSERT [dbo].[Tabl_Period_Log] ON

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (1, 2, 3, 11, 2, CAST(0x0000A08200000000 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (2, 2, 2, 11, 2, CAST(0x0000A08200000000 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (3, 2, 3, 11, 2, CAST(0x0000A08200000000 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (4, 6, 1, 11, 2, CAST(0x0000A08200000000 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (5, 7, 1, 11, 2, CAST(0x0000A08200000000 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (6, 2, 2, 11, 2, CAST(0x0000A08200000000 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (7, 3, 2, 11, 2, CAST(0x0000A08200000000 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (8, 4, 2, 11, 2, CAST(0x0000A08200000000 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (9, 5, 2, 11, 2, CAST(0x0000A08200000000 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (10, 5, 3, 11, 2, CAST(0x0000A08200000000 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (11, 5, 2, 11, 2, CAST(0x0000A08200000000 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (12, 8, 1, 11, 2, CAST(0x0000A08200000000 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (13, 6, 2, 11, 2, CAST(0x0000A08200000000 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (14, 6, 3, 11, 2, CAST(0x0000A08200000000 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (15, 6, 2, 11, 2, CAST(0x0000A08201422FB6 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (16, 6, 3, 11, 2, CAST(0x0000A082014230A2 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (17, 6, 2, 11, 2, CAST(0x0000A082014231A3 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (18, 7, 2, 11, 2, CAST(0x0000A08201423286 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (19, 8, 2, 11, 2, CAST(0x0000A08201423303 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (20, 9, 1, 11, 2, CAST(0x0000A082014233EB AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (21, 9, 2, 11, 2, CAST(0x0000A08201423531 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (22, 9, 3, 11, 2, CAST(0x0000A08201423620 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (23, 8, 3, 11, 2, CAST(0x0000A082014713B4 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (24, 7, 3, 11, 2, CAST(0x0000A08201471460 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (25, 6, 3, 11, 2, CAST(0x0000A08201471503 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (26, 5, 3, 11, 2, CAST(0x0000A0820147158F AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (27, 5, 2, 12, 2, CAST(0x0000A082014CC2A5 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (28, 6, 2, 12, 2, CAST(0x0000A082014CC3AA AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (29, 7, 2, 12, 2, CAST(0x0000A082014CC498 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (30, 8, 2, 11, 2, CAST(0x0000A08300C26E56 AS DateTime), N'null')

    INSERT [dbo].[Tabl_Period_Log] ([ID], [PeriodID], [Status1], [userid], [companyid], [entrydate], [flag]) VALUES (31, 9, 2, 11, 2, CAST(0x0000A08300C26FF0 AS DateTime), N'null')

    SET IDENTITY_INSERT [dbo].[Tabl_Period_Log] OFF

    i want to get latest user id who closed , open or reopen the period according to date..

    PeriodidopenClose Reopen

    1 11 12 11

    2 11 12 11

    3 11 11 12

    how i can write query for that ..

    Can i use CTE...

    Basically i need to loop each period id one by one..

    But i not want to use cursor..

    any other method

    i written this query but not giving right result...

    select distinct Periodid,

    [open]=

    (select top 1 userid from Tabl_Period_Log

    where

    EntryDate =

    (select top 1 EntryDate

    from dbo.Tabl_Period_Log where status1=1 and companyid=2 and PeriodID=PeriodID order by EntryDate desc)

    and status1=1 and companyid=2 and PeriodID=PeriodID

    ),

    [Close]=

    (select top 1 userid from Tabl_Period_Log

    where

    EntryDate =

    (select top 1 EntryDate

    from dbo.Tabl_Period_Log where status1=2 and companyid=2 and PeriodID=PeriodID order by EntryDate desc)

    and status1=2 and companyid=2 and PeriodID=PeriodID

    ),

    [Reopen]=

    (select top 1 userid from Tabl_Period_Log

    where

    EntryDate =

    (select top 1 EntryDate

    from dbo.Tabl_Period_Log where status1=3 and companyid=2 and PeriodID=PeriodID order by EntryDate desc)

    and status1=3 and companyid=2 and PeriodID=PeriodID

    )

    from Tabl_Period_Log

  • This is my Solution with cursor

    ALTER procedure [dbo].[SP_Get_F_PEriod_Log]

    @cid bigint

    as

    create table #my_log (periodid bigint,[Open] varchar(100),[Close] varchar(100),Reopen varchar(100))

    declare @periodid bigint

    declare my_cur cursor for

    select distinct Periodid from Tabl_Period_Log where companyid=@cid

    open my_cur

    fetch next from my_cur into @periodid

    while @@fetch_status=0

    begin

    print @periodid

    -----------------------------------

    insert into #my_log

    select distinct @periodid,

    [open]=

    (select empid from Employe where id=(select top 1 userid from Tabl_Period_Log

    where

    EntryDate =

    (select top 1 EntryDate

    from dbo.Tabl_Period_Log where status1=1 and PeriodID=@periodid order by EntryDate desc)

    and status1=1 and PeriodID=@periodid

    )

    )

    ,[Close]=

    (select empid from Employe where id=(select top 1 userid from Tabl_Period_Log

    where

    EntryDate =

    (select top 1 EntryDate

    from dbo.Tabl_Period_Log where status1=2 and PeriodID=@periodid order by EntryDate desc)

    and status1=2 and PeriodID=@periodid

    )

    ),

    [Reopen]=

    (select empid from Employe where id=(select top 1 userid from Tabl_Period_Log

    where

    EntryDate =

    (select top 1 EntryDate

    from dbo.Tabl_Period_Log where status1=3 and PeriodID=@periodid order by EntryDate desc)

    and status1=3 and PeriodID=@periodid

    )

    )

    from Tabl_Period_Log where PeriodID=@periodid

    -------------------------------------------------

    fetch next from my_cur into @periodid

    end

    CLOSE my_cur

    deallocate my_cur

    select * from #my_log

    drop table #my_log

    if any one know some other way so please share with me ...

    Thanks

  • This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply