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  Site Related Forums  Article Discussion  Query suggestion required

Author  Topic  Nazim A custom title 1408 Posts Posted - 2001-06-27 : 05:09:32 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:) )