Forums Últimos mensajes - Powered by IBM
 
Tags Palabras claves

Ayuda con este select

24/05/2007 - 14:04 por Julio Ramos | Informe spam
Ayuda Hacer una pregunta
Hola grupo,
Para mi seria de gran ayuda si me dan una manito con esto ya que
no se como hacerlo.

Tengo la tabla 1 que tiene un valor cada 15 minuto asociado a la fecha

Lo que necesito es convertir este intervalo de cada 15 minuto a equivalente
en hora
Es decir el valor se me represente en hora.

La idea es Sumar los primeros cuatros intevalo de 15 minutos
para que corresponda a una hora

Estos cuatros invervalos de 15 segundo convertirlo en una hoara:
2007-04-01 00:00:00.000 146.839660644531
2007-04-01 00:15:00.000 142.848114013672
2007-04-01 00:30:00.000 146.749130249023
2007-04-01 00:45:00.000 143.555892944336

Ejemplo
Fecha Hora valor
2007-04-01 1 579.992798
2007-04-01 2 xxxxxxxxxx
2007-04-01 3 xxxxxxxxxx
2007-04-01 4 xxxxxxxxxx
2007-04-01 5 xxxxxxxxxx
2007-04-01 24 hasta la 24 se completa el primer dia
2007-04-02 1 xxxxxxxxxx
2007-04-02 2 xxxxxxxxxx
Esto es correspondiente al mes




Nota:
Esto es un contador de energia electrica que cuenta el consumo que
tenemos en la casa. Este consumo lo va registrando cada 15 segundo
pero los calculos para facturarcion se deben hacer horarios y por eso
necesito llevarlo a horas.
En resumen necesito saber el consumo que tuvo diario y la suma del mes

Gracias de antes manos por su ayuda

Otra cosa que hace esto un poco dificil es que la fecha van
desde la 2007-04-01 00:15:00.000 hasta 2007-04-30 00:00:00.000
ya que la 2007-04-01 00:00:00.000 se usa en el mes antrior



tabla 1
fecha Valor
2007-04-01 00:00:00.000 146.839660644531
2007-04-01 00:15:00.000 142.848114013672
2007-04-01 00:30:00.000 146.749130249023
2007-04-01 00:45:00.000 143.555892944336
2007-04-01 01:00:00.000 140.634231567383
2007-04-01 01:15:00.000 144.634017944336
2007-04-01 01:30:00.000 139.728942871094
2007-04-01 01:45:00.000 141.844039916992
2007-04-01 02:00:00.000 141.547760009766
2007-04-01 02:15:00.000 133.169631958008
2007-04-01 02:30:00.000 140.132202148438
2007-04-01 02:45:00.000 139.465576171875
2007-04-01 03:00:00.000 133.177856445313
2007-04-01 03:15:00.000 140.897598266602
2007-04-01 03:30:00.000 137.243469238281
2007-04-01 03:45:00.000 136.395782470703
2007-04-01 04:00:00.000 141.243255615234
2007-04-01 04:15:00.000 136.906051635742
2007-04-01 04:30:00.000 139.062301635742
2007-04-01 04:45:00.000 140.173355102539
2007-04-01 05:00:00.000 131.515396118164
2007-04-01 05:15:00.000 138.675491333008
2007-04-01 05:30:00.000 138.856552124023
2007-04-01 05:45:00.000 129.894088745117
2007-04-01 06:00:00.000 138.305145263672
2007-04-01 06:15:00.000 135.704467773438
2007-04-01 06:30:00.000 134.930847167969
2007-04-01 06:45:00.000 137.408081054688
2007-04-01 07:00:00.000 128.931182861328
2007-04-01 07:15:00.000 132.634674072266
2007-04-01 07:30:00.000 135.893753051758
2007-04-01 07:45:00.000 129.309753417969
2007-04-01 08:00:00.000 135.893753051758
2007-04-01 08:15:00.000 137.844268798828
2007-04-01 08:30:00.000 135.926681518555
2007-04-01 08:45:00.000 137.095336914063
2007-04-01 09:00:00.000 140.66716003418
2007-04-01 09:15:00.000 134.1572265625
2007-04-01 09:30:00.000 144.181365966797
2007-04-01 09:45:00.000 141.621841430664
2007-04-01 10:00:00.000 147.358139038086
2007-04-01 10:15:00.000 148.139999389648
2007-04-01 10:30:00.000 143.259613037109
2007-04-01 10:45:00.000 141.300872802734
2007-04-01 11:00:00.000 147.975402832031
2007-04-01 11:15:00.000 145.193649291992
2007-04-01 11:30:00.000 139.300979614258
2007-04-01 11:45:00.000 144.074371337891
2007-04-01 12:00:00.000 138.683731079102
2007-04-01 12:15:00.000 135.399963378906
2007-04-01 12:30:00.000 138.914169311523
2007-04-01 12:45:00.000 138.07470703125
2007-04-01 13:00:00.000 110.388984680176
2007-04-01 13:15:00.000 86.4890670776367
2007-04-01 13:30:00.000 86.3326950073242
2007-04-01 13:45:00.000 84.5385589599609
2007-04-01 14:00:00.000 83.9789199829102
2007-04-01 14:15:00.000 84.110595703125
2007-04-01 14:30:00.000 34.2203407287598
2007-04-01 14:45:00.000 0
2007-04-01 15:00:00.000 0
2007-04-01 15:15:00.000 0
2007-04-01 15:30:00.000 3.62942981719971
2007-04-01 15:45:00.000 61.840217590332
2007-04-01 16:00:00.000 92.6286468505859
2007-04-01 16:15:00.000 94.1676559448242
2007-04-01 16:30:00.000 87.6577301025391
2007-04-01 16:45:00.000 88.3078994750977
2007-04-01 17:00:00.000 87.2709197998047
2007-04-01 17:15:00.000 88.5136489868164
2007-04-01 17:30:00.000 87.0980911254883
2007-04-01 17:45:00.000 86.4561462402344
2007-04-01 18:00:00.000 86.3326950073242
2007-04-01 18:15:00.000 127.153495788574
2007-04-01 18:30:00.000 145.391174316406
2007-04-01 18:45:00.000 144.378890991211
2007-04-01 19:00:00.000 139.243362426758
2007-04-01 19:15:00.000 138.930633544922
2007-04-01 19:30:00.000 139.835922241211
2007-04-01 19:45:00.000 122.791595458984
2007-04-01 20:00:00.000 136.502777099609
2007-04-01 20:15:00.000 134.873229980469
2007-04-01 20:30:00.000 129.112243652344
2007-04-01 20:45:00.000 136.906051635742
2007-04-01 21:00:00.000 130.354965209961
2007-04-01 21:15:00.000 130.8076171875
2007-04-01 21:30:00.000 137.424530029297
2007-04-01 21:45:00.000 130.231521606445
2007-04-01 22:00:00.000 129.523742675781
2007-04-01 22:15:00.000 133.507049560547
2007-04-01 22:30:00.000 125.252365112305
2007-04-01 22:45:00.000 129.91877746582
2007-04-01 23:00:00.000 131.466018676758
2007-04-01 23:15:00.000 124.445823669434
2007-04-01 23:30:00.000 128.256317138672
2007-04-01 23:45:00.000 130.313812255859
2007-04-02 00:00:00.000 125.137145996094
2007-04-02 00:15:00.000 123.672210693359
2007-04-02 00:30:00.000 125.573333740234
2007-04-02 00:45:00.000 125.737937927246
2007-04-02 01:00:00.000 123.178405761719
2007-04-02 01:15:00.000 125.161834716797
2007-04-02 01:30:00.000 124.898475646973
2007-04-02 01:45:00.000 124.495208740234
2007-04-02 02:00:00.000 123.746276855469
2007-04-02 02:15:00.000 124.124855041504
2007-04-02 02:30:00.000 123.22779083252
2007-04-02 02:45:00.000 122.08381652832
2007-04-02 03:00:00.000 124.289459228516
2007-04-02 03:15:00.000 121.343116760254
2007-04-02 03:30:00.000 123.400619506836
2007-04-02 03:45:00.000 122.997344970703
2007-04-02 04:00:00.000 117.582008361816
2007-04-02 04:15:00.000 123.696899414063
2007-04-02 04:30:00.000 124.750335693359
2007-04-02 04:45:00.000 119.236236572266
2007-04-02 05:00:00.000 125.458114624023
2007-04-02 05:15:00.000 124.890243530273
2007-04-02 05:30:00.000 122.997344970703
2007-04-02 05:45:00.000 121.260818481445
2007-04-02 06:00:00.000 123.013809204102
2007-04-02 06:15:00.000 127.663757324219
2007-04-02 06:30:00.000 123.441764831543
2007-04-02 06:45:00.000 129.227462768555
2007-04-02 07:00:00.000 132.873352050781
2007-04-02 07:15:00.000 130.889923095703
2007-04-02 07:30:00.000 132.716979980469
2007-04-02 07:45:00.000 132.922729492188
2007-04-02 08:00:00.000 137.161178588867
2007-04-02 08:15:00.000 145.547546386719
2007-04-02 08:30:00.000 146.164794921875
2007-04-02 08:45:00.000 141.531311035156
2007-04-02 09:00:00.000 148.897155761719
2007-04-02 09:15:00.000 147.391067504883
2007-04-02 09:30:00.000 144.123764038086
2007-04-02 09:45:00.000 143.901550292969
2007-04-02 10:00:00.000 142.107406616211
2007-04-02 10:15:00.000 143.629959106445
2007-04-02 10:30:00.000 140.263885498047
2007-04-02 10:45:00.000 143.185531616211
2007-04-02 11:00:00.000 135.194213867188
2007-04-02 11:15:00.000 146.600982666016
2007-04-02 11:30:00.000 146.469299316406
2007-04-02 11:45:00.000 141.119812011719
2007-04-02 12:00:00.000 148.000091552734
2007-04-02 12:15:00.000 147.991851806641
2007-04-02 12:30:00.000 141.844039916992
2007-04-02 12:45:00.000 151.20979309082
2007-04-02 13:00:00.000 147.917785644531
2007-04-02 13:15:00.000 148.246994018555
2007-04-02 13:30:00.000 149.76953125
2007-04-02 13:45:00.000 142.214401245117
2007-04-02 14:00:00.000 152.386672973633
2007-04-02 14:15:00.000 151.464920043945
2007-04-02 14:30:00.000 139.193984985352
2007-04-02 14:45:00.000 149.860061645508
2007-04-02 15:00:00.000 150.419708251953
2007-04-02 15:15:00.000 149.646087646484
2007-04-02 15:30:00.000 145.366485595703
2007-04-02 15:45:00.000 173.093353271484
2007-04-02 16:00:00.000 196.952117919922
2007-04-02 16:15:00.000 198.836791992188
2007-04-02 16:30:00.000 178.64860534668
2007-04-02 16:45:00.000 167.299438476563
2007-04-02 17:00:00.000 166.19660949707
2007-04-02 17:15:00.000 167.998992919922
2007-04-02 17:30:00.000 165.587600708008
2007-04-02 17:45:00.000 166.246002197266
2007-04-02 18:00:00.000 168.583312988281
2007-04-02 18:15:00.000 205.865219116211
2007-04-02 18:30:00.000 202.178176879883
2007-04-02 18:45:00.000 198.433532714844
2007-04-02 19:00:00.000 205.412567138672
2007-04-02 19:15:00.000 205.001068115234
2007-04-02 19:30:00.000 212.259918212891
2007-04-02 19:45:00.000 213.288681030273
2007-04-02 20:00:00.000 207.9638671875
2007-04-02 20:15:00.000 210.432861328125
2007-04-02 20:30:00.000 212.194091796875
2007-04-02 20:45:00.000 207.848648071289
2007-04-02 21:00:00.000 207.741653442383
2007-04-02 21:15:00.000 209.132522583008
2007-04-02 21:30:00.000 206.902191162109
2007-04-02 21:45:00.000 208.309524536133
2007-04-02 22:00:00.000 211.000732421875
2007-04-02 22:15:00.000 207.099716186523
2007-04-02 22:30:00.000 208.482360839844
2007-04-02 22:45:00.000 214.243362426758
2007-04-02 23:00:00.000 205.848754882813
2007-04-02 23:15:00.000 208.28483581543
2007-04-02 23:30:00.000 204.136917114258
2007-04-02 23:45:00.000 203.890014648438
2007-04-03 00:00:00.000 198.960250854492
2007-04-03 00:15:00.000 199.067230224609
2007-04-03 00:30:00.000 195.13330078125
2007-04-03 00:45:00.000 192.326858520508
2007-04-03 01:00:00.000 197.04264831543
2007-04-03 01:15:00.000 192.902969360352
2007-04-03 01:30:00.000 191.660232543945
2007-04-03 01:45:00.000 192.145812988281
2007-04-03 02:00:00.000 191.619079589844
2007-04-03 02:15:00.000 193.99755859375
2007-04-03 02:30:00.000 183.792358398438
2007-04-03 02:45:00.000 191.97297668457
2007-04-03 03:00:00.000 192.516159057617
2007-04-03 03:15:00.000 182.730682373047
2007-04-03 03:30:00.000 193.191009521484
2007-04-03 03:45:00.000 187.611068725586
2007-04-03 04:00:00.000 176.228988647461
2007-04-03 04:15:00.000 193.61897277832
2007-04-03 04:30:00.000 182.467330932617
2007-04-03 04:45:00.000 179.669128417969
2007-04-03 05:00:00.000 193.191009521484
2007-04-03 05:15:00.000 181.265747070313
2007-04-03 05:30:00.000 183.05989074707
2007-04-03 05:45:00.000 190.178833007813
2007-04-03 06:00:00.000 181.726623535156
2007-04-03 06:15:00.000 187.462936401367
2007-04-03 06:30:00.000 194.384368896484
2007-04-03 06:45:00.000 190.195297241211
2007-04-03 07:00:00.000 196.219650268555
2007-04-03 07:15:00.000 195.528335571289
2007-04-03 07:30:00.000 195.429580688477
2007-04-03 07:45:00.000 198.129013061523
2007-04-03 08:00:00.000 203.453826904297
2007-04-03 08:15:00.000 199.651565551758
2007-04-03 08:30:00.000 206.902191162109
2007-04-03 08:45:00.000 205.206817626953
2007-04-03 09:00:00.000 200.573318481445
2007-04-03 09:15:00.000 210.268264770508
2007-04-03 09:30:00.000 207.552368164063
2007-04-03 09:45:00.000 200.853149414063
2007-04-03 10:00:00.000 208.268371582031
2007-04-03 10:15:00.000 206.918655395508
2007-04-03 10:30:00.000 202.778961181641
2007-04-03 10:45:00.000 208.103775024414
2007-04-03 11:00:00.000 209.823852539063
2007-04-03 11:15:00.000 209.914367675781
2007-04-03 11:30:00.000 208.482360839844
2007-04-03 11:45:00.000 212.737258911133
2007-04-03 12:00:00.000 210.55632019043
2007-04-03 12:15:00.000 219.370651245117
2007-04-03 12:30:00.000 213.971771240234
2007-04-03 12:45:00.000 215.296798706055
2007-04-03 13:00:00.000 212.333999633789
2007-04-03 13:15:00.000 213.955307006836
2007-04-03 13:30:00.000 220.703903198242
2007-04-03 13:45:00.000 215.765899658203
2007-04-03 14:00:00.000 216.276168823242
2007-04-03 14:15:00.000 218.926223754883
2007-04-03 14:30:00.000 200.984832763672
2007-04-03 14:45:00.000 201.503311157227
2007-04-03 15:00:00.000 199.528121948242
2007-04-03 15:15:00.000 194.919311523438
2007-04-03 15:30:00.000 190.861923217773
2007-04-03 15:45:00.000 193.133407592773
2007-04-03 16:00:00.000 187.059661865234
2007-04-03 16:15:00.000 189.866088867188
2007-04-03 16:30:00.000 192.409164428711
2007-04-03 16:45:00.000 190.195297241211
2007-04-03 17:00:00.000 190.853698730469
2007-04-03 17:15:00.000 205.075134277344
2007-04-03 17:30:00.000 202.869491577148
2007-04-03 17:45:00.000 196.293731689453
2007-04-03 18:00:00.000 205.042221069336
2007-04-03 18:15:00.000 204.910537719727
2007-04-03 18:30:00.000 206.836349487305
2007-04-03 18:45:00.000 218.119689941406
2007-04-03 19:00:00.000 214.951141357422
2007-04-03 19:15:00.000 212.613815307617
2007-04-03 19:30:00.000 210.753829956055
2007-04-03 19:45:00.000 209.66748046875
2007-04-03 20:00:00.000 211.848419189453
2007-04-03 20:15:00.000 216.55598449707
2007-04-03 20:30:00.000 219.238967895508
2007-04-03 20:45:00.000 219.724533081055
2007-04-03 21:00:00.000 218.745162963867
2007-04-03 21:15:00.000 219.197814941406
2007-04-03 21:30:00.000 215.370864868164
2007-04-03 21:45:00.000 216.36669921875
2007-04-03 22:00:00.000 214.901748657227
2007-04-03 22:15:00.000 211.889572143555
2007-04-03 22:30:00.000 218.037384033203
2007-04-03 22:45:00.000 211.321701049805
2007-04-03 23:00:00.000 208.638717651367
2007-04-03 23:15:00.000 210.169509887695
2007-04-03 23:30:00.000 199.100158691406
2007-04-03 23:45:00.000 197.388320922852
email Siga el debateRespuesta 14 respuestasRespuesta Tengo una respuesta

Preguntas similare

Leer las respuestas

#6 Julio Ramos
24/05/2007 - 17:58 | Informe spam
saludos alejandro, mira lo que me devuelve el select

select

dateadd(day, datediff(day, 0, IntervalEndTime), 0) as fecha,

datepart(hour, IntervalEndTime) + 1 as hora,

sum([177_Wh d]) as sum_valor

from

lectura

group by

dateadd(day, datediff(day, 0, IntervalEndTime), 0) ,

datepart(hour, IntervalEndTime) + 1

order by

IntervalEndTime,hora

Msg 8127, Level 16, State 1, Line 1

Column "lectura.IntervalEndTime" is invalid in the ORDER BY clause because
it is not contained in either an aggregate function or the GROUP BY clause.



Luego lo corri sin el Order by con la hora solo



Fecha hora valor

2007-01-11 00:00:00.000 1 203.040008544922
2007-01-20 00:00:00.000 1 198.148010253906
2007-01-01 00:00:00.000 1 32.1960020065308
2007-01-13 00:00:00.000 1 187.652008056641
2007-01-25 00:00:00.000 1 223.796012878418
2007-01-06 00:00:00.000 1 241.232013702393
2007-01-18 00:00:00.000 1 211.364009857178
2007-01-30 00:00:00.000 1 31.512001991272
2007-01-08 00:00:00.000 1 37.7720022201538
2007-01-17 00:00:00.000 1 195.116008758545
2007-01-29 00:00:00.000 1 30.3000016212463
2007-01-22 00:00:00.000 1 33.8080024719238
2007-01-03 00:00:00.000 1 241.760013580322
2007-01-15 00:00:00.000 1 16.0760006904602
2007-01-27 00:00:00.000 1 198.272006988525
2007-01-05 00:00:00.000 1 242.732013702393
2007-01-14 00:00:00.000 1 34.2600021362305
2007-01-26 00:00:00.000 1 216.868007659912
2007-01-07 00:00:00.000 1 31.1960010528564
2007-01-19 00:00:00.000 1 222.252014160156
2007-01-31 00:00:00.000 1 233.340015411377
2007-01-12 00:00:00.000 1 207.508014678955
2007-01-24 00:00:00.000 1 204.280010223389
2007-01-02 00:00:00.000 1 29.7720017433167
2007-01-23 00:00:00.000 1 194.580009460449
2007-01-04 00:00:00.000 1 261.884010314941
2007-01-16 00:00:00.000 1 221.176010131836
2007-01-28 00:00:00.000 1 30.8760018348694
2007-01-09 00:00:00.000 1 228.55200958252
2007-01-21 00:00:00.000 1 54.4320030212402
2007-01-31 00:00:00.000 2 224.748008728027
2007-01-19 00:00:00.000 2 218.508014678955
2007-01-24 00:00:00.000 2 205.788009643555
2007-01-12 00:00:00.000 2 208.108005523682
2007-01-17 00:00:00.000 2 200.400005340576
2007-01-05 00:00:00.000 2 254.92000579834
2007-01-10 00:00:00.000 2 89.3720054626465
2007-01-29 00:00:00.000 2 32.0840020179749
2007-01-03 00:00:00.000 2 252.148010253906
2007-01-22 00:00:00.000 2 35.1000022888184
2007-01-27 00:00:00.000 2 180.86400604248
2007-01-15 00:00:00.000 2 40.6640024185181
2007-01-20 00:00:00.000 2 192.264011383057
2007-01-08 00:00:00.000 2 39.2800025939941
2007-01-13 00:00:00.000 2 185.760009765625
2007-01-01 00:00:00.000 2 31.4240012168884
2007-01-25 00:00:00.000 2 217.148014068604
2007-01-18 00:00:00.000 2 205.936008453369
2007-01-30 00:00:00.000 2 33.7560014724731
2007-01-23 00:00:00.000 2 200.376014709473
2007-01-06 00:00:00.000 2 225.392009735107
2007-01-11 00:00:00.000 2 204.956008911133
2007-01-04 00:00:00.000 2 264.872009277344
2007-01-28 00:00:00.000 2 33.7000017166138
2007-01-16 00:00:00.000 2 215.252010345459
2007-01-21 00:00:00.000 2 66.8360023498535
2007-01-09 00:00:00.000 2 228.872009277344
2007-01-14 00:00:00.000 2 33.9560012817383
2007-01-02 00:00:00.000 2 30.6760010719299
2007-01-07 00:00:00.000 2 40.3040008544922
2007-01-26 00:00:00.000 2 211.640014648438
2007-01-22 00:00:00.000 3 33.1920022964478
2007-01-03 00:00:00.000 3 248.03201675415
2007-01-15 00:00:00.000 3 23.6720018386841
2007-01-24 00:00:00.000 3 195.464008331299
2007-01-05 00:00:00.000 3 254.192012786865
2007-01-17 00:00:00.000 3 192.84001159668
2007-01-29 00:00:00.000 3 30.1440014839172
2007-01-10 00:00:00.000 3 167.980007171631
2007-01-31 00:00:00.000 3 223.808010101318
2007-01-19 00:00:00.000 3 214.444011688232
2007-01-21 00:00:00.000 3 62.108003616333
2007-01-02 00:00:00.000 3 29.6680011749268
2007-01-12 00:00:00.000 3 207.632011413574
2007-01-14 00:00:00.000 3 33.4160013198853
2007-01-26 00:00:00.000 3 186.660007476807
2007-01-28 00:00:00.000 3 32.5520014762878
2007-01-09 00:00:00.000 3 213.216007232666
2007-01-16 00:00:00.000 3 206.116008758545
2007-01-07 00:00:00.000 3 37.7960023880005
2007-01-30 00:00:00.000 3 32.4160008430481
2007-01-11 00:00:00.000 3 197.704010009766
2007-01-04 00:00:00.000 3 259.920009613037
2007-01-23 00:00:00.000 3 192.788005828857
2007-01-06 00:00:00.000 3 221.908012390137
2007-01-25 00:00:00.000 3 204.308013916016
2007-01-18 00:00:00.000 3 184.868007659912
2007-01-27 00:00:00.000 3 186.584007263184
2007-01-08 00:00:00.000 3 37.1000022888184
2007-01-20 00:00:00.000 3 143.196002960205
2007-01-01 00:00:00.000 3 30.1400008201599
2007-01-13 00:00:00.000 3 181.804004669189











"Alejandro Mesa" wrote in message
news:
Mostrar la cita
Respuesta Responder a este mensaje
#7 Alejandro Mesa
24/05/2007 - 18:11 | Informe spam
Julio Ramos,

Usa el alias de la primera columna de la lista "select", para ordenar.

Mostrar la cita
...
order by fecha, hora



AMB


"Julio Ramos" wrote:

Mostrar la cita
Respuesta Responder a este mensaje
#8 Julio Ramos
24/05/2007 - 22:03 | Informe spam
Saludos Alejandro, hay algo que me estaba faltando para ver si es posible
trabajar esto.



Para facturar siempre se toma el mes anterior al mes actual es decir

que si voy a facturar abril tengo que tomar este intevalo para la
facturacion

incluyendo la hora como esto.



Inicio 1/4/2007 12:15:00 AM

Fin 1/5/2007 12:00:00 AM



Quiero ver si podemos integar esta logica en el query



Lectura que se toma para query



Ejemplo de parte de la lectura



4/1/2007 12:00:00 AM 37.5
4/1/2007 12:15:00 AM 44.508003234863281 Inicio
4/1/2007 12:30:00 AM 43.956001281738281
4/1/2007 12:45:00 AM 43.448001861572266
4/1/2007 1:00:00 AM 45.468002319335938
4/1/2007 1:15:00 AM 42.044002532958984
4/1/2007 1:30:00 AM 43.448001861572266




4/30/2007 11:00:00 PM 8.7440004348754883
4/30/2007 11:15:00 PM 9.1440000534057617
4/30/2007 11:30:00 PM 9.0440006256103516
4/30/2007 11:45:00 PM 8.8920001983642578
5/1/2007 12:00:00 AM 9.02400016784668 FIN













"Alejandro Mesa" wrote in message
news:
Mostrar la cita
Respuesta Responder a este mensaje
#9 Alejandro Mesa
24/05/2007 - 22:34 | Informe spam
Julio Ramos,

Trata:

select
dateadd(day, datediff(day, 0, IntervalEndTime), 0) as fecha,
datepart(hour, IntervalEndTime) + 1 as hora,
sum([177_Wh d]) as sum_valor
from
dbo.lectura
where
IntervalEndTime >= convert(varchar(7), dateadd(month, -1, getdate()), 126)
+ '-01 00:15:00.000'
and IntervalEndTime < convert(varchar(6), dateadd(month, 1, getdate()),
112) + '01'
group by
dateadd(day, datediff(day, 0, IntervalEndTime), 0) ,
datepart(hour, IntervalEndTime) + 1
order by
fecha,hora
go


AMB


"Julio Ramos" wrote:

Mostrar la cita
Respuesta Responder a este mensaje
#10 Julio Ramos
24/05/2007 - 23:55 | Informe spam
Alejandro,

El select me devuelve lo siguiente
Fecha Hora Valor
2007-04-01 00:00:00.000 1 131.912006378174
2007-04-01 00:00:00.000 2 175.756008148193
2007-04-01 00:00:00.000 3 178.288009643555
2007-04-01 00:00:00.000 4 178.972007751465
2007-04-01 00:00:00.000 5 179.272006988525
2007-04-01 00:00:00.000 6 175.568008422852
2007-04-01 00:00:00.000 7 182.420009613037
2007-04-01 00:00:00.000 8 189.128009796143


Esta es parte de la lectura que se le realizo el select

4/1/2007 12:00:00 AM 37.5
4/1/2007 12:15:00 AM 44.508003234863281 Inicio
4/1/2007 12:30:00 AM 43.956001281738281
4/1/2007 12:45:00 AM 43.448001861572266
4/1/2007 1:00:00 AM 45.468002319335938
4/1/2007 1:15:00 AM 42.044002532958984
4/1/2007 1:30:00 AM 43.448001861572266

Si sumamos los primeros cuatros valores es decir para hayar la primera hora
nos da 44.508003234863281 + 43.956001281738281 + 43.448001861572266
+ 45.468002319335938 = 177.3800087

El select para la primera hora me devuelve

2007-04-01 00:00:00.000 1 131.912006378174

Hay una diferencia aqui.

Gracias a Dios ya estamos avanzando mas














"Alejandro Mesa" wrote in message
news:
Mostrar la cita
Respuesta Responder a este mensaje
Ads by Google
Help Hacer una preguntaRespuesta Tengo una respuesta
Search Busqueda sugerida