How to send filtered record with sql stored procedure as a excel report I know very well how i need to scheduling to send these reports But on a diff interval I want to send filtered record
like at 1PM i want to send record from 00:00 am to 1Pm and at 4Pm ,and at 4Pm i want to send record from 1pm to 4Pm and also on every end of the month to be pulled for last 30 days.
how I apply the data filtering part for this ..Scheduling part I can by Using C# code..
following is my Stored Procedure
USE [XYZ]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CallCenterRptWithprint_sp]
(
@sdate varchar(12),
@edate varchar(12)
)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
CREATE TABLE #TBL_REDEEMEDPOINT
(
ID NUMERIC IDENTITY PRIMARY KEY,
MASTERID NUMERIC,
WATCHID NUMERIC,
MEMBERID NUMERIC
)
INSERT INTO #TBL_REDEEMEDPOINT
(
MASTERID,
WATCHID
)
SELECT top 100 MASTERID, ID FROM WATCHDEC08LEADS_TBL
WHERE ISFINISHED='y'
and Status in (Select Status from CallCenterValidStatus_tlk)
and (Purchasefrom in (Select OrderSource from CallCenterValidOrderSource_tlk where PrintReport=1)
and TotalPrice in (select OrderValue from dbo.PrintOrderValues_tlk))
and CreatedOn >=@sdate and CreatedOn<@edate
CREATE INDEX IDX_RP_MASTERID ON #TBL_REDEEMEDPOINT(MASTERID)
SELECT DISTINCT
WW.ID AS WID,
WW.MASTERID,
WW.CampaignId ,
WW.FIRSTNAME+' '+WW.LASTNAME AS MEMBERNAME,
WW.EMAIL,
ISNULL(WW.MOBILE,'')AS MOBILE,
ISNULL(WW.PHONE,'')AS PHONE,
DBO.STRIPDOUBLESPACES(REPLACE(REPLACE(ISNULL(WW.ADDRESS,''),'^^',','),'^',',')) AS ADDRESS,
C.CITYNAME +','+S.STATENAME AS CITYNAME,
REPLACE(ISNULL((WW.OTHERCITY),''),'"','') AS OTHERCITY,
WW.SizeValue,
WW.VerifiedOn,
DBO.STRIPDOUBLESPACES(REPLACE(ISNULL((WW.VERIFIEDCOMMENTS),''),'"','')) AS VERIFIEDCOMMENTS,
CM.CAMPAIGNNAME AS Products,
WW.PRICE AS PAYABLEAMOUNT,
WW.TotalPrice AS NETAMOUNT,
WW.UNIT,
CASE WHEN PGRESPONSE IS NULL
THEN WW.MODEOFPAYMENT
ELSE WW.MODEOFPAYMENT+'<br />'+'[ '+ISNULL(WW.PGRESPONSE,'')+' ]'
END AS MODEOFPAYMENT,
WW.STATUS,
ISNULL(WW.DISPATCHEDBY,'')AS DISPBY,
CONVERT(VARCHAR(10),WW.CREATEDON,103) AS ORDERDATE,
CASE
WHEN OrderExtension_tbl.LanguageId = 2 THEN 'Hindi'
ELSE 'English'
END as LanguageName,
CASE
WHEN ORDEREXTENSION_TBL.ISDNC =1 THEN 'Yes'
WHEN ORDEREXTENSION_TBL.ISDNC =0 THEN 'No'
ELSE 'No'
END AS DNC
FROM WATCHDEC08LEADS_TBL WW
LEFT OUTER JOIN MEMBERS_TBL ON MEMBERS_TBL.MEMBERID=WW.MEMBERID
INNER JOIN #TBL_REDEEMEDPOINT ON #TBL_REDEEMEDPOINT.MEMBERID=WW.MEMBERID
AND #TBL_REDEEMEDPOINT.MASTERID= WW.MASTERID
INNER JOIN CAMPAIGNS_TBL CM ON CM.CAMPAIGNID=WW.CAMPAIGNID
LEFT OUTER JOIN CITY_TBL C ON C.CITYID=WW.CITYID
LEFT OUTER JOIN STATES_TBL S ON C.STATEID=S.STATEID
LEFT OUTER JOIN USERS_TBL U ON U.USERID=WW.VERIFIEDBY
LEFT OUTER JOIN CAMPAIGNPROMOSITES_TBL PROMO ON PROMO.CAMPAIGNPROMOSITEID = WW.PROMOSITEID
LEFT OUTER JOIN OrderExtension_tbl ON OrderExtension_tbl.MasterId=#TBL_REDEEMEDPOINT.MasterId
LEFT OUTER JOIN Language_tlk ON Language_tlk.LanguageId=OrderExtension_tbl.LanguageId
ORDER BY ORDERDATE DESC
DROP TABLE #TBL_REDEEMEDPOINT