December 28, 2004 at 12:49 pm
I have a table containing all salesreps and I want to generate a report daily for each rep - but only if they had sales. Since the sales date is a report parameter, how do I check if the salesrep had sales for that date - in other words, can I use a report parameter in the query used to get the subscriber list? - for example -
select * from salesreps where salesrep.code in (select distinct salesrep from sales.table where sales.date=@reportparameter.date)
December 29, 2004 at 7:23 am
Hello Michael,
A general solution would use a stored procedure or a UDF. I like the UDF because it can be invoked in other T-SQL like a view or a table.
if exists (
select * from dbo.sysobjects
where id = OBJECT_ID(N'[dbo].[daily_salesrep_list_fn]')
and ISNULL(OBJECTPROPERTY(id, N'IsInlineFunction'),0)
+ ISNULL(OBJECTPROPERTY(id, N'IsScalarFunction'),0)
+ ISNULL(OBJECTPROPERTY(id, N'IsTableFunction'),0) >0 )
DROP FUNCTION [dbo].[daily_salesrep_list_fn]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.daily_salesrep_list_fn
(@report_dt AS datetime)
RETURNS TABLE
AS RETURN(
select *
from salesreps
where salesrep.code in (
select distinct salesrep
from sales.table
where sales.date = @report_dt)
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SELECT * FROM daily_salesrep_list_fn('12/29/2004')
GO
You can invoke this from other T-SQL as shown, and you can use it in T-SQL JOIN statements. You can also invoke it from a web app, a desktop app, or a reporting service.
Bob Monahon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply