Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RaghaSM
Yak Posting Veteran
52 Posts |
 Posted - 2008-07-14 : 15:05:01
|
| How to create a view dynamically in a stored procedure using a select statement that accepts few parameters ?For eg: If i pass startdate and endate to a stored proc then a sql select stmt executes , I wantto create a view everytime with that stmt and perform some queries on taht view.How should I do this? any suggestions?Your help will be very much appreciatedThanks,Ragha |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
RaghaSM
Yak Posting Veteran
52 Posts |
Posted - 2008-07-14 : 15:52:38
|
quote:
Originally posted by tkizer
This is a very bad idea. Why do you want to design a system like this?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
I am not familiar with sql server. So I dont know what I should do ? Let me paste here my query ... The follwing query gets the values of the T,c,n values for various training levels and based on the startdate and endate given , it also fetches the values that are null for a agency.select(DateName(month,@enddate) +' ' + CONVERT(VARCHAR(20),YEAR(@enddate))) AS MONTHYEAR , a.agencyid,a.agencyname, sum(CASE WHEN LevelID = 1 THEN t.T ELSE '' END) as Is100T_Sum , sum(CASE WHEN LevelID = 1 THEN t.C ELSE '' END) AS IS100C_Sum, sum(CASE WHEN LevelID = 1 THEN t.N ELSE '' END) AS IS100N_Sum, sum(CASE WHEN LevelID = 2 THEN t.T ELSE '' END) AS IS200T_Sum, sum(CASE WHEN LevelID = 2 THEN t.C ELSE '' END) AS IS200C_Sum, sum(CASE WHEN LevelID = 2 THEN t.N ELSE '' END) AS IS200N_Sum, sum(CASE WHEN LevelID = 3 THEN t.T ELSE '' END) AS IS700T_Sum, sum(CASE WHEN LevelID = 3 THEN t.C ELSE '' END) AS IS700C_Sum, sum(CASE WHEN LevelID = 3 THEN t.N ELSE '' END) AS IS700N_Sum, sum(CASE WHEN LevelID = 4 THEN t.T ELSE '' END) AS IS800T_Sum, sum(CASE WHEN LevelID = 4 THEN t.C ELSE '' END) AS IS800C_Sum, sum(CASE WHEN LevelID = 4 THEN t.N ELSE '' END) AS IS800N_Sum, sum(CASE WHEN LevelID = 5 THEN t.T ELSE '' END) AS IS300T_sum, sum(CASE WHEN LevelID = 5 THEN t.C ELSE '' END) AS IS300C_sum, sum(CASE WHEN LevelID = 5 THEN t.N ELSE '' END) AS IS300N_sum FROMETT_Agency A leftouter join (select * from ETT_TrainingStatusData where updateddate>=isnull(@startdate,updateddate) and updateddate<=isnull(@enddate,updateddate)) t on t.agencyid=a.agencyid groupBY a.AgencyID ,a.agencyname orderby a.agencyname Now.. I want to calclate the percentages based on IS100T_sum, IS100c_sum and so on for all the columns obtained. I am not sure how to write the query to obtain percentages taht optimizes the performance so I thought it would be better if I creat a view and then calcuate the percentages from taht view.Any suggestions would be appreciated.Also can some body giude me how can i learn SQL server to write queries and stored procedures? Your help will be very much appreciated.Thanks Ragha |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-07-15 : 00:05:41
|
| You can use CTEs to query once. I think you might also be able to use sum(x) over (.....) to get you totals across your groupings which will allow you to calculate %.On another note, you cannot use order by in a view. |
|
|
|
RaghaSM
Yak Posting Veteran
52 Posts |
Posted - 2008-07-15 : 08:24:43
|
quote:
Originally posted by LoztInSpace
You can use CTEs to query once. I think you might also be able to use sum(x) over (.....) to get you totals across your groupings which will allow you to calculate %.On another note, you cannot use order by in a view.
I am sorry, I doidn't understand what you have specified. what are CTEs? Can you expalin in detail abt this? Thanks for your help. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
Create a view dynamically