Getting groupby by record value in second row TSQL

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

Getting groupby by record value in second row TSQL

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2014-12-14 : 04:05:25

I have following query

Select Date, Item_Code, SUM(In_Quantity) as In_Quantity, SUM(Issue_Quantity) as Issue_Quantity, (SUM(In_Quantity)-SUM(issue_Quantity)) as BalanceQty from

(

select tbl_add_product.Date as Date, tbl_add_product.Item_Code, tbl_add_product.In_Quantity, 0 as Issue_Quantity from tbl_add_product
where Item_Code = 'pen'
union ALL

select tbl_issue_product.Date as Date, tbl_issue_product.Item_Code, 0 as In_Quantity, Issue_Quantity from tbl_issue_product
where Item_Code = 'pen'

)

X group by Item_Code, Date
which gives following result:

**Date Item_Code In_Quantity Issue_Quanitity BalanceQty**
2014-12-02 pen 100 0 100
2014-12-03 pen 50 50 0
I want to 100 in second row. the logic is that balance Qty from first row should be added to In_Qty so that when Issue_Quantity is subtracted from it, it gives BalanceQty

Please suggest solution

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-14 : 08:23:34

Not sure what you mean by "I want to 100 in second row." Do you mean:

1. I want two 100's in the second row?
2. I want to see 100 in <which column?> in the second row?
3. Something else?

Can you also please post:

1. Some input data
2. THe results you expect from your query using that input data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources