loop
in [Programming SQL]
|
Prev: Fastest way to Copy DB + Keys,Constraints to another server
Next: Union versus multiple join conditions
From: geebee on 3 Jun 2010 23:17 hi, i have the following: --****************************************** --make temp table --****************************************** CREATE TABLE #DCLNS2 ( B_DATE DATETIME, ACCT INT, LTR_CODE char(50), INV char(5) ) INSERT INTO #DCLNS2 ("B_DATE","acct","LTR_CODE","INV") (SELECT * FROM dcl_lts_CSV) --****************************************** --END of make temp table --****************************************** --****************************************** --select distinct B DATES from #DCLNS --****************************************** SELECT DISTINCT B_DATE INTO #DATES2 FROM #DCLNS2 --****************************************** --END of select distinct B DATES from #DCLNS --****************************************** select * into #temp_detail2 from tbl_MULTI_INB WHERE c_date BETWEEN '2010-04-13' AND '2010-06-02' --****************************************** --APPEND, looping through recordcount of #DATES, using @DT as criteria --****************************************** DECLARE @intFlag int DECLARE @intCount int SET @intCount = (SELECT COUNT(*) FROM #DATES2) SET @intFlag = 1 WHILE (@intFlag < (@intCount + 1)) BEGIN declare @DT AS varchar(10) declare @RowNum int declare DTlist cursor for SELECT CONVERT(varchar(4), YEAR(B_DATE)) + '-' + CASE WHEN month(B_DATE)<10 then '0' + CONVERT(varchar(1),month(B_DATE)) ELSE CONVERT(varchar(2), month(B_DATE)) END + '-' + CASE WHEN DAY(B_DATE) <9 THEN '0' + CONVERT(varchar(1),DAY(B_DATE)) ELSE CONVERT(varchar(2),DAY(B_DATE)) END from #DATES2 OPEN DTlist FETCH NEXT FROM DTlist INTO @DT set @RowNum = 0 WHILE @@FETCH_STATUS = 0 BEGIN set @RowNum = @RowNum + 1 print cast(@RowNum as varchar(1)) + ' ' + @DT print @DT SELECT 'MHA_ACCTS',cast(B.B_DATE as smalldatetime), RTRIM(B.LTR_CODE), cast(A.c_date as smalldatetime), COUNT(distinct A.Acct) FROM #temp_detail2 A LEFT JOIN #DCLNS2 B ON A.ACCT = B.ACCT WHERE A.applic_num=752 AND A.c_date > cast(@DT as smalldatetime) AND B.B_DATE is not null GROUP BY A.c_date, B.LTR_CODE, B.B_DATE FETCH NEXT FROM DTlist INTO @DT set @intflag = @intflag + 1 END END --****************************************** --END of APPEND, looping through recordcount of #DATES --****************************************** --****************************************** --cleanup --****************************************** CLOSE DTlist DEALLOCATE DTlist this is an attempt to loop through the dates in #DATES2 (assigned to @DT variable in this case) to be used in the query condition which appends data. so if there are 8 records in #DATES2 i want it to loop through the APPEND query 8 times, each time using the current DATE (@DT) value in the APPEND query's where clause. the only problem is that i think it is creating too many blocks of data, or bloating the results. i think the problem has something to do with how i am looping, but im not sure how to fix this. thanks in advance, geebee
From: Dan Guzman on 4 Jun 2010 08:29 Unfortunately, I don't have data for #DCLNS2 and tbl_MULTI_INB to test this but I think derived tables can eliminate the temp tables and loop. I suspect further refactoring is possible. If you post the schema for dbo.tbl_MULTI_INB along with sample data and expected results, maybe someone can provide a better solution. SELECT 'MHA_ACCTS', CAST(B.B_DATE as smalldatetime), RTRIM(B.LTR_CODE), CAST(A.c_date as smalldatetime), COUNT(distinct A.ACCT), ROW_NUMBER() OVER(ORDER BY B.B_DATE) AS rownumber --do you need this? FROM ( SELECT * FROM dbo.tbl_MULTI_INB WHERE c_date BETWEEN '2010-04-13' AND '2010-06-02' ) A JOIN ( SELECT DISTINCT B_DATE FROM #DCLNS2 ) AS DATES2 ON A.c_date > DATES2.B_DATE LEFT JOIN #DCLNS2 B ON A.ACCT = B.ACCT WHERE A.applic_num=752 GROUP BY A.c_date, B.LTR_CODE, B.B_DATE -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "geebee" <geraldjr30(a)hotmail.com(noSPAMs)> wrote in message news:27983A13-00F1-4F5E-A4AE-5F00DB34D6F7(a)microsoft.com... > hi, > > > i have the following: > > > --****************************************** > --make temp table > --****************************************** > CREATE TABLE #DCLNS2 > ( > B_DATE DATETIME, > ACCT INT, > LTR_CODE char(50), > INV char(5) > ) > > > INSERT INTO #DCLNS2 ("B_DATE","acct","LTR_CODE","INV") > (SELECT * FROM > dcl_lts_CSV) > --****************************************** > --END of make temp table > --****************************************** > > > > > > --****************************************** > --select distinct B DATES from #DCLNS > --****************************************** > SELECT DISTINCT > B_DATE > INTO #DATES2 > FROM #DCLNS2 > --****************************************** > --END of select distinct B DATES from #DCLNS > --****************************************** > > > select * > into #temp_detail2 > from tbl_MULTI_INB > WHERE > c_date BETWEEN '2010-04-13' AND '2010-06-02' > > > > > --****************************************** > --APPEND, looping through recordcount of #DATES, using @DT as criteria > --****************************************** > DECLARE @intFlag int > DECLARE @intCount int > SET @intCount = (SELECT COUNT(*) FROM #DATES2) > SET @intFlag = 1 > > WHILE (@intFlag < (@intCount + 1)) > BEGIN > declare @DT AS varchar(10) > declare @RowNum int > declare DTlist cursor for > > SELECT > CONVERT(varchar(4), YEAR(B_DATE)) > + '-' + > CASE > WHEN > month(B_DATE)<10 then > '0' + CONVERT(varchar(1),month(B_DATE)) > > ELSE > CONVERT(varchar(2), month(B_DATE)) > END > + '-' + > CASE > WHEN DAY(B_DATE) <9 > THEN > '0' + CONVERT(varchar(1),DAY(B_DATE)) > ELSE > CONVERT(varchar(2),DAY(B_DATE)) > END > > from #DATES2 > OPEN DTlist > FETCH NEXT FROM DTlist > INTO @DT > set @RowNum = 0 > WHILE @@FETCH_STATUS = 0 > BEGIN > set @RowNum = @RowNum + 1 > print cast(@RowNum as varchar(1)) + ' ' + @DT > print @DT > > > > SELECT 'MHA_ACCTS',cast(B.B_DATE as smalldatetime), RTRIM(B.LTR_CODE), > cast(A.c_date as smalldatetime), > COUNT(distinct A.Acct) > FROM > #temp_detail2 A > LEFT JOIN #DCLNS2 B ON A.ACCT = B.ACCT > WHERE A.applic_num=752 > AND A.c_date > cast(@DT as smalldatetime) > AND B.B_DATE is not null > GROUP BY A.c_date, B.LTR_CODE, B.B_DATE > > > FETCH NEXT FROM DTlist > INTO @DT > > set @intflag = @intflag + 1 > > END > END > --****************************************** > --END of APPEND, looping through recordcount of #DATES > --****************************************** > > > > > > > > --****************************************** > --cleanup > --****************************************** > CLOSE DTlist > DEALLOCATE DTlist > > > this is an attempt to loop through the dates in #DATES2 (assigned to @DT > variable in this case) to be used in the query condition which appends > data. > so if there are 8 records in #DATES2 i want it to loop through the APPEND > query 8 times, each time using the current DATE (@DT) value in the APPEND > query's where clause. the only problem is that i think it is creating too > many blocks of data, or bloating the results. i think the problem has > something to do with how i am looping, but im not sure how to fix this. > > > thanks in advance, > geebee >
From: Cleary on 7 Jun 2010 20:30
On Jun 3, 11:17 pm, geebee <geraldj...(a)hotmail.com(noSPAMs)> wrote: > hi, > > i have the following: > > --****************************************** > --make temp table > --****************************************** > CREATE TABLE #DCLNS2 > ( > B_DATE DATETIME, > ACCT INT, > LTR_CODE char(50), > INV char(5) > ) > > INSERT INTO #DCLNS2 ("B_DATE","acct","LTR_CODE","INV") > (SELECT * FROM > dcl_lts_CSV) > --****************************************** > --END of make temp table > --****************************************** > > --****************************************** > --select distinct B DATES from #DCLNS > --****************************************** > SELECT DISTINCT > B_DATE > INTO #DATES2 > FROM #DCLNS2 > --****************************************** > --END of select distinct B DATES from #DCLNS > --****************************************** > > select * > into #temp_detail2 > from tbl_MULTI_INB > WHERE > c_date BETWEEN '2010-04-13' AND '2010-06-02' > > --****************************************** > --APPEND, looping through recordcount of #DATES, using @DT as criteria > --****************************************** > DECLARE @intFlag int > DECLARE @intCount int > SET @intCount = (SELECT COUNT(*) FROM #DATES2) > SET @intFlag = 1 > > WHILE (@intFlag < (@intCount + 1)) > BEGIN > declare @DT AS varchar(10) > declare @RowNum int > declare DTlist cursor for > > SELECT > CONVERT(varchar(4), YEAR(B_DATE)) > + '-' + > CASE > WHEN > month(B_DATE)<10 then > '0' + CONVERT(varchar(1),month(B_DATE)) > > ELSE > CONVERT(varchar(2), month(B_DATE)) > END > + '-' + > CASE > WHEN DAY(B_DATE) <9 > THEN > '0' + CONVERT(varchar(1),DAY(B_DATE)) > ELSE > CONVERT(varchar(2),DAY(B_DATE)) > END > > from #DATES2 > OPEN DTlist > FETCH NEXT FROM DTlist > INTO @DT > set @RowNum = 0 > WHILE @@FETCH_STATUS = 0 > BEGIN > set @RowNum = @RowNum + 1 > print cast(@RowNum as varchar(1)) + ' ' + @DT > print @DT > > SELECT 'MHA_ACCTS',cast(B.B_DATE as smalldatetime), RTRIM(B.LTR_CODE), > cast(A.c_date as smalldatetime), > COUNT(distinct A.Acct) > FROM > #temp_detail2 A > LEFT JOIN #DCLNS2 B ON A.ACCT = B.ACCT > WHERE A.applic_num=752 > AND A.c_date > cast(@DT as smalldatetime) > AND B.B_DATE is not null > GROUP BY A.c_date, B.LTR_CODE, B.B_DATE > > FETCH NEXT FROM DTlist > INTO @DT > > set @intflag = @intflag + 1 > > END > END > --****************************************** > --END of APPEND, looping through recordcount of #DATES > --****************************************** > > --****************************************** > --cleanup > --****************************************** > CLOSE DTlist > DEALLOCATE DTlist > > this is an attempt to loop through the dates in #DATES2 (assigned to @DT > variable in this case) to be used in the query condition which appends data. > so if there are 8 records in #DATES2 i want it to loop through the APPEND > query 8 times, each time using the current DATE (@DT) value in the APPEND > query's where clause. the only problem is that i think it is creating too > many blocks of data, or bloating the results. i think the problem has > something to do with how i am looping, but im not sure how to fix this. > > thanks in advance, > geebee As Dan suggests, some sample data in and expected output might clarify what you want. However, I suspect that one of your problems ("bloating the results") might be caused by the GREATER THAN in > SELECT 'MHA_ACCTS',cast(B.B_DATE as smalldatetime), RTRIM(B.LTR_CODE), > cast(A.c_date as smalldatetime), > COUNT(distinct A.Acct) > FROM > #temp_detail2 A > LEFT JOIN #DCLNS2 B ON A.ACCT = B.ACCT > WHERE A.applic_num=752 > AND A.c_date > cast(@DT as smalldatetime) > AND B.B_DATE is not null > GROUP BY A.c_date, B.LTR_CODE, B.B_DATE > You are already looping over each distinct date, yet here you not only select and aggregate all the rows that match each distinct date, but also all other rows whose date is greater than each date. |