Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2008
    Posts
    10

    Unanswered: Problem getting the desired result..Need quick help please!!

    Hi,
    I want to write a query that would display data from the database on the basis of ethnicity in the last 3 years. Not only that, but I also want to see the number of starters, retention and achievement

    e.g.
    Code:
                                          Year 05         Year06        Year 07
    Ethnicity 1         Starters
                        Retention
                        Achievement
    
    Ethnicity 2         Starters
                        Retention
                        Achievement
    The problem is that there are multiple ethnicities and I cannot write separate queries for each of them and there are 3 years and I cannot write separate queries for them either. E.g. if i have 5 ethnicities and 3 years, in current situation, I am ending up with writing 15 queries for each of them because I can't write a cross tab query for these.

    When I do write the query as follows:

    Code:
    create table #temp_et (
    [Year]		int NOT NULL,
    [Ethnicity] varchar(20),
    [Starts]	float(4),
    [Success]	float(4),
    [Retention]	float(4),
    [Achievement]	float(4)
    )
    
    --Year 05/06
    insert into #temp_et
    select
    CAST(LEFT(pg_expendyrid,2) AS int)		[Year],
    pg_ethnicgroupname			[Ethinicity],
    sum([pvstart]) 				[Starts],
    
    (case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvstart)*1.00)*100)end)		[Success],
    (case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvcomp)*1.00)/(sum(pvstart)*1.00)*100)end)	[Retention],
    (case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvcomp)*1.00)*100)end)		[Achievement]
    --into temp_et
    from 	pvmd
    			      
    where pg_expendyrid = '05/06'
    
    group by 
    pg_ethnicgroupname,
    pg_expendyrid
    order by 
    pg_ethnicgroupname
    
    
    
    
    --Year 06/07
    insert into #temp_et
    
    select
    CAST(LEFT(pg_expendyrid,2) AS int)	[Year],
    pg_ethnicgroupname			[Ethinicity],
    sum([pvstart]) 				[Starts],
    
    (case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvstart)*1.00)*100)end)		[Success],
    (case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvcomp)*1.00)/(sum(pvstart)*1.00)*100)end)	[Retention],
    (case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvcomp)*1.00)*100)end)		[Achievement]
    --into temp_et
    from 	pvmd		      
    
    where pg_expendyrid = '06/07'		
    
    group by 
    pg_ethnicgroupname,
    pg_expendyrid
    order by 
    pg_ethnicgroupname
    
    
    --Year 07/08
    insert into #temp_et
    
    select
    CAST(LEFT(pg_expendyrid,2) AS int)		[Year],
    pg_ethnicgroupname			[Ethinicity],
    sum(pvstart)  				[Starts],
    
    (case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvstart)*1.00)*100)	end)	[Success],
    (case when sum(pvstart) = 0 THEN 0 ELSE convert(decimal(6,3),(sum(pvcomp)*1.00)/(sum(pvstart)*1.00)*100)end)	[Retention],
    (case when sum(pvcomp) = 0 THEN 0 ELSE convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvcomp)*1.00)*100)end)		[Achievement]
    --into temp_et
    from pvmd		      
    where 	pg_expendyrid = '07/08'		--Expected end year
    
    group by 
    pg_ethnicgroupname,
    pg_expendyrid
    order by 
    pg_ethnicgroupname
    
    
    SELECT 
    
    	(CASE [Year] WHEN 05 THEN [Starts] ELSE 0 END) AS [Starts],
    	(CASE [Year] WHEN 05 THEN [Retention] ELSE 0 END) AS [Retention],
    	(CASE [Year] WHEN 05 THEN [Achievement] ELSE 0 END) AS [Achievement],
    	(CASE [Year] WHEN 06 THEN [Starts] ELSE 0 END) AS [Starts],
    	(CASE [Year] WHEN 06 THEN [Retention] ELSE 0 END) AS [Retention],
    	(CASE [Year] WHEN 06 THEN [Achievement] ELSE 0 END) AS [Achievement],
    	(CASE [Year] WHEN 07 THEN [Starts] ELSE 0 END) AS [Starts],
    	(CASE [Year] WHEN 07 THEN [Retention] ELSE 0 END) AS [Retention],
    	(CASE [Year] WHEN 07 THEN [Achievement] ELSE 0 END) AS [Achievement]
    	
    
    from #temp_et
    This is the closest I could get to the output as in the end I want to put it into a report in Visual Studio .Net i.e. Linking that report through a stored procedure in SQL Server. If I copy all this code to a SP, and dont put the data in a temporary table, the SP will only return the top rows and not the data from year 06/07 and 07/08.

    All i can think of is creating a crosstab query, which I cant. So help would be much appreciated.

    P.S. The data for Ethnic Group is coming from a view and that is joined which I forgot to mention here in the code.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    DDL for you table, please....
    ...also, it looks as though you are summing averages, which would skew your results. Are you sure that math is what you want?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2008
    Posts
    10
    Well, Yes. that is exactly what I want to achieve because there are thousands of records in the database and I cant take each one of them individually in the report.

    There is other data coming from another view as well which I forgot to add in the code above, which is to create a join only. All the data is contained in one table and its structure is attached.

    Attached file has the table structure in it.

    Cheers.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •