SP 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 2008 Forums

Transact-SQL (2008)

SP question

Author

Topic

ATG
Starting Member

35 Posts

Posted - 2011-06-22 : 14:24:24

I'm not even sure if this is possible but I figure I'll give it a try anyway. I'm hoping to use this SP in a Crystal Report.

There is a table that has several fields. Two of the fields are Phasecode and Vendor. An issue we've been having is that people are not populating the Vendor field, but the Phasecode field does get populated since it is required. I'm trying to create a stored procedure that traverses all other entries based on the same Phasecodes to see if that Phasecode has an entry where a Vendor DOES exist, so that it can be applied to the entries that don't have Vendor populated.

For example:

PHASECODE VENDOR
1. 011040.0. 5660
2. 011040.0.
3. 011040.0. 5660

As you can see, entry 2 does not have the vendor entered but entries 1 and 3 do. I want to create a subreport that will look at entries 1 or 3 and grab the vendor number and apply it to entry 2. I don't want it to actually populate the field, but just to let me know that "5660" is PROBABLY the vendor that should go there. It's not 100% 1 to 1, more like 99.5%. Maybe using a temporary table?

Is it possible to create a procedure that does this?

Here is my starting point code.

ALTER PROCEDURE [dbo].[brptPMSLExposure]
(
@JCCo bCompany,
@Project bProject,
@Vendor bVendor,
@PPS bYN,
@Summary bYN
)

AS

DECLARE
@PossibleVendor int,
@PreviousVendor int,
@CurrentPhase VarChar(10),
@PreviousPhase VarChar(10)

SELECT PMSL.PMCo as [Company], PMSL.Phase as [Phase], PMSL.Amount as [Amount], PMSL.Vendor as [Vendor]

FROM PMSL

WHERE PMSL.PMCo=@JCCo and PMSL.Project=@Project and ((@Vendor=0 and (PMSL.Vendor>0 or PMSL.Vendor is null)) or (not(@Vendor is null) and PMSL.Vendor=@Vendor))

ORDER BY PMSL.Phase

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-22 : 14:55:15

You can assign the non-null value (and the maximum of the non-null values if there are multiple non-null values) using a windowing function such as this:


select
   PHASECODE,
   MAX(VENDOR) OVER (PARTITION BY PHASECODE) as VENDOR
FROM
   YourTable

ATG
Starting Member

35 Posts

Posted - 2011-06-22 : 17:51:44

That worked beautifully! Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources