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
 General SQL Server Forums
 New to SQL Server Programming
 help with a query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

agastyamahi
Starting Member

Canada
33 Posts

Posted - 12/12/2013 :  22:30:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 12/13/2013 :  02:26:20  Show Profile  Reply with Quote
;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

India
52325 Posts

Posted - 12/13/2013 :  07:05:46  Show Profile  Reply with Quote

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 


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

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 12/15/2013 :  21:46:14  Show Profile  Reply with Quote

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

agastyamahi
Starting Member

Canada
33 Posts

Posted - 12/17/2013 :  19:30:25  Show Profile  Reply with Quote
Thank you very much Visakh, it worked
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/18/2013 :  06:35:37  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
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.06 seconds. Powered By: Snitz Forums 2000