question

tlenzmeier avatar image
tlenzmeier asked

Need Help With Looping

Hello, I have a request from one of my business units. They want to run a query for each day in 2015 where the query looks at "today" and compares it to any previous day. The output will be inserted into a table. I expect the output to be substantial in terms of rows. I've heard that this is best done by creating an SSIS package or is it best done using a loop statement? Regardless, I am not quite sure how to write this up. I have to admit that this is a little over my head, but I'm the only show in town.
sql-server-2008ssis-2008looping
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered
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. ;)
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The query itself is rather lengthy. It calls out multiple tables with numerous where clauses. The person requesting this said that it needs to be iterative, starting with Jan. 2 and then adding 1 more day until we get to the end of 2015. The query essentially looks at today and compares it to any previous day and then compiles a dataset. If one were to do this manually, you would start on Jan 2, then Jan 3, Jan 4, and so on until you got to Dec 31. For each day in the year, you need to look back to any day prior to that. The query is basically trying to identify, in this case, a change in the vacancy status in a particular piece of property. I doubt you'd want to scroll through the exact code, but it's just short of 200 lines of code. It is not currently in a stored procedure. If this were simple, I could use a CTE and dateadd string.
0 Likes 0 ·
As your question is so vague then any answer will have to be equally vague. Depending on how your tables are set out this could be quite an easy task, but as you are giving no detail that is as much as I can tell you!
0 Likes 0 ·
I'd love to paste in the code, but the character limit precludes me from doing that. I could email it if that would help.
0 Likes 0 ·
If you can't edit the question to add the code, try posting it as an answer. That gets by the character limit for comments
0 Likes 0 ·
erlokeshsharma08 avatar image
erlokeshsharma08 answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sdoubleday avatar image
sdoubleday answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nikhil_satam avatar image
nikhil_satam answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tlenzmeier avatar image
tlenzmeier answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If this script is doing the task, you desire, you can add this script to create a SQL Server Job using SQL Server Agent and schedule it to execute every night or early morning. Note: The SQL Server job will follow the timezone of your Server, what it is configured to.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.