Select Query ? - 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

Select Query ?

Author

Topic

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2012-03-12 : 04:36:17

Hi

I have columns at my table.

ColumnN ColumnP DateTime

100 0 01.01.2000

0 200 01.01.2000

300 0 01.01.2000

0 500 01.01.2000

My Select should give me PNColumn and PNKindColumn too.

PNColumn PNkindColumn

100 N + 0 = 100 N

100 N + 200 P = 100 P

100 P + 300 N = 200 N

200N + 500 P= 300 P

Result should be like up.

For Example P is positive and N is negative. But price should be display positive but kindcolumn should display PNKind.

How can i do with SELECT this ? Can you write please ?

Thank You.

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2012-03-12 : 04:43:46

Can you please write the actual output, what do you actually want.

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2012-03-12 : 05:20:47

Hi. Thank you for reply.
For example there is below data.

Pozitive Negative
100 0
0 200
300 0

It can be more data from my example.

I need below result at SELECT query for my grid.
Pozitive Negative PozitiveNegative PozitiveNegativeKind
100 0 100 P
0 200 100 N
300 0 200 P

first row add second row and second row adding tree row.
Also i need SQL Query for below SELECT
SELECT x_carihareket.x_id, x_carihareket.x_grup2, x_carihareket.x_grup1, x_carihareket.x_ozelkod2, x_carihareket.x_ozelkod1, x_carihareket.x_dovizkur, x_carihareket.x_borcalacakdoviz, x_carihareket.x_bakiyedoviz, x_carihareket.x_alacakdovizCOLUMNNEGATIVE, x_carihareket.x_borcdovizCOLUMNPOZITIVE, x_carihareket.x_borcalacak, x_carihareket.x_bakiye, x_carihareket.x_alacakCOLUMNNEGATIVE, x_carihareket.x_borcCOLUMNPOZITIVE, x_carihareket.x_aciklama, x_carihareket.x_vadetarihi, x_carihareket.x_harekettarihiDATETIME, x_carihareket.x_bankaid, x_carihareket.x_kasaid, x_carihareket.x_islemturu, x_carihareket.x_makbuzno, x_carihareket.x_hareketno, x_carihareket.x_cariid, x_carihareket.x_subeid, x_carikarti.x_kod, x_carikarti.x_adi, x_bankakarti.x_kod AS x_bankakod, x_bankakarti.x_adi AS x_bankaadi, x_kasakarti.x_kod AS x_kasakod, x_kasakarti.x_adi AS x_kasaadi, x_subetanimlari.x_kod AS Expr5, x_subetanimlari.x_adi AS Expr6, x_doviztanimlari.x_sembol AS x_dovizturFROM x_carihareket INNER JOIN x_carikarti ON x_carihareket.x_cariid = x_carikarti.x_id LEFT OUTER JOIN x_bankakarti ON x_carihareket.x_bankaid = x_bankakarti.x_id LEFT OUTER JOIN x_kasakarti ON x_carihareket.x_kasaid = x_kasakarti.x_id LEFT OUTER JOIN x_doviztanimlari ON x_carihareket.x_doviztur = x_doviztanimlari.x_id LEFT OUTER JOIN x_subetanimlari ON x_carihareket.x_subeid = x_subetanimlari.x_idWHERE (NOT (x_carihareket.x_id IS NULL)) ORDER BY x_harekettarihiDATETIME, x_carihareket.x_id

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-12 : 06:06:37

[code]
declare @my_table table
(
row_no int identity,
ColumnN int,
ColumnP int,
DatetTime datetime
)
insert into @my_table select 100, 0, '2000-01-01'
insert into @my_table select 0, 200, '2000-01-01'
insert into @my_table select 300, 0, '2000-01-01'
insert into @my_table select 0, 500, '2000-01-01'

select t.*,
PNColumn = abs(isnull(PNkindColumn, 0)) + ColumnN + ColumnP,
PNkindColumn = isnull(PNkindColumn, 0) + ColumnP - ColumnN
from @my_table t
cross apply
(
select PNkindColumn = sum(ColumnP - ColumnN)
from @my_table x
where x.row_no < t.row_no
) pn
[/code]

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

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2012-03-12 : 06:14:49

Thank you Khan
But data count can be changed.
I need like below. Below code, i need lastly two column Sum for date and id column.
Can you help me for that
select PNColumn(select abs(SUM(P)-SUM(N)) from table where ... ),

case PNKindColumn=
when P>N then 'P'
when P<N then 'N'
when P=Nthen '-'

end

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-12 : 07:52:23

quote:
But data count can be changed.

what do you mean ?

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

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2012-03-12 : 09:13:18

is there a way without CTE ?
Also i have data. dont do insert for data please.

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2012-03-12 : 09:26:40

Below code is work good.
Anything is wrong ?
x_borc is P and x_alacak is N
But i need PN kind Column P or N as data.
How can i do for below code this ?
SELECT x_borc, x_alacak,
(SELECT ABS(SUM(x_borc) - SUM(x_alacak)) AS Expr1
FROM x_carihareket AS t2
WHERE (x_harekettarihi < t1.x_harekettarihi) OR
(x_harekettarihi = t1.x_harekettarihi) AND (x_id <= t1.x_id)) AS cum
FROM x_carihareket AS t1
ORDER BY x_harekettarihi, x_id

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-12 : 09:42:43

[code]
select t.*,
PNColumn = abs(isnull(PNkindColumn, 0)) + ColumnN + ColumnP,
PNkindColumn = isnull(PNkindColumn, 0) + ColumnP - ColumnN
from x_carihareket t
cross apply
(
select PNkindColumn = sum(ColumnP - ColumnN)
from x_carihareket x
where x.row_no < t.row_no
) pn
[/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