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.

Crosstabs and computed expressions

2 posts in DataWindow Last posting was on 2008-05-17 13:26:07.0Z
MattN Posted on 2008-05-17 09:37:13.0Z
From: MattN <mjn1rar@gmailSPAM.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Crosstabs and computed expressions
Message-ID: <jg9t24d6c4fr05as7d2hqkd1kucv8plnli@4ax.com>
X-Newsreader: Forte Agent 4.2/32.1118
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: 17 May 2008 02:37:13 -0700
X-Trace: forums-1-dub 1211017033 10.22.241.152 (17 May 2008 02:37:13 -0700)
X-Original-Trace: 17 May 2008 02:37:13 -0700, vip152.sybase.com
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:86882
Article PK: 416140

Trying to build a crosstab to show 6 columns(last months owed amt,
last months paid amt, last month balance, current months purchased
amt, tax, current months paid amt)

So I was thinking in the SQL retrieve all rows from the previous month
till the end of the current month.

Then in the crosstabuse expressions to alter the columns to display
required effect as indicated above..

But started out using an if statement but this does not give me the
correct results..

Anyone can point me in the right direction.Or maybe I should not use a
crosstab report...

Thanks

PB11.2


"Paul Horan[TeamSybase]" Posted on 2008-05-17 13:26:07.0Z
From: "Paul Horan[TeamSybase]" <phoran AT sybase DOT com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <jg9t24d6c4fr05as7d2hqkd1kucv8plnli@4ax.com>
Subject: Re: Crosstabs and computed expressions
Lines: 53
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <482edcef$1@forums-1-dub>
Date: 17 May 2008 06:26:07 -0700
X-Trace: forums-1-dub 1211030767 10.22.241.152 (17 May 2008 06:26:07 -0700)
X-Original-Trace: 17 May 2008 06:26:07 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:86883
Article PK: 416141

Queries like this are easy to to right in the SQL itself. The trick is to
use SUM() functions, and include an expression that evaluates to 1 or 0
based on your criteria.

(pseudo-code, since I don't know your column names.)

Select
customer_id,
sum( (if month = last month then 1 else 0 endif) * owed_amt ) as
last_month_owed,
sum( (if month = last month then 1 else 0 endif) * paid_amt ) as
last_month_paid
sum( (if month = this month then 1 else 0 endif) * owed_amt ) as
cur_month_owed,
sum( (if month = this month then 1 else 0 endif) * purch_amt ) as
cur_month_purch,
sum( (if month = this month then 1 else 0 endif) * tax_amt ) as
cur_month_tax,
sum( (if month = this month then 1 else 0 endif) * paid_amt ) as
cur_month_paid
from
theTable
where month between last_month and today()
group by customer_id
order by customer_id;

Paul Horan[TeamSybase]

"MattN" <mjn1rar@gmailSPAM.com> wrote in message
news:jg9t24d6c4fr05as7d2hqkd1kucv8plnli@4ax.com...
> Trying to build a crosstab to show 6 columns(last months owed amt,
> last months paid amt, last month balance, current months purchased
> amt, tax, current months paid amt)
>
> So I was thinking in the SQL retrieve all rows from the previous month
> till the end of the current month.
>
> Then in the crosstabuse expressions to alter the columns to display
> required effect as indicated above..
>
> But started out using an if statement but this does not give me the
> correct results..
>
> Anyone can point me in the right direction.Or maybe I should not use a
> crosstab report...
>
> Thanks
>
> PB11.2
>