How to compare 2 rows - 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

SQL Server 2000 Forums

Transact-SQL (2000)

How to compare 2 rows

Author

Topic

Imco20030
Starting Member

10 Posts

Posted - 2009-09-30 : 17:21:22

Hi all,

I have to include a statement in a query which allows to compare the current value of column A with the previous value of column A (same column). from there, I need to add a condition in order to have the expected result.
Let's take an example to illustrate what I want to achieve:

I have the following columns in table called 'Charges':

Ship_id batch_nr Order_nr Price
SID1111 9997 MD5551 50
SID1111 9998 MD5552 50
SID1111 9999 MD5553 50
SID2222 8887 MD6661 80
SID2222 8887 MD6662 80
SID2222 8887 MD6662 80
SID3333 6666 MD7771 90

I want to check if the ship_id of row 2 is equal to the ship_if of row 1.
If it is the case, then value 'Not to be invoiced' in row 2 under Price column.
PLease see below the expected result:
Ship_id batch_nr Order_nr Price
SID1111 9997 MD5551 50
SID1111 9998 MD5552 Not to be invoiced
SID1111 9999 MD5553 Not to be invoiced
SID2222 8887 MD6661 80
SID2222 8887 MD6662 Not to be invoiced
SID2222 8887 MD6663 Not to be invoiced
SID3333 6666 MD7771 90

Thanks in advance for your help, it is really urgent.

Imco20030

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-01 : 02:12:21

With this data it is not possible because there is no column to determine the right order of rows in table.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 02:23:24

[code]SELECT t.Ship_Id,
t.Batch_nr,
t.Order_nr,
CASE WHEN t1.Ship_id IS NULL THEN 'Not to be invoiced' ELSE CAST(Price AS varchar(10)) END AS Price
FROM table t
LEFT JOIN (SELECT Ship_id,MIN(Order_nr) AS First
FROM table
GROUP BY Ship_Id) t1
ON t.Ship_id=t1.Ship_id
AND t.Order_nr=t1.First
[/code]

Imco20030
Starting Member

10 Posts

Posted - 2009-10-01 : 05:51:11

I am sorry but your query does not work.
Could you please review it?

Many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 05:55:21

quote:
Originally posted by Imco20030

I am sorry but your query does not work.
Could you please review it?

Many thanks

why? can you illustrate why you think it doesnt work with some data?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-01 : 07:20:22

Visakh assumed that the Order_nr is useful to get the records in the right order.
If that is right then his solution should work fine for you.

What means "doesn't work"? Are there any errors or is the result not like expected?

No, you're never too old to Yak'n'Roll if you're too young to die.

Imco20030
Starting Member

10 Posts

Posted - 2009-10-01 : 08:03:52

Hi,

I think that it is better if I provide you the query that I use.
Then maybe, it would be easier to understand what i meant.

Here is the query that I use:

[ select
sl.ship_id,
o.ordnum,
o.reffld_5 "BatchNR",
sum(tc1.chrg_amt) "FreightPRC",
sum(tc2.chrg_amt) "FuelPRC",
sum (tc1.chrg_amt + tc2.chrg_amt + tc3.chrg_amt) "Total Price"

from ord_line ol
join ord o on (ol.ordnum = o.ordnum and ol.client_id = o.client_id)
join shipment_line sl on (ol.ordnum = sl.ordnum and ol.client_id = sl.client_id and ol.ordlin = sl.ordlin)
join adrmst a2 on (o.rt_adr_id = a2.adr_id)
left join tm_chrg tc1 on (tc1.chargetype = 'FREIGHT' and tc1.chrg_role = 'PRICE' and tc1.ship_id = sl.ship_id)
left join tm_chrg tc2 on (tc2.chargetype = 'FUELSURCHARGE'and tc2.chrg_role = 'PRICE' and tc2.ship_id = sl.ship_id)

where sl.ship_id = 'SID0132408'

group by o.client_id, o.ordnum, o.reffld_2, sl.ship_id, a2.adrnam, a2.adrln1, a2.adrpsz, a2.adrcty, a2.ctry_name,
o.reffld_5, ol.early_shpdte

order by ship_id
]

I removed some columns from the select as they are not relevant for the wanted action like 'adress details or other references'.

Now here is the result:

Shipment ID Order Number WMS Batch Freight Fuel Price Order Total Price
SID0132408 MDK-000014-05602649 04641401 110 10 120
SID0132408 MDK-000014-05602651 04641402 110 10 120
SID0132408 MDK-000014-05602652 04641363 110 10 120

as you can see, the 3 orders have the same shipment ID.
The expected result should be shown under column 'Total Price' as follows:

Shipment ID Order Number WMS Batch Freight Fuel Price Order Total Price
SID0132408 MDK-000014-05602649 04641401 110 10 120
SID0132408 MDK-000014-05602651 04641402 110 10 tog with 04641401
SID0132408 MDK-000014-05602652 04641363 110 10 tog with 04641401

I hope that it make things clearer.
Let me know if you need more information.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 15:15:39

will Order Number be always in increasing order for a shipmentid records?

Imco20030
Starting Member

10 Posts

Posted - 2009-10-01 : 15:24:56

Not necessarely,
but the order number is unique.
No risk to have duplicated order numbers.

FYI, I use winMSQL to query.

I sent you the query that I used, could you please amend it in order to get the expected result?

Much appreciate your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 15:36:30

quote:
Originally posted by Imco20030

Not necessarely,
but the order number is unique.
No risk to have duplicated order numbers.

FYI, I use winMSQL to query.

I sent you the query that I used, could you please amend it in order to get the expected result?

Much appreciate your help.

then on what basis you decide which is first order for shipmentid group?

Imco20030
Starting Member

10 Posts

Posted - 2009-10-01 : 15:39:57

Well, based on the order by ship_id at the end of the statement.
the output is sorted by ship_id, then from there, I can see the orders belonging to the shipment ID.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 15:50:22

nope i'm asking for records with same Shipment ID how would you determine which is first?

Imco20030
Starting Member

10 Posts

Posted - 2009-10-01 : 16:03:23

If you mean the sequence, then there is no sequence set up.

the same logic has been developed in Excel.

If I compare this to Excel, here is the formula:
(=IF(AND(A2=A1;LEFT(X1;3)="TOG");X1;IF(A2=A1;"Tog. With "&B1;SUM(C2;D2)))

column A = Shipment ID
column B = Batch number
column C = Freight
column D = Fuel
column X = expected result

Hope this can help you.

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 16:13:56

there's no concept of order in sql table unless you specify it explicitly unlike excel where you've cell numbers.

Imco20030
Starting Member

10 Posts

Posted - 2009-10-01 : 16:28:08

Then, I have to set up a sequence like the cell numbers in excel?
How can I do this?
And then, how to develop my query in order to get my expected result?

Many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 16:30:17

you might need a unique valued column to specify order

Imco20030
Starting Member

10 Posts

Posted - 2009-10-01 : 16:34:51

the values of Order nr column are unique.

Could you help me to achieve this query, please?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-01 : 17:45:22

quote:
Originally posted by webfred

With this data it is not possible because there is no column to determine the right order of rows in table.

No, you're never too old to Yak'n'Roll if you're too young to die.

here we go again

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources