How to compare filed of two rows

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 2008 Forums

Transact-SQL (2008)

How to compare filed of two rows

Author

Topic

dubaashu
Starting Member

10 Posts

Posted - 2013-12-18 : 02:33:01

Hi All,

Please help me for comparing Two rows

Eg.

Row No 1 having string XXXXXX
Row No 2 having string YYYYYY

How to compare two rows in SQL Row No 1 = Row No 2 in case statement

Thanks in Advance

Ashish gupte

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-18 : 02:49:17

you need to post your table DDL, sample data in DML and expected result.

Without this, i can only make a wild guess


select t1.string, t2.string
from   table t1
       inner join table t2 on t1.pk = t2.pk
where  t1.row_no = 1
and    t2.row_no = 2

KH
[spoiler]Time is always against us[/spoiler]

dubaashu
Starting Member

10 Posts

Posted - 2013-12-18 : 03:07:07

Hi ,
Thanks for the reply. Please find below is code

Select T2.Docnum, T1.DocDate ,ROW_NUMBER () over(Order by T1.Itemcode) as 'SNo' ,
T1.ItemCode , T1.Quantity as 'OrderQty' ,T1.OpenQty as 'PendingQty', SUM(T3.Onhand) as 'Instock'

From RDR1 T1
Inner Join ORDR T2 On T1.DocEntry = T2.DocEntry
Inner Join OITW T3 On T1.ItemCode = T3.ItemCode
Inner Join dbo.SO T4 On T1.ItemCode = T4.Itemcode
Where T1.LineStatus = 'O'
Group By T2.Docnum ,T1.DocDate, T1.ItemCode , T1.Quantity ,T1.OpenQty

My result is as

Docnum SNo ItemCode OrderQty PendingQty Instock
----------- -------------------- -------------------- --------------------------------------- --------------------------------------- ---------------------------------------
7391 1 I0000843 30.000000 5.000000 12.000000
7449 2 I0000843 30.000000 15.000000 12.000000
7497 3 I0000843 10.000000 10.000000 12.000000
7587 4 I0000843 5.000000 5.000000 12.000000
7868 5 I0000843 10.000000 10.000000 12.000000
8279 6 I0000843 100.000000 100.000000 12.000000

i want result for
Row No 1 is ( Instock - pendingQty) = Balance qty
Row No 2 Balance Qty - Pending Qty

and so on

Thanks

Ashish gupte

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-18 : 03:39:10

[code]; with
cte as
(
Select T2.Docnum, T1.DocDate ,ROW_NUMBER () over(Order by T1.Itemcode) as 'SNo' ,
T1.ItemCode , T1.Quantity as 'OrderQty' ,T1.OpenQty as 'PendingQty', SUM(T3.Onhand) as 'Instock'
From RDR1 T1
Inner Join ORDR T2 On T1.DocEntry = T2.DocEntry
Inner Join OITW T3 On T1.ItemCode = T3.ItemCode
Inner Join dbo.SO T4 On T1.ItemCode = T4.Itemcode
Where T1.LineStatus = 'O'
Group By T2.Docnum ,T1.DocDate, T1.ItemCode , T1.Quantity ,T1.OpenQty
)
select *
from cte c
cross apply
(
select Balance = sum(case when x.SNo = 1 then x.Instock else 0 end)
- sum(x.PendingQty)
from cte x
where x.Sno <= c.SNo
) b[/code]

KH
[spoiler]Time is always against us[/spoiler]

dubaashu
Starting Member

10 Posts

Posted - 2013-12-18 : 06:13:21

Hi Khtan,

your Query works absolutely fine when only consider One item Code. When taking more then one item then Balance Quantity is not giving correct result
and Correction which I had done is
; with
cte as
(
Select T2.Docnum, T1.DocDate ,ROW_NUMBER () over(Order by T1.Itemcode) as 'SNo' ,
T1.ItemCode , T1.Quantity as 'OrderQty' ,T1.OpenQty as 'PendingQty', SUM(T3.Onhand) as 'Instock'
From RDR1 T1
Inner Join ORDR T2 On T1.DocEntry = T2.DocEntry
Inner Join OITW T3 On T1.ItemCode = T3.ItemCode
Inner Join dbo.SO T4 On T1.ItemCode = T4.Itemcode
Where T1.LineStatus = 'O'
Group By T2.Docnum ,T1.DocDate, T1.ItemCode , T1.Quantity ,T1.OpenQty
)
select *
from cte c
cross apply
(
select Balance = sum(case when x.SNo = 1 then x.Instock else 0 end)
- sum(x.PendingQty)
from cte x
where x.Sno <= c.SNo and x.itemcode = c.itemcode
) b

Thanks a lot

Ashish gupte

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-18 : 10:58:36

[code]; with
cte as
(
Select T2.Docnum, T1.DocDate ,ROW_NUMBER () over(partition by T1.Itemcode Order by T1.Itemcode) as 'SNo' ,
T1.ItemCode , T1.Quantity as 'OrderQty' ,T1.OpenQty as 'PendingQty', SUM(T3.Onhand) as 'Instock'
From RDR1 T1
Inner Join ORDR T2 On T1.DocEntry = T2.DocEntry
Inner Join OITW T3 On T1.ItemCode = T3.ItemCode
Inner Join dbo.SO T4 On T1.ItemCode = T4.Itemcode
Where T1.LineStatus = 'O'
Group By T2.Docnum ,T1.DocDate, T1.ItemCode , T1.Quantity ,T1.OpenQty
)
select *
from cte c
cross apply
(
select Balance = sum(case when x.SNo = 1 then x.Instock else 0 end)
- sum(x.PendingQty)
from cte x
where x.Sno <= c.SNo
and x.Itemcode = c.Itemcode
) b[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources