SQL Server Forums
Profile | Register | 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
 New Topic  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  
 New 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.36 seconds. Powered By: Snitz Forums 2000