SQL question - SQL Server Forums

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)

SQL question

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2005-08-26 : 08:05:59

There are 4 fields in the tblPacketStatus that are INT fields. They are:

AEStatusID
SEStatusID
CEStatusID
ACStatusID

Basically it is a progression. an "AE" starts a packet. He submits it and AEStatusID of the record turns to 2
They the SE will see that the AE submited the packet and he opens the packet, approves it and submits it. The SEStatusID of the record turns to 2.
Same thing with the CE, BUT this time he opens the packet and makes a change and just SAVES it. CEStatusID turns to a 5. 5 is a saved Id
The tblPacketStatusDesc is below.

OK. that I need to do it find the curent userI need to look at a record and find the statusID of 2 or 3. by the way. each StatusID has a submit/saved date field, so...

AEStatusDate
go's with
AEStatusID

Here is my current SQL I have now:

SELECT tblPacketStatus.PacketStatusID, tblPacket.PacketID, tblPacketStatus.CompanyName, tblPacket.EnteredUserID, tblPacketStatus.packetStatusDate,
tblUsers.FirstName + ' ' + tblUsers.LastName AS repName, tblPacketStatusDesc.statusDesc, tblPacketStatusDesc.statusColor,
tblPacketStatus.SEStatusDate, tblPacketStatus.CSStatusDate, tblPacketStatus.AccStatusDate
FROM tblPacketStatus INNER JOIN
tblPacket ON tblPacketStatus.PacketID = tblPacket.PacketID INNER JOIN
tblUsers ON tblPacket.EnteredUserID = tblUsers.UserID INNER JOIN
tblPacketStatusDesc ON tblPacketStatus.AEStatusID = tblPacketStatusDesc.statusDescID
WHERE (tblPacket.EnteredUserID = 542) AND (tblPacketStatus.AEStatusID = 1) OR
(tblPacketStatus.AEStatusID = 2) OR
(tblPacketStatus.AEStatusID = 3)

tblPacketStatusDesc Table

0 No Status
1 Awaiting Approval
2 Submitted - Approved
3 Submitted - Changes Made
4 Submitted - Disapproved
5 Saved

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-26 : 09:12:13

Did you get any error?
If not, post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-26 : 09:32:26

You turned a logical workflow into a physical one. Since the staus of thing can only be one at a given time, you should have only 1 column and a status code column. Read the hint link below to aid us in helping you if you are stuck with that design.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources