#676213 Author: Kiran Member Level: Silver Member Rank: 794 Date: 19/Jun/2012 Rating:  Points: 1 |
I have this very big select query which i am not able to select based on input parameters i am passing inside the procedure.
SELECT T.Point_location,T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time, SUM(E_Reading) ASE_Reading , SUM(G_reading) AS G_Reading
FROM (
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, C.Act_value AS E_Reading, 0 AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE B.point_location = C.point_Location
AND A.equip_id = B.equip_id
AND SW.sw_id=S.sw_id
And C.log_date>= @Startdate
And C.log_date < @EndDate
AND CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) = CONVERT(VARCHAR(20), s.start_hrs)+ ':' + CONVERT(VARCHAR(20),s.end_hrs)
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id = 'RAW_POW'
UNION
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, 0 AS E_Reading, C.Act_value AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE B.point_location = C.point_Location
AND A.equip_id = B.equip_id
AND SW.sw_id=S.sw_id
And C.log_date>= @Startdate
And C.log_date < @EndDate
AND CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) = CONVERT(VARCHAR(20), s.start_hrs)+ ':' + CONVERT(VARCHAR(20),s.end_hrs)
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id = 'GAS') T
GROUP BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time
ORDER BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time
So i have planned to pass it inside a temporary table and then select it based on the input parameters.
Please guide me with the same.
|
| #676330 Author: Kiran Member Level: Silver Member Rank: 794 Date: 19/Jun/2012 Rating: Points: 1 |
I tried with while loop but in vain.
alter PROCEDURE [dbo].[Get_op_meter_data]
@Point_location VARCHAR(50),
@sw_name VARCHAR(30),
@p_equip_id CHAR(10),
@equip_id CHAR(10),
@Startdate datetime,
@EndDate datetime,
@source_type_id char(10)
As
Declare @log_date datetime, @log_time datetime, @ASE_Reading numeric,@G_Reading numeric
WHILE (SELECT T.Point_location,T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time, SUM(E_Reading) ASE_Reading , SUM(G_reading) AS G_Reading
FROM (
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, C.Act_value AS E_Reading, 0 AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE A.point_location=B.point_location
AND SW.sw_id= S.sw_id
AND A.p_equip_id=B.equip_id
AND A.equip_id = B.equip_id
And C.log_date>= @StartDate
And C.log_date < @EndDate
AND CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) = CONVERT(VARCHAR(20), s.start_hrs)+ ':' + CONVERT(VARCHAR(20),s.end_hrs)
AND B.point_location = C.point_Location
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id = 'RAW_POW'
UNION
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, 0 AS E_Reading, C.Act_value AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE A.point_location=B.point_location
AND SW.sw_id= S.sw_id
AND A.p_equip_id=B.equip_id
AND A.equip_id = B.equip_id
And C.log_date>= @StartDate
And C.log_date < @EndDate
AND CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) = CONVERT(VARCHAR(20), s.start_hrs)+ ':' + CONVERT(VARCHAR(20),s.end_hrs)
AND B.point_location = C.point_Location
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id = 'GAS') T
GROUP BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time
ORDER BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time)< @EndDate
BEGIN
SELECT T.Point_location,T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time, SUM(E_Reading) ASE_Reading , SUM(G_reading) AS G_Reading from
(SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, C.Act_value AS E_Reading, 0 AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE A.point_location=B.point_location
AND SW.sw_id= S.sw_id
AND A.p_equip_id=B.equip_id
AND A.equip_id = B.equip_id
And C.log_date>= @StartDate
And C.log_date < @EndDate
AND CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) = CONVERT(VARCHAR(20), s.start_hrs)+ ':' + CONVERT(VARCHAR(20),s.end_hrs)
AND B.point_location = C.point_Location
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id = 'RAW_POW'
UNION
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, 0 AS E_Reading, C.Act_value AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE A.point_location=B.point_location
AND SW.sw_id= S.sw_id
AND A.p_equip_id=B.equip_id
AND A.equip_id = B.equip_id
And C.log_date>= @StartDate
And C.log_date < @EndDate
AND CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) = CONVERT(VARCHAR(20), s.start_hrs)+ ':' + CONVERT(VARCHAR(20),s.end_hrs)
AND B.point_location = C.point_Location
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id = 'GAS') T
GROUP BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time
ORDER BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time
IF ( SELECT T.Point_location,T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time, SUM(E_Reading) ASE_Reading , SUM(G_reading) AS G_Reading from
(SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, C.Act_value AS E_Reading, 0 AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE A.point_location=B.point_location
AND SW.sw_id= S.sw_id
AND A.p_equip_id=B.equip_id
AND A.equip_id = B.equip_id
And C.log_date>= @StartDate
And C.log_date < @EndDate
AND CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) = CONVERT(VARCHAR(20), s.start_hrs)+ ':' + CONVERT(VARCHAR(20),s.end_hrs)
AND B.point_location = C.point_Location
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id = 'RAW_POW'
UNION
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, 0 AS E_Reading, C.Act_value AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE A.point_location=B.point_location
AND SW.sw_id= S.sw_id
AND A.p_equip_id=B.equip_id
AND A.equip_id = B.equip_id
And C.log_date>= @StartDate
And C.log_date < @EndDate
AND CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) = CONVERT(VARCHAR(20), s.start_hrs)+ ':' + CONVERT(VARCHAR(20),s.end_hrs)
AND B.point_location = C.point_Location
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id = 'GAS') T
GROUP BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time
ORDER BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time) > @EndDate
BREAK
ELSE
CONTINUE
END
|
|