Max value - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Max value

Author

Topic

fchachar
Starting Member

15 Posts

Posted - 2012-11-20 : 13:41:20

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-20 : 14:13:05

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];

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-20 : 16:15:06

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]

fchachar
Starting Member

15 Posts

Posted - 2012-11-21 : 08:21:01

Thanks alot guys I can try this on Monday and let you know how it works.

fchachar
Starting Member

15 Posts

Posted - 2012-11-26 : 10:33:53

Thanks Sunita and Lamprey, got my results accurately with ROW_NUMBER suggestion. Great stuff!!...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources