SQL Query - Shifting rows up for certain fields

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

SQL Query - Shifting rows up for certain fields

Author

Topic

Marco123
Starting Member

6 Posts

Posted - 2011-01-03 : 13:44:20

Hi all,

I have what, on the face of it, is a very simply issue. I have one table (tblMain), which has 11 columns, the first being the date column and the rest of the columns (Field1 to Field 10) having numerical values. E.g.

Date Field1, Field2, Field3…….Field10
01/01/2011 100, 129, 78
02/01/2011 74, 25, 101
03/01/2011
04/01/2011

What I need to be able to do is to shift the values from Field1 and Field2 from the next row/day into Field9 and Field10 in the previous day. For example, if Field1 and Field2 on 02/01/2011 have values 74 and 25, I want to put these values into Field9 and Firld10 for the row 01/01/2011. Any ideas how to do this in SQL?

I’m doing this is Access SQL by the way but hopefully the solution will be generic.

Cheers,

Marck

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-03 : 14:24:59

[code]
Create table #foo ([date] datetime not null,f1 int null, f2 int null, f3 int null)

Insert into #foo
Select '01/01/2011', 100, 129, 78 UNION
Select '02/01/2011', 74, 25, 101 UNION
Select '03/01/2011' ,33,44,55 UNION
Select '04/01/2011' ,66,77,88

Select a.[date], a.f1, a.f2, a.f3, b.f1,b.f2,b.f3
FROM #foo a LEFT JOIN #foo b
on a.[date] = DATEADD(m,-1,b.[date])

drop table #foo
[/code]

Something like that would work. the above will only work for dates that a 1st of a calendar month as per your sample data.

Poor planning on your part does not constitute an emergency on my part.

Marco123
Starting Member

6 Posts

Posted - 2011-01-03 : 16:06:18

Thanks, that did the job exactly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-04 : 12:43:53

will your dates be always continuos without gaps?

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

Marco123
Starting Member

6 Posts

Posted - 2011-01-05 : 16:16:49

quote:
Originally posted by visakh16

will your dates be always continuos without gaps?

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

Hi,

Yes they would be, though I can’t think of a better way to do this than the answer that’s already been given....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-08 : 01:15:29

are you using SQL 2005 or above?

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

Marco123
Starting Member

6 Posts

Posted - 2011-01-11 : 15:49:54

Nope, as far as I'm aware, I'm using MySQL in Access.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 08:44:14

actually then you may be better off posting the question in relevant forum in future. This is MS SQL Forum and solutions given here are guaranteed to work only in SQL Server

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources