Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help writing query logic Expand / Collapse
Author
Message
Posted Wednesday, December 22, 2010 3:07 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 71, Visits: 280
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# Transtype Amount InvoiceinPer
12/1/2010 12345 Inv 1000 Y
12/1/2010 12345 Credit -1000 Y
12/1/2010 12345 Rebill 2000 Y

Ex2
Date Order# Transtype Amount InvoiceinPer
12/1/2010 12345 Inv 1000 Y
12/3/2010 12345 Credit -1000 N
12/3/2010 12345 Rebill 2000 N
Post #1038524
Posted Thursday, December 23, 2010 10:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:11 PM
Points: 7,019, Visits: 12,909
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1038885
Posted Thursday, December 23, 2010 5:41 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 71, Visits: 280
Thanks, For your response. What does the ":with cte AS" statement do?

Thanks I'll give this a try
Post #1038984
Posted Thursday, December 23, 2010 6:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 36,714, Visits: 31,164
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1038995
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse