help for 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

SQL Server 2000 Forums

Transact-SQL (2000)

help for query..

Author

Topic

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2007-07-24 : 07:24:35

I have got two tables , the following:-

mstBranchDetail (branch_id (int), BrnchParent (varchar),BranchName (varchar),Notes (varchar),CancelId (int))

sample data: -

2 SLA DE N.A 0
3 SLA MU N.A 0
4 SLA GU N.A 0
5 SLA DI N.A 0
6 SLA NA N.A 0
7 SLA BH N.A 0
8 SLA KO (SLA) N.A 0
9 SLC KO (SLC) N.A 0
10 SLC CH N.A 0
11 SLC SH N.A 0
12 SLC BA N.A 0
13 SLC CO N.A 0
14 SLA RA N.A 0

mstStockDetail (Stock_Id (int), Tran_Id (bigint),oper_Id (tinyint),
BrnchParent (varchar),Stock_Date (datetime), BookRefNo (varchar) ,branch_id (int),
stock_qty (int),Folio_no (varchar))

stock_id = primary key

tran_id and oper_id wil always be 0 and 5

brnchparent = sla or slc

branch_id = from previous table.

bookrefno = atl06, art37 etc ( an alpha-numeric value)

stock_date = mainly 01-apr-07

stock_qty = 10 or -2 etc

folio_no = an alpha-numeric value

requirement :-

a query that finds out the branchnames, opening_stock, current_stock

opening_stock = this is a summation of stock_qty calculated from 01-apr-07 till any
date(input from the form),for a particular branch_id(input from the form),
bookrefno(input from the form) and brnchparent(input from the form.)

current_stock = this is a summation of stock_qty calculated from 01-apr-07 till any getdate()
for a particular branch_id(input from the form), bookrefno(input from the form)
and brnchparent(input from the form.)

i have written the following query..

select distinct(MSB.BranchName), MSD.Branch_Id,
(select sum(Stock_Qty) from mststockdetail
where stock_date between '01-apr-07' and '01-apr-07'
and bookrefno = 'atl06' and
BrnchParent = 'sla' and
Branch_Id = msd.Branch_Id) as Opening_Stock,

(select sum(stock_qty) from mststockdetail
Where stock_date <= getdate() and
bookrefno = 'atl06' and
BrnchParent = 'sla' and
branch_id = msd.branch_id) as current_stock

From mstStockDetail as MSD, mstBranchDetail as MSB
Where MSD.Branch_Id = MSB.Branch_Id And
MSB.BrnchParent = 'sla'

suppose ony one entry is present in the mststockdetail that is:-
1 0 5 SLA 2007-04-01 00:00:00.000 ATL06 8 10 1

the result set that is returned
branch_name= KO (SLA)
Opening_Stock = 10
current_stock = 10

But no other branchname is shown..

I want to modify the query that shows al the branchnames for that branchparent (sla) and
bookrefno

that is the required resultset wi be something like this :-

branchname Branch_Id Opening_Stock current_stock

BH 7 0 0
DE 2 0 0
DI 5 0 0
GU 4 0 0
KO (SLA) 8 10 10
MU 3 0 0
NA 6 0 0

any help is appreciated.

please feel free to post any questions, if you need any clarifications.

thanks and regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources