listing data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

listing data

Author

Topic

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2008-03-30 : 20:29:07

sample Table and records.

id uname punchdate punchtime
1 A    1/1/2007   7:00am
1 A    1/2/2007   8:00am
1 A    1/4/2007   7:30am
1 A    1/6/2007   7:40am

let say i want to get a result which punchdate is from 1/1/2007 to 1/8/2007, how can i get a result like this one.?

1    A   1/1/2007    7:00am
1    A   1/2/2007    8:00am
1    A   1/3/2007    <null>
1    A   1/4/2007    7:30am
1    A    1/5/2007    <null>
1    A    1/6/2007    7:40am
1    A    1/7/2007   <null>
1    A    1/8/2007    <null>

listing all data even if theres no punchdate and time in the table.

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-31 : 01:21:02

Hi,

Try with this

Declare @T Table (Id int, Uname char(4), Punchdate datetime, Punchtime varchar(100))
Insert into @T
Select 1 , 'A' , '1/1/2007' , '7:00am' Union all
Select 1 , 'A' , '1/2/2007' , '8:00am' Union all
Select 1 , 'A' , '1/4/2007' , '7:30am' Union all
Select 1 , 'A' , '1/6/2007' , '7:40am'

Declare @Table Table(Id Int, Uname Char(10), Punchdate datetime)

Declare @start datetime, @end datetime
Select @start = '1/1/2007', @end = '1/8/2007'

;WITH DateRange( ID , Uname , CurrentDate) AS
(
SELECT 1, 'A' , @start as CurrentDate
union all
SELECT 1,'A', DATEADD(day,1,CurrentDate)
FROM DateRange
WHERE CurrentDate < @end
)
Insert Into @Table
SELECT * FROM DateRange

Select B.Id, B.Uname, Convert(varchar(100), B.Punchdate,101) as Punchdate , A.Punchtime
From @T A
Right join @Table B on B.Punchdate = A.Punchdate

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-03-31 : 03:35:03

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate='2007-01-01 00:00:00'
SET @EndDate='2007-12-31 00:00:00'

WHILE @StartDate<@EndDate
BEGIN
INSERT INTO Datetest1 VALUES('1','A',@StartDate,NULL)
SET @StartDate=DATEADD(MM,1,@StartDate)
END

UPDATE D1 SET D1.punchdate=D2.PunchDate,D1.PunchTime=D2.PunchTime from Datetest1 D1
INNER JOIN Datetest D2
ON DATEPART(mm,D1.punchdate)=DATEPART(mm,D2.punchdate)
AND DATEPART(yy,D1.punchdate)=DATEPART(yy,D2.punchdate)

Note:
1. You need to provide @StartDate and @Enddate values
2. You can update the id and uname also by adding appropriate UPDATE statements
3. Datetst is your existing table and Datetest1 is your new table. If you dont want a new table, just replace it with a temp table

Prakash.P
The secret to creativity is knowing how to hide your sources!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources