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 |
agastyamahi
Starting Member
33 Posts |
Posted - 2013-12-12 : 22:30:03
|
Hi,I have a table like this below and a job runs every hour to insert the data into this table. Sometimes for some reason data doesn't get inserted for few dates in between, in that case I have to insert all the dates that are missing and get the same ProjectID, CurID and Amount from previous date RecordingDate ProjectID CurID Amount2013-11-11 00:00:00.000 28756 1 34009.270002013-11-12 00:00:00.000 28756 1 34009.270002013-11-13 00:00:00.000 28756 1 34009.270002013-11-14 00:00:00.000 28756 1 34009.270002013-11-15 00:00:00.000 28756 1 34009.270002013-11-16 00:00:00.000 28756 1 34009.270002013-11-17 00:00:00.000 28756 1 34009.270002013-11-18 00:00:00.000 28756 1 34009.270002013-11-19 00:00:00.000 28756 1 34009.270002013-11-20 00:00:00.000 28756 1 34009.270002013-11-21 00:00:00.000 28756 1 34009.270002013-11-22 00:00:00.000 28756 1 34009.270002013-11-23 00:00:00.000 28756 1 34009.270002013-11-24 00:00:00.000 28756 1 34009.270002013-11-25 00:00:00.000 28756 1 34009.270002013-12-03 00:00:00.000 28756 1 27597.420002013-12-04 00:00:00.000 28756 1 27597.42000My output should be like this2013-11-11 00:00:00.000 28756 1 34009.270002013-11-12 00:00:00.000 28756 1 34009.270002013-11-13 00:00:00.000 28756 1 34009.270002013-11-14 00:00:00.000 28756 1 34009.270002013-11-15 00:00:00.000 28756 1 34009.270002013-11-16 00:00:00.000 28756 1 34009.270002013-11-17 00:00:00.000 28756 1 34009.270002013-11-18 00:00:00.000 28756 1 34009.270002013-11-19 00:00:00.000 28756 1 34009.270002013-11-20 00:00:00.000 28756 1 34009.270002013-11-21 00:00:00.000 28756 1 34009.270002013-11-22 00:00:00.000 28756 1 34009.270002013-11-23 00:00:00.000 28756 1 34009.270002013-11-24 00:00:00.000 28756 1 34009.270002013-11-25 00:00:00.000 28756 1 34009.270002013-11-26 00:00:00.000 28756 1 34009.270002013-11-27 00:00:00.000 28756 1 34009.270002013-11-28 00:00:00.000 28756 1 34009.270002013-11-29 00:00:00.000 28756 1 34009.270002013-11-30 00:00:00.000 28756 1 34009.270002013-12-01 00:00:00.000 28756 1 34009.270002013-12-02 00:00:00.000 28756 1 34009.270002013-12-03 00:00:00.000 28756 1 27597.420002013-12-04 00:00:00.000 28756 1 27597.42000Can any one please help me how to form a query for this ?Thanks,Usha |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-12-13 : 02:26:20
|
;with demo_cte as (select min(RecordingDate) Min_Date,max(RecordingDate) Max_Date, 1 Min_Day from TableName union all select DateAdd(day,1,Min_Date), Max_date,Min_day+1 from demo_cte where DateAdd(day,1,Min_Date)<=Max_date)select Min_Date Stat_days,Min_Day from demo_cte--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-13 : 07:05:46
|
[code]insert into table (RecordingDate, ProjectID, CurID, Amount)select DATEADD(dd,v.number,t.RecordingDate),t.ProjectID,t.CurID,t.Amountfrom table tcross apply (select min(RecordingDate) as nextdate from table where ProjectID = t.ProjectID and CurID = t.CurID and RecordingDate > t.RecordingDate )t1cross join master..spt_values vWHERE nextdate > RecordingDate + 1and v.type='p'AND v.number > 0and DATEADD(dd,v.number,RecordingDate) < nextdate [/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2013-12-15 : 21:46:14
|
[code]DECLARE @a TABLE(RecordingDate DATETIME, ProjectID INT, CurID INT, Amount MONEY)INSERT INTO @a SELECT'2013-11-11 00:00:00.000',28756,1,34009.27000 union all select'2013-11-12 00:00:00.000',28756,1,34009.27000 union all select'2013-11-13 00:00:00.000',28756,1,34009.27000 union all select'2013-11-14 00:00:00.000',28756,1,34009.27000 union all select'2013-11-15 00:00:00.000',28756,1,34009.27000 union all select'2013-11-16 00:00:00.000',28756,1,34009.27000 union all select'2013-11-17 00:00:00.000',28756,1,34009.27000 union all select'2013-11-18 00:00:00.000',28756,1,34009.27000 union all select'2013-11-19 00:00:00.000',28756,1,34009.27000 union all select'2013-11-20 00:00:00.000',28756,1,34009.27000 union all select'2013-11-21 00:00:00.000',28756,1,34009.27000 union all select'2013-11-22 00:00:00.000',28756,1,34009.27000 union all select'2013-11-23 00:00:00.000',28756,1,34009.27000 union all select'2013-11-24 00:00:00.000',28756,1,34009.27000 union all select'2013-11-25 00:00:00.000',28756,1,34009.27000 union all select'2013-12-03 00:00:00.000',28756,1,27597.42000 union all select'2013-12-04 00:00:00.000',28756,1,27597.42000;WITH Sources AS ( SELECT *, LEAD(RecordingDate) OVER (ORDER BY RecordingDate) Forward FROM @a), CTE AS( SELECT Sources.RecordingDate , Sources.ProjectID , Sources.CurID , Sources.Amount , Sources.Forward FROM Sources WHERE RecordingDate + 1 <> Forward UNION ALL SELECT CTE.RecordingDate + 1 , CTE.ProjectID , CTE.CurID , CTE.Amount , CTE.Forward FROM CTE JOIN Sources ON CTE.ProjectID = Sources.ProjectID AND CTE.CurID = Sources.CurID WHERE CTE.RecordingDate < Sources.Forward - 1 AND Sources.RecordingDate + 1 <> Sources.Forward)SELECT RecordingDate, ProjectID, CurID, AmountFROM CTEUNIONSELECT *FROM @a[/code] |
|
|
agastyamahi
Starting Member
33 Posts |
Posted - 2013-12-17 : 19:30:25
|
Thank you very much Visakh, it worked |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-18 : 06:35:37
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|