Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

selecting total amount consumed yesterday


Hello there,

SELECT      timestamp
     , "kWh del"
     , "kWh rec"
     , "kWh del-rec"

  FROM   --(*vendor(PML),product(LogServer) EgyDmd Log@ALI.7600 *)--

  WHERE  (timestamp <= today_12AM()
         AND
        timestamp >= yesterday())

  ORDER BY timestamp DESC
 --  SQL generated with ION Enterprise version 5.5 build 318

What operation should i use to get the total energy consumed yesterday. I am querying it from a remote meters database.The above operation is giving me energy consumed every 15min for the whole day.But i want to see the total energy consumed .
Please help!!!Its urgent......I am not a SQL guru but more of a hardware guy.
Thank you
0
zolf
Asked:
zolf
  • 2
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
this will give you yesterday
SELECT CONVERT(varchar(10),GETDATE()-1,120)

SELECT      timestamp
     , "kWh del"
     , "kWh rec"
     , "kWh del-rec"

  FROM   --(*vendor(PML),product(LogServer) EgyDmd Log@ALI.7600 *)--

  WHERE  CONVERT(varchar(10),urDatetimeColumn-1,120)    = CONVERT(varchar(10),GETDATE()-1,120)
0
 
bruintjeCommented:
Hello zolf,

you could try
---------
  SELECT SUM("kWh del") AS TotalKwhDel
     , SUM("kWh rec") AS TotalKwhRec
     , SUM("kWh del-rec") AS TotalKwhDel-Rec

  FROM   --(*vendor(PML),product(LogServer) EgyDmd Log@ALI.7600 *)--

  WHERE  (timestamp <= today_12AM()
         AND
        timestamp >= yesterday())

  ORDER BY timestamp DESC
----------


hope this helps a bit
bruintje
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT [timestamp]   -- put a square bracket around this timestamp since it is a keyword
     , "kWh del"
     , "kWh rec"
     , "kWh del-rec"

  FROM   --(*vendor(PML),product(LogServer) EgyDmd Log@ALI.7600 *)--

  WHERE  CONVERT(varchar(10),[timestamp]1,120)    = CONVERT(varchar(10),GETDATE()-1,120)
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now