Combine Stored procedure 1

[saintedmunds]

HI
I have two groupby stored procedures which I would like to combine into one.

The result I would like is

ActivitieDate, Salesperson, Interest, AmountOfLeads, AmountOfUnuseable.

Below are the two SP that work

ALTER PROCEDURE dbo.spWebLeadsByDateUnuseable
(@FromDate smalldatetime,
@ToDate smalldatetime)
AS SELECT dbo.tSalesPerson.SalesPerson, COUNT(dbo.tSalesPerson.SalesPersonID) AS AmountOfUnUseable, dbo.tActivities.ActivitieDate
FROM dbo.tContacts INNER JOIN
dbo.tActivities ON dbo.tContacts.ContactID = dbo.tActivities.ContactID LEFT OUTER JOIN
dbo.tSalesPerson ON dbo.tActivities.SalesPersonID = dbo.tSalesPerson.SalesPersonID
WHERE (dbo.tActivities.RegardingID = 11) AND (dbo.tActivities.ActivitieDate BETWEEN CONVERT(DATETIME, @FromDate, 102) AND CONVERT(DATETIME,
@ToDate, 102)) AND (dbo.tContacts.ContactStageID = 5)
GROUP BY dbo.tSalesPerson.SalesPerson, dbo.tActivities.ActivitieDate

and

ALTER PROCEDURE dbo.spWebLeadsByDate
(@FromDate smalldatetime,
@ToDate smalldatetime)
AS

SELECT dbo.tSalesPerson.SalesPerson, COUNT(dbo.tSalesPerson.SalesPersonID) AS AmountOfLeads, dbo.tActivities.ActivitieDate, dbo.tContacts.Interest
FROM dbo.tContacts INNER JOIN
dbo.tActivities ON dbo.tContacts.ContactID = dbo.tActivities.ContactID LEFT OUTER JOIN
dbo.tSalesPerson ON dbo.tActivities.SalesPersonID = dbo.tSalesPerson.SalesPersonID
WHERE (dbo.tActivities.RegardingID = 11) AND (dbo.tActivities.ActivitieDate BETWEEN CONVERT(DATETIME, @FromDate, 102) AND CONVERT(DATETIME,
@ToDate, 102))
GROUP BY dbo.tSalesPerson.SalesPerson, dbo.tContacts.Interest,dbo.tActivities.ActivitieDate
HAVING (NOT (dbo.tContacts.Interest IS NULL))

Any help would be great

Cheers

 

[maswien]

The results from these two procedures contain different sets of columns, also the rows are duplicate, I don't see there is a need to combine the two result sets.

 

[gmmastros]

The trick (to making this simple) is to find the unique identifier(s) from each query, then make them subqueries joining the identifiers.

Sometimes it's best to just show you.

CREATE PROCEDURE dbo.spCombinedQueries
(@FromDate smalldatetime,
@ToDate smalldatetime)
AS 

Select A.SalesPerson,
       A.AmountOfUnUseable,
       A.ActivitieDate,
       B.AmountOfLeads,
       B.Interest
From   (
       [blue]SELECT dbo.tSalesPerson.SalesPerson, 
              COUNT(dbo.tSalesPerson.SalesPersonID) AS AmountOfUnUseable, 
              dbo.tActivities.ActivitieDate
       FROM   dbo.tContacts 
              INNER JOIN dbo.tActivities 
                 ON dbo.tContacts.ContactID = dbo.tActivities.ContactID 
              LEFT OUTER JOIN dbo.tSalesPerson 
                 ON dbo.tActivities.SalesPersonID = dbo.tSalesPerson.SalesPersonID
       WHERE  (dbo.tActivities.RegardingID = 11) 
              AND (dbo.tActivities.ActivitieDate BETWEEN CONVERT(DATETIME, @FromDate, 102) AND CONVERT(DATETIME, @ToDate, 102)) 
              AND (dbo.tContacts.ContactStageID = 5)
       GROUP BY dbo.tSalesPerson.SalesPerson, dbo.tActivities.ActivitieDate[/blue]
       ) As A
       Inner Join
       (
       [purple]SELECT dbo.tSalesPerson.SalesPerson, 
              COUNT(dbo.tSalesPerson.SalesPersonID) AS AmountOfLeads, 
              dbo.tActivities.ActivitieDate, 
              dbo.tContacts.Interest
       FROM   dbo.tContacts 
              INNER JOIN dbo.tActivities 
                ON dbo.tContacts.ContactID = dbo.tActivities.ContactID 
              LEFT OUTER JOIN dbo.tSalesPerson 
                ON dbo.tActivities.SalesPersonID = dbo.tSalesPerson.SalesPersonID
       WHERE  (dbo.tActivities.RegardingID = 11) 
              AND (dbo.tActivities.ActivitieDate BETWEEN CONVERT(DATETIME, @FromDate, 102) AND CONVERT(DATETIME, @ToDate, 102))
       GROUP BY dbo.tSalesPerson.SalesPerson, dbo.tContacts.Interest,dbo.tActivities.ActivitieDate
       HAVING      (NOT (dbo.tContacts.Interest IS NULL))
       [/purple]) As B
         On A.SalesPerson = B.SalesPerson
         And A.ActivitieDate = B.ActivitieDate

Notice that the blue and purple parts of the code are the original queries.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[saintedmunds]

HI There

Thank you for the replies I have tried the reply from gmmastros but have a slight problem.

I need to get all the results with AmountOfLeads between the dates and also the AmountOfUnUseable what I am getting is only the results where the is a value in AmountOfUnUseable.

I need the following if possible
SalesPerson,Interest,ActivitieDate,AmountOfLeads,AmountOfUnUseable

Joe Blogs, Investment, 06/06/2006, 6, 1

Does that make any sense to you?

Any help would be great.

Thank you