Trouble running a complex query in sql?

Question

I am pretty new to SQL Server and just started playing with it. I am trying to create a table that shows attendance percentage by department.

So first i run this query:

SELECT CrewDesc, COUNT(*)
FROM database.emp
    INNER JOIN database.crew on sim1 = sim2
GROUP BY CrewDesc

This gives a table like this:

Accounting     10
Marketing      5
Economics      20
Engineering    5
Machinery      5
Tech Support   10

Then i run another query:

SELECT DeptDescription, COUNT(*)
FROM database.Attendee
GROUP BY DeptDescription

This gives me the result of all the people that have attended meeting something like

Accounting     8
Marketing      5
Economics      15
Engineering    10
Tech Support   8

Then I get the current week in the year by SELECT Datepart(ww, GetDate()) as CurrentWeek To make this example easy lets assume this will be week "2".

Now the way i was going to create this was a table for each step but that seems like waste. Is there a way we can combine to tables in a query? So in the end result i would like a table like this

            Total#     Attd    Week  (Total*Week)       Attd/(Total*week)%
Accounting     10       8       2        20                 8/20
Marketing      5        5       2        10                 5/10
Economics      20       15      2        40                 15/40
Engineering    5        10      2        10                 10/10
Machinery      5        NULL    2        10                 0/10
Tech Support   10       8       2        20                 8/20

you can start from here http://stackoverflow.com/questions/5151018/divide-in-sql-server & http://stackoverflow.com/questions/19015652/how-to-use-count-and-division-operation-in-sql-statements — NoobEditor, Dec 09 '15 at 16:05
You should formulate the question better. The query doesn't seem complex but i don't bother to fill all the lacking documentation doing personal assumptions. — rfb, Dec 09 '15 at 16:06
You will need to do some joins that's it and in noe way is this remptely close to a complex query. Complex query implies you are using advanced concepts and modifying data all over the place and in row count in the hundreds. This is just select statements witha join. But as previusly stated by @rfb to answer the question you would need to provide more information like a mock database nae other then database and some documentation like what rows are common in between the querys and everything like that. — Wes Palmer, Dec 09 '15 at 16:56

score 1 · Accepted Answer · answered Dec 09 '15 at 16:10

Ok, note that my recommendation below is based on your exact existing queries - there are certainly other ways to construct this that may be more performant, but functionally this should work for your requirement. Also, it illustrates the key features of different join types that happen to be relevant for your request, as well as inline views (aka nested queries), which are a super-powerful technique in the SQL language as a whole.

select t1.CrewDesc, t1.Total, t2.Attd, t3.Week, 
    (t1.Total*t3.Week) as Total_x_Week, 
    case when isnull(t1.Total*t3.Week, 0) = 0 then 0 else isnull(t2.Attd, 0) / isnull(t1.Total*t3.Week, 0) end as PercentageAttd
from (
    SELECT CrewDesc, COUNT(*) AS Total
    FROM database.emp INNER JOIN database.crew on sim1 = sim2 
    GROUP BY CrewDesc
) t1
left outer join /* left outer to keep all rows from t1 */ (
    SELECT DeptDescription, COUNT(*) AS Attd 
    FROM database.Attendee GROUP BY DeptDescription
) t2
on t1.CrewDesc = t2.DeptDescription
cross join /* useful when adding a scalar value to all rows */ (
    SELECT Datepart(ww, GetDate()) as Week
) t3
order by t1.CrewDesc

Good luck!

Thanks this worked, but only missed thing (partially my fault for not posing it correctly) is 100 in the else statement on top to make it a percentage. — USER420, Dec 09 '15 at 17:11

score 0 · Answer 2 · answered Dec 09 '15 at 16:07

Try something like this

SELECT          COALESCE(a.crewdesc,b.deptdescription), 
                a.total, 
                b.attd, 
                Datepart(ww, Getdate()) AS week, 
                total * Datepart(ww, Getdate()), 
                b.attd/(a.total*Datepart(ww, Getdate())) 
FROM            (query 1) a 
FULL OUTER JOIN (query 2) b 
ON              a.crewdesc = b.deptdescription

score 0 · Answer 3 · edited Dec 09 '15 at 16:28

0

I'm assuming your queries are correct -- you give no real information about your model so I've no way to know. They look wrong since the same data is called CrewDesc in one table and Dept in another. Also the join sim1 = sim2 seems very strange to me. In any case given the queries you posted this will work.

With TAttend as
(
  SELECT CrewDesc, COUNT(*) as TotalNum
  FROM database.emp
  INNER JOIN database.crew on sim1 = sim2
  GROUP BY CrewDesc 
), Attend as
(
  SELECT DeptDescription, COUNT(*) as Attd
  FROM database.Attendee
  GROUP BY DeptDescription  
)
SELECT CrewDesc as Dept, TotalNum, ISNULL(Attd, 0) as Attd ,Datepart(ww, GetDate())  as Week, 
       CASE WHEN ISNULL(Attd, 0) > 0 THEN 0 
            ELSE ISNULL(Attd, 0) / (TotalNum * Datepart(ww, GetDate()) ) END AS Percent
FROM TAttend
LEFT JOIN Attend on CrewDesc = DeptDescription

edited Dec 09 '15 at 16:28

Pரதீப்

91,748
19
131
172

answered Dec 09 '15 at 16:08

Hogan

69,564
10
76
117

I see what you mean. Its because i had changed a few things hence it was confusing. When i took your code and changed the queries to fit my variables i get the following error. Any idea? `Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'AS'.` This is the last AS in the statement `AS Percent From TAttend LEFT....` – USER420 Dec 09 '15 at 16:28
`END` is missing in `Case` statement – Pரதீப் Dec 09 '15 at 16:29

score 0 · Answer 4 · answered Dec 09 '15 at 16:15

WITH       Total AS (   SELECT CrewDesc, COUNT(*) AS [Count]
                FROM database.emp 
                INNER JOIN database.crew on sim1 = sim2
                GROUP BY CrewDesc 
               ), 
       Attd AS (    SELECT DeptDescription, COUNT(*) AS [Count]
                FROM database.Attendee
                GROUP BY DeptDescription  
             )

SELECT  COALESCE(CrewDesc,DeptDescription) AS [Dept],
       Total.[Count] AS [Total#],Attd.[Count] AS [Attd],
       Total.[Count] * Datepart(ww, GetDate()) AS [(Total*Week)], 
       CAST(Attd.[Count] AS VARCHAR(10))+'/'+ CAST((Total.[Count] * Datepart(ww, GetDate()))AS VARCHAR(10))  AS [Attd/(Total*week)%]
 FROM Total INNER JOIN Attd ON Total.CrewDesc = Attd.DeptDescription

Trouble running a complex query in sql?

4 Answers4