I am trying to edit this query i already have that is fully functional for another report in reporting services. The report has visits sales reps made to stores, broken down by period, week and then date. Now, i need to filter it out by Visits. They want the Sales Reps who had less then 6 visits a day. How would i code that out in my stored procedure? When i have Stores and Account Status( which makes it pretty broken down). Any suggestions. Thanks..heres the code.
Code Snippet
ALTER PROCEDURE [dbo].[Testing_Visits_Exception]
(@.Region_Key int=null)
AS
BEGIN
SELECT dbo.Qry_Visits.Customer_code,
Qry_Sales_Group.Name,
dbo.Qry_Sales_Group.SR_Name,
dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,
dbo.Qry_Date_Dim.Date_Dimension_Date,
dbo.Qry_Date_Dim.Day_Of_Month,
dbo.Qry_Sales_Group.Region,
dbo.Qry_Visits.period_code,
dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,
dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,
dbo.Qry_Date_Dim.Date_Dimension_Year,
dbo.Qry_Date_Dim.Date_Dimension_Period,
CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,
dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]
FROM dbo.Qry_Visits
INNER JOIN dbo.Qry_Sales_Group
ON dbo.Qry_Visits.[SR Code]
COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
COLLATE Latin1_General_CI_AS
INNER JOIN dbo.Qry_Date_Dim
ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)
WHERE REGION_KEY=@.Region_Key
END
SET NOCOUNT OFF
If dbo.Qry_Visits.Visits stores the cumulative count of the visits made then you can add 'Qry_Visits.Visits < 6' to the where clause. To make it more generic instead of hard coding values, you may want to have to edit the signature of the proc to have an input parameter that represents the number of visits also.|||
try:
ALTER PROCEDURE [dbo].[Testing_Visits_Exception]
(@.Region_Key int=null)
AS
BEGIN
declare @.visit_req int
set @.visit_req=6
--only return rows that have at least 6 visits
SELECT dbo.Qry_Visits.Customer_code,
Qry_Sales_Group.Name,
dbo.Qry_Sales_Group.SR_Name,
dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,
dbo.Qry_Date_Dim.Date_Dimension_Date,
dbo.Qry_Date_Dim.Day_Of_Month,
dbo.Qry_Sales_Group.Region,
dbo.Qry_Visits.period_code,
dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,
dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,
dbo.Qry_Date_Dim.Date_Dimension_Year,
dbo.Qry_Date_Dim.Date_Dimension_Period,
CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,
dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]
FROM dbo.Qry_Visits
INNER JOIN dbo.Qry_Sales_Group
ON dbo.Qry_Visits.[SR Code]
COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
COLLATE Latin1_General_CI_AS
INNER JOIN dbo.Qry_Date_Dim
ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)
WHERE REGION_KEY=@.Region_Key
and dbo.Qry_Visits.Visits >= @.visit_req
END
SET NOCOUNT OFF
|||it does not show the cumulative. It shows one visit per sales person, per date. So its really broken down. So if i put <6 it will show everything, because each record has one visit. I only want the ones with less then 6 visits by a Sales Rep. Which means the sum of visits per Sales Rep, per day. How would i get that?|||I don't really understand the data structure, but is possible to just do an aggregate similar to:
Code Snippet
Pseudo Code:
SELECT YourColumns, SUM(Visits) AS Visits
FROM AllThoseTables
GROUP BY YourColumns
HAVING SUM(Visits) > 5
Then do:
ALTER PROCEDURE [dbo].[Testing_Visits_Exception]
(@.Region_Key int=null)
AS
BEGIN
declare @.visit_req int
set @.visit_req=6
--only return rows that have at least 6 visits
SELECT dbo.Qry_Visits.Customer_code,
Qry_Sales_Group.Name,
dbo.Qry_Sales_Group.SR_Name,
dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,
dbo.Qry_Date_Dim.Date_Dimension_Date,
dbo.Qry_Date_Dim.Day_Of_Month,
dbo.Qry_Sales_Group.Region,
dbo.Qry_Visits.period_code,
dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,
dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,
dbo.Qry_Date_Dim.Date_Dimension_Year,
dbo.Qry_Date_Dim.Date_Dimension_Period,
CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,
dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]
FROM dbo.Qry_Visits
INNER JOIN dbo.Qry_Sales_Group
ON dbo.Qry_Visits.[SR Code]
COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
COLLATE Latin1_General_CI_AS
INNER JOIN dbo.Qry_Date_Dim
ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)
WHERE REGION_KEY=@.Region_Key
and dbo.Qry_Visits.[SR Code] NOT IN(select [SR Code] from dbo.Qry_Visits
group by [SR Code]
having count(Visits) < @.visit_req)
END
SET NOCOUNT OFF
