loveindia Posted July 28, 2015 Report Share Posted July 28, 2015 ..nuvvu nijam gane devudivi bhayyo... LoveIndia created a table and inserted data for get solution for you.. not that he is asking you to Insert. idisey man, devudini ala anaku.. lempalesuko or else review video teestaa nee meeda.... Link to comment Share on other sites More sharing options...
4Vikram Posted July 28, 2015 Report Share Posted July 28, 2015 DECLARE @Table TABLE (minv_code INT, alert_msg VARCHAR(10), alert_time DATETIME ) INSERT INTO @Table VALUES (873939, 'Reverse', '7/24/2015 3:31:18'), (873939, 'Tamper', '7/24/2015 3:30:00') ,(873939, 'Meter', '7/24/2015 3:31:22') ;WITH CTE AS ( SELECT * , CASE WHEN alert_msg = 'Meter' THEN 1 WHEN alert_msg = 'Tamper' THEN 2 WHEN alert_msg = 'Reverse' THEN 3 END AS id FROM @table ) , CTE2 AS ( SELECT *, ROW_NUMBER() OVER (Partition by minv_code ORDER BY id) AS rnum FROM CTE ) SELECT minv_code, alert_msg, alert_time FROM CTE2 WHERE Rnum = 1 sHa_clap4 sHa_clap4 sHa_clap4 Link to comment Share on other sites More sharing options...
loveindia Posted July 28, 2015 Report Share Posted July 28, 2015 sHa_clap4 sHa_clap4 sHa_clap4 Link to comment Share on other sites More sharing options...
4Vikram Posted July 28, 2015 Report Share Posted July 28, 2015 idisey man, devudini ala anaku.. lempalesuko or else review video teestaa nee meeda.... vuuuuu we want video with hair sardhings Link to comment Share on other sites More sharing options...
loveindia Posted July 28, 2015 Report Share Posted July 28, 2015 vuuuuu we want video with hair sardhings inka emanna reqs untey cheppu man... Link to comment Share on other sites More sharing options...
4Vikram Posted July 28, 2015 Report Share Posted July 28, 2015 inka emanna reqs untey cheppu man... ade main requirement man anthaku minchi inka em avasaram ledu man Link to comment Share on other sites More sharing options...
nenu_devudni Posted July 28, 2015 Author Report Share Posted July 28, 2015 DECLARE @Table TABLE (minv_code INT, alert_msg VARCHAR(10), alert_time DATETIME ) INSERT INTO @Table VALUES (873939, 'Reverse', '7/24/2015 3:31:18'), (873939, 'Tamper', '7/24/2015 3:30:00') ,(873939, 'Meter', '7/24/2015 3:31:22') ;WITH CTE AS ( SELECT * , CASE WHEN alert_msg = 'Meter' THEN 1 WHEN alert_msg = 'Tamper' THEN 2 WHEN alert_msg = 'Reverse' THEN 3 END AS id FROM @table ) , CTE2 AS ( SELECT *, ROW_NUMBER() OVER (Partition by minv_code ORDER BY id) AS rnum FROM CTE ) SELECT minv_code, alert_msg, alert_time FROM CTE2 WHERE Rnum = 1 ee range sql eda sudaledu bhayya .. already ma appice lo oka fresher amayik helping .. bye1 edi fafa query .. nu chepina sqlelage na bhayya rayalsindi sSc_hidingsofa SELECT *,CASE WHEN uwtsmee_alert_type = 'Meter Removal' THEN 1 WHEN uwtsmee_alert_type = 'Tamper Alert' THEN 2 WHEN uwtsmee_alert_type = 'Reverse Energy' THEN 3 END AS id FROM uwtsmee , CTE2 AS ( SELECT *, ROW_NUMBER() OVER (Partition by uwtsmee_minv_code ORDER BY id) AS rnum FROM CTE) SELECT uwtsmee_minv_code, uwtsmee_alert_type, uwtsmee_alert_time FROM CTE2 WHERE rnum = 1 Link to comment Share on other sites More sharing options...
nenu_devudni Posted July 28, 2015 Author Report Share Posted July 28, 2015 ..nuvvu nijam gane devudivi bhayyo... LoveIndia created a table and inserted data for get solution for you.. not that he is asking you to Insert. saduvu rani vallu ante enduku bhayya antha chulakana sSa_j@il Link to comment Share on other sites More sharing options...
rikki Posted July 28, 2015 Report Share Posted July 28, 2015 SELECT TOP 1 alert_type FROM uwtsmee WHERE minv_code = 866642 ORDER BY minv_code , case when alert_type in ('Meter Removal') then 0 when alert_type in ('Tamper Alert') then 1 when alert_type in ('Reverse Energy') then 2 else null end Link to comment Share on other sites More sharing options...
loveindia Posted July 28, 2015 Report Share Posted July 28, 2015 SELECT TOP 1 alert_type FROM uwtsmee WHERE minv_code = 866642 ORDER BY minv_code , case when alert_type in ('Meter Removal') then 0 when alert_type in ('Tamper Alert') then 1 when alert_type in ('Reverse Energy') then 2 else null end What will happen if he doesn't pass minv_code man... you have to take all scenarios into account man... Link to comment Share on other sites More sharing options...
loveindia Posted July 28, 2015 Report Share Posted July 28, 2015 use this man.... ???? lo minv_code ki ye coloumn use chestunnavoo adi pettu man.... if there are any syntax issues please correct man... ;WITH CTE AS ( SELECT uwtsmee_prem_code, uwtsmee_model, uwtsmee_asvc_code, uwtsmee_alert_time, uwtsmee_alert_type, uwtsmee_alert_msg, CASE WHEN uwtsmee_alert_type = 'Meter Removal' Then 1 WHEN uwtsmee_alert_type = 'Tamper Alert' Then 2 WHEN uwtsmee_alert_type = 'Reverse Energy' Then 3 END alert_type from uwtsmee where uwtsmee_work_ind is null and uwtsmee_prem_code = '1018385' ) , CTE2 AS( SELECT *, ROW_NUMBER() OVER(PARTITION BY ????? ORDER BY alert_type) AS Rnum FROM CTE ) SELECT uwtsmee_prem_code, uwtsmee_model, uwtsmee_asvc_code, uwtsmee_alert_time, uwtsmee_alert_type, uwtsmee_alert_msg FROM CTE2 WHERE rnum = 1 Link to comment Share on other sites More sharing options...
loveindia Posted July 28, 2015 Report Share Posted July 28, 2015 devudu ammai number pm pls... I will help from my side man... Link to comment Share on other sites More sharing options...
nenu_devudni Posted July 28, 2015 Author Report Share Posted July 28, 2015 devudu ammai number pm pls... I will help from my side man... Link to comment Share on other sites More sharing options...
nenu_devudni Posted July 28, 2015 Author Report Share Posted July 28, 2015 use this man.... ???? lo minv_code ki ye coloumn use chestunnavoo adi pettu man.... if there are any syntax issues please correct man... ;WITH CTE AS ( SELECT uwtsmee_prem_code, uwtsmee_model, uwtsmee_asvc_code, uwtsmee_alert_time, uwtsmee_alert_type, uwtsmee_alert_msg, CASE WHEN uwtsmee_alert_type = 'Meter Removal' Then 1 WHEN uwtsmee_alert_type = 'Tamper Alert' Then 2 WHEN uwtsmee_alert_type = 'Reverse Energy' Then 3 END alert_type from uwtsmee where uwtsmee_work_ind is null and uwtsmee_prem_code = '1018385' ) , CTE2 AS( SELECT *, ROW_NUMBER() OVER(PARTITION BY ????? ORDER BY alert_type) AS Rnum FROM CTE ) SELECT uwtsmee_prem_code, uwtsmee_model, uwtsmee_asvc_code, uwtsmee_alert_time, uwtsmee_alert_type, uwtsmee_alert_msg FROM CTE2 WHERE rnum = 1 bhayya syntax chusta le kani .. oka doubt .. ee script lo edo kotha basha undi ..ee basha ni oracle forms6i accept chesatada ... casue ee script ni form lo cursor laga define chesi records fetch cheyali Link to comment Share on other sites More sharing options...
loveindia Posted July 28, 2015 Report Share Posted July 28, 2015 ee help kuda kaavalantey chestaa man... Link to comment Share on other sites More sharing options...
Recommended Posts