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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy