July 13, 2010 at 2:32 pm
Hi Guys,
I need some help with the SQL. I need to check for the IsValid Value and then derive the new column value.. this should be done per PID,PHId. So, I need to check iSValid Value and if it's "0" then "0" Else Increment by 1 starting with 1 for the whole set until the next IsValue = 0.. Any suggestions Please
See below for sample data
Thanks
July 13, 2010 at 6:45 pm
Based on your requirement:
...check iSValid Value and if it's "0" then "0" Else Increment by 1 starting with 1 for the whole set until the next IsValue = 0
I think you will likely have to use a Cursor to iterate row by row. This way you could increment a variable etc. Admittedly this is an ugly solution.
July 14, 2010 at 7:27 am
I tried to take the same approach using the basic SQL not cursor though, but i am unable to go any further with the code.. Can you help me out to write the sql
July 14, 2010 at 11:05 am
any suggestions/help please
July 14, 2010 at 1:16 pm
Danny,
Jeff Moden wrote a great article ... Solving the "Running Total" & "Ordinal Rank" Problems[/url] here on this site. The method he describes will work for what you're wanting to do, but it is a little involved so make sure you read the whole thing if that's the way you're going to go. Also, if you read it and you have any questions, just post updates on this thread since you already have your nice data samples here.
(I may have time to do a coded example for you this afternoon but no promises.)
You can use an explicit cursor, but that's really not preferred as performance will be poor.
July 14, 2010 at 1:55 pm
If you're going to do this, read the article by Jeff Moden in the link above.
First, I changed the insert statement a little because there were a few duplicate Id's.
Create Table #TEST
(
Id INT,
PHId INT,
PId INT,
Rate Float,
IsValid BIT,
PDate DateTime,
)
INSERT INTO #TEST
SELECT 164128,2,1195894,60,1,'2010-07-02 09:20:00.000' UNION ALL
SELECT 164129,2,1195894,80,1,'2010-07-02 09:25:00.000' UNION ALL
SELECT 164130,2,1195894,10,1,'2010-07-02 09:30:00.000' UNION ALL
SELECT 164131,2,1195894,60,1,'2010-07-02 09:35:00.000' UNION ALL
SELECT 164132,2,1195894,0,0,'2010-07-02 09:40:00.000' UNION ALL
SELECT 164133,2,1195894,0,0,'2010-07-02 09:45:00.000' UNION ALL
SELECT 164134,2,1195894,0,0,'2010-07-02 09:50:00.000' UNION ALL
SELECT 164135,2,1195894,0,0,'2010-07-02 09:55:00.000' UNION ALL
SELECT 164136,2,1195894,0,0,'2010-07-02 10:00:00.000' UNION ALL
SELECT 164137,2,1195894,0,0,'2010-07-02 10:05:00.000' UNION ALL
SELECT 164138,2,1195894,0,0,'2010-07-02 10:10:00.000' UNION ALL
SELECT 164139,2,1195894,0,0,'2010-07-02 10:15:00.000' UNION ALL
SELECT 164140,2,1195894,0,0,'2010-07-02 10:20:00.000' UNION ALL
SELECT 164141,2,1195894,0,0,'2010-07-02 10:25:00.000' UNION ALL
SELECT 164142,2,1195894,0,0,'2010-07-02 10:30:00.000' UNION ALL
SELECT 164143,2,1195894,20,1,'2010-07-02 10:35:00.000' UNION ALL
SELECT 164144,2,1195894,80,1,'2010-07-02 10:40:00.000' UNION ALL
SELECT 164145,2,1195894,0,1,'2010-07-02 10:45:00.000' UNION ALL
SELECT 164146,2,1195894,0,1,'2010-07-02 10:50:00.000' UNION ALL
SELECT 164147,1,1195894,0,0,'2010-07-02 10:05:00.000' UNION ALL
SELECT 164148,1,1195894,0,0,'2010-07-02 10:10:00.000' UNION ALL
SELECT 164149,1,1195894,0,0,'2010-07-02 10:15:00.000' UNION ALL
SELECT 164150,1,1195894,0,0,'2010-07-02 10:20:00.000' UNION ALL
SELECT 164151,1,1195894,0,0,'2010-07-02 10:25:00.000' UNION ALL
SELECT 164152,1,1195894,0,0,'2010-07-02 10:30:00.000' UNION ALL
SELECT 164153,1,1195894,20,1,'2010-07-02 10:35:00.000' UNION ALL
SELECT 164154,1,1195894,80,1,'2010-07-02 10:40:00.000' UNION ALL
SELECT 164155,1,1195894,0,1,'2010-07-02 10:45:00.000' UNION ALL
SELECT 164156,1,1195894,0,1,'2010-07-02 10:50:00.000' UNION ALL
SELECT 164157,1,1195890,0,1,'2010-07-02 10:05:00.000' UNION ALL
SELECT 164158,1,1195890,0,1,'2010-07-02 10:10:00.000' UNION ALL
SELECT 164159,1,1195890,0,1,'2010-07-02 10:15:00.000' UNION ALL
SELECT 164160,1,1195890,0,0,'2010-07-02 10:20:00.000' UNION ALL
SELECT 164161,1,1195890,0,0,'2010-07-02 10:25:00.000' UNION ALL
SELECT 164162,1,1195890,0,0,'2010-07-02 10:30:00.000' UNION ALL
SELECT 164163,1,1195890,20,1,'2010-07-02 10:35:00.000' UNION ALL
SELECT 164164,1,1195890,0,1,'2010-07-02 10:40:00.000' UNION ALL
SELECT 164165,1,1195890,0,0,'2010-07-02 10:45:00.000' UNION ALL
SELECT 164166,1,1195890,20,1,'2010-07-02 10:50:00.000' UNION ALL
SELECT 164167,1,1195890,20,1,'2010-07-02 10:45:00.000' UNION ALL
SELECT 164168,1,1195890,20,1,'2010-07-02 10:45:00.000'
And this is the query:
DECLARE @n_sec INT = 1
CREATE TABLE #TEST_FINAL
(RN INT PRIMARY KEY CLUSTERED
,Id INT
,PHId INT
,PId INT
,Rate Float
,IsValid BIT
,SectionId INT
,PDate DateTime
,SecFlag BIT)
;WITH cteBase AS
(
SELECT ROW_NUMBER() OVER (ORDER BY PId, PHId, Id) RN,
Id, PHId, PId, Rate, IsValid, PDate
FROM #TEST t
),
cteFlagging AS
(
SELECT c.*, secFlag = CASE WHEN c.IsValid <> ISNULL(cp.IsValid,0) THEN 1*c.IsValid END
FROM cteBase c
LEFT JOIN cteBase cp
ON cp.PId = c.PId
AND cp.PHId = c.PHId
AND cp.RN = c.RN - 1
),
cteSectionNumber AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY PId, PHId, SecFlag ORDER BY RN) as SectionID
FROM cteFlagging
)
INSERT INTO #TEST_FINAL
SELECT RN,
Id,
PHId,
PId,
Rate,
IsValid,
SectionID,
PDate,
SecFlag
FROM cteSectionNumber
UPDATE #TEST_FINAL
SET @n_sec = SectionID = CASE
WHEN IsValid = 1 AND SecFlag = 1 THEN SectionID
WHEN IsValid = 1 THEN @n_sec
ELSE 0 END
OPTION (MAXDOP 1)
SELECT Id, PHId, PId, Rate, IsValid, SectionId, PDate
FROM #TEST_FINAL
ORDER BY Id
July 16, 2010 at 5:12 pm
Thanks a Lot ,Bt
I used the same approach that you have suggested but in a little different fashion according to my Requirement.. I was working on other issues so could not see/reply back
Thanks again
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply