Help build query
in [Programming SQL]
|
From: Henrique on 25 Apr 2010 16:27 I have two tables: Counters:' Every 10 minutes a record is added with updating the counter Data_Hora ID_Contador E_SVazio 23-04-2010 0:00 CTE003 16.528.690 23-04-2010 0:00 CTE002 21.527.520 23-04-2010 0:10 CTE003 16.528.750 23-04-2010 0:10 CTE002 21.527.570 23-04-2010 1:20 CTE003 16.528.800 23-04-2010 1:20 CTE002 21.527.620 23-04-2010 1:30 CTE003 16.528.850 23-04-2010 1:30 CTE002 21.527.670 23-04-2010 2:40 CTE003 16.528.890 23-04-2010 2:40 CTE002 21.527.720 Data counters: ID Nome Uni_Fab PT Zona Equipamento CTE001 XPT0 Zebra PT1 Banhos Caixa1 CTE002 XPT1 Xoli PT2 Banhos Caixa2 CTE003 XPT2 Xoli PT1 Banhos Caixa3 Query: SELECT convert(varchar(2), contadores.Data_Hora, 108) as Hora, Sum(MAX(E_SVazio) - MIN(E_SVazio)) as H_SVazio, Contadores JOIN DadosContadores ON Contadores.ID_Contador = DadosContadores.ID WHERE Year(Data_Hora)= 2010 AND Month(Data_Hora)= 4 AND Day(Data_Hora) = 23 And E_SVazio<>0 AND Uni_Fab = 'Xoli' GROUP BY Year(Data_Hora),Month(Data_Hora),Day(Data_Hora),Convert(varchar(2), contadores.Data_Hora, 108) ORDER BY Convert(varchar(2), contadores.Data_Hora, 108) Asc This query d'ont whork whel What I want is the sum of the energies, which is given by the difference of two readings (Min and Max) to 23/04/2010 and Uni_Fab = Xoli grouping. example: "xoli" has two counters CTE002 and CTE003 and need to know the sum of these two counts someone help me? Thanks Se
From: Plamen Ratchev on 25 Apr 2010 18:38 Try this: SELECT CONVERT(CHAR(2), Data_Hora, 108) AS Hora, MAX(E_SVazio) - MIN(E_SVazio) AS H_SVazio FROM Contadores JOIN DadosContadores ON ID_Contador = ID WHERE Data_Hora >= '20100423' AND Data_Hora < '20100424' AND E_SVazio <> 0 AND Uni_Fab = 'Xoli' GROUP BY CONVERT(CHAR(2), Data_Hora, 108) ORDER BY Hora; -- Plamen Ratchev http://www.SQLStudio.com
From: Henrique on 26 Apr 2010 04:43 Hi! Thanks for your quick response. In my case I have the day, month and year with separate combobox. Xoli is a company, and has two counters and CTE002 CTE003. With your query, it will take min () of CTE003 and Max () of CTE002, resulting in erroneous data. What I want is the energy consumed by the company Xoli, CTE002 + CTE003. Thanks "Plamen Ratchev" wrote: > Try this: > > SELECT CONVERT(CHAR(2), Data_Hora, 108) AS Hora, > MAX(E_SVazio) - MIN(E_SVazio) AS H_SVazio > FROM Contadores > JOIN DadosContadores > ON ID_Contador = ID > WHERE Data_Hora >= '20100423' > AND Data_Hora < '20100424' > AND E_SVazio <> 0 > AND Uni_Fab = 'Xoli' > GROUP BY CONVERT(CHAR(2), Data_Hora, 108) > ORDER BY Hora; > > -- > Plamen Ratchev > http://www.SQLStudio.com > . >
From: Plamen Ratchev on 26 Apr 2010 10:35 You can concatenate year, month, and day together to form a date and use that as I demonstrated. using the separate components will result in inefficient query. Please post expected result based on the sample data you posted, I do not understand your requirements(and what data type is E_SVazio, it does not look like valid numeric). -- Plamen Ratchev http://www.SQLStudio.com
From: Henrique on 26 Apr 2010 12:16
Hi! Plamen Ratchev Thanks for yor help. E_SVazio is a Integer field Counters: (Energie counter)' Every 10 minutes a record is added with updating the counter with new "E_SVazio" incremented diferent velue. Data_Hora ID_Contador E_SVazio 23-04-2010 0:00 CTE003 16528690 23-04-2010 0:00 CTE002 21527520 23-04-2010 0:10 CTE003 16528750 23-04-2010 0:10 CTE002 21527570 23-04-2010 1:20 CTE003 16528800 23-04-2010 1:20 CTE002 21527620 23-04-2010 1:30 CTE003 16528850 23-04-2010 1:30 CTE002 21527670 23-04-2010 2:40 CTE003 16528890 23-04-2010 2:40 CTE002 21527720 Data counters: ID Nome Uni_Fab PT Zona Equipamento CTE001 XPT0 Zebra PT1 Banhos Caixa1 CTE002 XPT1 Xoli PT2 Banhos Caixa2 CTE003 XPT2 Xoli PT1 Banhos Caixa3 By combining the two tables is the result: ( JOIN DadosContadores ON Contadores.ID_Contador = DadosContadores.ID) For easie calculations i´m reduce the counter values Data_Hora ID_Contador E_SVazio Nome Uni_Fab PT Zona Equipamento 23-04-2010 0:00 CTE003 8690 XPT2 Xoli PT1 Banhos Caixa3 23-04-2010 0:00 CTE002 7520 XPT1 Xoli PT2 Banhos Caixa2 23-04-2010 0:10 CTE003 8750 XPT2 Xoli PT1 Banhos Caixa3 23-04-2010 0:10 CTE002 7570 XPT1 Xoli PT2 Banhos Caixa2 23-04-2010 1:20 CTE003 8800 XPT2 Xoli PT1 Banhos Caixa3 23-04-2010 1:20 CTE002 7620 XPT1 Xoli PT2 Banhos Caixa2 23-04-2010 1:30 CTE003 8850 XPT2 Xoli PT1 Banhos Caixa3 23-04-2010 1:30 CTE002 7670 XPT1 Xoli PT2 Banhos Caixa2 23-04-2010 2:40 CTE003 8890 XPT2 Xoli PT1 Banhos Caixa3 23-04-2010 2:40 CTE002 7720 XPT1 Xoli PT2 Banhos Caixa2 Correct result is for Uni_Fab='Xoli' agrupe by Hora ( Hora=hour ind inglish) Hora H_SVazio 00 1230 ( 8750-8690)+ (7570-7520) ' ( Max(CTE003) - Min(CTE003))+ (Max(CTE002) - Min(CTE002)) 01 1230 (8850-8800)+(7670-7620) '( Max(CTE003) - Min(CTE003))+ (Max(CTE002) - Min(CTE002)) 02 1170 (No data only 1 record per CTE003 and CTE002) wrong result with this query: SELECT convert(varchar(2), contadores.Data_Hora, 108) as Hora, (MAX(E_SVazio) - MIN(E_SVazio)) as H_SVazio FROM Contadores JOIN DadosContadores ON Contadores.ID_Contador = DadosContadores.ID WHERE Year(Data_Hora)= '2010' AND Month(Data_Hora)= '04' AND Day(Data_Hora) = '26' And E_SVazio<>0 AND E_Vazio<>0 AND E_Cheias<>0 AND E_Ponta<>0 AND DadosContadores.Uni_Fab ='Siscontil' GROUP BY Year(Data_Hora), Month(Data_Hora), Day(Data_Hora), Convert(varchar(2), contadores.Data_Hora, 108) ORDER BY Convert(varchar(2), contadores.Data_Hora, 108) Asc wrong result: Hora H_SVazio 00 1230 ( 8750-7520) 01 1230 (8850 -7620) 02 1170 (8890-7720) '----------------------------------------------------------------------------------------------------- Xoli is a company, and has two counters and CTE002 CTE003. With your query, it will take min () of CTE003 and Max () of CTE002, resulting in erroneous data. What I want is the energy consumed by the company Xoli, CTE002 + CTE003. Thank you for your interest in this topic S. |