Re: Sum and GroupBy problem,

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 04/13/04 

Date: Tue, 13 Apr 2004 06:36:26 +0100

Try this:

CREATE TABLE Accounts (acc_no INTEGER, acc_date DATETIME, acc_amount
NUMERIC(10,2) NOT NULL, PRIMARY KEY (acc_no, acc_date))

DECLARE @dt_from DATETIME, @dt_to DATETIME

SELECT acc_no,
 COALESCE(SUM(CASE WHEN acc_date < @dt_from THEN acc_amount END),0)
  AS opening_balance,
 COALESCE(SUM(CASE WHEN acc_date <= @dt_to THEN acc_amount END),0)
  AS closing_balance
 FROM Accounts
 GROUP BY acc_no 

-- 
David Portas
SQL Server MVP
--

Relevant Pages

  • Re: Data grouped by 20 minutes?
    ... Assuming the time you have in each record is a datetime, ... this example assumes that you have a table called Accounts with a datetime ... This seems to work on one of my databases. ... Basically works by converting datetime to an integer and then grouping on ...
    (comp.databases.ms-sqlserver)
  • Re: getdate() in UDF
    ... >> different result for each execution. ... > create function dbo.foo2returns datetime ... > declare @current_date datetime ... > accounts table. ...
    (microsoft.public.sqlserver.programming)
  • Re: Select
    ... SELECT * FROM accounts, jobs ... CHAR, VARCHAR, DATETIME, SMALLDATETIME. ... WHERE jobDate BETWEEN @Start AND @Finish; ...
    (microsoft.public.sqlserver.programming)
  • Re: List of past Dates
    ... Look at the script written by Irzik Ben-Gan ... CREATE FUNCTION fn_dates(@from AS DATETIME, ... RETURNS @Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY) ... DECLARE @rc AS INT ...
    (microsoft.public.sqlserver.programming)
  • SQL Query help
    ... I have a SQL table with the following fields: ... accounts, orderid's and datetime ... Using Query Analyzer, I'd like to run a query where the results are a count ...
    (comp.databases.ms-sqlserver)