Help writing an SQL Statement - 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

General SQL Server Forums

New to SQL Server Programming

Help writing an SQL Statement

Author

Topic

CaptainCharisma88
Starting Member

3 Posts

Posted - 2012-06-07 : 08:02:09

Hi guys Im new to using sql.

I need to develop the report to show for each course template in the system the total number of ‘pre-booked’ users on the template, sorted from highest to lowest.

How can may I right write an SQL statement to support the report.
The result of the SQL statement should show something like this (note: only fictional data below)..
Business Ethics 7
Introduction to MS Office 5
Money advice service 3
Basic French 0

Many thanks in advance

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-07 : 08:13:43

What does your data look like?

select t.template_id, sum(case when b.template_id is null then 0 else 1 end)
from templates t
left join bookings b
on t.template_id = b.template_id
group by t.template_id

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-07 : 08:13:58

Check if this works:


Select CourseTemplate, Count(Pre_booked)
From Table
Group By CourseTemplate
Order By Pre_booked Desc

If this is not what you are looking for then please post the DDL of the tables and some sample data which would show us what your requirement is.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

CaptainCharisma88
Starting Member

3 Posts

Posted - 2012-06-11 : 20:27:57

thanks for the replies guys.

these are the tables of interest. How would you go about this? I can post the decription of the tables as well if you would like.

-participant_prebooking (table with all pre-bookings for all course templates)
-e_componenttype (table with data on all templates .. for course templates and all other templates in the system)
-e_componenttype_d (this is the label(s) for the table above, as the system is multi lingual the text lables/names are always kept in an “_d” extention (standing for description)

CaptainCharisma88
Starting Member

3 Posts

Posted - 2012-06-11 : 21:28:21

also what function would i use to update the table on a daily basis? as students will be pre booking on to a course quite often. Thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources