Avatar of rhservan
rhservan
Flag 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
Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Anthony Perkins

8/22/2022 - Mon
rhservan

ASKER
If you need the sproc code I will post it but tell me how to post it with line numbers for referencing.
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?
rhservan

ASKER
Well it looks like it is returning two result sets and I thought that a sproc could only return a single result set.
Your help has saved me hundreds of hours of internet surfing.
fblack61
rhservan

ASKER
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
UnifiedIS

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
UnifiedIS

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.
rhservan

ASKER
Can I perform another Union All after email  for /*CALLS*/ . I want to add this to the total count.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
UnifiedIS

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
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).
Anthony Perkins

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck