Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

get previous value Expand / Collapse
Author
Message
Posted Friday, September 13, 2013 1:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 10:09 AM
Points: 189, Visits: 333
Hi,

I am having the below records

Date value
01-01-2013 10
02-01-2013 20
03-01-2013 0
04-01-2013 0
05-01-2013 30
06-01-2013 40
07-01-2013 0
08-01-2013 50

I want to display the value 20 for the dates 3rd and 4th and value 40 for the date 07th.
In general, for any date if the value is 0 then get the privoud non zero value.

Can anyone pls help me to get this one

Thanks
Naveen
Post #1494482
Posted Friday, September 13, 2013 2:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:38 AM
Points: 6,861, Visits: 14,160
;WITH SampleData (Date, value) AS (
SELECT '01-01-2013', 10 UNION ALL
SELECT '02-01-2013', 20 UNION ALL
SELECT '03-01-2013', 0 UNION ALL
SELECT '04-01-2013', 0 UNION ALL
SELECT '05-01-2013', 30 UNION ALL
SELECT '06-01-2013', 40 UNION ALL
SELECT '07-01-2013', 0 UNION ALL
SELECT '08-01-2013', 50
)
SELECT s.[Date], value = ISNULL(NULLIF(s.value,0), x.value)
FROM SampleData s
CROSS APPLY (
SELECT TOP 1 si.value
FROM SampleData si
WHERE si.[Date] <= s.[Date]
AND si.value > 0
ORDER BY si.[Date] DESC
) x
ORDER BY s.[Date]



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1494495
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse