SQL view or proceedure.. i'm missing something. what do i need here?

[B@Th*nG]

I effectively want to save the following as a view or procedure just so i can run it conveniently without having to copy and paste it in each day. It's nothing complicated but i can't seem to find a suitable container/process to store it. If it were a single SELECT then a view would do, but if i try to save this as a view it doesn't seem to like it. Any ideas?:

SELECT username
,(SELECT COUNT(*) AS totcount FROM TimeToComplete WHERE statusdesc NOT IN ('Wtg Client (Conf)','Wtg Client (info)','resolved','Development Investigation') AND username = ttc.username ) AS TotalCount
, statusdesc
, COUNT(*) AS StatusCount
FROM TimeToComplete AS ttc
WHERE statusdesc NOT IN ('Wtg Client (Conf)', 'resolved','Development Investigation','Wtg Client (info)')
AND username IN ('anne.petersen','chris.bowman','phil.mead','steve.doody','tony.rowland', 'jelena.odnobokova', 'jon.baker', 'andy.hardyman','gavin.reynolds','david.newton')
GROUP BY username, statusdesc
ORDER BY TotalCount ASC;

SELECT username, projectname, issueid, prioritydesc FROM TimeToComplete
WHERE statusdesc NOT IN ('Wtg Client (info)','Wtg Client (Conf)','resolved','Development Investigation')
AND username IN ('anne.petersen','chris.bowman','phil.mead','steve.doody','tony.rowland', 'jelena.odnobokova', 'jon.baker', 'andy.hardyman','gavin.reynolds','david.newton')
AND prioritydesc = 'Critical - P1'
ORDER BY username, issueid;

SELECT username, COUNT(*) AS Inc_Wtg_Client_Info FROM TimeToComplete
WHERE statusdesc NOT IN ('Wtg Client (Conf)','resolved','Development Investigation')
AND username IN ('anne.petersen','chris.bowman','phil.mead','steve.doody','tony.rowland', 'jelena.odnobokova', 'jon.baker', 'andy.hardyman','gavin.reynolds','david.newton')
GROUP BY username with rollup
ORDER BY Inc_Wtg_Client_Info;

SELECT username, prioritydesc, COUNT(*) AS NumberOfCalls FROM TimeToComplete
WHERE statusdesc NOT IN ('Wtg Client (info)','Wtg Client (Conf)','resolved','Development Investigation')
AND username IN ('anne.petersen','chris.bowman','phil.mead','steve.doody','tony.rowland', 'jelena.odnobokova', 'jon.baker', 'andy.hardyman','gavin.reynolds','david.newton')
GROUP BY username, prioritydesc
ORDER BY username, prioritydesc;

Many thanks,

B@

 

[Pho]

If you need to execute multiple things in the same batch (like you are) then use a stored procedure rather than a view or function.

Alternatively, save each of your SELECT statements as an individual function then create a stored procedure which calls them all individually - you get the same result in the end. This is better for future proofing.

 

[B@Th*nG]

that's great, sorted!

B@