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.
Author |
Topic |
nurazura
Starting Member
3 Posts |
Posted - 2013-01-16 : 04:31:58
|
Hi all,please help me.How to add daily date to 365 row.my result should be like this.staffno, zdateA001, 1/1/2013A001, 1/2/2013A001, 1/3/2013A001, 1/4/2013A001, 1/4/2013......A001, 12/30/2013A001, 12/31/2013A002, 1/1/2013A002, 1/2/2013A002, 1/3/2013A002, 1/4/2013A002, 1/4/2013......A002, 12/30/2013A002, 12/31/2013...tqvm! |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-16 : 05:08:21
|
[code]SET dateformat mdyGOdeclare @start DATE = '01/01/2013'declare @end DATE = '12/31/2013';with DateCte(staffno, zdate) AS( SELECT 'A' + RIGHT( '000' + convert(varchar(2), @start, 101), 3) , @start UNION ALL SELECT 'A' + RIGHT( '000' + convert(varchar(2), zdate, 101), 3), DATEADD( dd, 1, zdate) FROM DateCte WHERE CONVERT(VARCHAR(10), zdate, 101) < @end)SELECT staffno, zdate FROM DateCteOPTION (MAXRECURSION 0)[/code]--Chandu |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-01-17 : 05:01:09
|
As you have already staffno in your staff table(I assume), you should be able to do this on the flydeclare @start DATE SET @start = '20130101'declare @end DATE SET @end = '20131231'declare @staff table(staffNo char(4))insert into @staff (staffno)select 'A001' union allselect 'A002' union allselect 'A003' select staffNo,dateadd(day,sno-1,@start) as zdate from @staff cross join( select top 365 row_number() over (order by name) as sno from sys.columns) as t Which will repeat date ranges for each staffnoMadhivananFailing to plan is Planning to fail |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-17 : 05:06:31
|
solution without using any system tablesdeclare @start DATE = '20130101'declare @end DATE = '20131231';With Date_CTE(dt)(SELECT @StartUNION ALLSELECT DATEADD(dd,1,dt)FROM Date_CTEWHERE DATEADD(dd,1,dt)<=@End)select staffNo,dt from @staff cross join Date_CTEOPTION (MAXRECURSION 0) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|