Query to see items completed - 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 2005 Forums

Transact-SQL (2005)

Query to see items completed

Author

Topic

drdream
Starting Member

29 Posts

Posted - 2010-04-14 : 14:30:04

Hello I have a table that logs sections complete of a training application. I am logging the sectionID. To see if the Category is complete I am trying to count the number of sections vs the number of sections complete. I have both queries working but and stuck on how to make them work together.

This correctly displays a table with a Column indicating if the section in the category is complete. But to do it this way I would have to keep updating that column, I would like to count the sections automatically by counting the number of sections complete vs the total number of sections.


     SELECT 
		T.TrainingCategoryID, 
		T.TrainingCategoryName, 
		T.TrainingCategoryImageOn, 
		T.TrainingCategoryImageOff, 
                C.StatusCode        
     FROM 
		TrainingCategories T
 
     LEFT OUTER JOIN UserCategoryLog C on C.CategoryID = T.TrainingCategoryID

     Where 
     C.EmployeeID = @EmployeeID     
     OR 
     C.EmployeeID IS NULL

This correctly displays the number of sections complete vs the total number of sections in that category


SELECT Count(U.SectionID) as UserSectionsComplete, Count(T.SectionID) as TotalSections FROM TrainingSections T
LEFT OUTER JOIN dbo.UserSectionLog U
ON U.SectionID = T.SectionID

Where 
(EmployeeID = '1234567' And T.CategoryID=3)
OR 
(U.EmployeeID IS NULL AND T.CategoryID=3)

Im just trying to make them work together (Ignoring the StatusCode column)

Sachin.Nand

2937 Posts

Posted - 2010-04-14 : 14:36:20

Maybe this


SELECT T.TrainingCategoryID,Count(U.SectionID)over(partition by T.TrainingCategoryID) as UserSectionsComplete, Count(T.SectionID)over(partition by T.TrainingCategoryID) as TotalSections FROM TrainingSections T
LEFT OUTER JOIN dbo.UserSectionLog U
ON U.SectionID = T.SectionID

Where 
(EmployeeID = '1234567' And T.CategoryID=3)
OR 
(U.EmployeeID IS NULL AND T.CategoryID=3)
group by T.TrainingCategoryID

PBUH

drdream
Starting Member

29 Posts

Posted - 2010-04-14 : 14:48:32

Hmm Looks like that query only returns one row. I'll try to be more clear.

I would like the output to look like this per EmployeeID


CategoryName    SectionComplete 
------------------------------------
Intro Section	1
Math Section	1
Science Section	NULL
Biology	      NULL
Quiz	            NULL

This is what the first query does..

So pseudocode would be

SELECT CategoryName,
(Count(TotalSectionInThisCategory) = Count(SectionsCompletedByThisEmployee)) AS SectionComplete
from TrainingCategories Where

Where EmployeeID='1234567'
AND
CategoryID = 2

Sachin.Nand

2937 Posts

Posted - 2010-04-14 : 14:58:25

what is your table structure?
Post some sample data and output or else one will be just taking wild guesses.

PBUH

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-14 : 15:11:15

WHERE COLNAME IS NOT NULL will do the trick, also squish = SELECT DISTINCT

quote:
Originally posted by drdream

Hello I have a table that logs sections complete of a training application. I am logging the sectionID. To see if the Category is complete I am trying to count the number of sections vs the number of sections complete. I have both queries working but and stuck on how to make them work together.

This correctly displays a table with a Column indicating if the section in the category is complete. But to do it this way I would have to keep updating that column, I would like to count the sections automatically by counting the number of sections complete vs the total number of sections.

SELECT
T.TrainingCategoryID,
T.TrainingCategoryName,
T.TrainingCategoryImageOn,
T.TrainingCategoryImageOff,
C.StatusCode
FROM
TrainingCategories T

LEFT OUTER JOIN UserCategoryLog C on C.CategoryID = T.TrainingCategoryID

Where
C.EmployeeID = @EmployeeID
OR
C.EmployeeID IS NULL

This correctly displays the number of sections complete vs the total number of sections in that category

SELECT Count(U.SectionID) as UserSectionsComplete, Count(T.SectionID) as TotalSections FROM TrainingSections T
LEFT OUTER JOIN dbo.UserSectionLog U
ON U.SectionID = T.SectionID

Where
(EmployeeID = '1234567' And T.CategoryID=3)
OR
(U.EmployeeID IS NULL AND T.CategoryID=3)

Im just trying to make them work together (Ignoring the StatusCode column)

drdream
Starting Member

29 Posts

Posted - 2010-04-14 : 15:12:08

Table TrainingSections
CategoryID
SectionID
SectionName


CategoryID     SectionID     SectionName
1              1             Introduction 1
1              2             Introduction 2
2              3             Math 1
2              4             Math 2
3              5             Science 1
3              6             Science 2
3              7             Science 3

Table TrainingCategories
TrainingCategoryID (Joins to CategoryID Above)
TrainingCategoryName


TrainingCategoryID     TrainingCategoryName
--------------------------------------------
1                      Introduction
2                      Math Category
3                      Science Category

Table UserSectionLog
EntryID
EmployeeID
SectionID
SectionComplete (bool)


EmployeeID     SectionID     SectionComplete
---------------------------------------------
1234567        1             True
1234567        2             True
1234567        3             True

(This table only logs when section complete, there will not be any other rows if they did not complete the section)

Wanted Output

CategoryName CategoryComplete
---------------------------------
Introduction True
Math True
Science False

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-14 : 15:55:30

[code]
select TRAININGCATEGORYNAME, CategoryComplete from
(select
distinct TrainingCategoryName, COALESCE(SectionComplete,'False') as CategoryComplete, rownumber() over(partition by TrainingCategoryName
order by COALESCE(SectionComplete, '0')desc) rnk
from
TrainingSections a
INNER JOIN TrainingCategories b
on a.CategoryID = b.TrainingCategoryID
LEFT OUTER JOIN UserSectionLog c
on c.SectionID = a.SectionID AND c.EMPLOYEEID = 1234567
)t
where rnk = 1
order by TRAININGCATEGORYNAME[/code]

quote:
Originally posted by drdream

Table TrainingSections
CategoryID
SectionID
SectionName
[code]
CategoryID SectionID SectionName
1 1 Introduction 1
1 2 Introduction 2
2 3 Math 1
2 4 Math 2
3 5 Science 1
3 6 Science 2
3 7 Science 3
[/code]

Table TrainingCategories
TrainingCategoryID (Joins to CategoryID Above)
TrainingCategoryName

[code]
TrainingCategoryID TrainingCategoryName
--------------------------------------------
1 Introduction
2 Math Category
3 Science Category
[/code]
Table UserSectionLog
EntryID
EmployeeID
SectionID
SectionComplete (bool)

[code]
EmployeeID SectionID SectionComplete
---------------------------------------------
1234567 1 True
1234567 2 True
1234567 3 True
[/code]
(This table only logs when section complete, there will not be any other rows if they did not complete the section)

Wanted Output

CategoryName CategoryComplete
---------------------------------
Introduction True
Math True
Science False

Sachin.Nand

2937 Posts

Posted - 2010-04-14 : 16:02:53

Hopefully someone comes up with a much elegant solution.


declare @TrainingSections as table(CategoryID int,SectionID int,SectionName varchar(45))
insert into @TrainingSections
select 1,1,'Introduction 1' union all
select 1,2,'Introduction 2'union all
select 2,3,'Math 1'union all
select 2,4,'Math 2'union all
select 3,5, 'Science 1'union all
select 3,6,'Science 2'union all
select 3,7,'Science 3'

select * from @TrainingSections

declare @TrainingCategories as table(TrainingCategoryID int,TrainingCategoryName varchar(45))
insert into @TrainingCategories
select 1, 'Introduction' union all
select 2,'Math Category' union all
select 3,'Science Category'

select * from @TrainingCategories

declare @UserSectionLog as table(EmployeeID int,SectionID int,SectionComplete varchar(45))
insert into @UserSectionLog
select 1234567,1,'True' union all
select 1234567,2,'True'union all
select 1234567,3,'True' union all
select 1234567,5,'True'  union all
select 1234567,7,'True' 

select * from @UserSectionLog

select TrainingCategoryName,min(case when id=0 then 'True' else 'False' end)as  CategoryComplete from
(

select TrainingCategoryName,empid,sum(case when empid=1 then 1 else 0 end)id from

		(
			select distinct TrainingCategoryName,COUNT(TS.sectionid)over(partition by TrainingCategoryName)as catid,isnull(EmployeeID,1)as empid from @TrainingSections TS
			inner join @TrainingCategories TC  on TC.TrainingCategoryID=TS.CategoryID
			left join @UserSectionLog UL on UL.SectionID=TS.SectionID
			group by Ts.sectionid,TrainingCategoryName,EmployeeID
		)t group by TrainingCategoryName,empid
	
)t1

group by TrainingCategoryName

PBUH

drdream
Starting Member

29 Posts

Posted - 2010-04-14 : 16:27:29

Thanks so much for your time but we are trying not to use the SectionComplete flag. It would be easy if we used that flag but i'm trying to count the sections complete vs total sections

quote:
Originally posted by hanbingl

select TRAININGCATEGORYNAME, CategoryComplete from
(select
distinct TrainingCategoryName, COALESCE(SectionComplete,'False') as CategoryComplete, rownumber() over(partition by TrainingCategoryName
order by COALESCE(SectionComplete, '0')desc) rnk
from
TrainingSections a
INNER JOIN TrainingCategories b
on a.CategoryID = b.TrainingCategoryID
LEFT OUTER JOIN UserSectionLog c
on c.SectionID = a.SectionID AND c.EMPLOYEEID = 1234567
)t
where rnk = 1
order by TRAININGCATEGORYNAME

quote:
Originally posted by drdream

Table TrainingSections
CategoryID
SectionID
SectionName

CategoryID SectionID SectionName
1 1 Introduction 1
1 2 Introduction 2
2 3 Math 1
2 4 Math 2
3 5 Science 1
3 6 Science 2
3 7 Science 3

Table TrainingCategories
TrainingCategoryID (Joins to CategoryID Above)
TrainingCategoryName

TrainingCategoryID TrainingCategoryName
--------------------------------------------
1 Introduction
2 Math Category
3 Science Category

Table UserSectionLog
EntryID
EmployeeID
SectionID
SectionComplete (bool)

EmployeeID SectionID SectionComplete
---------------------------------------------
1234567 1 True
1234567 2 True
1234567 3 True

(This table only logs when section complete, there will not be any other rows if they did not complete the section)

Wanted Output

CategoryName CategoryComplete
---------------------------------
Introduction True
Math True
Science False

drdream
Starting Member

29 Posts

Posted - 2010-04-14 : 16:44:29

I was able to get this one to work (Some pretty crazy stuff man!) with some minor adjustments (EmployeeID is varchar) Just trying to sort on CategoryID



select TrainingCategoryName,min(case when id=0 then 'False' else 'True' end)as  CategoryComplete from
(

select TrainingCategoryName,empid,sum(case when empid='1234567' then 1 else 0 end)id from

		(
			select distinct TrainingCategoryName,COUNT(TS.sectionid)over(partition by TrainingCategoryName)as catid,ISNULL(EmployeeID, '1') as empid from TrainingSections TS
			inner join TrainingCategories TC  on TC.TrainingCategoryID=TS.CategoryID
			left join UserSectionLog UL on UL.SectionID=TS.SectionID
            
			group by Ts.sectionid,TrainingCategoryName,EmployeeID
		)t group by TrainingCategoryName,empid
	
)t1

group by TrainingCategoryName

quote:

Originally posted by Idera

Hopefully someone comes up with a much elegant solution.


declare @TrainingSections as table(CategoryID int,SectionID int,SectionName varchar(45))
insert into @TrainingSections
select 1,1,'Introduction 1' union all
select 1,2,'Introduction 2'union all
select 2,3,'Math 1'union all
select 2,4,'Math 2'union all
select 3,5, 'Science 1'union all
select 3,6,'Science 2'union all
select 3,7,'Science 3'

select * from @TrainingSections

declare @TrainingCategories as table(TrainingCategoryID int,TrainingCategoryName varchar(45))
insert into @TrainingCategories
select 1, 'Introduction' union all
select 2,'Math Category' union all
select 3,'Science Category'

select * from @TrainingCategories

declare @UserSectionLog as table(EmployeeID int,SectionID int,SectionComplete varchar(45))
insert into @UserSectionLog
select 1234567,1,'True' union all
select 1234567,2,'True'union all
select 1234567,3,'True' union all
select 1234567,5,'True'  union all
select 1234567,7,'True' 

select * from @UserSectionLog

select TrainingCategoryName,min(case when id=0 then 'True' else 'False' end)as  CategoryComplete from
(

select TrainingCategoryName,empid,sum(case when empid=1 then 1 else 0 end)id from

		(
			select distinct TrainingCategoryName,COUNT(TS.sectionid)over(partition by TrainingCategoryName)as catid,isnull(EmployeeID,1)as empid from @TrainingSections TS
			inner join @TrainingCategories TC  on TC.TrainingCategoryID=TS.CategoryID
			left join @UserSectionLog UL on UL.SectionID=TS.SectionID
			group by Ts.sectionid,TrainingCategoryName,EmployeeID
		)t group by TrainingCategoryName,empid
	
)t1

group by TrainingCategoryName

PBUH

drdream
Starting Member

29 Posts

Posted - 2010-04-14 : 16:54:23

quote:
Originally posted by hanbingl

WHERE COLNAME IS NOT NULL will do the trick

And after all is said and done, this was the icing on the cake

Where
C.EmployeeID = @EmployeeID
OR
C.EmployeeID IS NOT NULL
OR
C.EmployeeID IS NULL

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-14 : 16:59:43

that's why you have to illustrate in detail

quote:
Originally posted by drdream

quote:
Originally posted by hanbingl

WHERE COLNAME IS NOT NULL will do the trick

And after all is said and done, this was the icing on the cake

Where
C.EmployeeID = @EmployeeID
OR
C.EmployeeID IS NOT NULL
OR
C.EmployeeID IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources