DECLARE @startDate DATETIME = '20110115';
DECLARE @months INT = 108;

WITH calendar AS
(
	SELECT 1 as N, DATEADD(MONTH, DATEDIFF(MONTH, '18991231',@startDate),'18991231') AS Dt
	UNION ALL
	SELECT N+1, DATEADD(MONTH,DATEDIFF(MONTH, '18991231',@startDate)+N,'18991231') AS Dt 
	FROM calendar
	WHERE N < @months
)
SELECT DT FROM calendar ORDER BY 1 OPTION (MAXRECURSION 0); 

quote:

---

Originally posted by tkizer

Show us the query.

Tara Kizer

---

DECLARE @EndOfDayDate DATE
SET @EndOfDayDate = '2011-02-28'
SET @EndOfDayDate = DATEADD(DAY, 1, @EndOfDayDate)  
DECLARE @ActiveSubscriptions AS TABLE(SUBSCRIPTION_ID INT 
                                     ,FINANCIER_ID INT 
                                     ,PUBLISHING_COMPANY_ID INT 
                                     ,OUTSTANDING_SUBSCRIPTION_TOTAL MONEY)  
INSERT INTO @ActiveSubscriptions  
SELECT AG.SUBSCRIPTION_ID  
              ,AG.FINANCIER_ID 
              ,AG.PUBLISHING_COMPANY_ID 
              ,SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (1,2)  
THEN ISNULL(TH.PREMIUM_VALUE,0) + ISNULL(TH.INTEREST_VALUE, 0) 
ELSE (ISNULL(TH.PREMIUM_VALUE,0) + ISNULL(TH.INTEREST_VALUE,0)) * -1 END)  
AS OUTSTANDING_SUBSCRIPTION_TOTAL  
FROM SUBSCRIPTIONS AS AG  
       INNER JOIN TRANSACTION_HEADER AS TH  
              ON TH.SUBSCRIPTION_ID = AG.SUBSCRIPTION_ID  
WHERE (  
              AG.CANCELLATION_DATE IS NULL  
              OR AG.CANCELLATION_DATE > @EndOfDayDate  
         )  
       AND   
              (AG.RETIRED_DATE IS NULL  
              OR AG.RETIRED_DATE > @EndOfDayDate)  
       AND TH.TRANSACTION_TYPE_ID IN (1, 2, 3, 8, 11, 210, 211, 214)  
       AND TH.EFFECTIVE_DATE < @EndOfDayDate 
GROUP BY AG.SUBSCRIPTION_ID  
              ,AG.INSURER_ID  
              ,AG.PUBLISHING_COMPANY_ID  
              ,AG.FINANCIER_ID  
HAVING SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (1,2)  
                                  THEN TH.PREMIUM_VALUE  
                                  ELSE TH.PREMIUM_VALUE * -1  
                           END) <> 0 



-- Current Outstanding Balance and counts 
SELECT F.LONG_NAME AS FINANCIER_NAME 
              ,COUNT(AA.SUBSCRIPTION_ID) AS ACTIVESUBSCRIPTIONs 
FROM @ActiveSubscriptions AS AA 
       INNER JOIN FINANCIERS AS F 
              ON F.FINANCIER_ID = AA.FINANCIER_ID 
GROUP BY F.LONG_NAME
ORDER BY F.LONG_NAME


SELECT F.LONG_NAME AS FINANCIER_NAME 
              ,sum(AA.OUTSTANDING_SUBSCRIPTION_TOTAL) AS OPENINGSUBSCRIPTIONBALANCE
FROM @ActiveSubscriptions AS AA 
       INNER JOIN FINANCIERS AS F 
              ON F.FINANCIER_ID = AA.FINANCIER_ID 
GROUP BY F.LONG_NAME
ORDER BY F.LONG_NAME