Change single return record to multiple

  • I have a query that will return one record as its results if you provide two variables: @login and @record_date. This works great if you only want one result. However, now what I want to do is not provide those variables and get the result set back for each login and record_date combination. The hitch is that there are several other variables that are built off of the two that are supplied. Here is the query:

    DECLARE @login char(20), /*This sets the rep for the query.*/

    @record_date datetime,/*This is the date that we want to run this for.*/

    @RWPY decimal(18,2),/*This is the required wins per year.*/

    @OCPW decimal(18,2),/*This is the opportunities closed per week.*/

    @OACW decimal(18,2),/*This is opportunities advanced to close per week.*/

    @TOC decimal(18,2),/*This is the total number of opportunities in close.*/

    @OANW decimal(18,2),/*This is opportunities advanced to negotiate per week.*/

    @TON decimal(18,2),/*This is the total number of opportunities in negotiate.*/

    @OADW decimal(18,2),/*This is the opportunities advanced to demonstrate per week*/

    @TOD decimal(18,2),/*This is the total number of opportunities in demonstrate.*/

    @OAIW decimal(18,2),/*This is the opportunities advanced to interview per week.*/

    @TOI decimal(18,2),/*This is the total number of opportunities in interview.*/

    @OCW decimal(18,2),/*This is the opportunities created per week.*/

    @TOA decimal(18,2)/*This is the total number of opportunities in approach.*/

    SET @login = 'GREP'

    SET @record_date = '12/18/2007'

    SET @RWPY = (SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)/(SELECT target_deal FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)))

    SET @OCPW = (SELECT @RWPY/weeks FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

    SET @OACW = (SELECT @OCPW/cls_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

    SET @TOC = (SELECT @OACW*(cls_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

    SET @OANW = (SELECT @OACW/neg_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

    SET @TON = (SELECT @OANW*(neg_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

    SET @OADW = (SELECT @OANW/dem_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

    SET @TOD = (SELECT @OADW*(dem_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

    SET @OAIW = (SELECT @OADW/int_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

    SET @TOI = (SELECT @OAIW*(int_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

    SET @OCW = (SELECT @OAIW/app_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

    SET @TOA = (SELECT @OCW*(app_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

    SELECT loginname,

    CAST(@TOA AS decimal(18,1)) AS [Opps in Approach],

    app_time AS [Approach Average Time],

    app_perc_adv AS [Approach Perc Adv],

    CAST(@TOI AS decimal(18,1)) AS [Opps in Interview],

    int_time AS [Interview Average Time],

    int_perc_adv AS [Interview Perc Adv],

    CAST(@TOD AS decimal(18,1)) AS [Opps in Demonstrate],

    dem_time AS [Demonstrate Average Time],

    dem_perc_adv AS [Demonstrate Perc Adv],

    CAST(@TON AS decimal(18,1)) AS [Opps in Negotiate],

    neg_time AS [Negotiate Average Time],

    neg_perc_adv AS [Negotiate Perc Adv],

    CAST(@TOC AS decimal(18,1)) AS [Opps In Close],

    cls_time AS [Close Average Time],

    cls_perc_adv AS [Close Perc Adv]

    FROM #pipelinehist

    WHERE loginname = @login AND record_date = @record_date

    Here is some sample data to use with this. With this sample data what I want to get back is a total of 30 records in the result set each with its data specific to the login and record_date of that returned record.

    CREATE TABLE #pipelinehist (

    glusftboid int IDENTITY(1,1) NOT NULL,

    record_date datetime NOT NULL,

    loginname char(20) NOT NULL,

    app_new float NOT NULL,

    app_time float NOT NULL,

    app_perc_adv float NOT NULL,

    int_time float NOT NULL,

    int_perc_adv float NOT NULL,

    dem_time float NOT NULL,

    dem_perc_adv float NOT NULL,

    neg_time float NOT NULL,

    neg_perc_adv float NOT NULL,

    cls_time float NOT NULL,

    cls_perc_adv float NOT NULL,

    target_deal money NOT NULL,

    annual_quota money NOT NULL,

    weeks int NOT NULL

    ) ON [PRIMARY]

    INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'AREP', 56.8, 26.9, 0.57, 29.5, 0.47, 20, 0.67, 80.7, 0.53, 2.1, 0.97, 2194.93, 575000, 50)

    INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'BREP', 33.2, 0.5, 0.9, 7.7, 0.77, 8, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)

    INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'CREP', 210.2, 0.3, 0.87, 6.6, 0.5, 13.7, 0.4, 16.3, 0.43, 1.5, 0.91, 461.25, 330000, 50)

    INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'DREP', 47.6, 5, 0.53, 33.3, 0.6, 57.5, 0.53, 50, 0.7, 1.5, 1, 2045.7, 575000, 50)

    INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'EREP', 75.3, 110.9, 0.47, 36, 0.5, 17.4, 0.87, 20.3, 0.6, 7.2, 0.83, 2021.74, 775000, 50)

    INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'FREP', 17.2, 23.3, 0.73, 6.8, 0.8, 6.3, 0.93, 29.7, 0.67, 15.5, 0.83, 2218.95, 575000, 50)

    INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'GREP', 105.4, 67, 0.2, 32.9, 0.43, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)

    INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'HREP', 116.4, 118.5, 0.33, 30.9, 0.77, 46.3, 0.77, 46.3, 0.6, 0.9, 0.97, 1735.13, 1150000, 50)

    INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'IREP', 143.3, 9, 0.77, 96, 0.17, 21.6, 0.77, 39.9, 0.43, 0.9, 0.93, 1385.43, 400000, 50)

    INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 67.6, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)

    INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'KREP', 107.6, 38.2, 0.23, 47.5, 0.47, 21.3, 0.77, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)

    INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'LREP', 18.6, 8.3, 0.87, 23.2, 0.57, 2.6, 0.87, 12.2, 0.67, 1, 1, 1229.02, 330000, 50)

    INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'MREP', 4, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.4, 1, 1091.22, 350000, 50)

    INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'NREP', 54, 21.6, 0.57, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)

    INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'OREP', 37.6, 24.4, 0.57, 50.1, 0.43, 6.7, 0.87, 15.6, 0.73, 0.9, 0.97, 1163.48, 330000, 50)

    INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'AREP', 57.2, 32.5, 0.6, 29.5, 0.47, 20, 0.67, 85.6, 0.5, 2.1, 0.97, 2194.93, 575000, 50)

    INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'BREP', 33.9, 0.5, 0.93, 7.8, 0.73, 8.3, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)

    INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'CREP', 152.1, 0, 0.87, 4.3, 0.67, 9.7, 0.47, 15.7, 0.47, 1.8, 0.85, 396.43, 330000, 50)

    INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'DREP', 80.5, 9.8, 0.5, 40.7, 0.57, 68.3, 0.43, 64.2, 0.57, 1.5, 1, 2045.7, 575000, 50)

    INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'EREP', 61, 92.1, 0.5, 31, 0.53, 16.9, 0.83, 17.7, 0.6, 7.3, 0.83, 2318.04, 775000, 50)

    INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'FREP', 19.4, 21.1, 0.7, 5.3, 0.77, 2.2, 0.93, 33.3, 0.7, 9.7, 0.87, 1937.17, 575000, 50)

    INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'GREP', 81.7, 40.5, 0.3, 33, 0.37, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)

    INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'HREP', 128.6, 115.7, 0.3, 30.9, 0.77, 46.3, 0.77, 48.8, 0.6, 0.9, 0.97, 1728.29, 1150000, 50)

    INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'IREP', 100.9, 3.4, 0.77, 86.2, 0.27, 18, 0.8, 54.7, 0.37, 0.9, 0.93, 1385.43, 400000, 50)

    INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 63.5, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)

    INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'KREP', 285.2, 36.5, 0.1, 46, 0.43, 24.2, 0.73, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)

    INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'LREP', 17.6, 7.3, 0.9, 21.5, 0.57, 1.7, 0.87, 12.2, 0.67, 1, 1, 1250.54, 330000, 50)

    INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'MREP', 26.7, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.3, 1, 979.7, 350000, 50)

    INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'NREP', 61.6, 20.8, 0.5, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)

    INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'OREP', 31.6, 16.9, 0.63, 50.1, 0.43, 7.2, 0.87, 19.5, 0.7, 0.9, 0.97, 1303.48, 330000, 50)

  • SELECT loginname, record_date, CAST(((SELECT ((SELECT ((SELECT ((SELECT ((SELECT ((SELECT ((SELECT ((SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date)

    / (SELECT target_deal FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))))/weeks FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))/cls_perc_adv FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))/neg_perc_adv FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))/dem_perc_adv FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))/int_perc_adv FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))/app_perc_adv FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))*(app_time/7) FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date)) AS decimal(18,1)) AS [Opps in Approach], app_time AS [Approach Average Time], app_perc_adv AS [Approach Perc Adv],

    CAST(((SELECT ((SELECT ((SELECT ((SELECT ((SELECT ((SELECT ((SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date)

    / (SELECT target_deal FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))))/weeks FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))/cls_perc_adv FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))/neg_perc_adv FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))/dem_perc_adv FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))/int_perc_adv FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))*(int_time/7) FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date)) AS decimal(18,1)) AS [Opps in Interview], int_time AS [Interview Average Time], int_perc_adv AS [Interview Perc Adv],

    CAST(((SELECT ((SELECT ((SELECT ((SELECT ((SELECT ((SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date)

    / (SELECT target_deal FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))))/weeks FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))/cls_perc_adv FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))/neg_perc_adv FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))/dem_perc_adv FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))*(dem_time/7) FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date)) AS decimal(18,1)) AS [Opps in Demonstrate], dem_time AS [Demonstrate Average Time], dem_perc_adv AS [Demonstrate Perc Adv],

    CAST(((SELECT ((SELECT ((SELECT ((SELECT ((SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date)

    / (SELECT target_deal FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))))/weeks FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))/cls_perc_adv FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))/neg_perc_adv FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))*(neg_time/7) FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date)) AS decimal(18,1)) AS [Opps in Negotiate], neg_time AS [Negotiate Average Time], neg_perc_adv AS [Negotiate Perc Adv],

    CAST(((SELECT ((SELECT ((SELECT ((SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date)

    / (SELECT target_deal FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))))/weeks FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))/cls_perc_adv FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date))*(cls_time/7) FROM #pipelinehist WHERE loginname = A.loginname AND record_date = A.record_date)) AS decimal(18,1)) AS [Opps In Close], cls_time AS [Close Average Time], cls_perc_adv AS [Close Perc Adv]

    FROM #pipelinehist A

    ORDER BY loginname, record_date

  • Thanks. Actually ended up using the following based on another suggestion. Both work though.

    SELECT loginname,

    record_date,

    CAST(TOA AS decimal(18,1)) AS [Opps in Approach],

    app_time AS [Approach Average Time],

    app_perc_adv AS [Approach Perc Adv],

    CAST(TOI AS decimal(18,1)) AS [Opps in Interview],

    int_time AS [Interview Average Time],

    int_perc_adv AS [Interview Perc Adv],

    CAST(TOD AS decimal(18,1)) AS [Opps in Demonstrate],

    dem_time AS [Demonstrate Average Time],

    dem_perc_adv AS [Demonstrate Perc Adv],

    CAST(TON AS decimal(18,1)) AS [Opps in Negotiate],

    neg_time AS [Negotiate Average Time],

    neg_perc_adv AS [Negotiate Perc Adv],

    CAST(TOC AS decimal(18,1)) AS [Opps In Close],

    cls_time AS [Close Average Time],

    cls_perc_adv AS [Close Perc Adv]

    FROM (

    SELECT #pipelinehist.*,

    annual_quota/target_deal as RWPY ,

    (annual_quota/target_deal)/weeks as OCPW,

    ((annual_quota/target_deal)/weeks)/cls_perc_adv as OACW,

    (((annual_quota/target_deal)/weeks)/cls_perc_adv) * (cls_time/7) as TOC,

    (((annual_quota/target_deal)/weeks)/cls_perc_adv) /neg_perc_adv as OANW,

    ((((annual_quota/target_deal)/weeks)/cls_perc_adv) /neg_perc_adv) *(neg_time/7) as TON,

    ((((annual_quota/target_deal)/weeks)/cls_perc_adv) /neg_perc_adv)/dem_perc_adv as OADW,

    (((((annual_quota/target_deal)/weeks)/cls_perc_adv) /neg_perc_adv)/dem_perc_adv) *(dem_time/7) as TOD,

    (((((annual_quota/target_deal)/weeks)/cls_perc_adv) /neg_perc_adv)/dem_perc_adv)/int_perc_adv as OAIW,

    ((((((annual_quota/target_deal)/weeks)/cls_perc_adv) /neg_perc_adv)/dem_perc_adv)/int_perc_adv) *(int_time/7) as TOI,

    ((((((annual_quota/target_deal)/weeks)/cls_perc_adv) /neg_perc_adv)/dem_perc_adv)/int_perc_adv)/app_perc_adv as OCW,

    (((((((annual_quota/target_deal)/weeks)/cls_perc_adv) /neg_perc_adv)/dem_perc_adv)/int_perc_adv)/app_perc_adv) *(app_time/7) as TOA

    FROM #pipelinehist) as data

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply