Stored Procedure to return on most recent date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure to return on most recent date

Author

Topic

ccomstock2
Starting Member

18 Posts

Posted - 2004-07-30 : 11:21:37

I need a solution to fix a problem with a current sproc I have now which is:

SELECT DISTINCT officeID, officeName, loginFullName, companyName, companyAddress1, companyAddress2, companyAddress3, companyCity, companyState, companyZip, companyCountry, ctryID
FROM tblOffices, tblLogins WHERE tblLogins.officeNumber=tblOffices.officeID AND tblOffices.customerID=@customerID ORDER BY tblOffices.officeName

The problem it doesn't return the name of the most recent user associated with that office because there are multiple users associated with an office, some are disabled. All the login information is in tblLogins where userID is the pk and there is a field loginDate which should be the top name I need. Here is a sproc I have run to get a list of just the offices:

select DISTINCT officeID, officeName, companyName, companyAddress1,
companyAddress2, companyAddress3, companyCity, companyState, companyZip, companyCountry, ctryID
from tblOffices where officeID in
(Select distinct officeID from tblLogins where disable=0)
and customerID=@customerID
and officeName <> ''

But it is lacking the current user information.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-30 : 11:41:10

Post your table schema (in the form of CREATE TABLE statements), and some sample data (in the form of INSERT statements). Someone will be able to help you quicker then.

ccomstock2
Starting Member

18 Posts

Posted - 2004-07-30 : 12:01:05

CREATE TABLE [tblLogins] (
[loginUsername] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[loginPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[loginStatus] [int] NULL ,
[loginFullName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[currentBalance] [float] NULL ,
[officeNumber] [int] NULL ,
[loginEmail] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[customerID] [int] NULL ,
[isChannelPartner] [bit] NULL ,
[userID] [int] IDENTITY (1, 1) NOT NULL ,
[loginDate] [datetime] NULL CONSTRAINT [DF_tblLogins_loginDate] DEFAULT (getdate()),
[doneAnalysis] [bit] NULL CONSTRAINT [DF_tblLogins_doneAnalysis] DEFAULT (0),
[disable] [bit] NULL CONSTRAINT [DF_tblLogins_disable] DEFAULT (0),
CONSTRAINT [PK_tblLogins] PRIMARY KEY CLUSTERED
(
[userID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_tblLogins_tblCustomers] FOREIGN KEY
(
[customerID]
) REFERENCES [tblCustomers] (
[customerID]
),
CONSTRAINT [FK_tblLogins_tblOffices] FOREIGN KEY
(
[officeNumber]
) REFERENCES [tblOffices] (
[officeID]
) ON DELETE CASCADE
) ON [PRIMARY]

CREATE TABLE [tblOffices] (
[officeID] [int] IDENTITY (1, 1) NOT NULL ,
[officeName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[companyName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[companyAddress1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[companyAddress2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[companyAddress3] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[companyCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[companyState] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[companyZip] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[companyCountry] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[companyPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[companyFax] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[customerID] [int] NULL ,
[projectNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ctryID] [int] NULL ,
CONSTRAINT [PK_tblOffices] PRIMARY KEY CLUSTERED
(
[officeID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_tblOffices_tblCustomers] FOREIGN KEY
(
[customerID]
) REFERENCES [tblCustomers] (
[customerID]
)
) ON [PRIMARY]
GO

Sample Data:
tblLogins

loginUsername loginFullName officeNumber loginEmail loginDate
sales Sales Partner 130 demo@webgistix.com 2009-09-19 00:00:00.000

Sample Data from first sproc in previous post:

'1', 'Atlanta, GA', 'Gensler', '101 Marietta St., N.W.', 'Suite 3000', ' ', 'Atlanta',
'GA', '30303', 'US', '1'

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-30 : 15:15:03

When you say you want something like "most recent user" then you typically will use a MAX() statement, either in your main SELECT with a GROUP BY, or in a subquery. However you need to define how you'll meausre "most recent". Is that based on highest userID? Latest loginDate?

You also might want to change your syntax to use the JOIN syntax within the FROM clause instead of defining your join in the WHERE clause. I find that a lot easier to read. And rather than your subquery in version 2, you might the technique in my article here to be helpful.

If you want further assistance, could you also provide a set of INSERT statements to go with the DDL you posted previously, and identify what the result set should be?

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

ccomstock2
Starting Member

18 Posts

Posted - 2004-07-30 : 16:00:03

I found a viable solution :

select DISTINCT officeID, officeName, companyName, companyAddress1,
companyAddress2, companyAddress3, companyCity, companyState, companyZip, companyCountry, ctryID, loginFullName
from tblOffices inner join tblLogins on tblOffices.officeID=tblLogins.officeNumber where userID in
(select max(userID) from tblLogins where customerID=16 group by officeNumber)
and tblOffices.customerID=@customerID
and officeName <> ''
order by officeName

Thanks for the help. I was making it harder then it needed to be.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources