December 19, 2007 at 10:51 pm
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)
December 20, 2007 at 1:10 am
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
December 20, 2007 at 5:08 pm
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