InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 17 Jun 2013 23:41:43 GMT20http://www.sqlservercentral.com/Forums/Topic1038524-1292-1.aspx[quote][b]stevenplee (12/23/2010)[/b][hr]Thanks, For your response. What does the ":with cte AS" statement do?[/quote]A whole lot. Take a look at "WITH common_table_expression" in Books Online... there's just too much info to post here.Thu, 23 Dec 2010 18:43:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1038524-1292-1.aspxThanks, For your response. What does the ":with cte AS" statement do?Thanks I'll give this a tryThu, 23 Dec 2010 17:41:43 GMTstevenpleehttp://www.sqlservercentral.com/Forums/Topic1038524-1292-1.aspxSomething like this?[code="sql"]DECLARE @tbl TABLE( DATE DATETIME, OrderNmbr INT, Transtype VARCHAR(10), Amount INT, InvoiceinPer CHAR(1))--/*INSERT INTO @tblSELECT '12/1/2010', 12345 ,'Inv', 1000 ,'N' UNION ALLSELECT '12/3/2010', 12345 ,'Credit', -1000 ,'N' UNION ALLSELECT '12/3/2010', 12345 ,'Rebill', 2000 ,'N'--*//*insert into @tblselect '12/1/2010', 12345 ,'Inv', 1000 ,'N' union allselect '12/1/2010', 12345 ,'Credit', -1000 ,'N' union allselect '12/1/2010', 12345 ,'Rebill', 2000 ,'N'*/;WITH cte AS( SELECT TOP 1 DATE FROM @tbl t ORDER BY DATE)UPDATE @tbl SET InvoiceinPer='Y'FROM @tbl tINNER JOIN cte ON t.date = cte.dateSELECT *FROM @tbl[/code]Thu, 23 Dec 2010 10:25:41 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1038524-1292-1.aspxI need a little help figuring out this logic. I need to write a query that will set the Field InvoiceinPer value to Y if the order# is the same and the date is the same regardless to the transType. (see example1)Example2 Will set the invoiceinper value to Y only if the transtype is anything other than Credit or rebill and the dates are different. Suggestion would be appreciated.Ex1Date Order# Transtype Amount InvoiceinPer12/1/2010 12345 Inv 1000 Y12/1/2010 12345 Credit -1000 Y12/1/2010 12345 Rebill 2000 YEx2 Date Order# Transtype Amount InvoiceinPer12/1/2010 12345 Inv 1000 Y12/3/2010 12345 Credit -1000 N12/3/2010 12345 Rebill 2000 NWed, 22 Dec 2010 15:07:59 GMTstevenplee