Solved

Ranking query not needs to exclude Zero field values

Posted on 2015-01-31
2
121 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
2 Comments
 
LVL 33

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now