Solved

Ranking query not needs to exclude Zero field values

Posted on 2015-01-31
2
128 Views
Last Modified: 2015-01-31
The other day I was given the solution of how to find the last payment date and amount in a table of receipts:

SELECT     [VisitID], CONVERT(numeric(14, 2), - [Amount]) AS LPA, Type, CONVERT(char, [ReceiptDateTime], 101) AS LPD
FROM         (SELECT     VisitID, Amount, Type, ReceiptDateTime, ROW_NUMBER() OVER (Partition BY VisitID
                       ORDER BY ReceiptDateTime DESC) AS rn
FROM         [livedb].[dbo].[BarCollectionTransactions]) AS derirved
WHERE     Amount <> 0 AND Type = 'R' AND VisitID IS NOT NULL

Open in new window


This SQL codes works pretty well.  However, the data reported for the following VisitID came over as Zero:

VisitID                     Amount      ReceiptDateTime                      Type
A10000141660      0.00      2010-05-11 00:00:00.000      R
A10000141660      -237.50      2010-04-23 00:00:00.000      R

As you can see the latest date had a Zero amount.  I need the ouputted amount to be the (237.50)

How do I modify the code to give me the correct data?

thanks

Glen
0
Comment
Question by:GPSPOW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 40581730
try this:

SELECT [VisitID]
      , CONVERT(numeric(14, 2), - [Amount]) AS LPA
      , Type
      , CONVERT(char, [ReceiptDateTime], 101) AS LPD
FROM (SELECT VisitID
               , Amount
               , Type
               , ReceiptDateTime
               , ROW_NUMBER() OVER (Partition BY VisitID ORDER BY ReceiptDateTime DESC) AS rn
         FROM [livedb].[dbo].[BarCollectionTransactions]
         WHERE Amount <> 0 AND Type = 'R' AND VisitID IS NOT NULL ) AS derirved
0
 

Author Closing Comment

by:GPSPOW
ID: 40581757
Thanks

Worked great.

Glen
0

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question