Create a view to get latest status for each application

I would like some help creating a view that will display the latest status for each application. The lastest status should be based on CreateDt.

For example:

Table Structure:

============

Application: ApplicationID, Name, Address, City, State, Zip, etc..

ApplicationAction: ApplicationActionID, ApplicationID, Status (Ex:new, reviewed, approved, closed), CreateDt

View should display:

==============

ApplicantID, ApplicantActionID, Status, CreateDt

Example:

==========

ApplicantID=4, Name=Bob Smith, etc...

ApplicantActionID=1, ApplicantID=4, Status=New, CreatDt=1/3/20071:00

ApplicantActionID=2, ApplicantID=4, Status=Reviewed, CreatDt=1/3/2007 2:00

ApplicantActionID=3, ApplicantID=4, Status=Approved, CreatDt=1/4/2007 1:00

... etc...

View should return:

Applicant=4, ApplicantActionID=3, Status=Approved, CreatDt=1/4/2007 1:00

etc...

Hint: Use MAX(CreatDt) to get the information you need.|||

well I got that far, I need help getting beyond that:( Any ideas?

|||Can you post what you have so far?|||

Oh well..

Declare @.ApTable (ApplicationIDint ,Name varchar(50), Addressvarchar(50), Cityvarchar(50), Statevarchar(50), Zipvarchar(50))Declare @.AATable (ApplicationActionIDint, ApplicationIDint, Statusvarchar(50), CreateDtdatetime)Insert into @.ApSELECt 4,'Bob Smith','123 street','SomeCity','SS','12345'UNIONALLSELECT 5,'New Smith','sss','ss','a','4455'Insert into @.AASELECT 1,4,'New','1/3/2007 1:00'UNIONALLSELECT 2,4,'Reviewed','1/3/2007 2:00'UNIONALLSELECT 3,4,'Approved','1/4/2007 1:00'UNIONALLSELECT 1,5,'New','5/24/2006 1:00'Select *from @.ApSelect *from @.AASELECT aa.*FROM (select applicationid,max(Createdt)as MxCreateDtfrom @.AAgroup by applicationid) Xjoin @.AA AAon aa.createdt = X.MXcreatedt

create a script to display all the rows of the view

I have a view that has a link to 3 tables.
I need to display all the rows of the view.
here is the code for the view.
USE Aromatherapy
GO
if exists (select name from sysobjects
where name = 'Oils_Cautions_View' and type
= 'V')
DROP VIEW
Oils_Cautions_View
go
CREATE VIEW Oils_Cautions_View AS
SELECT o.oilID, oilName,
Description FROM Oils AS o, Cautions as c, OilCautions as
oc
Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
I like to thank you ahead for the help
Thank you
Cristianselect * from Oils_Cautions_View
"Cristian" <vitanc@.hotmail.com> wrote in message
news:0c9901c392a4$2903c2b0$a001280a@.phx.gbl...
> I have a view that has a link to 3 tables.
> I need to display all the rows of the view.
> here is the code for the view.
> USE Aromatherapy
> GO
> if exists (select name from sysobjects
> where name = 'Oils_Cautions_View' and type
> = 'V')
> DROP VIEW
> Oils_Cautions_View
> go
> CREATE VIEW Oils_Cautions_View AS
> SELECT o.oilID, oilName,
> Description FROM Oils AS o, Cautions as c, OilCautions as
> oc
> Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
> I like to thank you ahead for the help
> Thank you
> Cristian
>|||1. Create DB Links OLE DB SQL Links for all the 3 servers.
say:
DBLINK1 points to Server1.employee
DBLINK2 points to Server2.employee
DBLINK3 points to Server3.employee
create view v_employee as
select * from dblink1...employee
union
select * from dblink2...employee
union
select * from dblink3...employee
go
select * from v_employee
There is no need for 'Hardcoding' the db servername.
If server needs to changed, you change it at the Link creation time.

CR Ver 10 String to Memo Field

How can I convert a String Field to a Memo Field so I can display more then 255 characters on my report? I've tried the "Can Grow" option and it's not working.......After setting the can grow option, did you increase the field height?

CR again

i want the detail to be display is 3 record per page for the same trans_code..

the format is like this :

trans_code : 1
page : 1
----
id_customer
----
1
2
3
----
total per page : 3

trans_code : 1
page : 2
----
id_customer
----
4
5
----
total per page : 2
total for trans_code : 1 is 5

i'm new at CR and i don't know the syntax that use in CR..
does anyone can help me plz?

thanks,
erickHi,

Open Section Criteria, Find New Page After event in detail section and
copy below code,

RecordNumber MOD 3=0

It will display only 3 records per page.

-Yags|||i already try that..thanks..it works..
but the problem is that it can't show the group footer..
i want to show how many records display in first page, and so on..and in last page i want to show all the records that has been display..

it make me counfuse coz i don't know the syntax that use in CR

Counting Result Set from SQL Query Before Fill()ing a DataSet

I need to display something like "Results x-y of z." The problem is, I can't figure out how to get the right value for z.

I am using SqlDataAdapter and Fill()ing a DataSet with subset x through y of the result set. How can I get the right value for z?

You are going to have to query the database to get the record number.

Select Count(OneOfYourFields) from YourTable.

You can command.executescalar to get the result

|||

Thank you for the reply.

I am using a Stored Procedure, so this makes things a bit more complicated. I know with MySQL you can use mysql_num_rows() to fetch the number of rows a query will return, without actually grabbing all the data from that query and pulling it onto the web server. Is there any similar functionality in C#/ASP.NET?

|||What ken means is that you need to run a count(*) query to retrieve the number of rows. ExecuteScalar will return the first record in the first row which is all you need since you only need the count. Then just display that number.|||I think I understood what Ken said. However, I am not just selecting from one table, I am executing a complicated Stored Procedure on multiple tables. How can I run a COUNT(*) on the Stored Procedure?|||

Hi,

As far as I know, we cannot run COUNT() on the result set of a stored procedure. The only way I think is to add some SELECT COUNT(*) for each table in the stored procedure and return the count as OUTPUT parameters.