SQL Server Query

Question

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.

I think group by ID_HW, max(DATA), min(DATA)?

kosa
– kosa

2012-03-02 17:22:22 +00:00
Commented Mar 2, 2012 at 17:22 — kosa
– kosa, Commented Mar 2, 2012 at 17:22

jzworkman · Accepted Answer · 2012-03-02 17:29:02Z

1

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.

answered Mar 2, 2012 at 17:29

jzworkman

2,71316 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniel Reis Over a year ago

Thanks, with a bit of brainstorming i got there! The final solution was:

SELECT     MINTABLE.ID_HW AS ID_HW,    MINTABLE.DATA AS MINDATA,    MAXTABLE.DATA AS MAXDATA,    DATEDIFF(DAY, MINTABLE.DATA, MAXTABLE.DATA) AS _DAYS_ ,    CONSUMED = (SELECT LAMPADA FROM TABLE WHERE ID =  MAXTABLE.ID)-(SELECT LAMPADA FROM TABLE WHERE ID =  MINTABLE.ID) FROM  (SELECT MAX(ID) AS ID, ID_HW, MAX(DATA) AS DATA FROM  TABLE  GROUP BY ID_HW)  AS MAXTABLE,  (SELECT MIN(ID) AS ID, ID_HW, MIN(DATA) AS DATA FROM  TABLE  GROUP BY ID_HW)  AS MINTABLE WHERE MAXTABLE.ID_HW = MINTABLE.ID_HW

ntziolis · Accepted Answer · 2012-03-02 17:38:50Z

0

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

answered Mar 2, 2012 at 17:38

ntziolis

10.2k1 gold badge36 silver badges50 bronze badges

Comments

Andriy M · Accepted Answer · 2012-03-02 19:34:37Z

0

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

answered Mar 2, 2012 at 19:34

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Comments

JayaPrakash · Accepted Answer · 2017-08-27 17:02:34Z

0

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

answered Aug 27, 2017 at 17:02

JayaPrakash

1991 silver badge6 bronze badges

Collectives™ on Stack Overflow

SQL Server Query

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related