venkiatmarut
04-28-2010, 04:59 AM
Hi,
:banghead:
Can we execute a group of SQL statements in Excel without creating a Stored procedure on database.
I have a group of sql statments
IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE name = 'rpt') DROP TABLE tempdb.guest.rpt
GO
CREATE TABLE tempdb.guest.rpt
( port_num INT NULL,
port_name VARCHAR(25) NULL,
pos_num INT NULL,
risk_mkt VARCHAR(25) NULL,
cmdty_full_name VARCHAR(40) NULL,
balance_period CHAR(8) NULL,
open_close_ind CHAR(1) NULL,
inv_cur_actual_qty FLOAT NULL,
inv_cost_uom_code CHAR(4) NULL,
pl_amt FLOAT NULL
)
IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE name = 'tmp') DROP TABLE tempdb.guest.tmp
GO
CREATE TABLE tempdb.guest.tmp
( pos_num INT NOT NULL,
pl_amt FLOAT NULL
)
DECLARE @trading_period VARCHAR(20)
DECLARE @pl_asof_date DATETIME
SELECT @trading_period = '201003'
SELECT @pl_asof_date = '20100319'
INSERT INTO tempdb.guest.rpt
(port_num,
port_name,
pos_num,
risk_mkt,
cmdty_full_name,
balance_period,
open_close_ind,
inv_cur_actual_qty,
inv_cost_uom_code
)
SELECT
DISTINCT
INV.port_num,
PRT.port_full_name,
INV.pos_num,
TIM.risk_mkt_code ,
CMD.cmdty_full_name,
INV.balance_period,
INV.open_close_ind,
SUM(INV.inv_curr_actual_qty),
INV.inv_cost_uom_code
FROM tbl1 AS INV
LEFT JOIN tbl2 AS PRT ON INV.port_num=PRT.port_num
LEFT JOIN tbl3 AS CMD ON INV.cmdty_code=CMD.cmdty_code
LEFT JOIN tbl4 AS TIM ON INV.trade_num=TIM.trade_num
AND INV.order_num = TIM.order_num
AND INV.sale_item_num = TIM.item_num
WHERE
1 = 1
AND balance_period = @trading_period
AND INV.port_num IN (SELECT DISTINCT port_num FROM portfolio_group
WHERE parent_port_num IN (00004,00002,54645,4556,
))
AND INV.trade_num <> 56
GROUP BY
INV.pos_num,
--INV.trade_num,
--inv_num,
INV.port_num,
PRT.port_full_name,
--INV.del_loc_code,
CMD.cmdty_full_name,
balance_period,
open_close_ind,
--inv_curr_actual_qty,
--inv_avg_cost,
INV.inv_cost_uom_code,
TIM.risk_mkt_code
INSERT INTO tempdb.guest.tmp
SELECT TMP.pos_num, SUM(PLH.pl_amt)
FROM
tempdb.guest.rpt TMP,
pl_history PLH
WHERE 1=1
AND PLH.pos_num = TMP.pos_num
AND PLH.pl_asof_date = @pl_asof_date
AND PLH.pl_owner_sub_code IS NULL
GROUP BY TMP.pos_num
UPDATE tempdb.guest.rpt
SET tempdb.guest.rpt.pl_amt = tempdb.guest.tmp.pl_amt
FROM tempdb.guest.tmp
WHERE tempdb.guest.rpt.pos_num = tempdb.guest.tmp.pos_num
SELECT * FROM tempdb.guest.rpt ORDER by port_num
Kindly help me on this.
Thanks.
:banghead:
Can we execute a group of SQL statements in Excel without creating a Stored procedure on database.
I have a group of sql statments
IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE name = 'rpt') DROP TABLE tempdb.guest.rpt
GO
CREATE TABLE tempdb.guest.rpt
( port_num INT NULL,
port_name VARCHAR(25) NULL,
pos_num INT NULL,
risk_mkt VARCHAR(25) NULL,
cmdty_full_name VARCHAR(40) NULL,
balance_period CHAR(8) NULL,
open_close_ind CHAR(1) NULL,
inv_cur_actual_qty FLOAT NULL,
inv_cost_uom_code CHAR(4) NULL,
pl_amt FLOAT NULL
)
IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE name = 'tmp') DROP TABLE tempdb.guest.tmp
GO
CREATE TABLE tempdb.guest.tmp
( pos_num INT NOT NULL,
pl_amt FLOAT NULL
)
DECLARE @trading_period VARCHAR(20)
DECLARE @pl_asof_date DATETIME
SELECT @trading_period = '201003'
SELECT @pl_asof_date = '20100319'
INSERT INTO tempdb.guest.rpt
(port_num,
port_name,
pos_num,
risk_mkt,
cmdty_full_name,
balance_period,
open_close_ind,
inv_cur_actual_qty,
inv_cost_uom_code
)
SELECT
DISTINCT
INV.port_num,
PRT.port_full_name,
INV.pos_num,
TIM.risk_mkt_code ,
CMD.cmdty_full_name,
INV.balance_period,
INV.open_close_ind,
SUM(INV.inv_curr_actual_qty),
INV.inv_cost_uom_code
FROM tbl1 AS INV
LEFT JOIN tbl2 AS PRT ON INV.port_num=PRT.port_num
LEFT JOIN tbl3 AS CMD ON INV.cmdty_code=CMD.cmdty_code
LEFT JOIN tbl4 AS TIM ON INV.trade_num=TIM.trade_num
AND INV.order_num = TIM.order_num
AND INV.sale_item_num = TIM.item_num
WHERE
1 = 1
AND balance_period = @trading_period
AND INV.port_num IN (SELECT DISTINCT port_num FROM portfolio_group
WHERE parent_port_num IN (00004,00002,54645,4556,
))
AND INV.trade_num <> 56
GROUP BY
INV.pos_num,
--INV.trade_num,
--inv_num,
INV.port_num,
PRT.port_full_name,
--INV.del_loc_code,
CMD.cmdty_full_name,
balance_period,
open_close_ind,
--inv_curr_actual_qty,
--inv_avg_cost,
INV.inv_cost_uom_code,
TIM.risk_mkt_code
INSERT INTO tempdb.guest.tmp
SELECT TMP.pos_num, SUM(PLH.pl_amt)
FROM
tempdb.guest.rpt TMP,
pl_history PLH
WHERE 1=1
AND PLH.pos_num = TMP.pos_num
AND PLH.pl_asof_date = @pl_asof_date
AND PLH.pl_owner_sub_code IS NULL
GROUP BY TMP.pos_num
UPDATE tempdb.guest.rpt
SET tempdb.guest.rpt.pl_amt = tempdb.guest.tmp.pl_amt
FROM tempdb.guest.tmp
WHERE tempdb.guest.rpt.pos_num = tempdb.guest.tmp.pos_num
SELECT * FROM tempdb.guest.rpt ORDER by port_num
Kindly help me on this.
Thanks.