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
 help with a query

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 Amount
2013-11-11 00:00:00.000 28756 1 34009.27000
2013-11-12 00:00:00.000 28756 1 34009.27000
2013-11-13 00:00:00.000 28756 1 34009.27000
2013-11-14 00:00:00.000 28756 1 34009.27000
2013-11-15 00:00:00.000 28756 1 34009.27000
2013-11-16 00:00:00.000 28756 1 34009.27000
2013-11-17 00:00:00.000 28756 1 34009.27000
2013-11-18 00:00:00.000 28756 1 34009.27000
2013-11-19 00:00:00.000 28756 1 34009.27000
2013-11-20 00:00:00.000 28756 1 34009.27000
2013-11-21 00:00:00.000 28756 1 34009.27000
2013-11-22 00:00:00.000 28756 1 34009.27000
2013-11-23 00:00:00.000 28756 1 34009.27000
2013-11-24 00:00:00.000 28756 1 34009.27000
2013-11-25 00:00:00.000 28756 1 34009.27000
2013-12-03 00:00:00.000 28756 1 27597.42000
2013-12-04 00:00:00.000 28756 1 27597.42000


My output should be like this

2013-11-11 00:00:00.000 28756 1 34009.27000
2013-11-12 00:00:00.000 28756 1 34009.27000
2013-11-13 00:00:00.000 28756 1 34009.27000
2013-11-14 00:00:00.000 28756 1 34009.27000
2013-11-15 00:00:00.000 28756 1 34009.27000
2013-11-16 00:00:00.000 28756 1 34009.27000
2013-11-17 00:00:00.000 28756 1 34009.27000
2013-11-18 00:00:00.000 28756 1 34009.27000
2013-11-19 00:00:00.000 28756 1 34009.27000
2013-11-20 00:00:00.000 28756 1 34009.27000
2013-11-21 00:00:00.000 28756 1 34009.27000
2013-11-22 00:00:00.000 28756 1 34009.27000
2013-11-23 00:00:00.000 28756 1 34009.27000
2013-11-24 00:00:00.000 28756 1 34009.27000
2013-11-25 00:00:00.000 28756 1 34009.27000
2013-11-26 00:00:00.000 28756 1 34009.27000
2013-11-27 00:00:00.000 28756 1 34009.27000
2013-11-28 00:00:00.000 28756 1 34009.27000
2013-11-29 00:00:00.000 28756 1 34009.27000
2013-11-30 00:00:00.000 28756 1 34009.27000
2013-12-01 00:00:00.000 28756 1 34009.27000
2013-12-02 00:00:00.000 28756 1 34009.27000
2013-12-03 00:00:00.000 28756 1 27597.42000
2013-12-04 00:00:00.000 28756 1 27597.42000

Can 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
Go to Top of Page

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.Amount
from table t
cross apply (select min(RecordingDate) as nextdate
from table
where ProjectID = t.ProjectID
and CurID = t.CurID
and RecordingDate > t.RecordingDate
)t1
cross join master..spt_values v
WHERE nextdate > RecordingDate + 1
and v.type='p'
AND v.number > 0
and DATEADD(dd,v.number,RecordingDate) < nextdate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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
, Amount
FROM CTE
UNION
SELECT *
FROM @a[/code]
Go to Top of Page

agastyamahi
Starting Member

33 Posts

Posted - 2013-12-17 : 19:30:25
Thank you very much Visakh, it worked
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-18 : 06:35:37
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -