Difficult Task(a query unable to make any sense)
-
i am newly appointed person in a company, a person who was working before me, has left the job without notice /without any documents
iam being given one task which iam difficult to understand, iam given one stored procedure and i had been ask, to tell what this stored procedure does
so far 3 days have passed iam unable to understand anything
iam posting the procedure and schema of tables below
===========Stored procedure=============
-- sp_mfund_upt_rm_dtsrc_cams 'CAMS'
CREATE PROCEDURE [sp_mfund_upt_rm_dtsrc_cams]
@data_source_cd varchar(10)
AS
set @data_source_cd = 'cams'
select a.folio_no,a.product_cd,a.trade_dt,a.rm_cd,a.branch_cd into #tmp1
from mftrans a WHERE 1=2
select a.folio_no,a.product_cd,a.trade_dt,a.rm_cd,a.branch_cd into #tmp2
from mftrans a WHERE 1=2
INSERT INTO #tmp1
select a.folio_no,a.product_cd,a.trade_dt,a.rm_cd,a.branch_cd
from mftrans a join rm b on a.rm_cd = b.rm_cd
where b.type = 's' and data_source_cd = @data_source_cd
--and ltrim(rtrim(folio_no))+ltrim(rtrim(product_CD)) in (select distinct ltrim(rtrim(folio_no))+ltrim(rtrim(product_CD)) from mfdump)
order by folio_no,product_cd, trade_dt desc
INSERT INTO #tmp2
select a.folio_no,a.product_cd,a.trade_dt,a.rm_cd,a.branch_cd
from mftrans a join rm b on a.rm_cd = b.rm_cd
--and ltrim(rtrim(folio_no))+ltrim(rtrim(product_CD)) in (select distinct ltrim(rtrim(folio_no))+ltrim(rtrim(product_CD)) from mfdump)
where b.type = 'g' and data_source_cd = @data_source_cd order by folio_no,product_cd, trade_dt desc
update mftrans set rm_cd = R.rm_cd ,branch_cd = R.branch_Cd
from (select folio_no,product_cd,min(rm_cd) as rm_cd,min(branch_cd) as branch_cd
from #tmp1 group by folio_no,product_cd
having count(distinct rm_cd) = 1 )R , rm c
where mftrans.folio_no = R.folio_no and mftrans.product_cd = R.product_cd
and mftrans.rm_cd = c.rm_cd and c.type <> 's' --and mftrans.folio_no = '100230/10'
and data_source_cd = @data_source_cd
update mftrans set rm_cd = R.rm,branch_cd = branch
from
(
select a.folio_no,a.product_cd,a.trade_dt,b.rm_cd as rm,b.branch_cd as branch
from mftrans a join #tmp1 b
on a.folio_no = b.folio_no and a.product_cd = b.product_cd
and a.trade_dt >= b.trade_Dt --and a.folio_no = '100230/10'
join (select a.folio_no,a.product_cd,a.trade_dt,max(b.trade_dt) as mx_dt from mftrans a
join #tmp1 b
on a.folio_no = b.folio_no and a.product_cd = b.product_cd
and a.trade_dt >= b.trade_Dt
left join rm c on a.rm_cd = c.rm_cd
where a.rm_cd <> b.rm_cd --and a.folio_no = '100230/10'
and c.type <> 's'
and data_source_cd = @data_source_cd
group by a.folio_no,a.product_cd,a.trade_dt
 
DT on a.folio_no = DT.folio_no and a.product_cd = DT.product_cd and DT.trade_dt = a.trade_dtand DT.mx_Dt = b.trade_dt
left join rm c on a.rm_cd = c.rm_cd
where a.rm_cd <> b.rm_cd --and a.folio_no = '100230/10'
and c.type <> 's'
and data_source_cd = @data_source_cd
--order by a.folio_no,a.product_cd,a.trade_dt asc,b.trade_dt desc
 
Rwhere mftrans.folio_no = R.folio_no and mftrans.product_cd = R.product_cd and mftrans.trade_dt = R.trade_dt
--and mftrans.folio_no = '100230/10'
update mftrans set rm_cd = R.rm , branch_cd = branch
from
(
select a.folio_no,a.product_cd,a.trade_dt,b.rm_cd as rm,b.branch_cd as branch
from mftrans a join #tmp2 b
on a.folio_no = b.folio_no and a.product_cd = b.product_cd
and a.trade_dt >= b.trade_Dt --and a.folio_no = '100230/10'
join (select a.folio_no,a.product_cd,a.trade_dt,max(b.trade_dt) as mx_dt from mftrans a
join #tmp2 b
on a.folio_no = b.folio_no and a.product_cd = b.product_cd
and a.trade_dt >= b.trade_Dt
left join rm c on a.rm_cd = c.rm_cd
where a.rm_cd <> b.rm_cd --and a.folio_no = '100230/10'
--and c.type = 'g'
and (ltrim(rtrim(a.rm_Cd)) = '0' or a.rm_cd is null)
and data_source_cd = @data_source_cd
group by a.folio_no,a.product_cd,a.trade_dt
 
DT on a.folio_no = DT.folio_no and a.product_cd = DT.product_cd and DT.trade_dt = a.trade_dtand DT.mx_Dt = b.trade_dt
left join rm c on a.rm_cd = c.rm_cd
where a.rm_cd <> b.rm_cd --and a.folio_no = '100230/10'
--and c.type = 'g'
and (ltrim(rtrim(a.rm_Cd)) = '0' or a.rm_cd is null)
and data_source_cd = @data_source_cd
--order by a.folio_no,a.product_cd,a.trade_dt asc,b.trade_dt desc
 
Rwhere mftrans.folio_no = R.folio_no and mftrans.product_cd = R.product_cd and mftrans.trade_dt = R.trade_dt
-- and mftrans.folio_no = '100230/10'
update mftrans set rm_cd = r.rm_cd from
(select max(rm_cd) as rm_cd ,folio_no , product_cd from mftrans where ltrim(rtrim(folio_no))+ltrim(rtrim(product_cd))
in
(select ltrim(rtrim(folio_no))+ltrim(rtrim(product_cd)) from mftrans where rm_cd = '0'
and data_source_cd = @data_source_cd )
and rm_cd <> '0' and
data_source_cd = @data_source_cd group by folio_no , product_cd) r
where mftrans.rm_cd = '0' and mftrans.subbroker_Cd is null and
ltrim(rtrim(mftrans.folio_no))+ltrim(rtrim(mftrans.product_cd)) = ltrim(rtrim(r.folio_no))+ltrim(rtrim(r.product_cd))
and mftrans.transaction_type in(select transaction_type from transactiontype where transaction_effect = 'add')
--// Now Update the rm_cd for transactions which are updated manually.
select distinct A.AMC_CD ,a.folio_no , a.product_cd , A.rm_Cd , a.update_datetime
INTO #TMP
from mftrans_rm a WHERE 1=2
INSERT INTO #TMP
select distinct A.AMC_CD ,a.folio_no , a.product_cd , A.rm_Cd , a.update_datetime
from mftrans_rm a join
(select A.amc_cd,folio_no,product_cd ,max(update_datetime) as upt_dttime
from mftrans_rm a join amc b on a.amc_Cd = b.amc_Cd
where registrar = @data_source_cd
group by A.amc_cd,folio_no,product_cd ) B
on a.amc_cd = b.amc_Cd and a.folio_no = b.folio_no and a.product_cd = b.product_Cd
and a.update_datetime = b.upt_dttime
update mftrans set rm_cd = R.rm_cd , BRANCH_CD = R.BRANCH_CD from
(SELECT A.* , B.BRANCH_CD FROM #TMP A JOIN RM B ON A.RM_CD = B.RM_CD )R
where mftrans.amc_cd = R.amc_cd and mftrans.folio_no = R.folio_no
and mftrans.product_cd = R.product_cd
AND MFTRANS.data_source_cd = @data_source_cd
-- added by anish on 27/05/2003
UPDATE MFTRANS SET BRANCH_CD = A.BRANCH_CD
FROM RM A WHERE MFTRANS.RM_CD = A.RM_CD AND MFTRANS.rm_Cd <> '0'
and MFTRANS.branch_cd = 'NA'
UPDATE MFTRANS SET RM_CD = RM.RM_CD
FROM RM
WHERE MFTRANS.BRANCH_CD = RM.BRANCH_CD
AND MFTRANS.BRANCH_CD = 'NA' AND MFTRANS.RM_CD <> '0' and ltrim(rtrim(MFTRANS.RM_CD)) = ''
UPDATE MFTRANS SET branch_cd = RM.branch_cd
FROM RM
WHERE MFTRANS.rm_cd = RM.rm_cd
AND MFTRANS.BRANCH_CD <> 'NA' AND MFTRANS.RM_CD = '0'
/*
update mftrans set rm_cd = R.rm_cd from
(select a.* from mftrans_rm a join
(select amc_cd,folio_no,product_cd , max(update_datetime) as upt_dttime from mftrans_rm
group by amc_cd,folio_no,product_cd) B
on a.amc_cd = B.amc_cd and a.folio_no = B.folio_no and a.product_cd = B.product_cd
and a.update_datetime = B.upt_dttime )R
where mftrans.amc_cd = R.amc_cd and mftrans.folio_no = R.folio_no and mftrans.product_cd = R.product_cd
AND MFTRANS.data_source_cd = @data_source_cd
*/
/*
update mftrans set rm_cd = R.rm_cd from
(select a.* from mftrans_rm a join (select amc_cd,folio_no,product_cd,transaction_type,transaction_no,transaction_mode,trade_dt,max(update_datetime) as upt_dttime from mftrans_rm
group by amc_cd,folio_no,product_cd,transaction_type,transaction_no,transaction_mode,trade_dt) B
on a.amc_cd = B.amc_cd and a.folio_no = B.folio_no and a.product_cd = B.product_cd and a.transaction_type =B.transaction_type
and a.transaction_no = B.transaction_no and a.transaction_mode = B.transaction_mode and a.trade_dt=B.trade_dt
and a.update_datetime = B.upt_dttime )R
where mftrans.amc_cd = R.amc_cd and mftrans.folio_no = R.folio_no and mftrans.product_cd = R.product_cd
and mftrans.transaction_type =R.transaction_type and mftrans.transaction_no = R.transaction_no
and mftrans.transaction_mode = R.transaction_mode
and mftrans.trade_dt=R.trade_dt
AND MFTRANS.data_source_cd = @data_source_cd
*/
UPDATE MFTRANS SET RM_CD = '0' , BRANCH_CD = 'NA' WHERE RM_cD IS NULL
UPDATE MFTRANS SET RM_CD = '0' , BRANCH_CD = 'NA' WHERE BRANCH_CD IS NULL
UPDATE MFTRANS SET RM_CD = '0' , BRANCH_CD = 'NA' WHERE LTRIM(RTRIM(RM_cD)) = ''
-- exec procdatasourceuploadstatus 'I', @data_source_cd
===========================
======schema of mftrans table===
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ------------------------------------------------------
mftrans dbo user table 2005-08-08 19:13:37.540
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
mftrans_no int no 4 10 0 no (n/a) (n/a) NULL
amc_cd char no 5 yes no yes SQL_Latin1_General_CP1_CI_AS
folio_no varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
product_cd char no 15 yes no yes SQL_Latin1_General_CP1_CI_AS
investor_nm varchar no 100 yes no no SQL_Latin1_General_CP1_CI_AS
transaction_type char no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
transaction_no varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
transaction_mode char no 1 yes no yes SQL_Latin1_General_CP1_CI_AS
transaction_status char no 1 yes no yes SQL_Latin1_General_CP1_CI_AS
source_cd char no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
sourceserail_no char no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
trade_dt datetime no 8 yes (n/a) (n/a) NULL
posted_dt datetime no 8 yes (n/a) (n/a) NULL
price numeric no 9 18 4 yes (n/a) (n/a) NULL
units numeric no 9 18 4 yes (n/a) (n/a) NULL
purchase_amt numeric no 9 18 2 yes (n/a) (n/a) NULL
redemption_amt numeric no 9 18 2 yes (n/a) (n/a) NULL
broker_cd char no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
subbroker_cd char no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
brokerage_per numeric no 9 18 4 yes (n/a) (n/a) NULL
brokerage_amt numeric no 9 18 4 yes (n/a) (n/a) NULL
investor_id char no 16 yes no yes SQL_Latin1_General_CP1_CI_AS
datafeed_dt datetime no 8 yes (n/a) (n/a) NULL
datafeed_time varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
transactionsub_type varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
branch_cd char no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
rm_cd char no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
usr_id char no 15 yes no yes SQL_Latin1_General_CP1_CI_AS
upld_dt datetime no 8 yes (n/a) (n/a) NULL
p_units numeric no 9 18 4 yes (n/a) (n/a) NULL
r_units numeric no 9 18 4 yes (n/a) (n/a) NULL
redem_cost numeric no 9 18 4 yes (n/a) (n/a) NULL
amount numeric no 9 18 4 yes (n/a) (n/a) NULL
balance_units numeric no 9 18 4 yes (n/a) (n/a) NULL
balance_cost numeric no 9 18 4 yes (n/a) (n/a) NULL
data_source_cd char no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
IsValid char no 1 yes no yes SQL_Latin1_General_CP1_CI_AS
oTransType varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
inv_opt varchar no 25 yes no no SQL_Latin1_General_CP1_CI_AS
oPrice numeric no 9 18 4 yes (n/a) (n/a) NULL
redem_cost_kotak numeric no 9 18 4 yes (n/a) (n/a) NULL
balance_cost_kotak numeric no 9 18 4 yes (n/a) (n/a) NULL
balance_units_kotak numeric no 9 18 4 yes (n/a) (n/a) NULL
cams_no varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
transaction_flag varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
STT numeric no 9 18 4 yes (n/a) (n/a) NULL
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- -------------------
mftrans_no 1 1 0
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mftrans_indx nonclustered located on PRIMARY folio_no, product_cd, transaction_type, transaction_no, transaction_mode, trade_dt, units, amount, cams_no
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
=============schema of mftrans rm=========
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ------------------------------------------------------
mftrans_rm dbo user table 2003-05-20 18:22:30.827
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
mftrans_no int no 4 10 0 no (n/a) (n/a) NULL
amc_cd char no 5 yes no yes SQL_Latin1_General_CP1_CI_AS
folio_no varchar no 25 yes no no SQL_Latin1_General_CP1_CI_AS
product_cd char no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
transaction_type char no 15 yes no yes SQL_Latin1_General_CP1_CI_AS
transaction_no varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
transaction_mode char no 1 yes no yes SQL_Latin1_General_CP1_CI_AS
trade_dt datetime no 8 yes (n/a) (n/a) NULL
transactionsub_type varchar no 10 yes no no SQL_Latin1_General_CP1_CI_AS
old_rm_cd char no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
rm_cd char no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
usr_id char no 15 yes no yes SQL_Latin1_General_CP1_CI_AS
update_datetime datetime no 8 yes (n/a) (n/a) NULL
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
========================
===============Schema of Rm===============
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ------------------------------------------------------
rm dbo user table 2004-04-15 14:08:22.687
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
rm_cd varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
rm_name char no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
type char no 1 yes no yes SQL_Latin1_General_CP1_CI_AS
branch_cd char no 5 yes no yes SQL_Latin1_General_CP1_CI_AS
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pk_rm nonclustered located on PRIMARY rm_cd
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
======
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
Best approach is to execute the steps 1 by 1 and inspect the database and try to deduce the changes each section implements.
For a start the 1st 2 bits, create empty tables structures (1=2) based on column definitions in existing tables.
What this adds at a business-level, aside from what it does at at technical level, is another story.....it might be a good idea to go back at get the functional spec from the users!
-
Nope, thats not very useful.
my management wants to check my IQ, this is stock market iam working, data is in huge, cannot change anything unless i understand it correctly..
pls help, if u r Kool in SQL
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
"Nope, thats not very useful."...but it was a starter attempt for free.....it's taken you 3 days to get to this point and you're being paid (???)....you seem to have forgotten that none of the rest of us here will be getting a cheque at the end of this exercise
"my management wants to check my IQ"....there are better ways to do that!
"this is stock market iam working, data is in huge"...create a stripped down dataset in TEST.
"cannot change anything unless i understand it correctly"..good idea....but IN TEST, you could experiment (for education purposes) with a COPY of the original....and you wouldn't be modifying it, only running it in smaller sections.
-
Don't know why you bothered with the second reply Andrew. Nothing worse than rudeness.
suk - "Please be polite - If U R cruel in sql"
-
Eoin....
I wasn't being rude......I was being sarcastic. Rudeness is asking for help and then disparaging the "freely" given starter effort with a blunt "Nope, thats not very useful."
The points I made are VERY appropriate...the poster posed a difficult problem, but seem in the evidence/information presented to this website to expect contributors to work wonders, for free, without taking on board the 'sensible' suggestions made....If he/she did not like them, then the proper thing to do would have been to counterpoint them with valid reasons why.
There was no sample input data and no expected results and the expection coming from the poster is for us to debug something difficult and undocumented. I may be a fool at times in trying to help people, but I'm not an idiot to persist with other fools.
Off you go and solve it between the 2 of you man!!!! I suspect others will be adopting a hands-off approach on this one.
-
Erm... Andrew. I wasn't referring to you being rude. I was refering to Suk being rude to you and wondered why you even bothered to help him.
Apologies for the misunderstanding
-
ahaa....the value of punctuation/emoticons!!!..no offence taken (now)
suk still could learn from applying the hints.
why did I bother?...I did say i was a fool
-
Im' quite laz:y as re,gards . punctuation,: and
emoticons make me feel child,ish
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply