Link to home
Start Free TrialLog in
Avatar of rhservan
rhservanFlag for United States of America

asked on

Inherited sproc has 2 select queries back to back, Is that right use?

Is line 3 and 4 legitimate when performing Union All.  Is it possible to have two queries back to back?

1.Query
2.Union All
3.Query
4.Query
5.Union All
6.Query
Avatar of rhservan
rhservan
Flag of United States of America image

ASKER

If you need the sproc code I will post it but tell me how to post it with line numbers for referencing.
Avatar of UnifiedIS
UnifiedIS

That is a legitimate construct.
I think if you post code, EE will add line numbers for you.  

Without seeing the proc, I can't speculate on whether it is sensible to do it that way.

What is your concern about it?
Well it looks like it is returning two result sets and I thought that a sproc could only return a single result set.
ALTER PROCEDURE [dbo].[cspKPI_Observations]
      (
            @Employee VARCHAR(100),
            @STARTDATE DATETIME,
            @ENDDATE DATETIME,
            @LINE_OF_BUSINESS VARCHAR(1000) = NULL,
            @COMPANY_CODE VARCHAR(1000) = NULL
      )
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

 -- Insert statements for procedure here
-- Parse Company Code List into a temp table
DECLARE @TempListCompanyCode table
      (
            CompanyCode Varchar(100)
      )

DECLARE @CompanyCodeID varchar(10), @Pos int

SET @COMPANY_CODE = LTRIM(RTRIM(@COMPANY_CODE))+ ','
SET @Pos = CHARINDEX(',', @COMPANY_CODE, 1)

IF REPLACE(@COMPANY_CODE, ',', '') <> ''
BEGIN
      WHILE @Pos > 0
      BEGIN
            SET @CompanyCodeID = LTRIM(RTRIM(LEFT(@COMPANY_CODE, @Pos - 1)))
            IF @CompanyCodeID <> ''
            BEGIN
                  INSERT INTO @TempListCompanyCode (CompanyCode) VALUES (CAST(@CompanyCodeID AS Varchar)) --Use Appropriate conversion
            END
            SET @COMPANY_CODE = RIGHT(@COMPANY_CODE, LEN(@COMPANY_CODE) - @Pos)
            SET @Pos = CHARINDEX(',', @COMPANY_CODE, 1)

      END
END

-- Parse Line Of Business List into a temp table
DECLARE @TempListLOB table
      (
            LOB Varchar(100)
      )

DECLARE @LOBID varchar(10)

SET @LINE_OF_BUSINESS = LTRIM(RTRIM(@LINE_OF_BUSINESS))+ ','
SET @Pos = CHARINDEX(',', @LINE_OF_BUSINESS, 1)

IF REPLACE(@LINE_OF_BUSINESS, ',', '') <> ''
BEGIN
      WHILE @Pos > 0
      BEGIN
            SET @LOBID = LTRIM(RTRIM(LEFT(@LINE_OF_BUSINESS, @Pos - 1)))
            IF @LOBID <> ''
            BEGIN
                  INSERT INTO @TempListLOB (LOB) VALUES (CAST(@LOBID AS Varchar)) --Use Appropriate conversion
            END
            SET @LINE_OF_BUSINESS = RIGHT(@LINE_OF_BUSINESS, LEN(@LINE_OF_BUSINESS) - @Pos)
            SET @Pos = CHARINDEX(',', @LINE_OF_BUSINESS, 1)

      END
END

    -- Insert statements for procedure here
DECLARE @OBS TABLE (Clock_Number varchar(50),  Month varchar(10), Year varchar(10), Count Integer)
INSERT INTO @OBS
select v.cfClock_Number_ as [clock_number_], v.Month, v.Year, sum(v.count) as count
from
(
select rie.cfClock_Number_, Month(cfActivity_Completed_Date) as Month, Year(cfActivity_Completed_Date) as Year,
      count(r.Rn_Interactions_Id) as Count
from PivotalDev_ED..Rn_Interactions r

      inner join
            (
                  select distinct rn_interactions_id,rie.employee_id,e.cfLine_of_Business_Id,e.cfCompany_Code_Id,e.cfClock_Number_ from PivotalDev_ED..Rn_Int_Employee rie
                  inner join PivotalDev_ED..employee e on rie.Employee_Id = e.employee_id
            ) rie
                  on rie.Rn_Interactions_Id = r.Rn_Interactions_Id
                  
      inner join PivotalDev_ED..ctLine_of_Business lob
            on rie.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
      inner join PivotalDev_ED..ctCompany_Code cc
            on rie.cfCompany_Code_Id = cc.ctCompany_Code_Id
      inner join @TempListCompanyCode tcc
            on cc.company_code_name = tcc.CompanyCode
      inner join @TempListLOB tlob
            on lob.line_of_business_name = tlob.lob
      left outer join PivotalDev_ED..Meeting_ m
            on m.Rn_Interactions_Id=r.Rn_Interactions_Id
      LEFT OUTER JOIN PivotalDev_ED..ToDo td    --*****Testing
          on td.Rn_Interactions_Id=r.Rn_Interactions_Id      --*****Testing
      left outer join PivotalDev_ED..Call_ c
            on c.Rn_Interactions_Id=r.Rn_Interactions_Id
where coalesce(m.visibility,c.visibility) = 1 --Changed from r.access_type = 1
      and r.Rn_Interaction_Type in (6,7,9) -- Meeting & Calls ***** Testing
      and r.Rn_Interaction_Completed_Flag = 1
      --and (r.internal = 0 or r.internal is null) -- Removed from 6.0
                and rie.cfClock_Number_ = isnull(@Employee, rie.cfClock_Number_)
                and cfActivity_Completed_Date >= @STARTDATE
                and cfActivity_Completed_Date <= @ENDDATE
group by rie.cfClock_Number_, Year(cfActivity_Completed_Date), Month(cfActivity_Completed_Date)


union all

/* Emails */
select e.cfClock_Number_, Month(ri.Rn_Interaction_Date) as Month, Year(ri.Rn_Interaction_Date) as Year,
      count(ri.Rn_Interaction_Date) as Count
from PivotalDev_ED..Rn_Interactions ri
      inner join PivotalDev_ED..Rn_Int_Employee rie
            on rie.Rn_Interactions_Id = ri.Rn_Interactions_Id
      inner join PivotalDev_ED..employee e
            on rie.employee_id = e.employee_id
      inner join PivotalDev_ED..ctLine_of_Business lob
            on e.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
      inner join PivotalDev_ED..ctCompany_Code cc
            on e.cfCompany_Code_Id = cc.ctCompany_Code_Id
      inner join @TempListCompanyCode tcc
            on cc.company_code_name = tcc.CompanyCode
      inner join @TempListLOB tlob
            on lob.line_of_business_name = tlob.lob
      inner join PivotalDev_ED..Rn_Int_Email riem
            on riem.Rn_Interactions_Id  =ri.Rn_Interactions_Id
            
where ri.Rn_Interaction_Type = 10 --Email
      and riem.Rn_Email_Direction = 2 --Outbound
      --and ri.internal = 0 --Not an Internal email /* Internal field removed OOB in 6.0 * /
      and ri.Rn_Interaction_Completed_Flag = 1
      --and rip.type = 0 --From */ rn_interaction_player table removed in 6.0 */
      and e.cfClock_Number_ = isnull(@Employee, e.cfClock_Number_)
    and ri.Rn_Interaction_Date >= @STARTDATE
    and ri.Rn_Interaction_Date <= @ENDDATE
group by e.cfClock_Number_, Month(ri.Rn_Interaction_Date), Year(ri.Rn_Interaction_Date)
) v
group by v.cfClock_Number_, v.Month, v.Year

HERE IS WHERE THE 3 & 4 MEET AS IN THE ORIGINAL QUESTION ABOVE.

select e.cfClock_Number_ as [Clock_Number_], e.cfFull_Name as [Full_Name], q.cfQuota_Type as [Quota_Type], q.cfQuota_Month as [Quota_Month], q.cfQuota_Year as [Quota_Year], cast(eq.Forecast_Amount as Decimal(16,0)) as Goal, isnull(obs.Count,0) as Actual,
      Cast((isnull(obs.Count,0)/eq.Forecast_Amount) * 100 as Decimal(16,0)) as Actual_Percent
from PivotalDev_ED..SFA_Employee__Forecast eq -- changed from employee_Quota
      inner join PivotalDev_ED..Employee e
            on eq.Employee_Id = e.Employee_Id
      inner join PivotalDev_ED..ctLine_of_Business lob
            on e.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
      inner join PivotalDev_ED..ctCompany_Code cc
            on e.cfCompany_Code_Id = cc.ctCompany_Code_Id
      inner join @TempListCompanyCode tcc
            on cc.company_code_name = tcc.CompanyCode
      inner join @TempListLOB tlob
            on lob.line_of_business_name = tlob.lob
      inner join PivotalDev_ED..SFA_Forecast_Period q -- Changed from Quota_
            on eq.SFA_Forecast_Period_Id = q.SFA_Forecast_Period_Id
      left outer join @OBS obs
            on e.cfClock_Number_ = obs.Clock_Number
                  and q.cfQuota_Month = obs.Month
                  and q.cfQuota_Year = obs.Year
where q.cfQuota_Type = 'Observations'
              and e.cfClock_Number_ = isnull(@Employee, e.cfClock_Number_)
              and (q.cfQuota_Month >= MONTH(@STARTDATE) and q.cfQuota_Year >= YEAR(@STARTDATE))
              and (q.cfQuota_Month <= MONTH(@ENDDATE) and q.cfQuota_Year <= YEAR(@ENDDATE))
                    and e.primary_security_group like '%Regional Sales%'

union all

select 'Total' as Clock_Number_, 'Total' as Full_Name, 'Total' as Quota_Type, 13 as Quota_Month, 9999 as Quota_Year,
      isnull(cast(sum(eq.Forecast_Amount) as Decimal(16,0)),0) as Goal, isnull(sum(isnull(obs.Count,0)),0) as Actual,
      isnull(Cast((sum(isnull(obs.Count,0))/sum(eq.Forecast_Amount)) * 100 as Decimal(16,0)),0) as Actual_Percent
from PivotalDev_ED..SFA_Employee__Forecast eq -- Changed from Employee_Quota
      inner join PivotalDev_ED..Employee e
            on eq.Employee_Id = e.Employee_Id
      inner join PivotalDev_ED..ctLine_of_Business lob
            on e.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
      inner join PivotalDev_ED..ctCompany_Code cc
            on e.cfCompany_Code_Id = cc.ctCompany_Code_Id
      inner join @TempListCompanyCode tcc
            on cc.company_code_name = tcc.CompanyCode
      inner join @TempListLOB tlob
            on lob.line_of_business_name = tlob.lob
      inner join PivotalDev_ED..SFA_Forecast_Period q -- Changed from Quota_ table
            on eq.SFA_Forecast_Period_Id = q.SFA_Forecast_Period_Id
      left outer join @OBS obs
            on e.cfClock_Number_ = obs.Clock_Number
                  and q.cfQuota_Month = obs.Month
                  and q.cfQuota_Year = obs.Year
where q.cfQuota_Type = 'Observations'
       and e.cfClock_Number_ = isnull(@Employee, e.cfClock_Number_)
 and (q.cfQuota_Month >= MONTH(@STARTDATE) and q.cfQuota_Year >= YEAR(@STARTDATE))
              and (q.cfQuota_Month <= MONTH(@ENDDATE) and q.cfQuota_Year <= YEAR(@ENDDATE))
            and e.primary_security_group like '%Regional Sales%'
END
ASKER CERTIFIED SOLUTION
Avatar of UnifiedIS
UnifiedIS

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
BTW, procedures CAN return many result sets, it is up to the consuming application to determine which one to use.  Typically though, only one result set is returned as is the case with this proc.
Can I perform another Union All after email  for /*CALLS*/ . I want to add this to the total count.
Yes you can use multiple union statements so I think you'd end up with a structure like this:
with a union, you have to make sure each query contains the same number and type of columns.

INSERT INTO
query 1
union
query2
union
query 3

select
query 4
union
query5
union
query6
Avatar of PortletPaul
@rhservan it IS a good idea to post scripts here as part of the question, often this is the fastest way to reach an answer.

but... PLEASE use the "code" button in the toolbar. Not only does this display script better it provides line numbers for reference AND we don't have to scroll down/up multiple times (also this site has a mobile version).
Well it looks like it is returning two result sets and I thought that a sproc could only return a single result set.
I thought we had already discussed this in your previous question.  A Stored Procedure can return 0 or more resultsets.