January 13, 2009 at 3:45 pm
I have a query
SELECT A.SOLD_TO_CUST_ID, C.NAME1, DATEDIFF(DAY, (CONVERT(CHAR(10),B.STATUS_DT,121)),GETDATE()), A.PYMNT_TERMS_CD, A.ORDER_NO,C.SETID,C.CUST_ID
FROM PS_ORD_HEADER A, PS_ORD_HOLD_AUDIT B, PS_CUSTOMER C
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.ORDER_NO = B.ORDER_NO
AND A.SOLD_TO_CUST_ID = C.CUST_ID
AND ( A.PYMNT_TERMS_CD LIKE 'CIT%'
OR A.PYMNT_TERMS_CD LIKE 'BB%')
AND B.REASON_CD = 'CRD_APPR'
AND DATEDIFF(DAY, B.STATUS_DT,GETDATE()) > '28'
AND A.ORDER_STATUS = 'O'
ORDER BY 2, 3
and it runs fine but I get a few items that I should not. In the PS_ORD_HOLD_AUDIT I have customers with B.REASON_CD = 'CRD_APPR' but they also have a B.REASON_CD ='BNK_HOLD' after the B.REASON_CD = 'CRD_APPR' so what I think I want to change my where to say max B.REASON_CD = 'CRD_APPR' but I am not sure how to do use the MAX Function in a where.
January 13, 2009 at 3:49 pm
I am not understanding the specifics of your query, not do I fully understand why you're doing this.
that being said - you probably want to look up using the HAVING clause. This allows you to use aggregate functions in a "where"-like scenario.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 13, 2009 at 3:58 pm
But to use a have you need to be aggregating something and I am not?
is this correct?
January 13, 2009 at 4:00 pm
twdavis (1/13/2009)
But to use a have you need to be aggregating something and I am not?is this correct?
MAX is an aggregate.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 13, 2009 at 6:33 pm
The issue is that I am not sure what you're trying to go for. Could you mock up some data to show us what it is you're trying to achieve? Sounds you may be needing a WINDOWED aggregate function, or something having to do with a ROW_NUMBER(). But without some specifics - it's hard to tell.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 14, 2009 at 6:21 am
I'm not entirely sure what you're shooting for here either.
But, if I'm understanding you correctly, you're wondering how to use MAX in the WHERE clause.
You would do something like this (untested psuedo-code):
SELECT...
FROM TableX AS x
WHERE x.Id = 52
and x.Val = (SELECT MAX(x2.Val)
FROM TableX as X2
WHERE x2.Id = 52)
This is a simplistic example, and quite honestly there are better ways to solve the problem, but I think it's what you're trying for.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 14, 2009 at 6:40 am
SELECT A.SOLD_TO_CUST_ID
, C.NAME1
, DATEDIFF(DAY, ( CONVERT(CHAR(10), B.STATUS_DT, 121) ), GETDATE()) as DtDiff
, A.PYMNT_TERMS_CD
, A.ORDER_NO
, C.SETID
, C.CUST_ID
FROM PS_ORD_HEADER A
inner join PS_ORD_HOLD_AUDIT B
ON A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.ORDER_NO = B.ORDER_NO
AND ( A.PYMNT_TERMS_CD LIKE 'CIT%'
OR A.PYMNT_TERMS_CD LIKE 'BB%'
)
AND A.ORDER_STATUS = 'O'
inner join PS_CUSTOMER C
ON A.SOLD_TO_CUST_ID = C.CUST_ID
WHERE B.REASON_CD = 'CRD_APPR'
AND B.STATUS_DT < dateadd(d,-28, GETDATE()) -- use possible index for B.Status_Dt
and not exists (select * from PS_ORD_HOLD_AUDIT B1 ....
where B1.BUSINESS_UNIT = A.BUSINESS_UNIT
AND B1.ORDER_NO = A.ORDER_NO
and B1.REASON_CD ='BNK_HOLD'
and B1.STATUS_DT > B.STATUS_DT
)
ORDER BY C.NAME1, DtDiff
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 14, 2009 at 7:23 am
I have add a snap shot of the PS_ORD_HOLD_AUDIT for one customer that shows my trouble. My report is to pull all customer that have been CRD_APPR(credit approved) and the order is OPEN if the order goes over 30 days we have to rerun there credit which puts in the CIT_HOLD entry for 1-13-2009. Now this customer should not show up in my report.
My thought is if I could check the last entry for each customer in the PS_ORD_HOLD_AUDIT table and if the last entry = CRD_APPR then display this customers information.
I my be all wrong. Thanks Folks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply