x

Is there a way to use a While within a SQL Union query?

I have the following SQL query. I need to have a WHILE loop in the second half. I can't break the query into two queries, because the queries are in a SSRS report, and need to be in the same table. I get an error where the WHILE is. Is there any way around this?

select DATEName(month, DateAdd(d,1,trendsDate)) AS [TrendMonth],DATEPART(wk, DateAdd(d,1,trendsDate)) AS [TrendWeek], DATEName(weekday, DateAdd(d,1,trendsDate)) AS [TrendWeekDay], DateAdd(d,1,trendsDate) as [TrendsDate], 0 as [TrendsOccurrences], 'Weekend' as [trendsField], 'Weekend' as [reportCategory], 'Weekend' as [Team Name], 'Weekend' as [WLName], 0 as [repseq], DATEPART(weekday, DateAdd(d,1,trendsDate)) AS [TrendWeekDayNum], DATEPART(month, DateAdd(d,1,trendsDate)) AS [TrendMonthNum], DATEPART(year, DateAdd(d,1,trendsDate)) AS [TrendYear], 'Weekend' as [Metric], -1 as [calccount], 1 as [metricseq], 1 as [FieldCount], ''as [WLCode] from trends where trendsDate >= @ReportStartDt and trendsDate <> convert(varchar, GETDATE(), 101) and datename(weekday,trendsDate) = 'Friday' group by TrendsDate, trendsField

UNION

WHILE (@incdate <= @endDate) BEGIN

select DATEName(month, @incDate) AS [TrendMonth], DATEPART(wk, @incDate) AS [TrendWeek], DATEName(weekday, @incDate) AS [TrendWeekDay], @incDate as [TrendsDate], 1 as [TrendsOccurrences], clientTableDesc as [TrendsField], clientTableInfo as [reportCategory], clientTableInfo2 as [Team Name], clientTableDesc as [WLName], clientTableInfo3 as [repseq], DATEPART(weekday, @incDate) AS [TrendWeekDayNum], DATEPART(month, @incDate) AS [TrendMonthNum], DATEPART(year, @incDate) AS [TrendYear], 'Open' as [Metric], -1 as [calccount], 1 as [metricseq], count (distinct workListCode) as [FieldCount], workListCode as [WLCode] from workListRecs, clientSelfDescInfo where workListCode = clientTableCode and clientSelfDescInfo.clientTableType = 'DR' and @incDate NOT IN (select convert(datetime, virtualTableCode) from virtualSelfDescInfo where virtualTableType = 'HD') and clientTableInfo <> 'Draft' and clientTableInfo <> 'Prep/Draft' and workListCode <> 'PBI' and workListCode <> 'PPN' and clientTableInfo <> 'Claim Follow-Up Calls' and (@calculation = 'Average') and workListCode in (select clientTableCode from clientSelfDescInfo where clientTableType = 'OM' and SUBSTRING(clientTableInfo, convert(int,@rpReportType), 1) = 'Y')

set @incdate = DATEADD(dd, 1, @incdate)

END

more ▼

asked Apr 20, 2010 at 10:41 PM in Default

user-1140 (yahoo) gravatar image

user-1140 (yahoo)
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

nope. WHILE is flow control. You can't mix that into a SQL statement.

you can remove the while and for the 2nd query cross join to a number / tally table

select DATEName(month, dateadd(day, n.number, @incDate)),
. . . .
from . .. cross join number n
where n.number between 0 and @num_days_required
more ▼

answered Apr 20, 2010 at 11:09 PM

Squirrel 1 gravatar image

Squirrel 1
1.6k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x547
x369

asked: Apr 20, 2010 at 10:41 PM

Seen: 2054 times

Last Updated: Apr 20, 2010 at 10:41 PM