Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Assistance with a query

3 posts in General Discussion Last posting was on 2009-11-21 00:10:23.0Z
Orett Lewis Posted on 2009-11-20 17:26:05.0Z
Sender: 3c4a.4b06c955.1804289383@sybase.com
From: Orett Lewis
Newsgroups: sybase.public.ase.general
Subject: Assistance with a query
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b06d12d.3da2.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 20 Nov 2009 09:26:05 -0800
X-Trace: forums-1-dub 1258737965 10.22.241.41 (20 Nov 2009 09:26:05 -0800)
X-Original-Trace: 20 Nov 2009 09:26:05 -0800, 10.22.241.41
Lines: 18
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28663
Article PK: 77905

I have a table
CREATE TABLE dbo.RunningTotal
(
dateCreated datetime NOT NULL,
amount numeric(10,2) NOT NULL
)

I need to write a query that outout the month,total for the
last six months based on a date range inputed
eg

Month Total
Sept 80000.00 (last six months total Sept - April)
Oct 78999.13 (Oct - May)
Nov 101666.78 (Nov - June)

Can anyone assist me in constructing this query?
Thanks


"Mark A. Parsons" <iron_horse Posted on 2009-11-20 20:30:00.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Assistance with a query
References: <4b06d12d.3da2.1681692777@sybase.com>
In-Reply-To: <4b06d12d.3da2.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091118-0, 11/18/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b06fc48$1@forums-1-dub>
Date: 20 Nov 2009 12:30:00 -0800
X-Trace: forums-1-dub 1258749000 10.22.241.152 (20 Nov 2009 12:30:00 -0800)
X-Original-Trace: 20 Nov 2009 12:30:00 -0800, vip152.sybase.com
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28665
Article PK: 77907

Please provide the following:

- version of ASE (select @@version); this will help determine what T-SQL capabilities are available with your version of ASE

- small set of sample of data would be preferable; insures we're all working with the same data and (hopefully)
generating the same set of results; since the dateCreated column is defined as datetime ... please provide the time
component in your sample data (or do all dateCreated values default to a time component of 12:00AM?)

- sample of a report date range and the desired results (ie, sample input, sample output)

- definition of a 'month'; for example, today is November 20th ... are you looking for a summary of amounts from May
20th through November 20th ... or are you looking for a summary of amounts from June 1st through November 20th ... or do
you have another definition for 'month' and if so, please provide an example of a 'six month' range

- your sample table has a datetime column; how do you want to handle time components? if a dateCreated value has a time
component (eg, 09:00AM, 05:45PM) ... do we truncate/ignore the time component ... do we round the date up/down based on
whether the time component is before or after 12:00PM/noon ... ???

- copy of query(s) you've worked on so far and the results you've obtained

- does a solution have to be limited to a single SELECT statement, or can a series of SQL queries be used to build the
result set?

Orett Lewis wrote:
> I have a table
> CREATE TABLE dbo.RunningTotal
> (
> dateCreated datetime NOT NULL,
> amount numeric(10,2) NOT NULL
> )
>
> I need to write a query that outout the month,total for the
> last six months based on a date range inputed
> eg
>
> Month Total
> Sept 80000.00 (last six months total Sept - April)
> Oct 78999.13 (Oct - May)
> Nov 101666.78 (Nov - June)
>
> Can anyone assist me in constructing this query?
> Thanks


Robert Densmore Posted on 2009-11-21 00:10:23.0Z
From: Robert Densmore <bdensmore@austin.rr.ignore.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Assistance with a query
Message-ID: <tkbeg59marhqmna9ievqcj95mbm0pe96ue@4ax.com>
References: <4b06d12d.3da2.1681692777@sybase.com>
X-Newsreader: Forte Agent 1.93/32.576 English (American)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 20 Nov 2009 16:10:23 -0800
X-Trace: forums-1-dub 1258762223 10.22.241.152 (20 Nov 2009 16:10:23 -0800)
X-Original-Trace: 20 Nov 2009 16:10:23 -0800, vip152.sybase.com
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28668
Article PK: 77911

If multiple queries can be used, consider putting 1 row per month into
a temp table for each month in your range:

create table #t1 (mth datetime)
go
insert into #t1 values ('20080901')
insert into #t1 values ('20081001')
insert into #t1 values ('20081101')
go
select a.mth, sum(b.amt)
from #t1 a, RunningTotal b
where b.dateCreated between dateadd(mm,-6,a.mth) and
dateadd(dd,-1,dateadd(mm,1,a.mth))
group by a.mth
go

Bob

On 20 Nov 2009 09:26:05 -0800, Orett Lewis wrote:

>I have a table
>CREATE TABLE dbo.RunningTotal
>(
> dateCreated datetime NOT NULL,
> amount numeric(10,2) NOT NULL
>)
>
>I need to write a query that outout the month,total for the
>last six months based on a date range inputed
>eg
>
>Month Total
>Sept 80000.00 (last six months total Sept - April)
>Oct 78999.13 (Oct - May)
>Nov 101666.78 (Nov - June)
>
>Can anyone assist me in constructing this query?
>Thanks