asked
Dynamic Stored Procedure
I need help please. Very new to stored procedures. Need step by step I have a code: SELECT 8*(round(STDEV(a.D_AR),5))+(round(AVG(a.D_AR),5)) TOTALVALUE FROM [Raw_Survey_Data_Test].[dbo].[MOD_SHIP_2015] a inner join dbo.vCellAssign2015 b on a.duns = b.duns where surveycell like '5090D' and a.DUNS <> '616837936' I need to create something( have no idea what I need to create), maybe a sp.....don't know, that will take the result set (totalvalue) of the above query and insert it into a new table. All this is to be done dynamically. The surveycell has multiply duns in them. The surveycell value are from the view. So pretty much checking the value of each duns. Then there's a condition. If the value of the TOTALVALUE is less than or equal to the value of D_AR, I want that inserted into the OUTMODE_D_AR_2015. if not move to the next record. insert into dbo.OUTMOD_D_AR_2015 select a.duns, b.D_AR from dbo.vCellAssign2015 a inner join dbo.MOD_SHIP_2015 b on a.duns=b.duns where a.duns = 616837936 Can you help me out, don't even know if am making sense. I need to run and create a table every quarter from this statement. HELP PLEASE
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
I've no way of testing this but wouldn't you do someting like this? insert into dbo.OUTMOD_D_AR_2015 select a.duns, b.D_AR from dbo.vCellAssign2015 a inner join dbo.MOD_SHIP_2015 b on a.duns=b.duns where a.D_AR >= (SELECT 8*(round(STDEV(a.D_AR),5))+(round(AVG(a.D_AR),5)) TOTALVALUE FROM [Raw_Survey_Data_Test].[dbo].[MOD_SHIP_2015] a inner join dbo.vCellAssign2015 b on a.duns = b.duns where surveycell like '5090D' and a.DUNS '616837936')
Let me shed more light on what i have and what i want to achieve. I have this table called [dbo].[OUTLIER]: CELLBLOCK DUNS D_AR D_GP 1020A 123 0 200 1020A 562 10 0 1020A 448 0 10 1020A 235 50 5 1020A 996 0 0 1020A 845 2 0 211B 423 0 500 211B 653 0 30 211B 805 0 1 6030C 523 100 0 6030C 323 0 0 6030C 722 1 0 4050D 755 0 0 4050D 222 0 0 5060E 6112F 7020G 652 100 100 7020G 985 0 300 7020G 198 0 0 7020G 288 20 50 8040H 999 0 0 8040H 565 100 1000 8040H 631 0 0 8040H 782 2 200 8040H 550 NULL NULL 9112J 433 0 0 9112J 111 NULL NULL 9112J 333 10 500 I want to create a stored procedure that will recursively calculate the outliers on this table. Each cellblock have Duns assosiated with it. I need to calculated per each cellblock ,the outlier for each of D_AR and D_GP. And this needs to be repeated on each cellblock. Then when an outlier is found, i need the Duns and it's D_AR or D_GP, put in a new table. For example, cellblock 1020A, has 6 DUNS each for D_AR and D_GP. DUNS D_AR D_GP 123 0 200 562 10 0 448 0 10 235 50 5 996 0 0 845 2 0 SELECT 8*(round(STDEV(cast(D_AR as float)),5))+(round(AVG(cast(D_AR as float)),5)) AR_VOL, 8*(round(STDEV(cast(D_GP as float)),5))+(round(AVG(cast(D_GP as float)),5)) GP_VOL FROM [dbo].[OUTLIER] where DUNS <> '562' So if the value of AR_VOL is <= D_AR, then that DUNS is an outlier, i need that DUNS with the D_AR or D_GP value inserted into a new table. If the value of AR_VOL is > move to the next DUNS. where the value of D_AR is 0, move to next DUNS and calculate the outlier. So basically the outlier value is only performed on DUNS that have a value greater than 0. If all the D_AR and D_GP in a cellblock is 0, move to next cellblock. If there is only 1 DUNS in a cellblock, move to next cellblock. With this stored procedure or CTE or whatever all this will end up being, i want to setup variables for: StartDate EndDate Pleeeeeese i need step by step walk through and or the all script.Thank you so much in advance, am totally lost as to how to go about achieving this. And after working on a cellblock move to the next and perform the same calculation again. For all the cellblocks in that table.
17 People are following this question.
