August 22, 2013 at 2:07 pm
Hello
I'm hoping someone can help me , I need to put together a script that will look at different rows in the data
I having problems creating a case statement that will look if a course is a A2 course and then put the enrollment figures from the previous years AS course in it.
The course data has a unique identify that identifies the course, the year it was run and if it is a AS or A2 course. and is stored like
09ASCOMP
10A2COMP (for example)
The data is being grouped by the course so the data is stored on different rows.
I hoping someone can give me some ideas with SQL inbuilt functions to use or that might help.
I'm using SQL 2012 and SSRS 2008.
August 22, 2013 at 2:24 pm
Hi and welcome to SSC. It is unfortunate that you store multiple pieces of information in a single column. This violates first normal form. At any rate we can certainly help you figure out a way to parse your data and get you the data you desire. The problem is that you haven't really posted anywhere near enough information to help much. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 22, 2013 at 2:27 pm
thanks, ill compile all that info and get back asap 🙂
thanks again
August 22, 2013 at 6:02 pm
Knives85 (8/22/2013)
HelloI'm hoping someone can help me , I need to put together a script that will look at different rows in the data
I having problems creating a case statement that will look if a course is a A2 course and then put the enrollment figures from the previous years AS course in it.
The course data has a unique identify that identifies the course, the year it was run and if it is a AS or A2 course. and is stored like
09ASCOMP
10A2COMP (for example)
The data is being grouped by the course so the data is stored on different rows.
I hoping someone can give me some ideas with SQL inbuilt functions to use or that might help.
I'm using SQL 2012 and SSRS 2008.
It sounds to me like you want to pivot the course data into columns by year using a cross tab query. Sean's signature links (the last 2) will probably get you where you need to be.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 23, 2013 at 8:23 am
Basically I have two common table expressions, one that works out the number of people that have moved from a AS course to a A2 course and one that works out the total number of people who went on these courses.
The data from the ctes looks like
cteCourseConversion
CourseNameLevelsreference AcademicYr1112
Chemistry A2 11A2CHEM 88
Chemistry AS 10ASCHEM 150
cteCourseAttendance
CourseNameLevel reference Enrollments
Chemistry A2 Level11A2CHEM 77
Chemistry AS Level10ASCHEM 122
I am then left joining these two ctes together and this is were im having the problem. essentially I need a new column that will have the 122 from 10ASCHEM on the same line as the 88 11A2CHEM, So i can then divide them by each other and get the percentage conversion from a AS to a A2 course.
I was trying to do this with a case statement but I don't believe the logic will work in this. Whatever the solution might be it needs to be summed so I can group by course name.
hopefully this problems a bit more information for someone to help me with. I'm unable to post any code as I'm still putting it together, I'm unsure what functions would be the best, etc
Thanks
I
August 23, 2013 at 8:34 am
I think you are overthinking what you need to post. This shouldn't take you hours and hours, it should take you minutes. I put together an example based on your last post. Now I realize this data is generated from a cte in your actual process but I just put them into a couple of temp tables so we have something to work with.
create table #cteCourseConversion
(
CourseName varchar(20),
Levels char(2),
reference varchar(20),
AcademicYr1112 int
)
insert #cteCourseConversion
select 'Chemistry', 'A2', '11A2CHEM', 88 union all
select 'Chemistry', 'AS', '10ASCHEM', 150
create table #cteCourseAttendance
(
CourseName varchar(20),
Level char(2),
reference varchar(20),
Enrollments int
)
insert #cteCourseAttendance
select 'Chemistry', 'A2', 'Level 11A2CHEM', 77 union all
select 'Chemistry', 'AS', 'Level 10ASCHEM', 122
I have no idea if I got the datatypes or the column separations correct. Please modify that as needed.
OK, so now we have a couple of tables with data. Based on this information what is the desired output?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 23, 2013 at 8:44 am
Essentially I need the enrollment data from the previous years AS on the same line as the A2. see below for an example
CourseNameLevelse_referenceAcademicYr1112Enrollments
ChemistryA211A2CHEM121-1Y-1Y-D78112
ChemistryAS10ASCHEM121-1Y-1Y-D1400
August 23, 2013 at 8:52 am
Your posted output doesn't seem to match the sample data you posted but I think you only need something like this???
select *
from #cteCourseAttendance ca
left join #cteCourseConversion cc on ca.CourseName = cc.CourseName and ca.Level = cc.Levels
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 23, 2013 at 8:57 am
Hi Sean
That wont work. The course name has AS or A2 at the front so joining on game wouldn't help, same goes for level.
I gave the output as an example, the problem I have is a cant put it together. On the A2 Chem line I need the total enrollment from the previous years AS course so I can divide them against each other and get the total conversion from AS to A2.
August 23, 2013 at 9:01 am
Knives85 (8/23/2013)
Hi SeanThat wont work. The course name has AS or A2 at the front so joining on game wouldn't help, same goes for level.
I gave the output as an example, the problem I have is a cant put it together. On the A2 Chem line I need the total enrollment from the previous years AS course so I can divide them against each other and get the total conversion from AS to A2.
The problem here is that we can't see your screen. The data as I posted is apparently not the same? From what you posted there is nothing to indicate current or previous year. You need to take a few minutes and put together ddl, sample data and desired output based on the sample data. Without a consistent base to work from we are shooting in the dark. Please take a few minutes and read the first article in my signature about best practices when posting.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply