help with Counting - 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 2012 Forums

Transact-SQL (2012)

help with Counting

Author

Topic

jamiesw
Starting Member

6 Posts

Posted - 2014-07-23 : 09:32:49

Hi there i have a problem i an trying to figure out the best way to do this.

I have 3 tables that are a view that have acouple of joins

SELECT a.TargetID, a.Year_id, a.CMode, a.SC, a.UK, a.CB, a.Revision_Number, b.dept_code, c.department_Name, b.DPO, a.RecordLocked
FROM dbo.tbl_targets AS a LEFT OUTER JOIN
dbo.tbl_department_lookup AS c INNER JOIN
dbo.tbl_all_courses AS b ON c.dept_code = b.dept_code ON a.CMode = b.CMode
WHERE (a.Year_id =(SELECT year_id FROM dbo.tbl_years_lookup WHERE (current_year = 1))) AND (b.DPO = N'D') AND (b.retired = 0) AND (b.IsDeleted = 0)

That returns me

TargetID | Year_id | CMode | SC | UK | CB | Revision_Number | dept_code | department_Name | DPO | RecordLocked
1136 6 CN210/RT 44.00 44.00 44.00 1 DPTIT Information & Technology D False
1137 6 CN210/RT 44.00 44.00 44.00 2 DPTIT Information & Technology D True
1138 6 CN220/RT 5.00 81.00 81.00 4 DPTIT Information & Technology D True
1139 6 CN210/RT 45.00 48.00 35.00 3 DPTIT Information & Technology D False
1140 6 CN220/RT 9.00 11.00 13.00 5 DPTIT Information & Technology D False
1141 6 CN220/RT 9000.00 11.00 13.00 6 DPTIT Information & Technology D False

So that is my View
I then have a storedproc that uses that view.
--------------------------------------------------------------------------------------------
declare @dept nvarchar(5)
set @dept = 'DPTIT'

declare @tmpTrgTable table(TargetID int, Year_ID int, CMode nvarchar(20), SC decimal(18,2), UK decimal(18,2),
CB decimal(18,2),Revision_Number int,dept nvarchar(10),department_name nvarchar(100),recordlocked bit)

insert into @tmpTrgTable(TargetID, Year_ID, CMode,SC, UK,CB, Revision_Number,dept,department_name,recordlocked)
Select TargetID, Year_ID, CMode,SC,UK,CB, Revision_Number,dept,department_name,recordlocked
from
(SELECT ROW_NUMBER() OVER (partition by cmode order by [Revision_Number] DESC) as [RevNum],TargetID, Year_id, CMode,SC,UK,CB, Revision_Number,dept,department_name,recordlocked FROM vGetCurrentTargets) RV where (RV.RevNum=1) and (dept = @dept)

--Output the Data

select sum(SC) as SCSUM, sum(UK) as UKSUM, sum(CB) as CBSUM from @tmpTrgTable

Select TargetID, Year_id, CMode,SC,UK,CB, Revision_Number ,dept,department_name,recordlocked from @tmpTrgTable

so the first out put is
SCSUM UKSUM CBSUM
9701.50 505.00 540.00

and the second table is something like;

TargetID|Year_id|CMode| SC| UK| CB|Revision_Number|dept|department_name|recordlocked
1139 6 CN210/RT 45.00 35.00 48.00 3 DPTIT Information & Technology 0
1141 6 CN220/RT 9000.00 13.00 11.00 6 DPTIT Information & Technology 0
1105 6 CN220/FTY 5.00 5.00 5.00 1 DPTIT Information & Technology 1
1104 6 CN220/WRE 0.50 0.00 0.00 1 DPTIT Information & Technology 1

The issue I am having is when the record is locked regardless of its revision number that is the one that must be used for the CMode and not the latest revision number.
Any ideas?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-23 : 10:56:46

Please post:

1. CREATE TABLE statements to create the tables for your query
2. INSERT INTO statements to populate the tables in step 1 with test data
3. The queries you are executing against these tables (you did this)
4. The results you are getting (you did this)
5. The results you want

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources