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.

SQL question

7 posts in General Discussion Last posting was on 2009-09-24 15:34:06.0Z
Mark Pare Posted on 2009-09-24 00:40:22.0Z
Sender: 3947.4ababf8e.1804289383@sybase.com
From: Mark Pare
Newsgroups: sybase.public.ase.general
Subject: SQL question
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ababff6.395e.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Sep 2009 17:40:22 -0700
X-Trace: forums-1-dub 1253752822 10.22.241.41 (23 Sep 2009 17:40:22 -0700)
X-Original-Trace: 23 Sep 2009 17:40:22 -0700, 10.22.241.41
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28355
Article PK: 77599

Hi, I have a table with the following data (related to
sports)

2009-10-06 H W
2009-10-09 H W
2009-10-12 A L
2009-10-14 H L
2009-10-17 H W
2009-10-21 A W
2009-10-23 A W
2009-10-27 H W
2009-10-27 H L
2009-11-01 H L
2009-11-03 A W

What I would like to do is identify streaks, so can anyone
tell me how to get the following in a single SQL statement
rather than have to loop through the data?

2009-10-06 2009-10-09 W 2
2009-10-12 2009-10-14 L 2
2009-10-17 2009-10-27 W 4
2009-10-27 2009-11-01 L 2
2009-11-03 2009-11-03 W 1


Let me know. Thanks


Robert Densmore Posted on 2009-09-24 03:32:39.0Z
From: Robert Densmore <bdensmore@austin.rr.ignore.com>
Newsgroups: sybase.public.ase.general
Subject: Re: SQL question
Message-ID: <lfplb512e8ii7h88k5pdl37pka9fh4r8oe@4ax.com>
References: <4ababff6.395e.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: 23 Sep 2009 20:32:39 -0700
X-Trace: forums-1-dub 1253763159 10.22.241.152 (23 Sep 2009 20:32:39 -0700)
X-Original-Trace: 23 Sep 2009 20:32:39 -0700, vip152.sybase.com
Lines: 73
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28356
Article PK: 77600

Here's one query, but you'll probably run into performance issues
you'll have to resolve.
Also, you are going to need a column called game number, which is a
sequential number for each game, since there's the possibility of
multiple games on a single day. The other possibility is to have the
game date include a start time. If you have that, then the below
query can be changed to use game_dt instead of game_nbr.

You may be able to rewrite the query to not have all the subqueries,
but I did not see a method easily.

Bob

select streak_start_dt = a.game_dt
,streak_end_dt =
(select max(f.game_dt)
from games f
where f.game_nbr >= a.game_nbr
and f.game_nbr < isnull((select min(g.game_nbr)
from games g
where g.win_loss != a.win_loss
and g.game_nbr > a.game_nbr),99999))

,a.win_loss
,streak =
(select count(*)
from games d
where d.game_nbr >= a.game_nbr
and d.game_nbr <
isnull((select min(e.game_nbr)
from games e
where e.win_loss != a.win_loss
and e.game_nbr > a.game_nbr),99999))
from games a
where a.win_loss !=
isnull((select b.win_loss
from games b
where b.game_nbr =
(select max(c.game_nbr)
from games c
where c.game_nbr < a.game_nbr)),space(1))

On 23 Sep 2009 17:40:22 -0700, Mark Pare wrote:

>Hi, I have a table with the following data (related to
>sports)
>
>2009-10-06 H W
>2009-10-09 H W
>2009-10-12 A L
>2009-10-14 H L
>2009-10-17 H W
>2009-10-21 A W
>2009-10-23 A W
>2009-10-27 H W
>2009-10-27 H L
>2009-11-01 H L
>2009-11-03 A W
>
>What I would like to do is identify streaks, so can anyone
>tell me how to get the following in a single SQL statement
>rather than have to loop through the data?
>
>2009-10-06 2009-10-09 W 2
>2009-10-12 2009-10-14 L 2
>2009-10-17 2009-10-27 W 4
>2009-10-27 2009-11-01 L 2
>2009-11-03 2009-11-03 W 1
>
>
>Let me know. Thanks


