Sybase Query 1

[chinnibay]

Hi

Can any one help me out with this query . Can this be shorter and better in performance ?

Thanks in Advance

1 SELECT
2 Average_Balance.acct_ttl_tx,
3 Average_Balance.acct_type,
4 Average_Balance.local_ccy_code
5 Average_Balance.bank_security_no,
6 Average_Balance.security_desc_line_1,
7 Average_Balance.Mon_Avg_Bal,
8 Average_Balance.End_Of_Month,
9 Average_Balance.Mth_Year,
10 Average_Balance.fd_family_nm,
11 Average_Balance.fd_nm,
12 Average_Balance.summary_flg,
13 FROM
14 (
15 SELECT
16 Average_Balance.acct_ttl_tx,
17 Average_Balance.acct_type,
18 Average_Balance.local_ccy_code
19 Average_Balance.bank_security_no,
20 Average_Balance.security_desc_line_1,
21 sum(Average_Balance.Mon_Avg_Bal) as Mon_AVG_Bal,
22 Average_Balance.End_Of_Month
23 Average_Balance.Mth_Year,
24 Average_Balance.fd_family_nm,
25 Average_Balance.fd_nm,
26 case when "YES"="YES" then NULL ELSE "1" END as summary_flg
27 FROM
28 (
29 select
30 tab2.acct_tt1_tx,
31 tab2.acct_type,
32 tab2.local_ccy_code,
33 tab2.bank_security_no,
34 tab2.security_desc_line_1,
35 tab1.Mon_Avg_Bal,
36 tab2.Clndr_date End_Of_Month,
37 RTRIM(convert (char(4),year(Clndr_date)) AS Mth_Year,
convert(char(4),year(Clndr_date)) AS Mth_Year,
38 tab2.fd_family_nm,
39 tab1.fd_nm,
40 tab1.Last_Day_in_Month,
41 tab1.End_Of_Month1
42 from
43 (
44 select
45 bocalendar.clndr_dt,
46 mkt.acct_no,
47 mkt.acct_tt1_tx,
48 mkt.acct_type,
49 mkt.local_ccy_code,
50 mkt.bank_security_no,
51 mkt.security_desc_line_1,
52 sum
53 (mkt.sum_mkt_value)/(case when (mkt.cur_dt >= bocalendar.clndr_dt)
and
54 (datepart(mm,mkt.cur_dt) <> datepart(mm,bocalendar.clndr_dt)or
55 datepart(yy,mkt.cur_dt) <> datepart(yy,bocalendar.clndr_dt)
56 )
57 then
58 datepart(dd,dateadd(dd,dateadd(dd, -day(dateadd(mm,1,bocalendar.clndr_dt)),
dateadd(mm,1,bocalendar.clndr_dt)))
59 else
60 datepart(dd,dateadd(day,-1,mkt.cur_dt))
61 end) as Mon_Avg_Bal,
62 case when
63 (mkt.cur_dt >= bocalendar.clndr_dt) and
64 (
65 datepart(mm,mkt.cur_dt) <> datepart(mm,bocalendar.clndr_dt)
66 or
67 datepart(yy,mkt.cur_dt) <> datepart(yy,bocalendar.clndr_dt)
68 )
69 then
70 dateadd(dd, -day(dateadd(mm,1,bocalendar.clndr_dt)), dateadd(mm,1,bocalendar.clndr_dt))
71 else
72 dateadd(day , -1 , mkt.cur_dt )
73 end End_Of_Month1,
74 case when
75 (mkt.cur_dt >= bocalendar.clndr_dt) and (datepart(mm,mkt.cur_dt) <>
datepart(mm,bocalendar.clndr_dt)
76 or datepart(yy,mkt.cur_dt) <> datepart(yy,bocalendar.clndr_dt)
77 ) then
78 datepart(dd,dateadd(dd, -day(dateadd(mm,1,bocalendar.clndr_dt)),
dateadd(mm ,1,bocalendar.clndr_dt)))
79 else
80 datepart(dd,dateadd(day, -1, mkt.cur_dt))
81 end Nf_Days,
82 datename(month,bocalendar.clndr_dt) AS MonthName,
83 datename(year,bocalendar.clndr_dt) AS Yearno,
84 mkt.fd_family_nm,
85 mkt.fd_nm,
86 mkt.Last_Day_in_Month
87 from
88 (
89 SELECT clndr.currency_code,
90 clndr_dt,
91 CASE
92 WHEN bus_day_f1 = "N" THEN clndr.prv_bus_dt
93 ELSE
94 clndr_dt
95 END bo_bdate,
96 CASE
97 WHEN wkdy_f1 = "N" THEN 1
98 ELSE
99 0
100 END f1_week_end,
101 cur_dt
102 FROM
103 dbo.t_mfd_rpt_clndr clndr,
104 dbo.t_mfd_rpt_currency ccy,
105 dbo.t_mfd_rpt_dt
106 WHERE clndr.clndr_dt BETWEEN '02/01/2009 12:0:0 am' AND
'01/31/2010 12:0:0 am '
107 AND ccy.currency_code
108 = clndr.currency_code AND ( clndr_dt < CONVERT(datetime, CONVERT (VARCHAR(02))
109 , datepart(MONTH, cur_dt)) + '/01' + CONVERT(VARCHAR(04), datepart(year,
110 cur_dt))))
111 )bocalendar
112 inner join
113 (
114 SELECT
115 t_mfd_rpt_acct.acct_no,
116 t_mfd_rpt_acct.acct_ttl_tx,
117 t_mfd_rpt_cur_asset_holdings.local_ccy_code,
118 t_mfd_rpt_cur_asset_holdings.bank_security_no,
119 t_mfd_rpt_cur_asset_holdings.security_desc_line_1,
120 t_mfd_rpt_cur_asset_holdings.acct_type,
121 t_mfd_rpt_dt.cur_dt,
122 dateadd(dd , -day(dateadd(mm , 1 ,
t_mfd_rpt_cur_asset_holdings.close_of_business_date
123 )) , dateadd(mm , 1,
t_mfd_rpt_cur_asset_holdings.close_of_business_date))AS
124 Last_Day_in_Month,
125 (t_mfd_rpt_cur_asset_holdings.close_of_business_date)
close_of_business_date,
126 (t_mfd_rpt_cur_asset_holdings.total_qty) sum_mkt_value,
127 dbo.t_mfd_rpt_fd_clas.fd_family_nm,
128 dbo.t_mfd_rpt_fd_clas.fd_nm,
129 FROM
130 dbo.t_mfd_rpt_dt, dbo.t_mfd_rpt_fd_clas RIGHT OUTER JOIN
dbo.t_mfd_rpt_cur_asset_holdings
131 ON (dbo.t_mfd_rpt_fd_clas.fd_no =
dbo.t_mfd_rpt_cur_asset_holdings.bank_security_no)
132 INNER JOIN dbo.t_mfd_rpt_acct ON (dbo.t_mfd_rpt_acct.acct_no=
dbo.t_mfd_rpt_cur_asset_holdings.acct_no)
133 INNER JOIN dbo.py_inx_ebi_get_unravel_accts ON (
dbo.t_mfd_rpt_acct.acct_no = dbo.py_inx_ebi_get_unravel_accts.subacct_no
134 )
135 WHERE
136 dbo.t_mfd_rpt_cur_asset_holdings.close_of_business_date
137 IN (
138 SELECT DISTINCT
139 CASE
140 WHEN bus_day_f1 = "N" THEN prv_bus_dt
141 ELSE
142 clndr_dt
143 END
144 FROM dbo.t_mfd_rpt_clndr
145 WHERE dbo.t_mfd_rpt_clndr.clndr_dt BETWEEN '02/01/2009 12 :0 :0 am '
AND '01/31/2010 12:0:0 am'
146 ) AND
147 (
148 dbo.t_mfd_rpt_cur_asset_holdings.bank_security_no LIKE 'S999%'
149 OR
150 dbo.t_mfd_rpt_cur_asset_holdings.bank_security_no LIKE 'X9X9%'
151 OR
152 dbo.t_mfd_rpt_cur_asset_holdings.bank_security_no LIKE 'S875996%'
153 )
154 AND
155 (
156 dbo.t_mfd_rpt_cur_asset_holdings.bank_security_no in ("ALL")
157 OR
158 'ALL' in ("ALL")
159 )
160 AND
161 (
162 dbo.t_mfd_rpt_fd_clas.fd_family_nm in ("ALL")
163 OR
164 'ALL' in ("ALL")
165 )
166 AND
167 dbo.py_inx_ebi_get_unravel_accts._user_no = 'MFDTSTJW'
168 AND
169 dbo.py_inx_ebi_get_unravel_accts._acct_no = (case when len('G3INF0000000042977')=12 then '000' ||
substring('G3INF0000000042977' ,3,6)
170 else 'G3INF0000000042977' end)
171 AND
172 dbo.py_inx_ebi_get_unravel_accts._ugrp_no = 'GRP028368'
173 AND
174 dbo.py_inx_ebi_get_unravel_accts._prod_no = 'SYST00000000523'
175 )
176 mkt
177 on
178 (mkt.close_of_business_date =bocalendar.bo_bdate) and
179 (mkt.local_ccy_code=bocalendar.currency_code)
180 group by
181 bocalendar.clndr_dt,
182 mkt.acct_no,
183 mkt.acct_ttl_tx,
184 mkt.acct_type,
185 mkt.local_ccy_code,
186 mkt.bank_security_no,
187 mst.security_desc_line_1,
188 case when
189 (mkt.cur_dt >= bocalendar.clndr_dt) and
190 (
191 datepart(mm,mkt.cur_dt) <> datepart (mm,bocalendar.clndr_dt)
192 or
193 datepart(yy,mkt.cur_dt) <> datepart(yy,bocalendar.clndr_dt)
194 )
195 then
196 dateadd(dd, -day(dateadd(mm , 1, bocalendar.clndr_dt)), dateadd(mm , 1 ,bocalendar.clndr_dt))
197 else
198 dateadd(day , -1 , mkt.cur_dt)
199 end,
200 case when
201 (mkt.cur_dt >= bocalendar.clndr_dt) and (datepart(mm,mkt.cur_dt) <> datepart(mm,bocalendar.clndr_dt)
202 or datepart(yy,mkt.cur_dt) <> datepart(yy,bocalendar.clndr_dt)
203 ) then
204 datepart(dd, dateadd(dd,-day(dateadd(mm , 1, bocalendar.clndr_dt)),
dateadd(mm,1, bocalendar.clndr_dt)))
205 else
206 datepart(dd,dateadd(day , -1 , mkt.cur_dt))
207 end,
208 datename(month,bocalendar.clndr_dt),
209 datediff(mm,'01/31/2010 12:0:0 am' , case when
210 (mkt.cur_dt >= bocalendar.clndr_dt) and
211 (
212 datepart(mm,mkt.cur_dt) <> datepart(mm,bocalendar.clndr_dt)
213 or
214 datepart(yy,mkt.cur_dt) <> datepart(yy,bocalendar.clndr_dt)
215 )
216 then
217 dateadd(dd,-day(dateadd(mm,1,bocalendar.clndr_dt)),dateadd(mm,1,bocalendar.clndr_dt))
218 else
219 dateadd(day , -1 , mkt.cur_dt)
220 end)
221 datename(year,bocalendar.clndr_dt),
222 mkt.fd_family_nm,
223 mkt.fd_nm,
224 mkt.Last_Day_in_Month
225 )tab1
226 Right outer join
227 (
228 select
229 distinct
230 dateadd(dd,-day(dateadd(mm,1,t_mfd_rpt_clndr.clndr_dt)),
dateadd(mm,1,t_mfd_rpt_clndr.clndr_dt)) AS clndr_date,
231 datediff (mm,'01/31/2010 12:0:0 am',case when
232 (cur_dt >= t_mfd_rpt_clndr.clndr_dt) and
233 (
234 datepart(mm,cur_dt) <> datepart(mm,t_mfd_rpt_clndr.clndr_dt)
235 or
236 datepart(yy,cur_dt) <> datepart(yy,t_mfd_rpt_clndr.clndr_dt)
237 )
238 then
239 dateadd(dd,-day(dateadd(mm,1,t_mfd_rpt_clndr.clndr_dt)),dateadd(mm,1,t_mfd_rpt_clndr.clndr_dt))
240 else
241 dateadd(day, -1, cur_dt)
242 end) AS MonthNo,
243 t_mfd_rpt_act.acct_no,
244 t_mfd_rpt_act.acct_ttl_tx,
245 t_mfd_rpt_cur_asset_holdings.local_ccy_code,
246 t_mfd_rpt_cur_asset_holdings.bank_security_no,
247 t_mfd_rpt_cur_asset_holdings.security_desc_line_1,
248 t_mfd_rpt_cur_asset_holdings.acct_type,
249 t_mfd_rpt_fd_class.fd_family_nm
250 from
251 dbo.t_mfd_rpt_clndr,
252 dbo.t_mfd_rpt_dt,dbo.t_mfd_rpt_acct,
253 dbo.t_mfd_rpt_cur_asset_holdings,
254 dbo.t_mfd_rpt_fd_class,
255 dbo.py_inx_ebi_get_unravel_accts
256 where
257 dbo.t_mfd_rpt_act.acct_no=dbo.py_inx_ebi_get_unravel_accts.subaccount_no
258 and t_mfd_rpt_act.acct_no=dbo.t_mfd_rpt_cur_asset_holdings
259 and t_mfd_rpt_fd_clas.fd_no=t_mfd_rpt_cur_asset_holdings.bank_security_no
260 and dbo.t_mfd_rpt_clndr.currency_code=t_mfd_rpt_cur_asset_holdings.local_ccy_code
261 AND
262 clndr_dt BETWEEN '02/01/2009 12 :0 :0 am 'AND '01/31/2010 12:0:0 am'
263 AND
264 dbo.py_inx_ebi_get_unravel_accts._user_no = 'MFDTSTJW'
265 AND
266 dbo.py_inx_ebi_get_unravel_accts._acct_no=(case when len ('G3INF0000000042977')=12 then '000' ||
substring('G3INF0000000042977' ,3,6)
267 else 'G3INF0000000042977' end)
268 AND
269 dbo.py_inx_ebi_get_unravel_accts._ugrp_no = 'GRP028368'
270 AND
271 dbo.py_inx_ebi_get_unravel_accts._prod_no = 'SYST00000000523'
272 )tab2
273 on
274 tab1.End_Of_Month1=tab2.Clndr_date AND
275 tab1.acct_no=tab2.acct_no AND
276 tab1.bank_security_no=tab2.bank_security_no AND
277 tab1.security_desc_line_1=tab2.security_desc_line_1 AND
278 tab1.fd_family_nm=tab2.fd_family_nm
279 ) Average_Balance
280 GROUP BY
281 Average_Balance.acct_ttl_tx,
282 Average_Balance.acct_type,
283 Average_Balance.local_ccy_code
284 Average_Balance.bank_security_no,
285 Average_Balance.security_desc_line_1,
286 Average_Balance.End_Of_Month,
287 Average_Balance.Mth_Year,
288 Average_Balance.fd_family_nm,
289 Average_Balance.fd_nm
290 ) Average_Balance

 

[NastiaMurzin]

select * from tablename;
go
or
select * from tablename where tablename.columnname = tablename2.columnvaluepreferrablythesamecolumnname;
go
or
select * from tablename where columnname = columnvalue;
go
All of the above is in transact-sql.