Maybe something very easy?

[chrisstuart]

Hi

I've got a table with the following
MonthNr
ColumnNr
RowNr
Column01
......
Column12

I want to calc the movements between this month and last month
Can this be done in 1 sql statement?
I want to select * from table where monthnr = 11 and rownr between 1 and 10
"minus" select * from table where monthnr = 10 and rownr between 1 and 10
in order to calc the movement
It can be done in a trigger and cursor, but that will just complicate matters.

Any ideas, maybe I'm just missing something very basic.
Many thanks

[deepuv04]

Hi

I've got a table with the following
MonthNr
ColumnNr
RowNr
Column01
......
Column12

I want to calc the movements between this month and last month
Can this be done in 1 sql statement?
I want to select * from table where monthnr = 11 and rownr between 1 and 10
"minus" select * from table where monthnr = 10 and rownr between 1 and 10
in order to calc the movement
It can be done in a trigger and cursor, but that will just complicate matters.

Any ideas, maybe I'm just missing something very basic.
Many thanks

Can you explain indetail (with example if possible) exactly wat you want

[chrisstuart]

Can you explain indetail (with example if possible) exactly wat you want

It would basically be a table with the structure

[#Year] char(4),
[#MonthNr] char(2),
[#RowNr] decimal(3, 0),
[#Column01] decimal(20, 4),
[#Column02] decimal(20, 4) ,
.....
.....
.....


With data for MonthNr 1 to 12 for the year.
i.e.
"2007", "10", 129, 1234.00, 4321.00
"2007", "11", 129, 1222.00, 4333.00
"2007", "10", 130, .........
...
...
"2007", "10", 140, 3234.00, 1321.00
"2007", "11", 140, 3222.00, 1333.00 .....

Now line 129 up to line 140 dataset that I use on the report. The users now require movements between the tow months; which is basically dataset for the line 129 to line 140 for month 11 minus the dataset for the 10th month.

I can use a cursor to do this, but is there not a way of saying this in just one sql statement something like

select
rownr, year,month,
column1 - (select column1 from table where rownr between 129 and 140 and year = 2007 and month = 10) as column1,
(same select statement for column2 as column1)
from table where rownr between 129 and 140 and year = 2007 and month = 11

When I try and run this sort of statement, the inner select returns more that 1 record, but thats what I want to do, dataset for 11th month minus the dataset from the 10th month

Any elegant solutions?

[Delerna]

Having difficulty understanding what you are after, but to answer you question is there an elegant solution then the answer is almost certainly Yes


"2007", "10", 129, 1234.00, 4321.00
"2007", "11", 129, 1222.00, 4333.00
"2007", "10", 130, .........
...
...
"2007", "10", 140, 3234.00, 1321.00
"2007", "11", 140, 3222.00, 1333.00 .....

1) In your first post you said line numbers between 1 and 10 and in the last tey are 129 to 140. Does this mean that there is no real boundary for the line numbers
2) do you want to subtract the sum of value1 of all the line numers for month 11 from the sum of value1 for month 10

3) Are there other years other that 2007, if so then there is a complication when it comes to subtracting the first month of 2008 from the last month of 2007. Or are you only ever going to do subtractions within the same year period?

Sorry to say this but a clearer description of the problem would be helpful
For example what are the line numbers, what are there ranges, are the values stock figures and the line numbers represent stock at a particular time .

[chrisstuart]

Hi, let me try and give all the details, but what I really want is to subtract a dataset (dataset, i.e. line 129 to line 150, for month 11, columns 1 to 12)
from the previous months dataset (i.e. month 10)

Example
Line Month Column1 Column2 Column3 Column99
129 11 9,700 4,000 4,600 6,300
130 11 7,800 8,300 1,500 3,900
131 11 4,400 4,500 6,000 7,100
132 11 500 8,600 1,900 300
133 11 6,900 5,400 9,000 2,600

MINUS

Line Month Column1 Column2 Column3 Column99
129 10 9,100 1,400 3,200 8,600
130 10 7,600 9,600 400 4,700
131 10 6,500 1,800 2,100 3,100
132 10 4,900 4,900 300 9,200
133 10 7,000 8,900 1,500 4,700

DIFFERENCE

Line Month Column1 Column2 Column3 Column99
129 - 600 2,600 1,400 -2,300
130 - 200 -1,300 1,100 -800
131 - -2,100 2,700 3,900 4,000
132 - -4,400 3,700 1,600 -8,900
133 - -100 -3,500 7,500 -2,100

Sorry about the bad formatting, but hope this helps
Thx

[Delerna]

Thats better, now I understand what you are after, at least I think I do
I am going to assume you are only working with one years worth of data

This is hard without data or a server (at home at the moment) so I might make some mistakes. I normaly build my queries in a step by step process.
I think you can work it out and make the necessay adjustments

SELECT a.line,a.month,a.col1-isnull(b.col1,0) as col1,a.col2-isnull(b.col2,0) as col2,.......
FROM
(SELECT line,month,col1,col2,......
FROM YourTable) a
left join
(SELECT line,month,col1,col2,......
FROM YourTable) b on a.line=b.line and a.month=b.month-1
)z

SubQuery a has every months data and joins to subQuery b to get each months (in a) last months data
I hope that last sentence makes sense and that this helps you

[chrisstuart]

SELECT a.line,a.month,a.col1-isnull(b.col1,0) as col1,a.col2-isnull(b.col2,0) as col2,.......
FROM
(SELECT line,month,col1,col2,......
FROM YourTable) a
left join
(SELECT line,month,col1,col2,......
FROM YourTable) b on a.line=b.line and a.month=b.month-1
)z

Hi Delerma

Thanks for the reply, Is this query still going to work for a dataset of records. I need for lines x to y to be minus-ed from the previous months lines x to y?
Just looking at the code it seems that you are saying that it is just this month's 1 line minus last month's 1 line?