Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Query suggestion required
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 06/27/2001 :  05:09:32  Show Profile  Reply with Quote
Hi there,

i have information stored in various table abt a item transaction.
the tables are

Inv_header Inv_Details
-------------------------------
INvhkey Invhkey
Trnno invdetkey
trndate item_code
Trntype qty
tot_amount rate
tot_discount

sr_header sr_Details
-------------------------------
srhkey srhkey
Trnno srdetkey
date item_code
Trntype qty
tot_amount rate
tot_discount
posted

pr_header pr_Details
------------ ----------------
INvhkey Invhkey
Trnno invdetkey
trndate item_code
Trntype qty
tot_amount rate
tot_discount
posted

srv_header srv_Details
-------------------------------
srvhkey srvhkey
Trnno srvdetkey
trndate item_code
Trntype qty
tot_amount rate
tot_discount
posted

adj_header adj_Details
-------------------------------
adjhkey adjvhkey
Trnno invdetkey
trndate item_code
Trntype qty
tot_amount rate
tot_discount
posted



do_header do_Details
-------------------------------
adjhkey adjvhkey
Trnno invdetkey
trndate item_code
Trntype qty
tot_amount rate
tot_discount
posted
inhkey


Other then this each of this table contains other information relating to the

transaction.

My Requirement is , i want to display all the transaction in the order of date i

specify.

i have done it creating a view

create view item_statistics as

select INV_HEADER.TRNNO,INV_HEADER.TRNDATE,'IN' as Type,

INV_HEADER.REF,INV_HEADER.CUSTOMERKEY,INV_DETAILS.ITEM_CODE,
INV_DETAILS.QTY from INV_DETAILS,INV_HEADER where
INV_DETAILS.INVHKEY=INV_HEADER.INVHKEY AND INV_HEADER.INV_TYPE='CA' and

inv_header.posted='Y'
UNION
select SR_HEADER.TRNNO,SR_HEADER.DATE,'SR' as

Type,SR_HEADER.REF,SR_HEADER.CUSTOMERKEY,SR_DETAILS.ITEM_CODE,
SR_DETAILS.QTY from SR_DETAILS,SR_HEADER where
SR_DETAILS.SRHKEY=SR_HEADER.SRHKEY and SR_HEADER.posted='Y'
UNION
select SRV_HEADER.TRNNO,SRV_HEADER.TRNDATE,'RC' as

Type,SRV_HEADER.ORDERTYPE,SRV_HEADER.SUPPLIERKEY,SRV_DETAILS.ITEM_CODE,
SRV_DETAILS.QTY from SRV_DETAILS,SRV_HEADER where
SRV_DETAILS.SRVHKEY=SRV_HEADER.SRVHKEY AND SRV_HEADER.POSTED='Y'
UNION
select PR_HEADER.TRNNO,PR_HEADER.TRNDATE,'PR' as

Type,PR_HEADER.ORDERTYPE,PR_HEADER.SUPPLIERKEY,PR_DETAILS.ITEM_CODE,
PR_DETAILS.QTY from PR_DETAILS,PR_HEADER where
PR_DETAILS.PRHKEY=PR_HEADER.PRHKEY AND POSTED='Y'
UNION
select STK_ADJ_HEADER.TRNNO,STK_ADJ_HEADER.DATE,'AD' as Type,STK_ADJ_HEADER.REF,
'',STK_ADJ_DETAILS.ITEM_CODE,
STK_ADJ_DETAILS.QTY from STK_ADJ_DETAILS,STK_ADJ_HEADER where
STK_ADJ_DETAILS.ADJHKEY=STK_ADJ_HEADER.ADJHKEY AND POSTED='Y'
UNION
select INV_HEADER.TRNNO,DO_HEADER.DATE,'DO' as

Type,CONVERT(VARCHAR(10),DO_HEADER.TRNNO) AS REF,
INV_HEADER.CUSTOMERKEY,DO_DETAILS.ITEM_CODE,
DO_DETAILS.QTY from DO_DETAILS,DO_HEADER,inv_header where
DO_DETAILS.DOHKEY=DO_HEADER.DOHKEY AND DO_HEADER.INVHKEY=INV_HEADER.INVHKEY
and do_header.posted='Y'

Another way i can do is adding a recored for every Item in a seperate table, but tht

will endup having couple of lakh records per week.

is there any other way i can do it, i seriously think this can be improved. it takes

lot of time to execute the above view , the prob is most of time the users want use it

for viewing the item history.

Looking forward from the sql guru's there.

I know ,i shouldnt use cursors(atleast thts wht nr recommends or rather propogates:) )



  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000