|
M1Mc
Starting Member
1 Post |
 Posted - 2011-09-25 : 08:33:53
|
| Hi, I have been running SQL under VBA for some time. I want to convert an SQL procedure from VBA to SQL Server for use. I have the SQL broken into 5 statements. The first two statements draw base data from one table, the third applies a percentage from the same table to break down the data further. Then a 4th statement pulls a weekly spit % and the 5th combines the weekly % applied against the 3rd statement. It is modular so it can be modified with variables as the time frames shift each week. I need to keep this modularity, but need to convert this to SQL server, eventually creating a job to run it. The code I have running in VBA is below, is there any way to do this in SQL Server?Thanks for the help. FamSqlStr = "SELECT AL1.VERSION, AL1.PRJ_NAME, AL1.PRODUCT, AL1.BBID, AL1.BB_CV, AL1.SUBGEO, AL1.SOURCE, AL1.LABEL, AL1.MEASURE, AL1.YEARMONTH1, AL1.YEARMONTH1_VALUE , AL1.YEARMONTH2," & _ " AL1.YEARMONTH2_VALUE , AL1.YEARMONTH3, AL1.YEARMONTH3_VALUE , AL1.YEARMONTH4, AL1.YEARMONTH4_VALUE , AL1.YEARMONTH5, AL1.YEARMONTH5_VALUE , AL1.YEARMONTH6, AL1.YEARMONTH6_VALUE " & _ " FROM dbo.OP_BI_OUT_MONTHLY_THU AL1 " & _ " WHERE AL1.VERSION = '20110819' AND AL1.SUBGEO = 'NA' AND AL1.MEASURE = 'CONSENSUS FCST' " PcntSqlStr = "SELECT AL2.VERSION, AL2.PRJ_NAME, AL2.PRODUCT, AL2.BBID, AL2.BB_CV, AL2.SUBGEO, AL2.SOURCE, AL2.LABEL, AL2.MEASURE, AL2.YEARMONTH1, AL2.YEARMONTH1_VALUE , AL2.YEARMONTH2," & _ " AL2.YEARMONTH2_VALUE , AL2.YEARMONTH3, AL2.YEARMONTH3_VALUE , AL2.YEARMONTH4, AL2.YEARMONTH4_VALUE , AL2.YEARMONTH5, AL2.YEARMONTH5_VALUE , AL2.YEARMONTH6, AL2.YEARMONTH6_VALUE " & _ " FROM dbo.OP_BI_OUT_MONTHLY_THU AL2 " & _ " WHERE AL2.VERSION = '20110819' AND AL2.SUBGEO = 'NA' AND AL2.MEASURE = 'Family Skew' " PrjSqlStr = "SELECT AL1.VERSION, AL1.PRJ_NAME, AL2.PRODUCT, AL1.BBID, AL1.BB_CV, AL1.SUBGEO, AL1.SOURCE, AL1.LABEL, AL1.MEASURE, AL1.YEARMONTH1, (" & M1 & " )AS MONTH1 , " & _ " AL1.YEARMONTH2 , ROUND((AL1.YEARMONTH2_VALUE * AL2.YEARMONTH2_VALUE),0)AS MONTH2 , AL1.YEARMONTH3, ROUND((AL1.YEARMONTH3_VALUE * AL2.YEARMONTH3_VALUE),0)AS MONTH3 , " & _ " ROUND((AL1.YEARMONTH4_VALUE * AL2.YEARMONTH4_VALUE),0)AS MONTH4 , ROUND((AL1.YEARMONTH5_VALUE * AL2.YEARMONTH4_VALUE),0)AS MONTH5 , " & _ " ROUND((AL1.YEARMONTH6_VALUE * AL2.YEARMONTH6_VALUE),0)AS MONTH6 " & _ " FROM (" & FamSqlStr & " ) AL1, (" & PcntSqlStr & " )AL2 " & _ " WHERE (AL1.PRJ_NAME = AL2.PRJ_NAME) AND (AL1.SUBGEO = AL2.SUBGEO) " WkSqlStr = "SELECT AL2.VERSION, AL2.PRJ_NAME, AL2.PRODUCT, AL2.BBID, AL2.BB_CV, AL2.SUBGEO, AL2.SOURCE, AL2.LABEL, AL2.MEASURE, AL2.YEARWEEK1, AL2.YEARWEEK1_VALUE , AL2.YEARWEEK2 ," & _ " AL2.YEARWEEK2_VALUE , AL2.YEARWEEK3, AL2.YEARWEEK3_VALUE , AL2.YEARWEEK4, AL2.YEARWEEK4_VALUE , AL2.YEARWEEK5, AL2.YEARWEEK5_VALUE , AL2.YEARWEEK6, AL2.YEARWEEK6_VALUE ," & _ " AL2.YEARWEEK7, AL2.YEARWEEK7_VALUE , AL2.YEARWEEK8, AL2.YEARWEEK8_VALUE , AL2.YEARWEEK9, AL2.YEARWEEK9_VALUE , AL2.YEARWEEK10, AL2.YEARWEEK10_VALUE " & _ " , AL2.YEARWEEK11, AL2.YEARWEEK11_VALUE , AL2.YEARWEEK12, AL2.YEARWEEK12_VALUE , AL2.YEARWEEK13, AL2.YEARWEEK13_VALUE , AL2.YEARWEEK14, AL2.YEARWEEK14_VALUE " & _ " , AL2.YEARWEEK15, AL2.YEARWEEK15_VALUE , AL2.YEARWEEK16, AL2.YEARWEEK16_VALUE , AL2.YEARWEEK17, AL2.YEARWEEK17_VALUE , AL2.YEARWEEK18, AL2.YEARWEEK18_VALUE " & _ " , AL2.YEARWEEK19, AL2.YEARWEEK19_VALUE , AL2.YEARWEEK20, AL2.YEARWEEK20_VALUE , AL2.YEARWEEK21, AL2.YEARWEEK21_VALUE , AL2.YEARWEEK22, AL2.YEARWEEK22_VALUE " & _ " , AL2.YEARWEEK23, AL2.YEARWEEK23_VALUE , AL2.YEARWEEK24, AL2.YEARWEEK24_VALUE , AL2.YEARWEEK25, AL2.YEARWEEK25_VALUE , AL2.YEARWEEK26, AL2.YEARWEEK26_VALUE " & _ " , AL2.YEARWEEK27, AL2.YEARWEEK27_VALUE " & _ " FROM dbo.OP_BI_OUT_THU AL2 " & _ " WHERE AL2.VERSION = '20110819' AND AL2.SUBGEO = 'NA' AND AL2.MEASURE = 'Monthly Skew(sys)' AND AL2.Type = 'ROL' " FinSqlStr = "SELECT DISTINCT AL1.VERSION, AL1.PRJ_NAME, AL2.PRODUCT, AL1.BBID, AL1.BB_CV, AL1.SUBGEO, AL1.SOURCE, AL1.LABEL, AL1.MEASURE, AL2.YEARWEEK1, ROUND(AL1.MONTH1 * AL2.YEARWEEK1_VALUE,0), " & _ " ROUND(AL1.MONTH1 * AL2.YEARWEEK2_VALUE,0), Al1.YEARMONTH2, AL2.YEARWEEK3,ROUND(AL1.MONTH2 * AL2.YEARWEEK3_VALUE,0), ROUND(AL1.MONTH2 * AL2.YEARWEEK4_VALUE,0), ROUND(AL1.MONTH2 * AL2.YEARWEEK5_VALUE,0) " & _ " , ROUND(AL1.MONTH2 * AL2.YEARWEEK6_VALUE,0), ROUND(AL1.MONTH3 * AL2.YEARWEEK7_VALUE,0)" & _ " FROM (" & PrjSqlStr & " ) AL1 , (" & WkSqlStr & " ) AL2 " & _ " WHERE AL1.VERSION = AL2.VERSION AND AL1.PRJ_NAME = AL2.PRJ_NAME AND AL1.SUBGEO = AL2.SUBGEO AND AL1.PRODUCT = AL2.PRODUCT" & _ " GROUP BY AL1.VERSION, AL1.PRJ_NAME, AL2.PRODUCT, AL1.BBID, AL1.BB_CV, AL1.SUBGEO, AL1.SOURCE, AL1.LABEL, AL1.MEASURE, AL2.YEARWEEK1, ROUND(AL1.MONTH1 * AL2.YEARWEEK1_VALUE,0), " & _ " ROUND(AL1.MONTH1 * AL2.YEARWEEK2_VALUE,0), Al1.YEARMONTH2, AL2.YEARWEEK3,ROUND(AL1.MONTH2 * AL2.YEARWEEK3_VALUE,0), ROUND(AL1.MONTH2 * AL2.YEARWEEK4_VALUE,0), ROUND(AL1.MONTH2 * AL2.YEARWEEK5_VALUE,0) " & _ " , ROUND(AL1.MONTH2 * AL2.YEARWEEK6_VALUE,0), ROUND(AL1.MONTH3 * AL2.YEARWEEK7_VALUE,0)" |
|
Dynamic VBA SQL conversion to SQL Server