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.
| Author |
Topic |
|
ASP_DRUG_DEALER
Yak Posting Veteran
61 Posts |
 Posted - 2004-08-23 : 12:21:28
|
| Hey all-I've got a table that holds actuals and budget for materials by project that we use and need to get totals by project. My problem is I am not really sure the best way to do this efficently. For example the budget may have been for part 123 but we actually ended up using part 456. It's ok if it does not match up perfectly, but I need to return both parts with their totals. So getting a comple list of part numbers for a job regardless if it was a budgeted item or an actual item is a must.My first thought is to hit the DB and get all distinct part numbers for a job. Once that is done then I create a cursor and get the totals for budget and actuals based upon that part and jobnumber.Any help or suggestions would be great!Thanks,Doug |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-23 : 12:28:19
|
| maybe using cube or rollup?Go with the flow & have fun! Else fight the flow :) |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-08-23 : 12:48:57
|
quote:
Originally posted by ASP_DRUG_DEALER
Any help or suggestions would be great!
Sure...post some ddl, dml and expected results....Sounds like a group by on the job id though....Brett8-) |
|
|
|
ASP_DRUG_DEALER
Yak Posting Veteran
61 Posts |
Posted - 2004-08-23 : 13:00:34
|
| Check this out....This should give me a total for actuals, budget and ETC (estimate to complete). ER_P_ID = PART NUMBEREO_TYPE = TRANSACTION TYPE (A=ACTUAL, B=BUDGET, E=ETC)ER_J_ID = JOB NUMBERSELECT ER_P_ID, SUM(CASE WHEN EO_TYPE = 'A' THEN EO_DOLLARS ELSE 0 END) AS ACTUALS, SUM(CASE WHEN EO_TYPE = 'B' THEN EO_DOLLARS ELSE 0 END) AS BUDGET, SUM(CASE WHEN EO_TYPE = 'E' THEN EO_DOLLARS ELSE 0 END) AS ETCFROM E_MASTERWHEREER_J_ID = 'D0041'AND ER_ACCOUNT LIKE '501%'GROUP BY ER_P_ID, EO_TYPEORDER BY ER_P_ID |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-23 : 13:28:39
|
| so this is a solution or something else?Go with the flow & have fun! Else fight the flow :) |
|
|
|
|
|
|
Get totals for Budget and Actuals