Query Question

[Jason MacKenzie]

I apologize if this gets posted twice. A lot of my messages are not getting
through


I could use some help with a query. I have a table called ProjectStatus
which contains the Status history of a project. I want my query to output
results in the following format:

Status MonthName Total MonthNumber
Complete Feb-2003 71 2
New Feb-2003 98 2
Outstanding Feb-2003 22 2
Complete Mar-2003 41 3
New Mar-2003 63 3
Outstanding Mar-2003 35 3
Complete Apr-2003 252 4
New Apr-2003 306 4

These results are not quite right but they give you an idea. The complete
and new totals are right but I need the outstanding status to be use this
formula:

(Last month's outstanding) + (this month's new) - (this month's completed) =
(this months outstanding)

So for example,

The outstanding for March should be: 22 + 63 - 41 = 44 (I currently get 35.
Hence this post )

In the ProjectStatus table, New is StatusID of 1 and Complete is 4

Any help is greatly appreciated,

Jason


CREATE TABLE [dbo].[ProjectStatus] (
[ProjectStatusID] [int] IDENTITY (1, 1) NOT NULL ,
[ProjectID] [numeric](10, 0) NOT NULL ,
[StatusID] [int] NOT NULL ,
[StatusDate] [datetime] NOT NULL
) ON [PRIMARY]

[Jason MacKenzie]

I should mention that I'm using a 3 queries here with the UNION clause. One
for New, Complete, and Outstanding. I only need help witht the Outstanding
one - unless of course there's a better way to do the whole thing.

Thanks,

Jason

[jobi]

select StatusID
, convert(varchar(7),statusdate,102) as yyyymm -- maybe use
datename()function if you want names be carefull with orderby
, count(*) as total
, min(datepart(mm,statusdate) as monthnr)
from projectstatus
group by statusid, convert(varchar(7),statusdate,102)
order by yyyymm desc


jobi
CREATE TABLE [dbo].[ProjectStatus] (

> [ProjectStatusID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProjectID] [numeric](10, 0) NOT NULL ,
> [StatusID] [int] NOT NULL ,
> [StatusDate] [datetime] NOT NULL

"Jason MacKenzie" <jmackenzie_nospam@formet.com> wrote in message
news:Oz4tIcjQDHA.3144@tk2msftngp13.phx.gbl...

> I apologize if this gets posted twice. A lot of my messages are not

getting

> through
>
>
> I could use some help with a query. I have a table called ProjectStatus
> which contains the Status history of a project. I want my query to output
> results in the following format:
>
> Status MonthName Total MonthNumber
> Complete Feb-2003 71 2
> New Feb-2003 98 2
> Outstanding Feb-2003 22 2
> Complete Mar-2003 41 3
> New Mar-2003 63 3
> Outstanding Mar-2003 35 3
> Complete Apr-2003 252 4
> New Apr-2003 306 4
>
> These results are not quite right but they give you an idea. The complete
> and new totals are right but I need the outstanding status to be use this
> formula:
>
> (Last month's outstanding) + (this month's new) - (this month's completed)

=

> (this months outstanding)
>
> So for example,
>
> The outstanding for March should be: 22 + 63 - 41 = 44 (I currently get

35.

> Hence this post )
>
> In the ProjectStatus table, New is StatusID of 1 and Complete is 4
>
> Any help is greatly appreciated,
>
> Jason
>
>
> CREATE TABLE [dbo].[ProjectStatus] (
> [ProjectStatusID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProjectID] [numeric](10, 0) NOT NULL ,
> [StatusID] [int] NOT NULL ,
> [StatusDate] [datetime] NOT NULL
> ) ON [PRIMARY]
>
>

[Jason MacKenzie]

Hi Jobi,

Thanks for the response. I'm not sure that that answers my question though.

All I need is this:

(Last month's outstanding) + (this month's new) - (this month's completed)
= (this months outstanding)

I consider a completed project to be one who's record with the
max(projectstatusid) has a statusid of 4. In other words, the last status is
4.

If this is my current result set with the Outstanding numbers being
incorrect.

Status MonthName Total MonthNumber
Complete Feb-2003 71 2
New Feb-2003 98 2
Outstanding Feb-2003 22 2
Complete Mar-2003 41 3
New Mar-2003 63 3
Outstanding Mar-2003 35 3


What I need from this query is:

Status MonthName Total MonthNumber
Outstanding Feb-2003 27 2
Outstanding Mar-2003 49 3

The 49 would be 27 + 63-41




"jobi" <jobi@reply2.group> wrote in message
news:be43a4$218$1@reader08.wxs.nl...

> select StatusID
> , convert(varchar(7),statusdate,102) as yyyymm -- maybe use
> datename()function if you want names be carefull with orderby
> , count(*) as total
> , min(datepart(mm,statusdate) as monthnr)
> from projectstatus
> group by statusid, convert(varchar(7),statusdate,102)
> order by yyyymm desc
>
>
> jobi
> CREATE TABLE [dbo].[ProjectStatus] (

> > [ProjectStatusID] [int] IDENTITY (1, 1) NOT NULL ,
> > [ProjectID] [numeric](10, 0) NOT NULL ,
> > [StatusID] [int] NOT NULL ,
> > [StatusDate] [datetime] NOT NULL

>
>
> "Jason MacKenzie" <jmackenzie_nospam@formet.com> wrote in message
> news:Oz4tIcjQDHA.3144@tk2msftngp13.phx.gbl...

> > I apologize if this gets posted twice. A lot of my messages are not

> getting

> > through
> >
> >
> > I could use some help with a query. I have a table called ProjectStatus
> > which contains the Status history of a project. I want my query to

output

> > results in the following format:
> >
> > Status MonthName Total MonthNumber
> > Complete Feb-2003 71 2
> > New Feb-2003 98 2
> > Outstanding Feb-2003 22 2
> > Complete Mar-2003 41 3
> > New Mar-2003 63 3
> > Outstanding Mar-2003 35 3
> > Complete Apr-2003 252 4
> > New Apr-2003 306 4
> >
> > These results are not quite right but they give you an idea. The

complete

> > and new totals are right but I need the outstanding status to be use

this

> > formula:
> >
> > (Last month's outstanding) + (this month's new) - (this month's

completed)

> =

> > (this months outstanding)
> >
> > So for example,
> >
> > The outstanding for March should be: 22 + 63 - 41 = 44 (I currently get

> 35.

> > Hence this post )
> >
> > In the ProjectStatus table, New is StatusID of 1 and Complete is 4
> >
> > Any help is greatly appreciated,
> >
> > Jason
> >
> >
> > CREATE TABLE [dbo].[ProjectStatus] (
> > [ProjectStatusID] [int] IDENTITY (1, 1) NOT NULL ,
> > [ProjectID] [numeric](10, 0) NOT NULL ,
> > [StatusID] [int] NOT NULL ,
> > [StatusDate] [datetime] NOT NULL
> > ) ON [PRIMARY]
> >
> >

>
>

[jobi]

Maybe looking for "running totals" might put you on the right track.

In this query I assume that the three statusses are known data.

select 'NewOutstandinig' as Status
, convert(datetime,yyyymm,102) as dtYearMonth
, total_new + total_outstanding - total_complete as Total
, monthnr as MonthNumber
from (
select
, convert(int,statusdate,102) as yyyymm
, case StatusID
when 'new' then 1
else 0
end as total_new
, case StatusID
when 'outstanding' then 1
else 0
end as total_outstanding
, case StatusID
when 'complete' then 1
else 0
end as total_complete
, min(datepart(mm,statusdate)) as monthnr
from projectstatus
group by convert(int,statusdate,102)

) t1

order by dtYearMonth desc


jobi

"Jason MacKenzie" <jmackenzie_nospam@formet.com> wrote in message
news:uJZ4PqjQDHA.4024@tk2msftngp13.phx.gbl...

> Hi Jobi,
>
> Thanks for the response. I'm not sure that that answers my question

though.

>
> All I need is this:
>
> (Last month's outstanding) + (this month's new) - (this month's

completed)

> = (this months outstanding)
>
> I consider a completed project to be one who's record with the
> max(projectstatusid) has a statusid of 4. In other words, the last status

is

> 4.
>
> If this is my current result set with the Outstanding numbers being
> incorrect.
>
> Status MonthName Total MonthNumber
> Complete Feb-2003 71 2
> New Feb-2003 98 2
> Outstanding Feb-2003 22 2
> Complete Mar-2003 41 3
> New Mar-2003 63 3
> Outstanding Mar-2003 35 3
>
>
> What I need from this query is:
>
> Status MonthName Total MonthNumber
> Outstanding Feb-2003 27 2
> Outstanding Mar-2003 49 3
>
> The 49 would be 27 + 63-41
>
>
>
>
> "jobi" <jobi@reply2.group> wrote in message
> news:be43a4$218$1@reader08.wxs.nl...

> > select StatusID
> > , convert(varchar(7),statusdate,102) as yyyymm -- maybe use
> > datename()function if you want names be carefull with orderby
> > , count(*) as total
> > , min(datepart(mm,statusdate) as monthnr)
> > from projectstatus
> > group by statusid, convert(varchar(7),statusdate,102)
> > order by yyyymm desc
> >
> >
> > jobi
> > CREATE TABLE [dbo].[ProjectStatus] (

> > > [ProjectStatusID] [int] IDENTITY (1, 1) NOT NULL ,
> > > [ProjectID] [numeric](10, 0) NOT NULL ,
> > > [StatusID] [int] NOT NULL ,
> > > [StatusDate] [datetime] NOT NULL

> >
> >
> > "Jason MacKenzie" <jmackenzie_nospam@formet.com> wrote in message
> > news:Oz4tIcjQDHA.3144@tk2msftngp13.phx.gbl...

> > > I apologize if this gets posted twice. A lot of my messages are not

> > getting

> > > through
> > >
> > >
> > > I could use some help with a query. I have a table called

ProjectStatus

> > > which contains the Status history of a project. I want my query to

> output

> > > results in the following format:
> > >
> > > Status MonthName Total MonthNumber
> > > Complete Feb-2003 71 2
> > > New Feb-2003 98 2
> > > Outstanding Feb-2003 22 2
> > > Complete Mar-2003 41 3
> > > New Mar-2003 63 3
> > > Outstanding Mar-2003 35 3
> > > Complete Apr-2003 252 4
> > > New Apr-2003 306 4
> > >
> > > These results are not quite right but they give you an idea. The

> complete

> > > and new totals are right but I need the outstanding status to be use

> this

> > > formula:
> > >
> > > (Last month's outstanding) + (this month's new) - (this month's

> completed)

> > =

> > > (this months outstanding)
> > >
> > > So for example,
> > >
> > > The outstanding for March should be: 22 + 63 - 41 = 44 (I currently

get

> > 35.

> > > Hence this post )
> > >
> > > In the ProjectStatus table, New is StatusID of 1 and Complete is 4
> > >
> > > Any help is greatly appreciated,
> > >
> > > Jason
> > >
> > >
> > > CREATE TABLE [dbo].[ProjectStatus] (
> > > [ProjectStatusID] [int] IDENTITY (1, 1) NOT NULL ,
> > > [ProjectID] [numeric](10, 0) NOT NULL ,
> > > [StatusID] [int] NOT NULL ,
> > > [StatusDate] [datetime] NOT NULL
> > > ) ON [PRIMARY]
> > >
> > >

> >
> >

>
>

[Bickel]

Hai,
I have a table with 2 cols numid, subid both int
numid subid
1 2
2 1
3 1
1 3
2 4
3 8
1 5
2 5
3 9

Now i like to make a query in dbaccess wich would give my this result:
1 <--numid
2 <\
3 < --all the subid's belonging to numid 1
5 </
2 <--numid
1 <\
4 < --all the subid's belonging to numid 2
5 </
3 <--numid
1 <\
8 < --all the subid's belonging to numid 3
9 </

I tried it with union, i tried it with the set datatype via an temp
table, but i can't get it working.
Any ideas?

Ronald

[Paul Watson]

Check out cdi on groups.google.com, this has been covered in the
last couple of days

Bickel wrote:

>
> Hai,
> I have a table with 2 cols numid, subid both int
> numid subid
> 1 2
> 2 1
> 3 1
> 1 3
> 2 4
> 3 8
> 1 5
> 2 5
> 3 9
>
> Now i like to make a query in dbaccess wich would give my this result:
> 1 <--numid
> 2 <\
> 3 < --all the subid's belonging to numid 1
> 5 </
> 2 <--numid
> 1 <\
> 4 < --all the subid's belonging to numid 2
> 5 </
> 3 <--numid
> 1 <\
> 8 < --all the subid's belonging to numid 3
> 9 </
>
> I tried it with union, i tried it with the set datatype via an temp
> table, but i can't get it working.
> Any ideas?
>
> Ronald

--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
[url]www.oninit.com[/url] #

[Jonathan Leffler]

Bickel wrote:

> I have a table with 2 cols numid, subid both int
> numid subid
> 1 2
> 2 1
> 3 1
> 1 3
> 2 4
> 3 8
> 1 5
> 2 5
> 3 9
>
> Now i like to make a query in dbaccess wich would give my this result:
> 1 <--numid
> 2 <\
> 3 < --all the subid's belonging to numid 1
> 5 </
> 2 <--numid
> 1 <\
> 4 < --all the subid's belonging to numid 2
> 5 </
> 3 <--numid
> 1 <\
> 8 < --all the subid's belonging to numid 3
> 9 </
>
> I tried it with union, i tried it with the set datatype via an temp
> table, but i can't get it working.
> Any ideas?

SELECT DISTINCT numid AS numid, numid AS subid FROM table
UNION
SELECT DISTINCT numid AS numid, subid AS subid FROM table
INTO TEMP t;

SELECT subid FROM t ORDER BY numid, subid; -- IDS 9.40 only!

Everywhere else, you'd have to select numid as well as subid and
ignore the value.

--
Jonathan Leffler #include <disclaimer.h>
Email: [email]jleffler@earthlink.net[/email], [email]jleffler@us.ibm.com[/email]
Guardian of DBD::Informix v2003.04 -- [url]http://dbi.perl.org/[/url]

[Bickel]

Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<7NEsb.11218> >

>
> SELECT DISTINCT numid AS numid, numid AS subid FROM table
> UNION
> SELECT DISTINCT numid AS numid, subid AS subid FROM table
> INTO TEMP t;
>
> SELECT subid FROM t ORDER BY numid, subid; -- IDS 9.40 only!
>
> Everywhere else, you'd have to select numid as well as subid and
> ignore the value.

Sorry, but this doesn't work. i get a result of only 11 rows where i
expect 12. Also on 9.40.
As you can see here....numid 2 and 3 show 4 times, instead of 3 time.
Also ignoring the numid (or working with 9.40) are no options.
So i guess it can only be done with spl..?
numid subid
1 1
1 3
1 5
2 1
2 2
2 4
2 5
3 1
3 3
3 8
3 9

[June Hunt]

[email]bickel@zonnet.nl[/email] (Bickel) wrote:

>Jonathan Leffler <jleffler@earthlink.net> wrote in message
>news:<7NEsb.11218> >

> >
> > SELECT DISTINCT numid AS numid, numid AS subid FROM table
> > UNION
> > SELECT DISTINCT numid AS numid, subid AS subid FROM table
> > INTO TEMP t;
> >
> > SELECT subid FROM t ORDER BY numid, subid; -- IDS 9.40 only!
> >
> > Everywhere else, you'd have to select numid as well as subid and
> > ignore the value.

>
>Sorry, but this doesn't work. i get a result of only 11 rows where i
>expect 12. Also on 9.40.
>As you can see here....numid 2 and 3 show 4 times, instead of 3 time.
>Also ignoring the numid (or working with 9.40) are no options.
>So i guess it can only be done with spl..?
> numid subid
> 1 1
> 1 3
> 1 5
> 2 1
> 2 2
> 2 4
> 2 5
> 3 1
> 3 3
> 3 8
> 3 9

I was able to get the results you requested, but it isn't pretty. The test
that I ran is as follows:

create temp table testit
(numid integer,
subid integer) with no log;

insert into testit values (1, 2);
insert into testit values (2, 1);
insert into testit values (3, 1);
insert into testit values (1, 3);
insert into testit values (2, 4);
insert into testit values (3, 8);
insert into testit values (1, 5);
insert into testit values (2, 5);
insert into testit values (3, 9);

create temp table worktable
(col1 integer,
col2 integer) with no log;

insert into worktable
select unique numid * 10000 + numid, numid from testit;

insert into worktable
select numid * 10000 + subid * 100, subid from testit;

select col1 c1, col2 c2 from worktable order by col1 into temp showtable;

select c2 from showtable;

drop table worktable;
drop table showtable;
drop table testit;


With the results as in your original request:

1
2
3
5
2
1
4
5
3
1
8
9


I don't have 9.40 available for testing, so had to go another way. I used
two temp tables; the first holds a modified value to allow proper sorting,
the second column is the item that is to print. (It is why I asked for the
size of the actual values in an earlier email to you. I am guessing at a
size that will work for the sort value (e.g. 10000 and 100).) The second
temp table is simply built for the final output. I used an 'order by' when
populating the second temp table, but... I would not trust that the sort
order will always be maintained. If this is a one-time operation for you,
you might want to give it a shot (and cross your fingers that the sort order
is maintained), otherwise the SPL may be the way to go.

As I said, this isn't pretty, but it did work. I'm not clear if you have
9.40 yourself, but if so, you could try building the first temp table as I
did and select from it with the 'order by' as Jonathan suggested. I think
you are going to have to go just a little further than he suggested for the
sort. Using his example but selecting both fields from temp table t, the
sort order did not appear as in your original request. I did, however, get
all twelve results.

I hope there is something here that you can use.

--
June Hunt

__________________________________________________ _______________
MSN Shopping upgraded for the holidays! Snappier product search...
[url]http://shopping.msn.com[/url]

sending to informix-list

[Jonathan Leffler]

Bickel wrote:

> Jonathan Leffler <jleffler@earthlink.net> wrote:

>>SELECT DISTINCT numid AS numid, numid AS subid FROM table
>>UNION
>>SELECT DISTINCT numid AS numid, subid AS subid FROM table
>>INTO TEMP t;
>>
>>SELECT subid FROM t ORDER BY numid, subid; -- IDS 9.40 only!
>>
>>Everywhere else, you'd have to select numid as well as subid and
>>ignore the value.

>
>
> Sorry, but this doesn't work. i get a result of only 11 rows where i
> expect 12. Also on 9.40.
> As you can see here....numid 2 and 3 show 4 times, instead of 3 time.
> Also ignoring the numid (or working with 9.40) are no options.
> So i guess it can only be done with spl..?
> numid subid
> 1 1
> 1 3
> 1 5
> 2 1
> 2 2
> 2 4
> 2 5
> 3 1
> 3 3
> 3 8
> 3 9

OK - I see what I missed. I dunno whether it's worth rescuing what I
suggested, but you can add another column to be ignored except in the
sort phase:

SELECT DISTINCT numid AS numid, numid AS subid, 1 AS key FROM table
UNION
SELECT DISTINCT numid AS numid, subid AS subid, 2 AS key FROM table
INTO TEMP t;

SELECT subid FROM t ORDER BY numid, key, subid; -- IDS 9.40 only!

That makes sure that the 'key' columns for the numid appear before the
other values. I think that answers your query.

--
Jonathan Leffler #include <disclaimer.h>
Email: [email]jleffler@earthlink.net[/email], [email]jleffler@us.ibm.com[/email]
Guardian of DBD::Informix v2003.04 -- [url]http://dbi.perl.org/[/url]

[SSgt P]

I've got two tables, 'associates' and 'pics'. One guy in associates will often
have multiple entries in the pics table. I display each associate along with
his pics. This works great using a join in the query and grouped output on the
page. Here's my problem, how do I deal with associates who don't have an entry
in the pics table. Right now they just don't show because they don't meet the
requirements of the query. thanks Kevin

[Sameer]

Post your query please.

[SSgt P]

SELECT associates.assocID, associates.assocTgt, associates.assocName,
pics.imgID, pics.imgAssoc, pics.imgThumb, pics.imgFull FROM associates, pics
WHERE associates.assocTgt = #URL.tgtID# AND associates.assocID = pics.imgAssoc
I need a way to deal with that assocID = imgAssoc piece.

[Sameer]

Try this :-

SELECT
associates.assocID,
associates.assocTgt,
associates.assocName,
pics.imgID,
pics.imgAssoc,
pics.imgThumb,
pics.imgFull

FROM associates
LEFT JOIN pics ON associates.assocID = pics.imgAssoc AND associates.assocTgt =
#URL.tgtID#

WHERE pics.imgAssoc IS NULL

[philh]

This is a SQL Server solution, Sarge; YMMV on other platforms and, no, I'm not
deciphering Access syntax for joins. SELECT associates.assocID,
associates.assocTgt, associates.assocName, pics.imgID, pics.imgAssoc,
pics.imgThumb, pics.imgFull FROM associates LEFT OUTER JOIN pics on
associates.assocID = pics.imgAssoc WHERE associates.assocTgt = #URL.tgtID#
This query returns NULL values for the pics columns, but still returns a record
for the associate. HTH, (381SMW, 343COMPW, 831AD 11JAN1978-2FEB1987)

[theDude28]

Hi guys, I have created an update query something simple like:

UPDATE tableName
SET variable = anotherVariable
WHERE aVariable=var

I was wondering could I have made this an Insert query as when i have tried to
make this an insert query?
It seems to be having problems with the where part of the statement.

Thanks in advance.

[kyle969]

If you are adding a new record it will be an insert query, but I'm not sure I understand the question.

[Aegis Kleais]

Update MODIFIES an EXISTING record
Insert CREATES a NEW record

They cannot be interchanged; their syntax is different too

UPDATE table
SET column = value
WHERE column = value

INSERT into TABLE (columns)
VALUES (variables)

[Samall]

Hello,
Can somebody help me on this issue:

I have on DB table

ID
NAME
REFER_TO_OTHER_PERSON_ID
REFER_TO_OTHER_PERSON_ID_2

1, jack, NULL, NULL
2, bryan, NULL, NULL
3, martin, 1,NULL
4, james, NULL, 3

What I want in the output is:
JACK
--martin
----james
BRYAN

Can somebody help me on the query?
Thanks!