Extra row in CTE

Hi,

I have a table (#qed_missing_quarters) with the following rows in it:

row_num   quarter_end_date
1	2002-09-30 00:00:00.000
2	2002-12-31 00:00:00.000
3	2003-03-31 00:00:00.000
4	2003-06-30 00:00:00.000
5	2003-09-30 00:00:00.000

And I have the following code as a test to traverse across the table:

;WITH qed_missing_values(row_num, quarter_end_date)
AS
(
	SELECT row_num AS [row_num], quarter_end_date AS [quarter_end_date]
	FROM #qed_missing_quarters WHERE row_num = 1

	UNION ALL

	SELECT a.row_num + 1, a.quarter_end_date
	FROM #qed_missing_quarters AS a
	INNER JOIN qed_missing_values AS b
	ON a.row_num = b.row_num
)
SELECT * FROM qed_missing_values

However, I always end up with an extra first row, so 6 rows are
returned (the first one twice).
And I can't seem to get rid of the extra row.
Can anyone explain why I get the extra row and how to remove it
please.

Many thanks,
Frank.
0
Frank
1/29/2010 1:38:31 PM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
1240 Views

Similar Articles

[PageSpeed] 21

"Frank" <francis.moore@gmail.com> wrote in message 
news:b6d40f9c-eb13-4681-b470-8608f699a628@k5g2000yqf.googlegroups.com...
> Hi,
>
> I have a table (#qed_missing_quarters) with the following rows in it:
>
> row_num   quarter_end_date
> 1 2002-09-30 00:00:00.000
> 2 2002-12-31 00:00:00.000
> 3 2003-03-31 00:00:00.000
> 4 2003-06-30 00:00:00.000
> 5 2003-09-30 00:00:00.000
>
> And I have the following code as a test to traverse across the table:
>
> ;WITH qed_missing_values(row_num, quarter_end_date)
> AS
> (
> SELECT row_num AS [row_num], quarter_end_date AS [quarter_end_date]
> FROM #qed_missing_quarters WHERE row_num = 1
>
> UNION ALL
>
> SELECT a.row_num + 1, a.quarter_end_date
> FROM #qed_missing_quarters AS a
> INNER JOIN qed_missing_values AS b
> ON a.row_num = b.row_num
> )
> SELECT * FROM qed_missing_values
>
> However, I always end up with an extra first row, so 6 rows are
> returned (the first one twice).
> And I can't seem to get rid of the extra row.
> Can anyone explain why I get the extra row and how to remove it
> please.
>
> Many thanks,
> Frank.

have you tried removing the following?

> SELECT row_num AS [row_num], quarter_end_date AS [quarter_end_date]
> FROM #qed_missing_quarters WHERE row_num = 1
>
> UNION ALL

if its required then change the UNION ALL to union

Simon 


0
Simon
1/29/2010 2:04:52 PM
Hi Simon,

Thanks for the response.
I probably didn't explain what I was trying to do with the CTE.
I'm trying to recurse across a table row by row and perform a
computation on each row.
If I remove the UNION ALL or change it to a UNION then the CTE loses
it's recursiveness.

Regards,
Frank.
0
Frank
1/29/2010 3:13:55 PM
This happens because you join the first row twice. You have to change the predicate in the recursive query to join on 
the next row:

;WITH qed_missing_values(row_num, quarter_end_date)
AS
(
	SELECT row_num AS [row_num], quarter_end_date AS [quarter_end_date]
	FROM #qed_missing_quarters
	WHERE row_num = 1

	UNION ALL

	SELECT a.row_num, a.quarter_end_date
	FROM #qed_missing_quarters AS a
	INNER JOIN qed_missing_values AS b
	   ON a.row_num = b.row_num + 1
)
SELECT row_num, quarter_end_date
FROM qed_missing_values;

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
1/29/2010 3:27:00 PM
Plamen,

Thanks, that makes sense now that you've pointed it out.
I think that 'a.row_num = b.row_num + 1' was about the only
combination that I didn't try.

Cheers,
Frank.

0
Frank
1/29/2010 4:41:27 PM
Reply:

Similar Artilces:

Send mail on behalf of UserX with UserX's mailbox as extra mailbox
Hi, Running Exchange and Outlook 2000 I need a solution for this scenario. I have some users A and B who have UserX's mailbox configured as a extra mailbox in their Outlook client, so they can respond to mail being recieved by UserX (i.e. a helpdesk account). These same users A and B need to send mail "on behalf of" UserX so our disclaimer software (eXclaimer) adds users A and B signature to email being send by UserX's mail address. In this scenario "Send as" will add UserX's signature, not users A or B's signature. Assigning only "Send on behal...

Excel is adding 2 rows of data source together when charting
I'm trying to create a simple chart that shows trending during the year. I have several rows of data -- 1/2 of them being actual financial performance numbers and the other 1/2 being budget numbers. When I select the data ranges, whether using the Control Key to select all ranges at once, OR by selecting them individually, some of my data ranges are being added together, resulting in the ranges NOT reflecting what the source table numbers are. The ranges appear to be correct. I've tried crafting each range individually with the same result. Why is this happening? Wh...

Extra space between paragraphs when sending
Outlook 2007 adds extra “line feeds” between paragraphs on all outgoing messages. I can’t seem to stop it and it is slowly driving me mad. People getting my e-mails must think I can’t write. I have checked (“don’t add extra space…”) in the style formats. Doesn’t help. The composed messages look fine. They get changed when they are sent. Sometimes happens on incoming mail. I would go back to Office 2003 but too many file extensions are now different. If I can’t fix this I may go to a server based mail system. Why MS made this change is beyond me. It’s in the same category as c...

How do I lock a row in place so that when I sort the worksheet, t.
How do I lock a row in place so that when I sort the worksheet, the row stays at its location, and the other rows sort around it? I believe you can use Data/Filter. Ideally there is already some column in the worksheet that will show all of the records but that row (maybe a helper column would be necessary). Then sort the data and set the filter to show all records. "primenumber" <primenumber@discussions.microsoft.com> wrote in message news:4CD4DE06-48C8-4650-9190-E42BB641A689@microsoft.com... > How do I lock a row in place so that when I sort the worksheet, the r...

Extra field for Items
Does anyone have an addin for extra user defined fields for items? -- Anthony Anthony, We could easily deliver this to you but we do not have anything pre-made. If you are interested please contact us at 1-888-267-RITE or drop us an email at sales@rite.us -- Thank you, Ryan Sakry Program Manager Retail Information Technology Enterprises (RITE) rsakry@rite.us http://www.rite.us 320-230-2282 ext. 4002 (Office) 320-230-1796 (Fax) "Anthony Novielli" <AnthonyNovielli@discussions.microsoft.com> wrote in message news:76CE74F3-5716-4520-9883-6D07C7670143@microsoft.com... > D...

An extra Row Source Field needed
SELECT tblOwnerInfo.OwnerID, IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ' ') & IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & ' ') AS OwnerName FROM tblOwnerInfo ORDER BY IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ' ') & IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & ' '); If tblOwnerInfo.Email has some value in it, add "@" After tblOwnerInfo.OwnerLastName ie Gates @ Bill Thanks f...