Mark Pare Posted on 2009-09-24 10:55:07.0Z
Sender: 3947.4ababf8e.1804289383@sybase.com
From: Mark Pare
Newsgroups: sybase.public.ase.general
Subject: Re: SQL question
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4abb500b.52ae.1681692777@sybase.com>
References: <lfplb512e8ii7h88k5pdl37pka9fh4r8oe@4ax.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Sep 2009 03:55:07 -0700
X-Trace: forums-1-dub 1253789707 10.22.241.41 (24 Sep 2009 03:55:07 -0700)
X-Original-Trace: 24 Sep 2009 03:55:07 -0700, 10.22.241.41
Lines: 83
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28358
Article PK: 77602

Hi Bob, thanks for this. Actually, there isn't a need for
game number since the query is for a single team at a time
and they can only play one game a day. I'll see if I can
re-write what you've written to not include game number, but
if you can too, that would ge great!

> Here's one query, but you'll probably run into performance
> issues you'll have to resolve.
> Also, you are going to need a column called game number,
> which is a sequential number for each game, since there's
> the possibility of multiple games on a single day. The
> other possibility is to have the game date include a start
> time. If you have that, then the below query can be
> changed to use game_dt instead of game_nbr.
>
> You may be able to rewrite the query to not have all the
> subqueries, but I did not see a method easily.
>
> Bob
>
> select streak_start_dt = a.game_dt
> ,streak_end_dt =
> (select max(f.game_dt)
> from games f
> where f.game_nbr >= a.game_nbr
> and f.game_nbr < isnull((select min(g.game_nbr)
> from games g
> where g.win_loss !=
> a.win_loss
> and g.game_nbr >
> a.game_nbr),99999))
>
> ,a.win_loss
> ,streak =
> (select count(*)
> from games d
> where d.game_nbr >= a.game_nbr
> and d.game_nbr <
> isnull((select min(e.game_nbr)
> from games e
> where e.win_loss != a.win_loss
> and e.game_nbr > a.game_nbr),99999))
> from games a
> where a.win_loss !=
> isnull((select b.win_loss
> from games b
> where b.game_nbr =
> (select max(c.game_nbr)
> from games c
> where c.game_nbr < a.game_nbr))
> ,space(1))
>
>
> On 23 Sep 2009 17:40:22 -0700, Mark Pare wrote:
>
> >Hi, I have a table with the following data (related to
> >sports)
> >
> >2009-10-06 H W
> >2009-10-09 H W
> >2009-10-12 A L
> >2009-10-14 H L
> >2009-10-17 H W
> >2009-10-21 A W
> >2009-10-23 A W
> >2009-10-27 H W
> >2009-10-27 H L
> >2009-11-01 H L
> >2009-11-03 A W
> >
> >What I would like to do is identify streaks, so can
> anyone >tell me how to get the following in a single SQL
> statement >rather than have to loop through the data?
> >
> >2009-10-06 2009-10-09 W 2
> >2009-10-12 2009-10-14 L 2
> >2009-10-17 2009-10-27 W 4
> >2009-10-27 2009-11-01 L 2
> >2009-11-03 2009-11-03 W 1
> >
> >
> >Let me know. Thanks
>


"Mark A. Parsons" <iron_horse Posted on 2009-09-24 11:38:25.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: SQL question
References: <lfplb512e8ii7h88k5pdl37pka9fh4r8oe@4ax.com> <4abb500b.52ae.1681692777@sybase.com>
In-Reply-To: <4abb500b.52ae.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090922-0, 09/22/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4abb5a31$1@forums-1-dub>
Date: 24 Sep 2009 04:38:25 -0700
X-Trace: forums-1-dub 1253792305 10.22.241.152 (24 Sep 2009 04:38:25 -0700)
X-Original-Trace: 24 Sep 2009 04:38:25 -0700, vip152.sybase.com
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28359
Article PK: 77604


Mark Pare wrote:
... snip ...
> they can only play one game a day.

... snip ...

Uhhhh, your sample data shows 2 games played on the same day (eg, baseball double header?):

>>> 2009-10-06 H W
>>> 2009-10-09 H W
>>> 2009-10-12 A L
>>> 2009-10-14 H L
>>> 2009-10-17 H W
>>> 2009-10-21 A W
>>> 2009-10-23 A W
>>> 2009-10-27 H W -- game 1 on 10/27/2009
>>> 2009-10-27 H L -- game 2 on 10/27/2009
>>> 2009-11-01 H L
>>> 2009-11-03 A W


Mark Pare Posted on 2009-09-24 15:34:06.0Z
Sender: 6042.4abb9120.1804289383@sybase.com
From: Mark Pare
Newsgroups: sybase.public.ase.general
Subject: Re: SQL question
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4abb916e.6051.1681692777@sybase.com>
References: <4abb5a31$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Sep 2009 08:34:06 -0700
X-Trace: forums-1-dub 1253806446 10.22.241.41 (24 Sep 2009 08:34:06 -0700)
X-Original-Trace: 24 Sep 2009 08:34:06 -0700, 10.22.241.41
Lines: 25
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28367
Article PK: 77610

Nice catch, that was me typing it in wrong. But Bob was
right, I did need some kind of sequential number, which I
have as gameID.

So thanks again Bob, your code worked like a charm!

> Mark Pare wrote:
> ... snip ...
> > they can only play one game a day.
> ... snip ...
>
> Uhhhh, your sample data shows 2 games played on the same
> day (eg, baseball double header?):
>
> >>> 2009-10-06 H W
> >>> 2009-10-09 H W
> >>> 2009-10-12 A L
> >>> 2009-10-14 H L
> >>> 2009-10-17 H W
> >>> 2009-10-21 A W
> >>> 2009-10-23 A W
> >>> 2009-10-27 H W -- game 1 on 10/27/2009
> >>> 2009-10-27 H L -- game 2 on 10/27/2009
> >>> 2009-11-01 H L
> >>> 2009-11-03 A W


"Mark A. Parsons" <iron_horse Posted on 2009-09-24 12:35:48.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: SQL question
References: <4ababff6.395e.1681692777@sybase.com>
In-Reply-To: <4ababff6.395e.1681692777@sybase.com>
Content-Type: multipart/mixed; boundary="------------020609000509060803000305"
X-Antivirus: avast! (VPS 090922-0, 09/22/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4abb67a4@forums-1-dub>
Date: 24 Sep 2009 05:35:48 -0700
X-Trace: forums-1-dub 1253795748 10.22.241.152 (24 Sep 2009 05:35:48 -0700)
X-Original-Trace: 24 Sep 2009 05:35:48 -0700, vip152.sybase.com
Lines: 231
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28362
Article PK: 77606

With a thorough understanding of the idiosyncrasies of application context funtions (ACFs), a couple changes to your
original data, an understanding of how function invocations are compiled/executed, and (potentially) a couple aspirin ...

--------------

ACFs

- must be running ASE version 12.5 (or better)

- ACFs are usually discussed under the heading of 'row-level access control'; we'll be using the attribute/value tuples
as global variables; the objective will be to use ACF values to keep a running track of where we are in the data

- all ACF input parameters must be of the (var)char datatype, ie, use of other datatypes requires additional
convert()/cast() function calls

- the set_appcontext() and rm_appcontext() functions return numeric values; while the numeric values are typically used
to determine function success/failure, we'll be ignoring the numbers and, where necessary, masking them by multiplying
them by 0 (zero); NOTE: This step of ignoring/masking the function return values obviously makes troubleshooting a
little harder

- in order to update/reset an ACF value you must first delete the value [rm_appcontext()] and then create
[set_appcontext()] a new attribute/value tuple containing the new value

- since ACF values are stored in memory at the session level, ACFs 'stick around' until either a) you delete them
[rm_appcontext()] or b) the session is terminated; this means that ACF values persist from one query to another, this
can lead to unexpected results if certain ACF values are not reset/removed before running a follow-on query; in this
example we'll take the easy way out and run a stand-alone rm_appcontext() invocation to reset our 'prior W/L' ACF value
prior to running our main query

--------------

data

- need to add a sequence column to distinguish between games played on the same day (eg, baseball double header)

- the sub-query making up the derived table relies on the data being read/processed in date+seq order (so that the ACFs
can keep a running track of where we are in the data); since the sub-query cannot contain an 'order by' clause it will
be necessary to insure the data is physically stored (and read) in the desired order; hence the requirement to use
allpages locking, a single partition, no parallel processing (ie, worker thread count = 1), and a clustered index on
date+seq

--------------

function invocations

- during execution of a query, if a function call does not reference a column then the dataserver typically executes the
function *once* for the entire query and then reuses the return value for all records processed by the query

- if on the other hand the function references a column from the underlying table(s), then the function will fire for
each record of data processed by the optimizer

- therefore, in order to get ACFs to fire for each record of game data we must force each ACF to reference at least one
column of the game data; this typically means performing some sort of no-op function call against a column (eg, left(wl,0))

---------------

The general idea is to perform a single pass through the game data.

As we pass through the data we'll keep track of the current W/L streak, dragging along the streak start date (for
successive Wins or Losses), and setting a new streak start date when we switch between a Win and a Loss.

The sub-query that defines the derived table generates the following result set:

start_streak end_streak wl game_count
------------ ---------- -- -----------
2009-10-06 2009-10-06 W 1
2009-10-06 2009-10-09 W 1
2009-10-12 2009-10-12 L 1
2009-10-12 2009-10-14 L 1
2009-10-17 2009-10-17 W 1
2009-10-17 2009-10-21 W 1
2009-10-17 2009-10-23 W 1
2009-10-17 2009-10-27 W 1
2009-10-27 2009-10-27 L 1
2009-10-27 2009-11-01 L 1
2009-11-03 2009-11-03 W 1

The rest of the query collapses this data into the desired result set:

Start End W/L Streak
---------- ---------- --- -----------
2009-10-06 2009-10-09 W 2
2009-10-12 2009-10-14 L 2
2009-10-17 2009-10-27 W 4
2009-10-27 2009-11-01 L 2
2009-11-03 2009-11-03 W 1

---------------

While this exercise stores the dates as strings in the form YYYY-MM-DD (this format is perfect for an ascending index
key), you could store the dates as (small)datetime data if you wish ... you'll just have to do a little reformatting of
the final result set to get the data into your YYYY-MM-DD format.

See attached file for data setup and proposed query.

Mark Pare wrote:
> Hi, I have a table with the following data (related to
> sports)
>
> 2009-10-06 H W
> 2009-10-09 H W
> 2009-10-12 A L
> 2009-10-14 H L
> 2009-10-17 H W
> 2009-10-21 A W
> 2009-10-23 A W
> 2009-10-27 H W
> 2009-10-27 H L
> 2009-11-01 H L
> 2009-11-03 A W
>
> What I would like to do is identify streaks, so can anyone
> tell me how to get the following in a single SQL statement
> rather than have to loop through the data?
>
> 2009-10-06 2009-10-09 W 2
> 2009-10-12 2009-10-14 L 2
> 2009-10-17 2009-10-27 W 4
> 2009-10-27 2009-11-01 L 2
> 2009-11-03 2009-11-03 W 1
>
>
> Let me know. Thanks

use tempdb
go

set nocount on
go

if exists(select name
from sysobjects
where type = 'U'
and name = 'games')
begin
drop table games
end
go

create table games
(dt char(10) -- game date
,seq tinyint -- distinguish between multiple
-- games on the same day (eg,
-- baseball double header)
,ha char(1) -- home/away
,wl char(1) -- win/loss
)
lock allpages
go

