Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Max value
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fchachar
Starting Member

15 Posts

Posted - 11/20/2012 :  13:41:20  Show Profile  Reply with Quote
Is there a way I could tweak my query below to get only results when Table1.description1 has the last value as '%Transfer%'. I can use MAX and use where clause to filter the results but again that wont be accurate.

With the Query below I get all results when the following condition is true.

select distinct dbo.Table1.ustatus,Table1.description1, Table2.ustatus,Table2.vc_Balance,dbo.Table1.Posting_Date as [Posting Date] from
dbo.Table3 
INNER JOIN dbo.Table2 ON dbo.Table3.vc_ID=dbo.Table2.vc_ID
INNER JOIN dbo.Table1 on dbo.Table3.svc_ID=dbo.Table1.svc_ID
INNER JOIN dbo.Table4 on dbo.Table2.person_ID=dbo.Table4.person_ID
where  dbo.Table1.ustatus IN ('1', '2')
AND Table1.description1 LIKE '%Transfer%'
AND  Table2.ustatus = '1'
AND Table2.vc_Balance > 0.00
AND Table1.Posting_Date  >= '10/01/2012'
ORDER BY [Posting Date]


Thanks.

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/20/2012 :  14:13:05  Show Profile  Reply with Quote
I may not have understood your requirement correctly but would this work for you?
;WITH cte AS
(
	SELECT DISTINCT dbo.Table1.ustatus,
		   Table1.description1,
		   Table2.ustatus,
		   Table2.vc_Balance,
		   dbo.Table1.Posting_Date AS [Posting Date]
	FROM   dbo.Table3
		   INNER JOIN dbo.Table2
				ON  dbo.Table3.vc_ID = dbo.Table2.vc_ID
		   INNER JOIN dbo.Table1
				ON  dbo.Table3.svc_ID = dbo.Table1.svc_ID
		   INNER JOIN dbo.Table4
				ON  dbo.Table2.person_ID = dbo.Table4.person_ID
	WHERE  dbo.Table1.ustatus IN ('1', '2')
		   AND Table1.description1 LIKE '%Transfer%'
		   AND Table2.ustatus = '1'
		   AND Table2.vc_Balance > 0.00
		   AND Table1.Posting_Date >= '10/01/2012'
)
SELECT c1.* FROM cte c1
WHERE (SELECT TOP 1 c2.description1 FROM cte c2 ORDER BY c2.[Posting Date] DESC) LIKE '%Transfer%'
ORDER BY c1.[Posting Date];
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/20/2012 :  16:15:06  Show Profile  Reply with Quote
Here is my guess too.. ;)
SELECT *
FROM
    (
        SELECT 
            Table1.ustatus,
            Table1.description1, 
            Table2.ustatus,
            Table2.vc_Balance,
            Table1.Posting_Date as [Posting Date] ,
            ROW_NUMBER()  OVER 
            (
                PARTITION BY 
                    Table1.ustatus,
                    Table1.description1, 
                    Table2.ustatus,
                    Table2.vc_Balance
                ORDER BY
                    dbo.Table1.Posting_Date DESC
            ) AS RowNum
        FROM
            dbo.Table3 
        INNER JOIN 
            dbo.Table2 
            ON Table3.vc_ID = Table2.vc_ID
        INNER JOIN 
            dbo.Table1 
            ON Table3.svc_ID = Table1.svc_ID
        INNER JOIN dbo.Table4 
            ON dbo.Table2.person_ID=dbo.Table4.person_ID
        WHERE  
            Table1.ustatus IN ('1', '2')
            AND  Table2.ustatus = '1'
            AND Table2.vc_Balance > $0.00
            AND Table1.Posting_Date  >= '2012-10-01'
    ) AS T
WHERE
    RowNum = 1
    AND description1 LIKE '%Transfer%'
ORDER BY 
    [Posting Date]
Go to Top of Page

fchachar
Starting Member

15 Posts

Posted - 11/21/2012 :  08:21:01  Show Profile  Reply with Quote
Thanks alot guys I can try this on Monday and let you know how it works.
Go to Top of Page

fchachar
Starting Member

15 Posts

Posted - 11/26/2012 :  10:33:53  Show Profile  Reply with Quote
Thanks Sunita and Lamprey, got my results accurately with ROW_NUMBER suggestion. Great stuff!!...
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000