Formating Row and Col headers
How can I change the format and color for Excel Row and Col. Headers? Right-click an empty spot on your desktop, then AppearanceTab > Scheme:....choose and apply, (default is Windows Standard, if you want to get back to normal) Vaya con Dios, Chuck, CABGx3 "Bob Spencer" <bob@k2e.com> wrote in message news:e9lBNhcbFHA.2984@TK2MSFTNGP15.phx.gbl... > How can I change the format and color for Excel Row and Col. Headers? > > ...

Comapring rows of a table to multple rows within the same table
Hi, I have the following data which I have been tasked with identifying a primary case for each customer. Unfortunately the data contains duplicate records such as Case 1 and 2. I would be extremely grateful if you could tell me how can I compare all the rows related to Customer 1 and flag a case as the primary case. CaseID, CustomerID, PrimaryStatus, SecondaryStatus, GroupA, GroupB 1,1,New, Unassigned, RU, HK 2,1,New, Unassigned, RU, HK 3,1,In Progress, Normal Review, RU, HK 4,1,New, Unassigned, IN, UK 5,2,New, Assigned, JP, 6,2,In Progress, Special Review, BR, HK 7,2,Co...

using vb for copying and inserting rows and formulae
I earlier posted the followin message: >I have tried to create a macro that will insert a new row >into a user specified position on worksheet 1, and then >the macro then inserts a new row into the same row number >on worksheets 2-6 >So if i have highlighted row 5 on worksheet 1 and then run >the macro, a new row is inserted on worksheet 1 and then >also the same happens, in the same position, on the >following 5 worksheets. > >My problem arises in the fact that i want the formulae i >have in the cells above(or below - it makes no difference) >the...

Extra Extra Pages
I have finished my word document and saved it. When I open it back up to preview before I send it, it shows an extra page between my body of work and reference page. I don't want to send that to my professor. Can anyone help me? Thanks Dennis -- Dennis Jackson See http://sbarnhill.mvps.org/WordFAQs/BlankPage.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Dennis Jackson" <Dennis.Jackson.60bcd11@wordbanter.com> wrote in message news:Dennis.Jackson.60bcd11@wordbanter.com... > ...

Getting a row to autofit
How can you get a row to autofit which a bunch of text in it? It will only go so far for me. Thanks, Jeff Jeff See this google search result for several answers/opinions on this subject. http://shorterlink.com/?3AP86D Gord Dibben Excel MVP On 11 Jan 2004 00:01:37 GMT, jefffinnan@aol.com (JeffFinnan) wrote: >How can you get a row to autofit which a bunch of text in it? It will only go >so far for me. > >Thanks, >Jeff > ...

Excel should provide an easy way to switch a column into a row, n.
A very useful feature for Excel would be the ability to easily switch a column of data into a row. This would allow a column of entries to be used as a row of headers, for example. Excel already allows switching a column into a row in the charting area, but not in the spreadsheet. One can work around this by exporting the column into Word, changing the delimiter between fields from a paragraph mark (normally hidden) to a comma, then re-importing the data into Excel - which will then be as a row. This would also allow Access to import the former column into a row, which could then be...

Hide all rows where one specific cell in that row = 0?
Just trying to help my mum complete a stock take for her company. She' using Excel to calculate the value of the stock with columns for th code, the name, the quantity, rate and then total (rate * quantity) Some of the stock isn't always "in stock" but they still stock it o occasions so it can't be deleted. She wants to be able to hide an columns that have a total value of 0 quickly. What's the best way to g about doing something like this? Macro maybe? I don't want to go all th way through the 2000 somthing items and hide any I see. Any ideas i that makes sense?...

insert a row on ALL sheets
I have a spreadsheet with 12 sheets in it, one for each month of the year. Is there a way to insert a row on all 12 sheets at once or do I have to go into each sheet and insert the row one at a time? In other words, do I have to do it 12 times or is there a way to do it only once and have it affect all 12 sheets? Thanks, BW "bw" <anonymous@discussions.microsoft.com> wrote in message news:0e7301c3a9f0$6e399ae0$a301280a@phx.gbl... > I have a spreadsheet with 12 sheets in it, one for each > month of the year. Is there a way to insert a row on all > 12 sheets at onc...

Autofit row height in only some rows
Is it possible to change this macro to only autofit the row height on rows 254-332 in the workbook? I am using this macro: Option Explicit Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Application.EnableEvents = False Sh.Rows.AutoFit Sh.Rows("1.33").AutoFit Application.EnableEvents = True EndSub Thanks in advance for your help. VBA Code: -------------------- Option Explicit Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Application.EnableEvents = False Sh.Rows("254:332").AutoFit Application.EnableEvents = True ...

Lock Rows
We have a tracking spreadsheet that we use to manage architectural process of producting blueprints for our customer. There are three rows for each site. Sometimes we need to rearrange the sites by site numbers or site name or some other function like filter so we can see how many and which sites are needing something. OF course as we try to manipulate the sites we loose the formatting for each site through a sort or filter command. I am seeking a method or command to lock those three rows for each site together so we do not loose the formatting for each site. Any help with this is greatl...

Trying to find easy way to copy 400K rows of data from Subtotal
I have an excel file that has over 400K rows of information. I had to subtotal on a colums to be able to effectively run a pivot table. But now I have to copy all teh data from the row above the total to get the rest of the information. So say my total is in column a row 4 to get the row above I am doing a=3. Yay, it works, but I have 25 columns that I need to do this for and the only way I can figure how to get the information to populate all the way down is by doing copy/paste and it pastes the formula...However when I try to do a Special Paste it is not, so I have been doing it row ...

Extra line space
I have run into the problem several times, where I have several lines of words in seperate merged cells and it appears to be spaced correctly, but when I print there is an extra line space in the document. Is there some way to correct this? I have the row height so it matches the amount of space needed, but it is adding an extra line on the printed version..... The only fix I know of for this is to manually adjust something to make it look nice--either columnwidth or rowheight. And I don't think fixing it for your specific printer is any assurance that it'll be fixed for the pers...

switching rows with columns..is it possible?
Hallo, I'd like to know if I could swap data from rows and columns: e.g. from: 1 2 3 4 a b c d in: a b c d 1 2 3 4 without doing too much edit or similar... I have a huge raw data file that needs to get sorted. th -- nixis0 ----------------------------------------------------------------------- nixis00's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1475 View this thread: http://www.excelforum.com/showthread.php?threadid=26380 Hi you can copy the range & then paste /special - transpose - i would suggest doing the paste in a new worksheet ...

Can I have default data in new rows?
I have a sheet that the first 5 rows have "administrative" data and some sums of the data below, basically it's a "header" section, the entire worksheet below that, the first 15 columns hold all the data for the worksheet. What I want to do is whenever someone inserts a new row or starts on a blank row at the bottom, to autofill some of the data within the columns, some of them are formulas and some are simple like "Yes/No" drop down lists. Can you do a "onNewRow" event type macro to copy formulas or is there simply a default data fil...

Extra Fonts
I'm trying to open a file with certain embedded fonts that my system does not have. The letters are showing up as blocks..... I cannot find these four fonts: paintstroke elegance Oliveoil Braddon Have you chosen the option to substitute them? -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. "Rob" <Rob@discussions.microsoft.com> wrote in message news:EAFF0684-461D-439E-B341-ED138A5B0407@microsoft.com... > I'm trying to open a file with certain embedded ...

toggle row/column headings
Hi, I need a procedure to toggle on/off row and column headings. Can this be done? I use XL97. Thank you very much, Sub RowColumn() With ActiveWindow .DisplayHeadings = Not .DisplayHeadings End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Billy" <wthomasss@hotmail.com> wrote in message news:1131914304.408168.5840@g47g2000cwa.googlegroups.com... > Hi, > > I need a procedure to toggle on/off row and column headings. Can this > be done? I use XL97. > > Thank you very much, > You could use a tog...

Determining the current row and col
What's the VB syntax for determining the current row and current co prior to a DoubleClick? Thanks k48 -- k48 ----------------------------------------------------------------------- k483's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1079 View this thread: http://www.excelforum.com/showthread.php?threadid=48821 If you mean the cell in which the double click occurred, then one way: Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) With Target MsgBox "Address: " &...

Row highlight based on the 1-31 day of the month
I've got a table with dates displayed in the first column (ascending order). I would like to highlight each row based on the day of the month with one of 5 chosen colours (red, yellow, green, blue and orange). rows with the 1st of the month: red rows with the 2nd of the month: yellow ....... rows with the 6th of the month: red ....... Julian. You would use conditional formatting to display different colors based upon the day value. Your conditional formatting formula would look at the day value of the date. You will have multiple conditions for each row. Condition 1 Formula is: ...

rows and colums
I'm a new user on 2003 excel. How do i change the amount of cells. I need ten down only Hide them lizfox wrote: > I'm a new user on 2003 excel. How do i change the amount of cells. I need > ten down only hi in 2003 the rows are fixed at 65536 and columns at 256. no way to increase or decrease. you can hide rows and columns that you don't want to see but it's an optical illusion. they are still there, just hidden. on the menu bar>format>rows>hide (or unhide) same for columns on the menu bar>format>columns>hide (or unhide) and si...