insert games values ('2009-10-06',1,'H','W')
insert games values ('2009-10-09',1,'H','W')
insert games values ('2009-10-12',1,'A','L')
insert games values ('2009-10-14',1,'H','L')
insert games values ('2009-10-17',1,'H','W')
insert games values ('2009-10-21',1,'A','W')
insert games values ('2009-10-23',1,'A','W')
insert games values ('2009-10-27',1,'H','W')
insert games values ('2009-10-27',2,'H','L')
insert games values ('2009-11-01',1,'H','L')
insert games values ('2009-11-03',1,'A','W')
go

-- derived table depends on data being pulled back
-- in date+seq order

create clustered index idx1 on games(dt,seq)
go

-- @dummy is used to reset our prior-wl ACF; while not really
-- needed for this particular example, the resetting of ACFs is
-- generally necessary if/when a query is re-run within a
-- single session, ie, ACFs are stored/maintained at the
-- session level so ACF values left over at the end of query
-- will be available for reuse at the beginning of a follow-on
-- query
--
-- While it is *sometimes* possible to reset ACFs within the
-- actual query, this typically involves more convoluted logic
-- which simply makes the final query much harder to understand
-- let alone debug

declare @dummy int

select @dummy = rm_appcontext('prior','wl')

select start_streak as Start,
max(end_streak) as 'End',
wl as 'W/L',
count(game_count) as Streak
from
(
select left(left('dummy',
case when isnull(get_appcontext('prior','wl'+left(wl,0)),'X') != wl

then rm_appcontext('start','dt'+left(wl,0)) +
set_appcontext('start','dt',dt) +
rm_appcontext('prior','wl'+left(wl,0)) +
set_appcontext('prior','wl',wl)
end * 0
) +
isnull(get_appcontext('start','dt'),dt),
10
) as start_streak,
dt as end_streak,
wl,
1 as game_count
from games
) dt
group by start_streak, wl
order by start_streak
go


"Mark A. Parsons" <iron_horse Posted on 2009-09-24 12:52:55.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: SQL question
References: <4ababff6.395e.1681692777@sybase.com> <4abb67a4@forums-1-dub>
In-Reply-To: <4abb67a4@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090922-0, 09/22/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4abb6ba7@forums-1-dub>
Date: 24 Sep 2009 05:52:55 -0700
X-Trace: forums-1-dub 1253796775 10.22.241.152 (24 Sep 2009 05:52:55 -0700)
X-Original-Trace: 24 Sep 2009 05:52:55 -0700, vip152.sybase.com
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28363
Article PK: 77607

And some (obvious ?) comments about the quality of the data ...

- the values placed in the seq column should insure the clustered index stores the games in the desired order; while
there's no requirement that the seq values must start with 1 and increase by 1 (eg, you could use a daily seq of 3, 17,
29, ...) you (obviously ?) don't want to randomly generate seq values on a given day (ie, first game = 9, second game =
2) else the clustered index will re-order the data thus leading to unexpected results

- you'll need to insure the wl column stores the same exact value for a Win as well as for a Loss; for exmaple, if you
mix uppercase and lowercase, and you're using case sensitive character set ordering, then 'w' and 'W' will be considered
as different thus leading to unexpected results

"Duh, Mark!" ?

Mark A. Parsons wrote:
... snip ...
> insert games values ('2009-10-06',1,'H','W')
> insert games values ('2009-10-09',1,'H','W')
> insert games values ('2009-10-12',1,'A','L')
> insert games values ('2009-10-14',1,'H','L')
> insert games values ('2009-10-17',1,'H','W')
> insert games values ('2009-10-21',1,'A','W')
> insert games values ('2009-10-23',1,'A','W')
> insert games values ('2009-10-27',1,'H','W')
> insert games values ('2009-10-27',2,'H','L')
> insert games values ('2009-11-01',1,'H','L')
> insert games values ('2009-11-03',1,'A','W')

... snip ...