quote:

---

Originally posted by vijays3

Hi 

I have created one package where it retrieves the datafrom SQL server and load into excel file. But my challenge is here that this package needs to be call by job and this will run on 2nd of eery month .Now Problem here in my query where dates are mentioned:


SELECT EED.Month_Name as RenewMonth, TP.TieredPricingPlanEnglish, COUNT(E.LineContractNbr) as SubscriberCount
,CASE	WHEN E.EnrollEventID IN (101) THEN 'ENROLL'
		WHEN E.EnrollEventID IN (102) THEN 'RE-ENROLL'
		WHEN E.EnrollEventID IN (211) THEN 'RENEW'
END [Enroll Stat]
FROM FACT.Enroll E with (nolock)
INNER JOIN DIM.Date EED WITH (NOLOCK) 
	ON E.EnrollEventDateID = EED.ID 
--	AND EED.Date >= '2012/3/1' and EED.Date <= '2012/3/31'
               AND EED.Date >= DateAdd(Month,DateDiff(Month,0,getdate())-1,0)
               AND EED.Date < DateAdd(Month,DateDiff(Month,0,getdate()),0)
INNER JOIN DIM.TieredPricingPlan TP 
	ON TP.TieredPricingPlanID = E.TieredPricingPlanID
LEFT JOIN FACT.Enroll PE with (nolock) 
	ON PE.LineContractNbr = E.LineContractNbr 
	AND PE.RowNumberAsc + 1 = E.RowNumberAsc
WHERE E.Invalid=0 AND TP.TieredPricingPlanID IN (1,2,3)
AND (
	E.EnrollEventID In (101,102)
	OR 
	(E.TieredPricingPlanID <> PE.TieredPricingPlanID And E.EnrollEventID = 211)
)
Group By EED.Month_Name, TP.TieredPricingPlanEnglish, CASE	WHEN E.EnrollEventID IN (101) THEN 'ENROLL'
		WHEN E.EnrollEventID IN (102) THEN 'RE-ENROLL'
		WHEN E.EnrollEventID IN (211) THEN 'RENEW'
END
Order By EED.Month_Name, TP.TieredPricingPlanEnglish

Currently this are hardcoded but hard coded in not allowed I ran this job on Apr-12 so that it could retrieve the records of Marc-12 dates are mentioned already EED.Date >= '2012/3/1' and EED.Date <= '2012/3/31'. This should be behave like if I run it May it should get the data of betwwen 2012/4/1 and  2012/4/30 ...and so on..
I am not sure how to implement this.  

---