February 28, 2012 at 6:18 am
I’d like to create several views using the command below, but would like a loop that goes through a list of client IDs, i.e. TA, TB, TC etc and changes the view name and client each time. Is there a quick and easy way to do this?
CREATE VIEW TA_opprofit_brt AS
SELECT dim1 AS account,
dim2 AS costc,
CASE WHEN dim1 LIKE '3%' -- revenue accounts
THEN SUM(-amount)
ELSE 0
END AS income,
CASE WHEN dim1 LIKE '[45678]%' -- expenditure accounts
THEN SUM(amount)
ELSE 0
END AS expenditure,
SUM(-amount) AS profit,
client,
period
FROM aglaggadp
WHERE dim1 LIKE '[345678]%'
AND client = 'TA'
GROUP BY dim1, dim2, client, period
;
February 29, 2012 at 5:02 am
tobbens (2/28/2012)
I’d like to create several views using the command below, but would like a loop that goes through a list of client IDs, i.e. TA, TB, TC etc and changes the view name and client each time. Is there a quick and easy way to do this?CREATE VIEW TA_opprofit_brt AS
SELECT dim1 AS account,
dim2 AS costc,
CASE WHEN dim1 LIKE '3%' -- revenue accounts
THEN SUM(-amount)
ELSE 0
END AS income,
CASE WHEN dim1 LIKE '[45678]%' -- expenditure accounts
THEN SUM(amount)
ELSE 0
END AS expenditure,
SUM(-amount) AS profit,
client,
period
FROM aglaggadp
WHERE dim1 LIKE '[345678]%'
AND client = 'TA'
GROUP BY dim1, dim2, client, period
;
Maybe something like this:
-- Create View Name List
DECLARE @ViewNames AS TABLE (view_name SYSNAME)
INSERT @ViewNames
VALUES
('View1'), ('View2'), ('View3');
-- Generate Create View Sql
SELECT
'CREATE VIEW ' + view_name +' AS
SELECT dim1 AS account,
dim2 AS costc,
CASE WHEN dim1 LIKE ''3%'' -- revenue accounts
THEN SUM(-amount)
ELSE 0
END AS income,
CASE WHEN dim1 LIKE ''[45678]%'' -- expenditure accounts
THEN SUM(amount)
ELSE 0
END AS expenditure,
SUM(-amount) AS profit,
client,
period
FROM aglaggadp
WHERE dim1 LIKE ''[345678]%''
AND client = ''TA''
GROUP BY dim1, dim2, client, period;
GO'
FROM @ViewNames;
You will have to execute the output.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply