Query input needed! - 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

Query input needed!

Author

Topic

xkel936
Starting Member

4 Posts

Posted - 2007-06-25 : 17:13:45

This SQL query has been haunting me for the last couple days. If someone would point me in the right direction, I'd really appreciate it.

I have two tables:


Tasks_new
         Task_ID    WBS    Total_task_Hours
          23       CE.1234        500
          24       CE.1234       1000
          25       CE.1234        400
          26       CE.1000        550
          27       CE.1000        600
          28       CE.1252       1000
          29       CE.1555       1000


Entry_Data 
         Task_ID    Hours_input      
          23           50
          24           60 
          27           50
          28          100


Finished_product    
            WBS         Total_task_Hours    Hours_input   Total_hrs_ remaining
         CE.1234           1900                 110              1790
         CE.1000           1150                  50              1100
         CE.1252           1000                 100               900
         CE.1555           1000                Null              1000

The Entry_data table is the table that the user input his or her data into and may or may not include ALL Task_id's from the Tasks_new table.

I am ultimately looking roll up the total hours input against the wbs column in the entry_data table against the total wbs hours in the Tasks_new Table.

If I was unclear, please let me know. Its probably simple to do but sometimes you just get stuck in the ruts.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-25 : 17:18:40

What have you found this far?
Please post your current query.

Peter Larsson
Helsingborg, Sweden

xkel936
Starting Member

4 Posts

Posted - 2007-06-25 : 17:33:20

[code]select WBS, sum(hours) as Total_hrs
from tasks_new, entry_data
where tasks_new.task_id = entry_data.task_id
and wbs is not null
group by wbs
UNION select WBS, SUM(TOTAL_TASK_HRS) as Total_hrs
from tasks_new
where wbs is not null
group by wbs;

[/code]

This is the best attempt I have written so far. It gives me the right numbers, just doesn't put them in the right place. My second attempt doesn't work either as it returns an error that says "You have written a subquery that can return more than one field..."

[code]
SELECT wbs, Sum(entry_data.hours) AS Total_input_hours_YTD, (select wbs, SUM(total_task_hrs)
from tasks_new group by WBS) AS Total_task_hours
FROM entry_data, tasks_new
WHERE Tasks_new.task_id = Entry_data.task_id and WBS is not null
GROUP BY wbs;
[/code]

To simplify the code, lets take out Total_hrs_ remaining from the Finished_product table

[code]Finished_product
WBS Total_task_Hours Hours_input
CE.1234 1900 110
CE.1000 1150 50
CE.1252 1000 100
CE.1555 1000 Null
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-25 : 17:44:27

[code]-- If Entry_Data table can hold multiple records with same Task_ID
SELECT ts.WBS,
SUM(ts.Total_task_Hours) AS Total_task_Hours,
SUM(ed.Hours_Input) AS Hours_Input
FROM Tasks_New AS ts
LEFT JOIN (
SELECT Task_ID,
SUM(Hours_Input) AS Hours_Input
FROM Entry_Data
GROUP BY Task_ID
) AS ed ON ed.Task_ID = ts.Task_ID
GROUP BY ts.WBS

-- If Entry_Data table can't hold multiple records with same Task_ID
SELECT ts.WBS,
SUM(ts.Total_task_Hours) AS Total_task_Hours,
SUM(ed.Hours_Input) AS Hours_Input
FROM Tasks_New AS ts
LEFT JOIN Entry_Data AS ed ON ed.Task_ID = ts.Task_ID
GROUP BY ts.WBS[/code]

Peter Larsson
Helsingborg, Sweden

xkel936
Starting Member

4 Posts

Posted - 2007-06-27 : 12:02:48

Peso,

Thank you very much. It helped me out. It was slightly different as I'm not using SQL server but it gave me a good idea where to go.

Thanks once again!

-Ryan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources