check this 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

check this query

Author

Topic

Areeba.ali.88
Starting Member

7 Posts

Posted - 2010-08-30 : 04:54:21

Hi All

i am writing a query

SELECT T0.DocNum, T0.DocDate, T0.CardName, T1.Dscription, T0.U_TranCat,T0.U_RGPStatus,
T0.Filler, T1.WhsCode,T1.StockPrice,
(case when t0.U_TranCat='RGP' then T1.Quantity else null end)'RGPQTY'
,(case when t0.U_TranCat='RRGP' then T1.Quantity else null end)'RRGPQTY'
FROM dbo.owtr T0 Left Outer Join wtr1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.DocEntry = T1.DocEntry
and T0.U_TranCat in('RGP','RRGP')
And T0.CardName = 'emp'
and T1.Dscription ='test'

it's giving me some result like


DocNum    Docdate     cardName Dscription U_TranCat    U_RGPStatus Filler    WhsCode     stock    RGPQTY     RRGPQTY
488       2010-08-23  Emp      Test       RGP	          O	   W-DI-HLD  W-DI-RGP	   500      10          NULL 
489       2010-08-23  Emp      Test       RRGP	          O	   W-DI-RGP  W-DI-HLD	   500      NULL        5
490       2010-08-27  Emp      Test       RGP	          N	   W-DI-HLD  W-DI-RGP	   500      15          NULL

I want Add more column that give me result Sum of RGPQTY column And Sum of RRGPQTY and A column that give the result Subtration of ( RGPQTY - RRGPQTY ) As RemainQTY

Exmple
Sum of RGPQTY coloum = 10+15=25
Sum of RRGP coloum = 5
RemainQTY =20
I want this output

DocNum Docdate     cardName Dscription U_TranCat U_RGPStatus Filler  WhsCode   stock  RGPQTY RRGPQTY SUMRGPQTY SUMRGPRQTY RemainQTY
488   2010-08-23    Emp	    Test       RGP	  O	    W-DI-HLD W-DI-RGP	500   10      NULL     25       5          20
489   2010-08-23    Emp	    Test       RRGP	  O	    W-DI-RGP W-DI-HLD	500   NULL    5
490   2010-08-27    Emp	    Test       RGP	  N	    W-DI-HLD W-DI-RGP	500   15      NULL

If it is possible Plz Provide the Query Solution
Thanks & regard
Areeba Ali

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 05:00:11

Make use of the new windowed functions available in SQL Server 2005 and later.

,SUM(RGPQTY) OVER (PARTITION BY cardName)

N 56°04'39.26"
E 12°55'05.63"

Areeba.ali.88
Starting Member

7 Posts

Posted - 2010-08-30 : 05:14:36

quote:
Originally posted by Peso

Make use of the new windowed functions available in SQL Server 2005 and later.
,SUM(RGPQTY) OVER (PARTITION BY cardName)

N 56°04'39.26"
E 12°55'05.63"

Can u provide me full code actually i am new in sql

Areeba

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 05:23:56

It depends. Do you use SQL Server 2005 or later?

N 56°04'39.26"
E 12°55'05.63"

Areeba.ali.88
Starting Member

7 Posts

Posted - 2010-08-30 : 06:46:16

I am using sql 2005

Areeba

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 06:59:32

SELECT T0.DocNum, T0.DocDate, T0.CardName, T1.Dscription, T0.U_TranCat,T0.U_RGPStatus,
T0.Filler, T1.WhsCode,T1.StockPrice,
(case when t0.U_TranCat='RGP' then T1.Quantity else null end)'RGPQTY'
,(case when t0.U_TranCat='RRGP' then T1.Quantity else null end)'RRGPQTY'
,SUM(case when t0.U_TranCat='RGP' then T1.Quantity else 0 end) OVER (PARTITION BY cardName)
FROM dbo.owtr T0 Left Outer Join wtr1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.DocEntry = T1.DocEntry
and T0.U_TranCat in('RGP','RRGP')
And T0.CardName = 'emp'
and T1.Dscription ='test'

N 56°04'39.26"
E 12°55'05.63"

Sachin.Nand

2937 Posts

Posted - 2010-08-30 : 07:09:14

[code]
select *,
sum(sum(RGPQTY))over() SUMRGPQTY,
sum(sum(RRGPQTY))over() SUMRRGPQTY from
(
SELECT T0.DocNum, T0.DocDate, T0.CardName, T1.Dscription, T0.U_TranCat,T0.U_RGPStatus,
T0.Filler, T1.WhsCode,T1.StockPrice,
(case when t0.U_TranCat='RGP' then T1.Quantity else null end)'RGPQTY'
,(case when t0.U_TranCat='RRGP' then T1.Quantity else null end)'RRGPQTY'
FROM dbo.owtr T0 Left Outer Join wtr1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.DocEntry = T1.DocEntry
and T0.U_TranCat in('RGP','RRGP')
And T0.CardName = 'emp'
and T1.Dscription ='test'
)T add the group by clauses for the all the columns in your select list
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Areeba.ali.88
Starting Member

7 Posts

Posted - 2010-08-30 : 07:13:16

very thanks peso

I got my two column Sum of RGPQTY and Sum of RRGPQTY but i not got remainqty column means ( RGPQTY - RRGPQTY ) As RemainQTY

i got value 25 and 5 now i want subtract these two column and want value 20 .
i am using ur giving query

SELECT T0.DocNum, T0.DocDate, T0.CardName, T1.Dscription, T0.U_TranCat,T0.U_RGPStatus,
T0.Filler, T1.WhsCode,T1.StockPrice,
(case when t0.U_TranCat='RGP' then T1.Quantity else null end)'RGPQTY'
,(case when t0.U_TranCat='RRGP' then T1.Quantity else null end)'RRGPQTY'
,SUM(case when t0.U_TranCat='RGP' then T1.Quantity else 0 end) OVER (PARTITION BY cardName)'sumofRGP'
,SUM(case when t0.U_TranCat='RRGP' then T1.Quantity else 0 end) OVER (PARTITION BY cardName)'SUMOFRRGP'
FROM dbo.owtr T0 Left Outer Join wtr1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.DocEntry = T1.DocEntry
and T0.U_TranCat in('RGP','RRGP')
And T0.CardName = 'emp'
and T1.Dscription ='test'

Thanks
Areeba Ali

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 07:29:03

,SUM(case t0.U_TranCat when 'RGP' then T1.Quantity when 'RRGP' THEN -T1.Quantity else 0 end) OVER (PARTITION BY cardName) AS RemainQty

N 56°04'39.26"
E 12°55'05.63"

Areeba.ali.88
Starting Member

7 Posts

Posted - 2010-08-30 : 07:42:45

thanks very much Peso

my problem has been solved
very very thanks

Thanks & regard
Areeba Ali

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources