Update Statement - SQL Server Forums

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

Update Statement

Author

Topic

vtxvtx
Starting Member

18 Posts

Posted - 2012-08-22 : 06:10:43

I'm not totally sure how to explain this problem, but hopwfully it will make sense with the code.

I have the code that currently works which updates a table based of data from another at a certain date. However i want the date that is used to be different for each row.
I want DATEADD(MONTH,rowID,@daytocheck) to be the date to look at, however when i add that to my code, it updates the whole column to the same value.

What can I change to make this work?

Not sure how much this makes sense, so if you want any more explanation, i'll try and help.

@daytocheck = 'Whatever' - datetime

exec('
Update TestTable
SET Earned_' + @ColName + ' =
isnull(
	(SELECT 
		SUM(
		Sales.Cost *(
			(CASE
				WHEN 
					Sales.SaleDate >= ''' + @daytocheck + '''  THEN 
					0	
				WHEN 
					DATEADD(MONTH,Sales.Term,Sales.SaleDate) <=  ''' + @daytocheck + ''' THEN
					DATEDIFF(DAY,Sales.SaleDate,DATEADD(MONTH,Sales.Term,Sales.SaleDate))
				ELSE 
					DATEDIFF(DAY,Sales.SaleDate, ''' + @daytocheck + ''')
			END)
			/(CAST(DATEDIFF(DAY,Sales.SaleDate,DATEADD(MONTH,Sales.Term,Sales.SaleDate))As Decimal))
		)
		) 
	FROM
		Sales
	WHERE 
		Sales.InvoiceYear = TestTable.InvYear
		AND Sales.InvoiceMonth = TestTable.InvMonth)
,0)
')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 10:44:06

how do you get value of date from? Can you give an example to illustrate?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vtxvtx
Starting Member

18 Posts

Posted - 2012-08-23 : 04:00:45

The value of the date could be anything. I just set it to be the date I want to check.

vtxvtx
Starting Member

18 Posts

Posted - 2012-08-23 : 04:02:12

i could add a bit mkore clarification,
say i want to check '2010-01-01' on row 2 i'd then want to check '2010-02-01', row 3 '2010-03-01',...,row 13 '2011-01-01' etc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 10:09:53

quote:
Originally posted by vtxvtx

i could add a bit mkore clarification,
say i want to check '2010-01-01' on row 2 i'd then want to check '2010-02-01', row 3 '2010-03-01',...,row 13 '2011-01-01' etc

so where does these value come from ?
another table?
or same table another column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vtxvtx
Starting Member

18 Posts

Posted - 2012-08-23 : 11:30:44

The value of the first date is defined in a parameter in the query. Its the adding a month per row that doesnt exist anywhere, which is what i need.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources