1

I'm relative new to sql and I would like some help with this one. Basically, I'm trying to do some calculations in the query because functions were a bit slow. Could you guys improve on this?

-- Query to retrive ADA from every school
Select  Distinct DY, 
        DATENAME(MM,DT) as 'Month',
        CONVERT(nvarchar,DT,101) as 'Date',
        (
            Select ((@M_stu - (Select Count(SC) from dbo.ATT where sc=1 AND al!='' AND DY=T.DY))/@M_stu)*100
        ) as 'Merced ADA',
        (
            Select ((@A_stu - (Select Count(SC) from dbo.ATT where sc=2 AND al!='' AND DY=T.DY))/@A_stu)*100
        ) as 'Atwater ADA',
        (
            Select ((@L_stu - (Select Count(SC) from dbo.ATT where sc=3 AND al!='' AND DY=T.DY))/@L_stu)*100
        ) as 'Livingston ADA',
        (
            Select ((@B_stu - (Select Count(SC) from dbo.ATT where sc=4 AND al!='' AND DY=T.DY))/@B_stu)*100
        ) as 'Buhach ADA',
        (
            Select ((@Y_stu - (Select Count(SC) from dbo.ATT where sc=5 AND al!='' AND DY=T.DY))/@Y_stu)*100
        ) as 'Yosemite ADA',
        (
            Select ((@I_stu - (Select Count(SC) from dbo.ATT where sc=6 AND al!='' AND DY=T.DY))/@I_stu)*100
        ) as 'Independence ADA',
        (
            Select ((@G_stu - (Select Count(SC) from dbo.ATT where sc=10 AND al!='' AND DY=T.DY))/@G_stu)*100
        ) as 'Golden Valley ADA',
        (
            Select ((@S_stu - (Select Count(SC) from dbo.ATT where sc=92 AND al!='' AND DY=T.DY))/@S_stu)*100
        ) as 'Sequoia ADA'
From dbo.ATT as T
Order by DY ASC
Daniel Pratt
  • 12,007
  • 2
  • 44
  • 61
rne1223
  • 33
  • 5

3 Answers3

3

Try this

Select  DY, 
        DATENAME(MM,DT) as 'Month',
        CONVERT(nvarchar,DT,101) as 'Date',
        @M_stu - (Count(case when sc=1 AND al!='' AND DY=T.DY then 1 else null end)/@M_stu)*100 as 'Merced ADA',
        @A_stu - (Count(case when sc=2 AND al!='' AND DY=T.DY then 1 else null end)/@A_stu)*100 as 'Atwater ADA',
        ...
From dbo.ATT as T
GROUP BY DY, DT
Order by DY ASC
Igor Borisenko
  • 3,806
  • 3
  • 34
  • 49
  • 3
    remove `Distinct` and add `Group By DY` and you'll (almost) have it. – RBarryYoung May 31 '13 at 15:09
  • This works nicely. It definitely improve my query. I decided to put the code in a View to call the query as if it was a normal table, but now I can't keep the constant (@M_stu ect) in the query. Do you know a way around this? – rne1223 Jun 24 '13 at 21:17
  • @me1223 No, you can't use parameters in a view, but you can use table functions instead. http://stackoverflow.com/questions/1687279/can-we-pass-parameter-to-a-view-in-sql – Igor Borisenko Jun 25 '13 at 00:59
  • Yes I read about them, but they are slow right? Anyways, thanks for replying so fast. – rne1223 Jun 25 '13 at 17:22
3

Try this solution:

SELECT  DISTINCT 
        t.DY                            AS DY, 
        DATENAME(MM,t.DT)               AS [Month],
        CONVERT(NVARCHAR,t.DT,101)      AS [Date],
        ((@M_stu - d.[1])/@M_stu)*100   AS [Merced ADA],
        ((@A_stu - d.[2])/@A_stu)*100   AS [Atwater ADA],
        ((@L_stu - d.[3])/@L_stu)*100   AS [Livingston ADA],
        ((@B_stu - d.[4])/@B_stu)*100   AS [Buhach ADA],
        ((@Y_stu - d.[5])/@Y_stu)*100   AS [Yosemite ADA],
        ((@I_stu - d.[6])/@I_stu)*100   AS [Independence ADA],
        ((@G_stu - d.[10])/@G_stu)*100  AS [Golden Valley ADA],
        ((@S_stu - d.[92])/@S_stu)*100  AS [Sequoia ADA]
FROM    dbo.ATT AS t
OUTER APPLY (
    SELECT  c.*
    FROM (
        SELECT  a.sc
        FROM    dbo.ATT AS a
        WHERE   a.sc IN (1,2,3,4,5,6,10,92) 
        AND     a.al!='' 
        AND     a.DY=t.DY
    ) b
    PIVOT( COUNT(b.sc) FOR a.sc IN ([1],[2],[3],[4],[5],[6],[10],[92]) ) c
) d
ORDER BY t.DY ASC;

You should check also if you have one of the following indices: CREATE [UNIQUE] INDEX index_name ON dbo.ATT (DY, sc, al) or CREATE [UNIQUE] INDEX index_name ON dbo.ATT (DY, sc) INCLUDE (al).

Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57
  • Sadly this is a bit too complicated for me right now. Could you please let me know why you decide to use OUTER APPLY? – rne1223 Jun 24 '13 at 22:11
  • Do you want to know why I used `OUTER APPLY` instead of `CROSS APPLY` or do you want to know if `OUTER APPLY` is a better option than those 8 subqueries ? 1) Using `CROSS APPLY` you may loose rows if `DY` column allows and contains `NULLs`. 2) The `OUTER APPLY` solution is compact and, from my experience, `PIVOT` execution plans tend to perform good. Of course, you should test and decide yourself. And when you have performance related questions you should post the all indexes in order to get a proper answer. – Bogdan Sahlean Jun 25 '13 at 08:11
  • Alright thanks. I'm still a bit new writing sql queries so I'm trying to keep as readable for me as possible. Most likely once I gain experience I will be able to use OUTER APPLY correctly. Thanks once again tho – rne1223 Jun 25 '13 at 17:26
1

You'll have to compare the results with your current query, but this is how I would probably accomplish this:

Select  Distinct DY, 
        DATENAME(MM,DT) as Month,
        CONVERT(char(10),DT,101) as Date,
        @M_stu - sum(case when sc=1 then 1.0 else 0.0 end)/@M_stu)*100 as [Merced ADA],
        @A_stu - sum(case when sc=2 then 1.0 else 0.0 end)/@A_stu)*100 as [Atwater ADA],
        @L_stu - sum(case when sc=3 then 1.0 else 0.0 end)/@L_stu)*100 as [Livingston ADA],
        @B_stu - sum(case when sc=4 then 1.0 else 0.0 end)/@B_stu)*100 as [Buhach ADA],
        @Y_stu - sum(case when sc=5 then 1.0 else 0.0 end)/@Y_stu)*100 as [Yosemite ADA],
        @I_stu - sum(case when sc=6 then 1.0 else 0.0 end)/@I_stu)*100 as [Independence ADA],
        @G_stu - sum(case when sc=10 then 1.0 else 0.0 end)/@G_stu)*100 as [Golden Valley ADA],
        @S_stu - sum(case when sc=92 then 1.0 else 0.0 end)/@S_stu)*100 as [Sequoia ADA]
From    dbo.ATT as T
Where   al!=''
Order by DY ASC

Without having sample data to test with, I think this would be the easiest method to take.

BAReese
  • 491
  • 2
  • 5