Help Required - 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

General SQL Server Forums

New to SQL Server Programming

Help Required

Author

Topic

nirene
Yak Posting Veteran

98 Posts

Posted - 2009-03-03 : 05:37:18

Cust Id # DateOf Sale # SalFlag
INDGA0000096 # 2009-01-30 # NULL
INDGA0000096 # 2009-01-31 # NULL
INDGA0000096 # 2009-02-01 # L
INDGA0000096 # 2009-02-02 # NULL
INDGA0000096 # 2009-02-03 # NULL
INDGA0000097 # 2009-01-30 # NULL
INDGA0000097 # 2009-01-31 # L
INDGA0000097 # 2009-02-01 # L
INDGA0000097 # 2009-02-02 # L
INDGA0000097 # 2009-02-03 # NULL

I want to find the sales flag which is marked as L and find the previous
and next Date of when sales was not made

Result
Cust Id # DOS1 # DOS2
INDGA0000096 # 2009-01-31 # 2009-02-02
INDGA0000097 # 2009-01-30 # 2009-02-03

Thanking you in anticipation

Nirene

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 05:43:16

Are you using SQL Server 2005?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 05:48:48

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
CustID CHAR(12),
DateOfSale DATETIME,
SalFlag CHAR(1)
)

INSERT @Sample
SELECT 'INDGA0000096', '2009-01-30', NULL UNION ALL
SELECT 'INDGA0000096', '2009-01-31', NULL UNION ALL
SELECT 'INDGA0000096', '2009-02-01', 'L' UNION ALL
SELECT 'INDGA0000096', '2009-02-02', NULL UNION ALL
SELECT 'INDGA0000096', '2009-02-03', NULL UNION ALL
SELECT 'INDGA0000097', '2009-01-30', NULL UNION ALL
SELECT 'INDGA0000097', '2009-01-31', 'L' UNION ALL
SELECT 'INDGA0000097', '2009-02-01', 'L' UNION ALL
SELECT 'INDGA0000097', '2009-02-02', 'L' UNION ALL
SELECT 'INDGA0000097', '2009-02-03', NULL

-- Do the work
SELECT DISTINCT s.CustID,
(SELECT MAX(p.DateOfSale) FROM @Sample AS p WHERE p.CustID = s.CustID AND p.DateOfSale < s.DateOfSale AND p.SalFlag IS NULL) AS DateOfSale1,
(SELECT MIN(n.DateOfSale) FROM @Sample AS n WHERE n.CustID = s.CustID AND n.DateOfSale > s.DateOfSale AND n.SalFlag IS NULL) AS DateOfSale2
FROM @Sample AS s
WHERE s.SalFlag = 'L'
ORDER BY s.CustID[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources