SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Display Data in tabular
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micnie_2020
Posting Yak Master

Malaysia
227 Posts

Posted - 10/09/2013 :  02:25:11  Show Profile  Reply with Quote
Hi All,

I have table with data:

User Description StartDate EndDate
A AAA 01/Oct/2010 8:00:00 03/Oct/2010 18:00:00
B BBB 01/Oct/2010 13:00:00 01/Oct/2010 18:00:00
C CCC 03/Oct/2010 13:00:00 03/Oct/2010 13:00:00
D DDD 01/Oct/2010 08:00:00 02/Oct/2010 12:00:00
E EEE 02/Oct/2010 08:00:00 02/Oct/2010 12:00:00

Output needed:

User 01/Oct/2010 02/Oct/2010 03/Oct/2010
AM PM AM PM AM PM
----------------------------------------------------------
A AAA AAA AAA AAA AAA AAA
B - BBB - - - -
C - - - - - CCC
D DDD DDD DDD - - -
E - - EEE - - -

Please advise how can i wrote a stored procedure to output the data as tabular above?


Thank you.

Regards,
Micheale

micnie_2020
Posting Yak Master

Malaysia
227 Posts

Posted - 10/09/2013 :  03:50:02  Show Profile  Reply with Quote
I tried this method:


I replace User to be ID.
Description to be Text.



Declare @date int
Declare @id int
Declare @Curid int
Declare @Curids int
Declare @tbl table
(
id int,
[text] nvarchar(max),
date_ datetime
)

SELECT @id=id,@date =(DATEDIFF(hour, SDt,EDt)/24)+1 from temp2


while(@date != 0 )
Begin
insert into @tbl
SELECT id,[text],cast(dateadd(day,@date,SDt) as Date) from temp2

set @date = @date -1
END

SELECT * from @tbl
order by date_


However, it's can't show all the looping date. Only partial Date show.


http://oi39.tinypic.com/ebbiol.jpg

Refer to image above.
eg: ID 828 should loop 5 times, but it's only loop for 3 times.

Please advise.

Thank you.

Regards,
Micheale
Go to Top of Page

micnie_2020
Posting Yak Master

Malaysia
227 Posts

Posted - 10/09/2013 :  04:13:48  Show Profile  Reply with Quote
Solved it!

Here you go:
Declare @date int
Declare @id int
Declare @Curid int
Declare @Curids int
Declare @tbl table
(
id int,
[text] nvarchar(max),
date_ datetime
)




DECLARE db_cursor CURSOR FOR
SELECT id from temp2

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN


SELECT @date =(DATEDIFF(hour, SDt,EDt)/24)+1 from temp2 where id in (@id)

while(@date != 0 )
Begin
insert into @tbl
SELECT id,[text],cast(dateadd(day,@date,SDt-1) as Date) from temp2 where id=@id
set @date = @date -1
End


FETCH NEXT FROM db_cursor INTO @id
END

CLOSE db_cursor
DEALLOCATE db_cursor


SELECT * from @tbl
order by date_

Thank you.

Regards,
Micheale
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/09/2013 :  06:48:02  Show Profile  Reply with Quote
Here's a set based alternative


--sample table for illustration
create table tst
(
UserName varchar(2),
[Description] varchar(10),
StartDate datetime,
EndDate datetime
)
insert tst
values('A','AAA','01/Oct/2010 8:00:00','03/Oct/2010 18:00:00'),
('B','BBB','01/Oct/2010 13:00:00','01/Oct/2010 18:00:00'),
('C','CCC','03/Oct/2010 13:00:00','03/Oct/2010 13:00:00'),
('D','DDD','01/Oct/2010 08:00','02/Oct/2010 12:00:00'),
('E','EEE','02/Oct/2010 08:00:00','02/Oct/2010 12:00:00')


select p.UserName,DATEADD(dd,DATEDIFF(dd,-number,StartDate),0) AS DateVal ,DATEADD(dd,DATEDIFF(dd,-number,StartDate),Start) AS STart,DATEADD(dd,DATEDIFF(dd,-number,StartDate),[End]) AS [ENd],Slot
INTO temp
from tst p
cross join master..spt_values v
cross join (SELECT 'AM' AS Slot,'08:00' AS STart,'12:00' AS [ENd]
             UNION ALL
			 SELECT 'PM' AS Slot,'13:00' AS STart,'18:00' AS [ENd]
			 )t
			 WHERE v.number BETWEEN 0 AND DATEDIFF(dd,startdate,enddate)
			 and v.type='p'
DECLARE @DateList varchar(5000)=''
DECLARE @SQL varchar(max)
SET @DateList=  STUFF((SELECT DISTINCT ',['+ CONVERT(varchar(11),DateVal,121) + Slot + ']'
FROM temp
ORDER BY ',['+ CONVERT(varchar(11),DateVal,121) + Slot + ']' FOR XML PATH('')),1,1,'')
               
  --SELECT @DateList               


		SET @SQl='SELECT UserName,COALESCE(' + REPLACE(@DateList,',',',''---''),COALESCE(') + ',''---'')
		FROM
		(
		SELECT m.UserName,CONVERT(varchar(11),DateVal,121) + Slot AS DateSlot,COALESCE(n.[Description],''---'') AS Description
FROM temp m
			 LEFT JOIN tst n
			 ON n.UserName=m.UserName
			 AND (m.STart BETWEEN n.StartDate AND n.EndDate
			 OR m.[ENd] BETWEEN n.StartDate AND n.EndDate)
			 )t
			 PIVOT (MAX(Description) FOR DateSlot IN (' + @DateList + '))p'
	
	
			 EXEC(@SQL)

--destroy tables after use
DROP TABLE temp
DROP TABLE tst

output
----------------------------------------------------------------------------------------------------------------
UserName	2010-10-01 AM	2010-10-01 PM	2010-10-02 AM	2010-10-02 PM	2010-10-03 AM	2010-10-03 PM
-----------------------------------------------------------------------------------------------------------------
A	        AAA	        AAA	        AAA	        AAA	        AAA	        AAA
B	        ---	        BBB	        ---	        ---	        ---	        ---
C	        ---	        ---	        ---	        ---	        ---	        CCC
D	        DDD	        DDD	        DDD	        ---	        ---	        ---
E	        ---	        ---	        EEE	        ---	        ---	        ---



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000