question

The answer depends on the complexity of the query. If possible, it might be much faster to just run it all as one set. Unfortunately, the various window functions I would normally recommend (LAG, LEAD, etc.) aren't available in SQL Server 2008, but you might still be able to do something similar. It's difficult to answer not knowing more details. If the query is too complex or perhaps already contained in a stored procedure that you're not comfortable editing, you could write a T-SQL loop or use SSIS to repeatedly call it. If you already have everything else in SQL, I don't see a need for SSIS. If, however, you need to collect data from multiple sources or perform other more complex tasks, then an SSIS package might be a better option. In short, it depends. ;)

I would suggest doing it with ssis ..... for better control over the process... you could use a for loop container and iterate it for 365 times.. use oledb source and queries to get the data and dump it into destination table...feature of checkpoints is there which would allow to continue from the point where error occurred last time; using checkpoints with forloop is debatable though

You can use a date dimension and APPLY for this. If you don't have a date dimension, see further below. If you do, then this pattern may work for you (adjust the column names as needed, and slot in your 200 line query): SELECT dimDate.FullDate ,PriorDates.FullDate AS PriorDate ,PriorDates.HowLongAgo FROM dimDate OUTER APPLY ( /*This happens once for each row in the main query. We limit it in the where clause - note that we are referencing tables from *outside* the APPLY. Do whatever work you need to do for each date in here. In this case, I'm listing out all the days in the same calendar year that are before this date, then determining how many days ago each was.*/ SELECT DATEDIFF(DAY, dimDate.FullDate, d.FullDate) AS HowLongAgo ,d.FullDate FROM dimDate d WHERE d.FullDate < dimDate.FullDate AND d.CalendarYear = dimDate.CalendarYear ) as PriorDates WHERE dimDate.CalendarYear = 2015 ORDER BY dimDate.FullDate If you don't have a date dimension, here's my generic script for mocking one up; you can substitute the above query for the "SELECT * FROM dimDate" at the end of this one: /*This is my standard code for mocking up a date dimension when using an actual table is for some reason impractical.*/ SET NOCOUNT ON /*Treat Monday as the first day of the week*/ SET DATEFIRST 1 /*These act as parameters. Variables would work too.*/ ;with myStartDate AS ( SELECT CAST('2012-01-01' AS DATE) AS StartDate ,7 AS CalendarMonth_FirstMonthOfFiscalYear ) ,n AS ( SELECT one FROM (VALUES (1) ,(1) ,(1) ,(1) ,(1) ,(1) ,(1) ,(1) ,(1) ,(1) ) AS Ten(One) ) , myNumbers AS ( SELECT ROW_NUMBER() OVER (ORDER BY n.one ) AS number FROM n CROSS APPLY n AS nToPowerOf2 CROSS APPLY n AS nToPowerOf3 CROSS APPLY n AS nToPowerOf4 ) , myDates AS ( SELECT CAST(DATEADD(DAY, number - 1, StartDate) AS DATETIME) AS FullDate ,CalendarMonth_FirstMonthOfFiscalYear ,GETDATE() AS Today from myNumbers Cross apply myStartDate ) ,dimDate AS ( SELECT FullDate , DATEPART(dw, FullDate) AS DayNumberOFWeek , DATENAME(WEEKDAY, FullDate) AS EnglishDayNameOfWeek , DAY(FullDate) AS DayNumberOfMonth , DATEPART(dy, FullDate) AS DayNumberOfYear , DATEPART(wk, FullDate) AS WeekNumberOfYear , DATENAME(Month, FullDate) AS EnglishMonthName , MONTH(FullDate) AS MonthNumberOfYear ,RIGHT('00' + CAST( MONTH(FullDate) AS VARCHAR(50)) ,2) + ' - ' + DATENAME(Month, FullDate) AS MonthNumberAndName , DATEPART(q, FullDate) AS CalendarQuarter , YEAR(FullDate) AS CalendarYear , CASE WHEN MONTH(FullDate) < 7 THEN 1 ELSE 2 END AS CalendarSemester , DATEPART(q, DATEADD(MONTH, CalendarMonth_FirstMonthOfFiscalYear -1, FullDate)) AS FiscalQuarter , YEAR(DATEADD(MONTH, CalendarMonth_FirstMonthOfFiscalYear -1, FullDate)) AS FiscalYear , CASE WHEN MONTH(DATEADD(MONTH, CalendarMonth_FirstMonthOfFiscalYear -1, FullDate)) < 7 THEN 1 ELSE 2 END AS FiscalSemester ,RIGHT('00' + CAST( MONTH(DATEADD(MONTH, 6, FullDate)) AS VARCHAR(50)) ,2) + ' - ' + DATENAME(Month, FullDate) AS FiscalMonthNumberAndName , CAST(CAST(YEAR(FullDate) AS CHAR(4) ) + RIGHT('00' + CAST(MONTH(FullDate) AS VARCHAR(4)),2) AS INT) AS CalendarYearAndMonthNumber ,CASE WHEN YEAR(FullDate) = YEAR(Today) THEN 'Current Calendar Year' WHEN YEAR(FullDate) = YEAR(Today) - 1 THEN 'Prior Calendar Year' WHEN YEAR(FullDate) = YEAR(Today) - 2 THEN 'Two Calendar Years Ago' ELSE 'Not' END AS Current_CalendarYear ,CASE WHEN YEAR(DATEADD(MONTH, CalendarMonth_FirstMonthOfFiscalYear -1, FullDate)) = YEAR(DATEADD(MONTH, CalendarMonth_FirstMonthOfFiscalYear -1, Today)) THEN 'Current Fiscal Year' WHEN YEAR(DATEADD(MONTH, CalendarMonth_FirstMonthOfFiscalYear -1, FullDate)) = YEAR(DATEADD(MONTH, CalendarMonth_FirstMonthOfFiscalYear -1, Today)) - 1 THEN 'Prior Fiscal Year' WHEN YEAR(DATEADD(MONTH, CalendarMonth_FirstMonthOfFiscalYear -1, FullDate)) = YEAR(DATEADD(MONTH, CalendarMonth_FirstMonthOfFiscalYear -1, Today)) - 2 THEN 'Two Fiscal Years Ago' ELSE 'Not' END AS Current_FiscalYear ,CASE WHEN CAST(CAST(YEAR(FullDate) AS CHAR(4) ) + RIGHT('00' + CAST(MONTH(FullDate) AS VARCHAR(4)),2) AS INT) = CAST(CAST(YEAR(Today) AS CHAR(4) ) + RIGHT('00' + CAST(MONTH(Today) AS VARCHAR(4)),2) AS INT) THEN 'Current Month and Year' ELSE 'Not' END AS Current_MonthAndYear FROM myDates ) SELECT * FROM dimDate

Hey tlenzmeier, I will suggest, you can write a Stored Procedure or even a SSIS Package to execute your query. Once you have developed one of these, schedule it on your SQL Server by creating a job using SQL Server Agent. That's it. You are all set. The job will take care of executing your script/ETL everyday.

Here's the SQL statement. I was told not to change it! USE MISProductivity DECLARE @Today VARCHAR (10) DECLARE @YESTERDAY VARCHAR(10) SET @Today = CONVERT(VARCHAR(10),dateadd(d,-1,getdate ()),120) SET @YESTERDAY = CONVERT(VARCHAR(10),dateadd(d,-2,getdate ()),120) SELECT DISTINCT a.account_nbr AS acctno, a.completed_dttm, a.prop_for_sale_flg, a.vacant_flg, created_by, updated_dt, VENDOR_CD INTO #propsale1 -- old ones /* drop table #propsale1 */ FROM AS400_Extraction.DBO.P_CO_VENDOR_SITE_SERVICES a JOIN ( SELECT account_nbr, MAX(completed_dttm) AS completed_dttm FROM AS400_Extraction.DBO.P_CO_VENDOR_SITE_SERVICES WHERE --CONVERT(VARCHAR(10),completed_dttm,120) < '2011-01-24'--@Today--<= '2010-09-23' updated_dt < @Today--'2011-01-25' AND RTRIM(LTRIM(request_type_cd)) in (3,5,30)-- IBPO and Inspections and No Contact Inspection AND status_cd = 1 AND CONVERT(VARCHAR(10),completed_dttm,120) IS NOT NULL AND ISDATE(CONVERT(VARCHAR(10),completed_dttm,120))=1 GROUP BY account_nbr ) b ON a.account_nbr = b.account_nbr and a.completed_dttm = b.completed_dttm SELECT DISTINCT a.account_nbr AS acctno, a.completed_dttm, a.prop_for_sale_flg, a.vacant_flg, created_by, j.APPR_TYPE AS Source_new, a.updated_dt, VENDOR_CD INTO #propsale2 -- new ones /* drop table #propsale2 */ FROM AS400_Extraction.DBO.P_CO_VENDOR_SITE_SERVICES a JOIN ( SELECT account_nbr, MAX(completed_dttm) AS completed_dttm, MAX(updated_dT) AS updated_dt FROM AS400_Extraction.DBO.P_CO_VENDOR_SITE_SERVICES WHERE --CONVERT(VARCHAR(10),completed_dttm,120) >= '2011-01-24'--@Today--> '2010-09-23' updated_dt = @Today--'2011-01-25' AND vacant_flg <> '' AND RTRIM(LTRIM(request_type_cd)) in (3,5,30)-- IBPO and Inspections and No Contact Inspection AND status_cd = 1 AND CONVERT(VARCHAR(10),completed_dttm,120) IS NOT NULL AND ISDATE(CONVERT(VARCHAR(10),completed_dttm,120))=1 GROUP BY account_nbr ) b ON a.account_nbr = b.account_nbr AND a.completed_dttm = b.completed_dttm AND a.updated_dt = b.updated_dt left outer join [MISProductivity].[dbo].[tbl_APPR_Types] j on a.request_type_cd = j.Code SELECT a.acctno AS acctno_new, a.completed_dttm AS completed_dttm_new, a.source_new, a.prop_for_sale_flg AS prop_for_sale_flg_new, a.vacant_flg AS vacant_flg_new, b.acctno AS acctno_old, b.completed_dttm AS completed_dttm_old, b.prop_for_sale_flg AS prop_for_sale_flg_old, b.vacant_flg AS vacant_flg_old, a.created_by, a.updated_dt, a.VENDOR_CD INTO #final /* drop table #final */ FROM #propsale2 a JOIN #propsale1 b on a.acctno = b.acctno WHERE a.vacant_flg <> b.vacant_flg AND a.vacant_flg <> 'n' INSERT INTO #final SELECT a.acctno AS acctno_new, a.completed_dttm AS completed_dttm_new, a.source_new, a.prop_for_sale_flg AS prop_for_sale_flg_new, a.vacant_flg AS vacant_flg_new, b.acctno AS acctno_old, b.completed_dttm AS completed_dttm_old, b.prop_for_sale_flg AS prop_for_sale_flg_old, b.vacant_flg AS vacant_flg_old, a.created_by, a.updated_dt, a.VENDOR_CD FROM #propsale2 a left join #propsale1 b on a.acctno = b.acctno WHERE b.acctno is null and a.vacant_flg = 'y' SELECT * INTO #VACANT_FLAG FROM AS400_Extraction.DBO.P_CO_VENDOR_SITE_SERVICES WHERE vacant_flg = 'Y' AND RTRIM(LTRIM(request_type_cd)) in (3,5,30)-- IBPO and Inspections and No Contact Inspection INSERT INTO #final SELECT fmacct AS acctno_new, CONVERT(VARCHAR(10),CONVERT(SMALLDATETIME,CONVERT(VARCHAR(10),fmdate,120)),120) AS completed_dttm_new, source_new = 'UCSE', NULL, fmaf13 as vacant_flg_new, NULL, NULL,NULL, fmbf13 as vacant_flg_old, FMUSRI AS created_by, null, NULL --FROM as400_extraction.dbo.R_SRPSMALL from [dreporting\sqldvl1].misworKdb.dbo.SRPSMALL_20160115 WHERE fmcode = 66 and fmaf13 = 'y' AND CONVERT(VARCHAR(10),CONVERT(SMALLDATETIME,CONVERT(VARCHAR(10),fmdate,120)),120) = @Today AND fmacct NOT IN (SELECT acctno_new FROM #final) AND fmacct NOT IN (SELECT account_nbr FROM #vacant_flag) INSERT INTO #final select acctno as acctno_new, convert(varchar(10),convert(smalldatetime,convert(varchar(10),fmdate,120)),120) as completed_dttm_new, source_new = 'UCSE', NULL, fmaf13 as vacant_flg_new, NULL, NULL,NULL, fmbf13 as vacant_flg_old, teller as created_by, null, NULL --from as400_extraction.dbo.p_clpsmall from [dreporting\sqldvl1].misworKdb.dbo.CLPSMALL_20160115 where fmcode = 297 and fmaf13 = 'y' and convert(varchar(10),convert(smalldatetime,convert(varchar(10),fmdate,120)),120) = @Today and acctno not in (select acctno_new from #final) SELECT DISTINCT a.acctno_new, a.vacant_flg_new, a.vacant_flg_old, CONVERT(VARCHAR(10),a.completed_dttm_new,120)AS completed_dttm_new, a.Source_new, a.prop_for_sale_flg_new, T1.Pmt_Due_Dt duedate, T1.Principal_Bal prnbal, T1.Loan_Status calcstatus, T3.Lien_Position calien1, T2.DQ_Portfolio PORTFOLIO2, T5.DQ_Cd calcDELQ, a.created_by, CASE WHEN T1.Loan_Status = 'Chapter 7' THEN B2.CVNAME WHEN T1.Loan_Status LIKE 'Ch%' THEN B3.CVNAME ELSE d.collectorname END AS Collector, CASE WHEN T1.Loan_Status LIKE 'CH%' THEN '' ELSE d.director END director, CASE WHEN T1.Loan_Status LIKE 'CH%' THEN '' ELSE d.manager END manager, T3.Prod_Category product1, a.UPDATED_DT, v.Vendor, T4.Prop_State_Cd as 'State', LEFT(T1.Source_Sys_Nm,3) Source_Sys_nm, CASE WHEN C.LOANCD = 1 THEN 'FHA' WHEN C.LOANCD = 2 THEN 'VA' WHEN T6.RURAL_DEVELOPMENT_FLG = 'Y' THEN 'USDA' WHEN T8.LO_TYPE IN (1,5,7) THEN 'FHA' WHEN T8.LO_TYPE IN (2) THEN 'VA' WHEN T8.LO_TYPE IN (9) THEN 'USDA' WHEN T2.LOAN_NBR IS NULL THEN 'FHLMC' WHEN T9.SBSCOD = 60 THEN 'FNMA' ELSE 'Non-GSE' END [TYPE], CASE WHEN LEFT(T3.Prod_Category,2) NOT IN ('HE','MH') THEN 'Other' ELSE LEFT(T3.Prod_Category,2) END 'Loan_Type', T1.Repo_Dt INTO #RESULTS FROM #final a JOIN GT_Rpt.dbo.Current_Core T1 ON T1.Loan_Nbr = A.acctno_new JOIN GT_IDS.dbo.Portfolio_Group T2 ON T2.Loan_Nbr = T1.Loan_Nbr AND T2.BIA_End_Dt IS NULL JOIN GT_Rpt.dbo.Current_Loan T3 ON T3.Loan_Nbr = T1.Loan_Nbr JOIN GT_IDS.dbo.Property T4 ON T4.Loan_Nbr = T1.Loan_Nbr JOIN GT_Rpt.dbo.Current_Delq T5 ON T5.Loan_Nbr = T1.Loan_Nbr JOIN GT_Rpt.dbo.Current_Loan_Activity T7 ON T7.Loan_Nbr = T1.Loan_Nbr LEFT JOIN as400_extraction.dbo.p_clpacmst c ON a.acctno_new = c.acctno LEFT JOIN AS400_Extraction.dbo.P_CLPFNDG T9 ON T9.ACCTNO = T1.Loan_Nbr LEFT JOIN AS400_EXTRACTION.dbo.P_CL_ORIGINATION_DATA T6 ON T6.ACCOUNT_NBR = T1.LOAN_NBR LEFT JOIN (SELECT LOAN_NUMBER, LO_type FROM MSP_BDE.dbo.LOAN WHERE LOAN_END_DT IS NULL) T8 ON T8.LOAN_NUMBER = T1.Loan_Nbr LEFT JOIN Web_Store.dbo.tblQSUM_Goals d ON T1.Collector_Queue = d.queue and T7.Servicing_Region_Nbr = d.region LEFT JOIN MISProductivity.dbo.tbl_APPR_Vendors v ON (VENDOR_CD = Vendor_ID) LEFT JOIN AS400_Extraction.dbo.B_BK_ACCOUNT_OWNER_XREF B1 ON B1.ACCOUNT_NBR = A.ACCTNO_NEW LEFT JOIN Warehouses.dbo.tbl_EmployeeID B2 ON B2.CVCUID = B1.CHP7_OWNER_USER_ID AND B2.Today = REPLACE(@Today,'-','') LEFT JOIN Warehouses.dbo.tbl_EmployeeID B3 ON B3.CVCUID = B1.CHP13_OWNER_USER_ID AND B3.Today = REPLACE(@Today,'-','') WHERE T1.Open_Loan_Ind = 1 AND T1.Loan_Status NOT LIKE 'REO%' AND T3.Lien_Position = 1 AND a.vacant_flg_new = 'y' AND T4.Prop_State_Cd NOT IN ('MD') AND T2.DQ_Portfolio <> 'walter' ORDER BY T1.Pmt_Due_Dt DESC SELECT * FROM #RESULTS /*WHERE [TYPE] in (@GSE) AND Source_Sys_Nm in (@SourceSystem) AND Loan_Type in (@Loan_Type)*/ --DROP TABLE #RESULTS,#propsale2, #propsale1, #final, #VACANT_FLAG