CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Daniel Reis
Daniel Reis

Reputation: 25

SQL Server Query

I want to go from here:

ID  ID_HW   DATA    LAMPADA
305 3517    02-02-2012 00:00    1208
277 3517    30-12-2011 00:00    1127
179 3517    06-12-2011 00:00    1092
134 3517    03-11-2011 00:00    962
89  3517    06-10-2011 00:00    851
69  3517    07-09-2011 00:00    786
42  3517    25-08-2011 00:00    786
306 3518    02-02-2012 00:00    2082
250 3518    30-12-2011 00:00    2045
180 3518    06-12-2011 00:00    2004
163 3518    08-11-2011 00:00    1695
90  3518    06-10-2011 00:00    1516
68  3518    07-09-2011 00:00    1426
43  3518    25-08-2011 00:00    1315
307 3519    02-02-2012 00:00    482
251 3519    02-01-2012 00:00    375
181 3519    06-12-2011 00:00    347
137 3519    07-11-2011 00:00    229
91  3519    06-10-2011 00:00    92
67  3519    07-09-2011 00:00    2305
44  3519    25-08-2011 00:00    2304

to here:

ID_HW   MINDATA MAXDATA _DAYS_  CONSUMED
3517    25-08-2011 00:00    02-02-2012 00:00    161 422
3518    25-08-2011 00:00    02-02-2012 00:00    161 767
3519    25-08-2011 00:00    02-02-2012 00:00    161 -1822

Note: For each id_hw, i want the max, min of data, datediff in days, and the dificult part for me is making the diference of the values associated with the date, creating the consumed values in the range.

Upvotes: 0

Views: 76

Answers (4)

JayaPrakash
JayaPrakash

Reputation: 199

Except columns 'days' and 'consumed', take the result out using group by as a CTE, 

 With cte as
 (
 Select id_hw, min (data) Mindata,max(data) maxdata,
 From Maintable
 Group by Id_hw
)

Then add a correlated subquery in select statement of CTE pass Id_hw and max date, to get max value of lampada for id_hw , similarly pass id_hw and min date, take difference between them now.

  Select *,datediff(day,Maxdata,Mindata)[_days_],
  (select lampada from maintable where id_hw=CTE.id_hw and date=CTE.Maxdata)-(select lampada from maintable where id_hw=CTE.id_hw and date=CTE.Mindata) Consumed
  from cte

Upvotes: 0

Andriy M
Andriy M

Reputation: 77667

It seems like lower values are always associated with earlier dates and vice versa. So you could do something like this:

SELECT
  ID_HW,
  MINDATA  = MIN(DATA),
  MAXDATA  = MAX(DATA),
  _DAYS_   = DATEDIFF(DAY, MIN(DATA), MAX(DATA)),
  CONSUMED = MAX(LAMPADA) - MIN(LAMPADA)
FROM yourtable
GROUP BY
  ID_HW

Upvotes: 0

ntziolis
ntziolis

Reputation: 10221

SELECT ID_HW, MINTABLE.DATA AS MINDATA, MAXTABLE.DATA AS MAXMINDATA, (MAXTABLE.DATA - MINTABLE.DATA) AS DAYS, (MAXTABLE.LAMPADA - MINTABLE.LAMPADA) AS CONSUMED

FROM TABLE,     

SELECT ID_HW, MIN(data) AS DATA, LAMPADA
FROM Table
HAVING MIN(data) = data) AS MINTABLE,   

(SELECT ID_HW, MAX(data) AS DATA, LAMPADA
FROM Table
HAVING MAX(data) = data) AS MAXTABLE

WHERE TABLE.ID_HW = MINTABLE.ID_HW
WHERE TABLE.ID_HW = MAXTABLE.ID_HW

Upvotes: 0

jzworkman
jzworkman

Reputation: 2703

SELECT ID_HW, 
   minTable.DATA as MINDATA, 
   maxTable.DATA as MAXDATA, 
   (maxTable.DATA - minTable.DATA) as _DAYS_, 
   (maxTable.LAMPDA - minTable.LAMPDA) as consumed 
from (SELECT ID_HW, MAX(data) as DATA, LAMPDA from table group by ID_HW) maxTable, 
 (SELECT ID_HW, MIN(DATA) as DATA, LAMPDA from table group by ID_HW) minTable 
where maxTable.ID_HW = minTable.ID_HW

Hope that at least points you in the right direction. Make two sub-queries, one for the max and one for the min and then combine the data with a select from those two queries.

Upvotes: 1

Related Questions