Solved

# selecting total amount consumed yesterday

Posted on 2006-04-27
354 Views

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 .
Thank you
0
Question by:zolf

LVL 75

Accepted Solution

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

LVL 44

Assisted Solution

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

LVL 75

Assisted Solution

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

### Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.