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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
2241 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
52326 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
982 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
52326 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  
 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.04 seconds. Powered By: Snitz Forums 2000