October 22, 2009 at 9:04 am
I have written a query but I'm not sure how to handle the version_number field. When a record gets updated a new row is written and the version_number gets incremented. I need to add a clause that says if there is more than one bp_id exists then ignore. I can't say version_number > 1 because there are two separate rows. Ideas?
example:
bp_version_id version_number bp_id title description
520 1 540 some title some description
972 2 540 some title some description
Currently my query grabs the first row because it meets all conditions but I don't want the first row because the bp_id has a newer version that doesn't meet the conditions
QUERY:
SELECT DISTINCT
d.dow_name,
'http://mspweb02/bestpractice/bp_read.aspx?bp_version_id=' + CONVERT(varchar,b.bp_version_id) AS [EventHyperlink],
b.bp_version_id,
b.effective_date,
b.title, u.firstname + ' ' + u.lastname AS Steward,
case b.status_code_id WHEN 4 THEN 'Draft' END AS Status,
a.action_description AS 'History Status'
FROM
dbo.best_practice_version AS B INNER JOIN
dbo.action AS A ON b.bp_version_id = a.bp_version_id
INNER JOIN dbo.best_practice AS BP ON BP.bp_id = b.bp_id
INNER JOIN dbo.steward_og_dow AS S ON S.steward_og_id = b.steward_og_id
INNER JOIN dbo.ad_user AS U ON U.ad_user_id = S.ad_user_id
INNER JOIN dbo.division_of_work AS D ON D.dow_id = S.dow_id
WHERE
b.status_code_id = 4
and a.pending_review_flag = 1
and a.action_type_id = 32
October 22, 2009 at 9:35 am
You are more likely to get a reply if you provide test data and expected results.
I, for one, do not understand what you are trying to do.
October 22, 2009 at 9:43 am
Hope this helps. I only want row one if row two doesn't exist. The second column is the version number. So if there is a version_number >1 then I don't want to return anything.
520 1 540 4 7 Wind Energy - How to Analyze a Geotechnical Report & Identify Risks This IWI is designed to help estimators get a better understanding of the Geotech Report and identify any possible risks which may affect the project. An RFP may include a preliminary Geotech Report or none at all. 2007-10-04 00:00:00.000 2007-10-04 14:36:05.833 1501 2007-10-04 14:36:05.833 1501 11 693 0
972 2 540 5 7 Wind Energy - How to Analyze a Geotechnical Report & Identify Risks This IWI is designed to help estimators get a better understanding of the Geotech Report and identify any possible risks which may affect the project. An RFP may include a preliminary Geotech Report or none at all. 2008-12-30 00:00:00.000 2008-12-30 07:50:42.920 392 2008-12-30 07:50:42.920 392 11 693 0
October 22, 2009 at 10:03 am
If you want the most recent version for a given bp_id try the ROW_NUMBER() approach.
As you have not given test data, I do not know which table bp_id is in.
(Add the appropiate alias after PARTITION BY)
SELECT DISTINCT dow_name, EventHyperlink, bp_version_id, effective_date, Steward
,[Status], HistoryStatus
FROM
(
SELECT --DISTINCT
d.dow_name,
'http://mspweb02/bestpractice/bp_read.aspx?bp_version_id=' + CONVERT(varchar(50),b.bp_version_id) AS [EventHyperlink],
b.bp_version_id,
b.effective_date,
b.title, u.firstname + ' ' + u.lastname AS Steward,
CASE b.status_code_id WHEN 4 THEN 'Draft' END AS [Status],
a.action_description AS HistoryStatus
,ROW_NUMBER() OVER (PARTITION BY bp_id ORDER BY version_number DESC) AS RowNum
FROM dbo.best_practice_version AS B
JOIN dbo.action AS A
ON b.bp_version_id = a.bp_version_id
JOIN dbo.best_practice AS BP
ON BP.bp_id = b.bp_id
JOIN dbo.steward_og_dow AS S
ON S.steward_og_id = b.steward_og_id
JOIN dbo.ad_user AS U
ON U.ad_user_id = S.ad_user_id
JOIN dbo.division_of_work AS D
ON D.dow_id = S.dow_id
WHERE b.status_code_id = 4
AND a.pending_review_flag = 1
AND a.action_type_id = 32
) D
WHERE RowNum = 1
[edit] DISTINCT moved
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply