Ayuda con este select

24/05/2007 - 14:04 por Julio Ramos | Informe spam
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

Preguntas similare

Leer las respuestas

#1 Julio Ramos
24/05/2007 - 14:20 | Informe spam
Disculpen lo de segundo, todo es de minuto a hora
"Julio Ramos" wrote in message
news:


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

Respuesta Responder a este mensaje
#2 Alejandro Mesa
24/05/2007 - 15:16 | Informe spam
Julio Ramos,

Trata:

select
dateadd(day, datediff(day, 0, fecha), fecha) as fecha,
datepart(hour, fecha) + 1 as hora,
sum(valor) as sum_valor
from
dbo.t1
group by
dateadd(day, datediff(day, 0, fecha), fecha) ,
datepart(hour, fecha) + 1
order by
fecha,
hora
go


AMB


"Julio Ramos" wrote:



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
Respuesta Responder a este mensaje
#3 Julio Ramos
24/05/2007 - 16:36 | Informe spam
Gracias alejandro por la ayuda, Lo veo todo bien lo que necesito ahora como
explique
es que me ignore la primera lectura es decir el mes va desde la 2007-04-01
00:15:00.000 hasta 2007-04-30 00:00:00.000
y la fecha que me devuelve mira como sale. este resultado fue del codigo que
me enviaste.



Fecha Hora Valor
2114-01-01 00:00:00.000 1 8.06800079345703 Se ignora
2114-01-01 00:15:00.000 1 8.07999992370605 Aqui es que debe
de empezar
2114-01-01 00:30:00.000 1 7.88800048828125
2114-01-01 00:45:00.000 1 8.16000080108643
2114-01-01 01:00:00.000 2 8.20800018310547
2114-01-01 01:15:00.000 2 7.97200059890747
2114-01-01 01:30:00.000 2 8.02400016784668
2114-01-01 01:45:00.000 2 7.22000026702881
2114-01-01 02:00:00.000 3 7.08400011062622
2114-01-01 02:15:00.000 3 7.3600001335144
2114-01-01 02:30:00.000 3 7.72000026702881

hay que tener en cuenta que hay varios meses aculumados para que lo devuelta
por mes



"Alejandro Mesa" wrote in message
news:
Julio Ramos,

Trata:

select
dateadd(day, datediff(day, 0, fecha), fecha) as fecha,
datepart(hour, fecha) + 1 as hora,
sum(valor) as sum_valor
from
dbo.t1
group by
dateadd(day, datediff(day, 0, fecha), fecha) ,
datepart(hour, fecha) + 1
order by
fecha,
hora
go


AMB


"Julio Ramos" wrote:



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
Respuesta Responder a este mensaje
#4 Alejandro Mesa
24/05/2007 - 17:19 | Informe spam
Julio Ramos,

Eso fue un error de mi parte. La formula de la fecha debe ser:

select
dateadd(day, datediff(day, 0, fecha), fecha) as fecha,
datepart(hour, fecha) + 1 as hora,
sum(valor) as sum_valor
from
dbo.t1
group by
dateadd(day, datediff(day, 0, fecha), 0) ,
datepart(hour, fecha) + 1
order by
fecha,
hora
go


AMB

"Julio Ramos" wrote:

Gracias alejandro por la ayuda, Lo veo todo bien lo que necesito ahora como
explique
es que me ignore la primera lectura es decir el mes va desde la 2007-04-01
00:15:00.000 hasta 2007-04-30 00:00:00.000
y la fecha que me devuelve mira como sale. este resultado fue del codigo que
me enviaste.



Fecha Hora Valor
2114-01-01 00:00:00.000 1 8.06800079345703 Se ignora
2114-01-01 00:15:00.000 1 8.07999992370605 Aqui es que debe
de empezar
2114-01-01 00:30:00.000 1 7.88800048828125
2114-01-01 00:45:00.000 1 8.16000080108643
2114-01-01 01:00:00.000 2 8.20800018310547
2114-01-01 01:15:00.000 2 7.97200059890747
2114-01-01 01:30:00.000 2 8.02400016784668
2114-01-01 01:45:00.000 2 7.22000026702881
2114-01-01 02:00:00.000 3 7.08400011062622
2114-01-01 02:15:00.000 3 7.3600001335144
2114-01-01 02:30:00.000 3 7.72000026702881

hay que tener en cuenta que hay varios meses aculumados para que lo devuelta
por mes



"Alejandro Mesa" wrote in message
news:
> Julio Ramos,
>
> Trata:
>
> select
> dateadd(day, datediff(day, 0, fecha), fecha) as fecha,
> datepart(hour, fecha) + 1 as hora,
> sum(valor) as sum_valor
> from
> dbo.t1
> group by
> dateadd(day, datediff(day, 0, fecha), fecha) ,
> datepart(hour, fecha) + 1
> order by
> fecha,
> hora
> go
>
>
> AMB
>
>
> "Julio Ramos" wrote:
>
>>
>>
>> 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
Respuesta Responder a este mensaje
#5 Alejandro Mesa
24/05/2007 - 17:28 | Informe spam
Julio Ramos,

Dejame ir despacio esta vez.

select
dateadd(day, datediff(day, 0, fecha), 0) as fecha,
datepart(hour, fecha) + 1 as hora,
sum(valor) as sum_valor
from
dbo.t1
group by
dateadd(day, datediff(day, 0, fecha), 0) ,
datepart(hour, fecha) + 1
order by
fecha,
hora
go


AMB

"Alejandro Mesa" wrote:

Julio Ramos,

Eso fue un error de mi parte. La formula de la fecha debe ser:

select
dateadd(day, datediff(day, 0, fecha), fecha) as fecha,
datepart(hour, fecha) + 1 as hora,
sum(valor) as sum_valor
from
dbo.t1
group by
dateadd(day, datediff(day, 0, fecha), 0) ,
datepart(hour, fecha) + 1
order by
fecha,
hora
go


AMB

"Julio Ramos" wrote:

> Gracias alejandro por la ayuda, Lo veo todo bien lo que necesito ahora como
> explique
> es que me ignore la primera lectura es decir el mes va desde la 2007-04-01
> 00:15:00.000 hasta 2007-04-30 00:00:00.000
> y la fecha que me devuelve mira como sale. este resultado fue del codigo que
> me enviaste.
>
>
>
> Fecha Hora Valor
> 2114-01-01 00:00:00.000 1 8.06800079345703 Se ignora
> 2114-01-01 00:15:00.000 1 8.07999992370605 Aqui es que debe
> de empezar
> 2114-01-01 00:30:00.000 1 7.88800048828125
> 2114-01-01 00:45:00.000 1 8.16000080108643
> 2114-01-01 01:00:00.000 2 8.20800018310547
> 2114-01-01 01:15:00.000 2 7.97200059890747
> 2114-01-01 01:30:00.000 2 8.02400016784668
> 2114-01-01 01:45:00.000 2 7.22000026702881
> 2114-01-01 02:00:00.000 3 7.08400011062622
> 2114-01-01 02:15:00.000 3 7.3600001335144
> 2114-01-01 02:30:00.000 3 7.72000026702881
>
> hay que tener en cuenta que hay varios meses aculumados para que lo devuelta
> por mes
>
>
>
> "Alejandro Mesa" wrote in message
> news:
> > Julio Ramos,
> >
> > Trata:
> >
> > select
> > dateadd(day, datediff(day, 0, fecha), fecha) as fecha,
> > datepart(hour, fecha) + 1 as hora,
> > sum(valor) as sum_valor
> > from
> > dbo.t1
> > group by
> > dateadd(day, datediff(day, 0, fecha), fecha) ,
> > datepart(hour, fecha) + 1
> > order by
> > fecha,
> > hora
> > go
> >
> >
> > AMB
> >
> >
> > "Julio Ramos" wrote:
> >
> >>
> >>
> >> 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
Respuesta Responder a este mensaje
Ads by Google
Help Hacer una preguntaSiguiente Respuesta Tengo una respuesta
Search Busqueda sugerida