Dynamic For Loop

My below code returns a union fine in northwind. However, I'd like add some
FOR LOOP's while building the SELECT statements to cut down on the redundant
code. As you can see, each @.SQLx statement has a variable from 1 to 3. I
created this exmple to get an idea of using a FOR LOOP to cut down on code.
CODE:
declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000), @.SQL3
varchar(4000)
declare @.dtYear1 int, @.dtYear2 int, @.dtYear3 int, @.dtStartDate1 datetime,
@.dtEndDate1 datetime
declare @.dtStartDate2 datetime, @.dtEndDate2 datetime, @.dtStartDate3
datetime, @.dtEndDate3 datetime
declare @.typeID int, @.debug int
set @.typeID = '1'
set @.dtStartDate1='19960101'
set @.dtEndDate1='19961231'
set @.dtStartDate2='19970101'
set @.dtEndDate2='19971231'
set @.dtStartDate3='19980101'
set @.dtEndDate3='19981231'
set @.dtYear1='1996'
set @.dtYear2='1997'
set @.dtYear3='1998'
SET @.SQL1 = ' SELECT ' + '''Year $$@.dtYear1$$''' + ' AS Date, '
SET @.SQL2 = ' SELECT ' + '''Year $$@.dtYear2$$''' + ' AS Date, '
SET @.SQL3 = ' SELECT ' + '''Year $$@.dtYear3$$''' + ' AS Date, '
set @.SQL1 = REPLACE(@.SQL1,'$$@.dtYear1$$',@.dtYear1)
set @.SQL2 = REPLACE(@.SQL2,'$$@.dtYear2$$',@.dtYear2)
set @.SQL3 = REPLACE(@.SQL3,'$$@.dtYear3$$',@.dtYear3)
set @.debug = 0
IF @.typeID = 1
BEGIN
SET @.SQL1 = @.SQL1 + 'MAX(Orders.Freight) AS MaxOfFreight '
SET @.SQL2 = @.SQL2 + 'MAX(Orders.Freight) AS MaxOfFreight '
SET @.SQL3 = @.SQL3 + 'MAX(Orders.Freight) AS MaxOfFreight '
END
IF @.typeID = 2
BEGIN
SET @.SQL1 = @.SQL1 + 'COUNT(*) AS SalesCount '
SET @.SQL2 = @.SQL2 + 'COUNT(*) AS SalesCount '
SET @.SQL3 = @.SQL3 + 'COUNT(*) AS SalesCount '
END
SET @.SQL1 = @.SQL1 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL2 = @.SQL2 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL3 = @.SQL3 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL1 = @.SQL1 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate1, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate1, 112) + ''''
SET @.SQL2 = @.SQL2 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate2, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate2, 112) + ''''
SET @.SQL3 = @.SQL3 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate3, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate3, 112) + ''''
SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2 + ' UNION ALL ' + @.SQL3
IF @.debug = 1
PRINT @.SQL
ELSE
EXEC(@.SQL)Scott
SQL Server does not have FOR LOOP , bit it does have WHILE LOOP
See soem examples
DECLARE @.i INT
SET @.i=1
WHILE @.i<100
BEGIN
INSERT INTO Table VALUES (@.i)
SET @.i=@.i+1
END
"Scott" <sbailey@.mileslumber.com> wrote in message
news:eR6CT9EBGHA.272@.TK2MSFTNGP09.phx.gbl...
> My below code returns a union fine in northwind. However, I'd like add
> some FOR LOOP's while building the SELECT statements to cut down on the
> redundant code. As you can see, each @.SQLx statement has a variable from 1
> to 3. I created this exmple to get an idea of using a FOR LOOP to cut down
> on code.
> CODE:
> declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000),
> @.SQL3 varchar(4000)
> declare @.dtYear1 int, @.dtYear2 int, @.dtYear3 int, @.dtStartDate1 datetime,
> @.dtEndDate1 datetime
> declare @.dtStartDate2 datetime, @.dtEndDate2 datetime, @.dtStartDate3
> datetime, @.dtEndDate3 datetime
> declare @.typeID int, @.debug int
> set @.typeID = '1'
> set @.dtStartDate1='19960101'
> set @.dtEndDate1='19961231'
> set @.dtStartDate2='19970101'
> set @.dtEndDate2='19971231'
> set @.dtStartDate3='19980101'
> set @.dtEndDate3='19981231'
> set @.dtYear1='1996'
> set @.dtYear2='1997'
> set @.dtYear3='1998'
> SET @.SQL1 = ' SELECT ' + '''Year $$@.dtYear1$$''' + ' AS Date, '
> SET @.SQL2 = ' SELECT ' + '''Year $$@.dtYear2$$''' + ' AS Date, '
> SET @.SQL3 = ' SELECT ' + '''Year $$@.dtYear3$$''' + ' AS Date, '
> set @.SQL1 = REPLACE(@.SQL1,'$$@.dtYear1$$',@.dtYear1)
> set @.SQL2 = REPLACE(@.SQL2,'$$@.dtYear2$$',@.dtYear2)
> set @.SQL3 = REPLACE(@.SQL3,'$$@.dtYear3$$',@.dtYear3)
> set @.debug = 0
> IF @.typeID = 1
> BEGIN
> SET @.SQL1 = @.SQL1 + 'MAX(Orders.Freight) AS MaxOfFreight '
> SET @.SQL2 = @.SQL2 + 'MAX(Orders.Freight) AS MaxOfFreight '
> SET @.SQL3 = @.SQL3 + 'MAX(Orders.Freight) AS MaxOfFreight '
> END
> IF @.typeID = 2
> BEGIN
> SET @.SQL1 = @.SQL1 + 'COUNT(*) AS SalesCount '
> SET @.SQL2 = @.SQL2 + 'COUNT(*) AS SalesCount '
> SET @.SQL3 = @.SQL3 + 'COUNT(*) AS SalesCount '
> END
> SET @.SQL1 = @.SQL1 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL2 = @.SQL2 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL3 = @.SQL3 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL1 = @.SQL1 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate1, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate1, 112) + ''''
> SET @.SQL2 = @.SQL2 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate2, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate2, 112) + ''''
> SET @.SQL3 = @.SQL3 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate3, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate3, 112) + ''''
> SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2 + ' UNION ALL ' + @.SQL3
> IF @.debug = 1
> PRINT @.SQL
> ELSE
> EXEC(@.SQL)
>|||A better way would be something like this:
SELECT 'Year '+DATENAME(YEAR,MIN(orderdate)) AS [date],
MAX(O.Freight) AS maxoffreight
FROM Customers AS C
JOIN Orders AS O
ON C.customerid = O.customerid
WHERE O.orderdate BETWEEN @.startdate AND @.enddate
GROUP BY YEAR(O.orderdate) ;
David Portas
SQL Server MVP
--|||I guess what I'm asking is there a way in SQL to create dynamic variables?
So for example, my code would loop 3 times and each time my variables like
@.dtStartDate1 would increase from @.dtStartDate1 to @.dtStartDate2 and then
@.dtStartDate3.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23aBO7zFBGHA.2512@.TK2MSFTNGP09.phx.gbl...
> Scott
> SQL Server does not have FOR LOOP , bit it does have WHILE LOOP
> See soem examples
> DECLARE @.i INT
> SET @.i=1
> WHILE @.i<100
> BEGIN
> INSERT INTO Table VALUES (@.i)
> SET @.i=@.i+1
> END
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:eR6CT9EBGHA.272@.TK2MSFTNGP09.phx.gbl...
>|||I would do it like that except we have custom year begin and end dates and i
didn't want to take time to write a custom function.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134997569.867513.55250@.f14g2000cwb.googlegroups.com...
>A better way would be something like this:
> SELECT 'Year '+DATENAME(YEAR,MIN(orderdate)) AS [date],
> MAX(O.Freight) AS maxoffreight
> FROM Customers AS C
> JOIN Orders AS O
> ON C.customerid = O.customerid
> WHERE O.orderdate BETWEEN @.startdate AND @.enddate
> GROUP BY YEAR(O.orderdate) ;
> --
> David Portas
> SQL Server MVP
> --
>|||scott wrote:
> I would do it like that except we have custom year begin and end dates and
i
> didn't want to take time to write a custom function.
>
Are your annual begin and end dates fixed in the Calendar year or are
they based on some specific non-Gregorian calendar rules? If it's a
Calendar year, just use DATEADD to offset the date by the required
number of days. If it's specific company calendar logic, create a
calendar table and populate it with the information about your
customer's calendar. Join the calendar table into the query. Either
way, you don't need dynamic SQL.
David Portas
SQL Server MVP
--