Help writing query logic

Question

Post reply

Help writing query logic

stevenplee

SSCommitted

Points: 1626
More actions
December 22, 2010 at 3:08 pm

#388490

I 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.
Ex1
Date Order# TranstypeAmountInvoiceinPer
12/1/201012345Inv1000Y
12/1/201012345Credit-1000Y
12/1/201012345Rebill2000Y
Ex2
Date Order# TranstypeAmountInvoiceinPer
12/1/201012345Inv1000Y
12/3/201012345Credit-1000N
12/3/201012345Rebill2000N

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Something like this?

DECLARE @tbl TABLE

(

DATE DATETIME, OrderNmbr INT, Transtype VARCHAR(10), Amount INT, InvoiceinPer CHAR(1)

)

--/*

INSERT INTO @tbl

SELECT '12/1/2010', 12345 ,'Inv', 1000 ,'N' UNION ALL

SELECT '12/3/2010', 12345 ,'Credit', -1000 ,'N' UNION ALL

SELECT '12/3/2010', 12345 ,'Rebill', 2000 ,'N'

--*/

/*

insert into @tbl

select '12/1/2010', 12345 ,'Inv', 1000 ,'N' union all

select '12/1/2010', 12345 ,'Credit', -1000 ,'N' union all

select '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 t

INNER JOIN cte ON t.date = cte.date

SELECT *

FROM @tbl

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

stevenplee SSCommitted Points: 1626 More actions · Answer 2

Thanks, For your response. What does the ":with cte AS" statement do?

Thanks I'll give this a try

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

stevenplee (12/23/2010)
Thanks, For your response. What does the ":with cte AS" statement do?

A whole lot. Take a look at "WITH common_table_expression" in Books Online... there's just too much info to post here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)