November 6, 2008 at 2:20 am
Hi there,
i am using SQL Server 2000 and T-sql
I have a report i want to create which will show me infomation like the following:
LoginID , Start_Date, End_Date and Total Time
However all this information is heald in one table but the info comes like this
LoginID Start_Date End_Date Total Time
1 12-04-07 00:10:00 12-04-07 00:20:00 10
1 12-04-10 01:10:10 12-04-10 00:20:00 30
2 12-04-07 00:10:00 12-04-07 00:20:00 10
2 12-04-07 02:10:00 12-04-07 00:20:00 10
2 12-04-07 03:10:00 12-04-07 00:20:00 10
3 12-04-07 04:10:00 12-04-07 00:20:00 10
Now i would like to see the data like this in a linear fashion:
'First row' 1 12-04-07 00:10:00 12-04-07 00:20:00 12-04-10 01:10:10 12-04-10 00:20:00 40
'Second row' 2 12-04-07 00:10:00 12-04-07 00:20:00
12-04-07 02:10:00 12-04-07 00:20:00
12-04-07 03:10:00 12-04-07 00:20:00 30
However differnt Loginid will have different Start_Date and End_Date of data in it so some may have 5 rows othere could have ten?
is this possiable ?
Thank you
Gaurav
November 6, 2008 at 2:28 am
i think you should look into pivot operator.
"Keep Trying"
November 6, 2008 at 2:32 am
Hi,
Thanks for ur reply!
But I am using Sql Server 2000. Is Pivot can be used in Sql Server 2000 as well.
Gaurav
November 6, 2008 at 3:56 am
Can anybody help me out from this?
Gaurav
November 6, 2008 at 4:11 am
HI there,
I'm not sure I understand how you are getting your result?
Please could you explain a bit more how to construct the new row?
e.g
Show distinct start_dates + All End dates + Sum of total time for that LoginId
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 6, 2008 at 4:23 am
Hi Chris,
I wanted the records Group wise(LoginID). Only one row will be generated for one LoginID. Say if i have 3 rows and 4 columns for loginid 2 then one row with 8 columns should be generated.
my Columns are now like this LoginID DT_Start DT_End total_Time
They should be generated like this
LoginID DT_Start1 DT_End1 DT_Start2 DT_End2 DT_Start3 DT_End3 total_Time
Thanks in Advance
Gaurav
November 7, 2008 at 12:50 am
gauravw (11/6/2008)
Hi,Thanks for ur reply!
But I am using Sql Server 2000. Is Pivot can be used in Sql Server 2000 as well.
Gaurav
No pivot cant be used in sql 2000. I assumed you would be using sql 2005 since you posted in a sql 2005 forum.
"Keep Trying"
November 7, 2008 at 1:05 am
No direct command available related to PIVOT in SQL 2000, but you can get it, see the SQL BOL and search "PIVOT Table".
November 7, 2008 at 2:22 am
Hi guys,
I ve' developed the solution for the same. it is as follows.
declare @StartDate DateTime, @EndDate DateTime
select @StartDate = @DateStart, @EndDate = @DateEnd
declare @r as bigint, @i as bigint
select * into #tbl_AgentDnTime from tbl_AgentDnTime
where (Login_Time between @DateStart and @EndDate) and Logout_Time <= @EndDate
select @i=1, @r=Max(Total) from ( select LoginID, Count(LoginID) Total from #tbl_AgentDnTime group by LoginID ) a
--select @r
select LoginID, Sum(Total_Time) TotalTime into #Report1 from #tbl_AgentDnTime group by LoginID --order by 1
while @i <= @r
begin
exec ('alter table #Report1 add Login_Time'+@i+' datetime ')
exec ('alter table #Report1 add Logout_Time'+@i+' datetime ')
exec (' update #Report1 set Login_Time'+@i+'=MinDate, Logout_Time'+@i+'=MaxDate
from #Report1 R join (select LoginID, Min(Login_Time) MinDate, Min(Logout_Time) MaxDate
from #tbl_AgentDnTime group by LoginID) A
on R.LoginID= A.LoginID ')
delete #tbl_AgentDnTime
from (select LoginID, Min(Login_Time) StartDate --, Min(Dt_End) EndDate
from #tbl_AgentDnTime A group by LoginID) A
where #tbl_AgentDnTime.LoginID = A.LoginId and #tbl_AgentDnTime.Login_Time = A.StartDate
select @i = @i + 1
end
select * from #Report1
drop table #Report1
drop table #tbl_AgentDnTime
November 7, 2008 at 2:40 am
Hi there,
I'm sure there is a set based solution to this which I will try and find.
But for now here is my solution.
I left the last part out which is that you just have to delimit your new column into mulitple columns 🙂
--PREPARE TEST DATA
DECLARE @MyTable TABLE
(
[LoginId] INT,
[Start_Date] DATETIME,
[End_Date] DATETIME,
[Total_Time] INT
)
DECLARE @Results TABLE
(
[id] INT IDENTITY(1,1),
[LoginId] INT,
[New_Field] VARCHAR(MAX),
[Total_Time] INT
)
INSERT INTO @MyTable
SELECT 1,'12-04-07 00:10:00','12-04-07 00:20:00',10 UNION ALL
SELECT 1,'12-04-10 01:10:10','12-04-10 00:20:00',30 UNION ALL
SELECT 2,'12-04-07 00:10:00','12-04-07 00:20:00',10 UNION ALL
SELECT 2,'12-04-07 02:10:00','12-04-07 00:20:00',10 UNION ALL
SELECT 2,'12-04-07 03:10:00','12-04-07 00:20:00',10 UNION ALL
SELECT 3,'12-04-07 04:10:00','12-04-07 00:20:00',10
--DECLARE VARIABLES
DECLARE @loop INT
DECLARE @loopMax INT
DECLARE @String VARCHAR(MAX)
DECLARE @LoginID INT
INSERT INTO @Results
SELECT
LoginId,'',SUM([Total_Time])
FROM @MyTable
GROUP BY LoginID
SELECT
@loopMax = MAX(id),
@loop = 1,
@String = ''
FROM @Results
SELECT @loopMax,@loop,@String
--Loop for Records
WHILE (@loop<=@loopMax)
BEGIN
SELECT @LoginID = LoginId
FROM @Results
WHERE id = @Loop
SELECT @String = @String + CONVERT(VARCHAR,[Start_Date],120) + ',' + CONVERT(VARCHAR,[End_Date],120) + ','
FROM @MyTable
WHERE LoginId = @LoginID
UPDATE @Results
SET [New_Field] = @String
WHERE LoginId = @LoginID
SET @Loop = @Loop + 1
END
SELECT *
FROM @Results
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 7, 2008 at 2:57 am
Hi Chris,
Thanks for ur solution.
Guarav
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply