July 3, 2012 at 1:22 am
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
July 3, 2012 at 2:21 am
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
July 3, 2012 at 2:44 am
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