I am running into a few problems getting data that looks to be correct, it appears the sum function is adding more records then I want.

table structure
ID
Code
RunID
Amt
HoursOrDays
Type * Tells if the record is hours or days
---------

I am trying to return the following:
sum(Code) WHERE RunID=6,
sum(Code) WHERE RunID=6 + sum(Code) WHERE RunID=6 AND RunID=3,
(Sum(HoursOrDays) WHERE RunID=6 + Sum(HoursOrDays) WHERE RunID=6) AND WHERE Type=1,
(Sum(HoursOrDays) WHERE RunID=6 + Sum(HoursOrDays) WHERE RunID=6) AND WHERE Type=2

Any help would be greatly appreciated.

Are u adding RunId=6's sum(Code) twice with that of RunId=3?

Do you want twice the sum(Code) here in the above statements??

I'm not sure if this is what you are looking for.

SELECT
(
    (SELECT Sum(Code) FROM <TABLENAME> WHERE RunId=6) as SUM_RUNID_6,
    (SELECT Sum(Code) FROM <TABLENAME> WHERE RunId in(6,3)) as SUM_RUNID_6AND3,
    (SELECT Sum(HoursOrDays) FROM <TABLENAME> WHERE RunId=6 and Type=1) as SUM_RUNID_6_Type_1,
    (SELECT Sum(HoursOrDays) FROM <TABLENAME> WHERE RunId=6 and Type=2) as SUM_RUNID_6_Type_2
)

Hope that Helps.
Cheers!

-Vijay G

Thanks, that didnt quite work but I am not sure if I explained what I was looking for correctly.

Let's say I have the following rows,

ID | Code | RunID | Amt | LiqAmt | HoursOrDays | Type
1 | A1 | 3 | 50 | 50 | 20 | 1
1 | A1 | 3 | 50 | 50 | 4 | 2
1 | A2 | 3 | 50 | 0 | 20 | 1
1 | A2 | 3 | 50 | 0 | 5 | 2
1 | A1 | 6 | 150 | 0 | 0 | 1
1 | A1 | 6 | 250 | 0 | 0 | 1
1 | A2 | 6 | 50 | 0 | 0 | 1

What I am trying to return here is:
1) Sum (HoursOrDays) for RunID=3 AND Type 1
2) Sum (HoursOrDays) for RunID=3 AND Type 2

3) Sum (Amt) for RunID=3
4) Sum (Amt) For RunID=3 + Sum(Amt) from RunId =6
5) Sum (LiqAmt) For RunID=3 + Sum(LiqAmt) from RunId =6

I think the problem that I am running into is I need these grouped by Code, when doing this and using sum I get odd Totals.

An example of what I am looking for to be returned in

Code | Hours | Days | RunTotal | Amt (Run3+Run6 Total) | LiqAmt (Run3+Run6 Total)
A1 | 20 | 4 | 100 | 500 | 100
A2 | 20 | 5 | 100 | 150 | 0

The query I cam up with that kind of works is:
SELECT
    <Table>.Code,
    SUM(<Table>.Amt) AS RunTotal,
    (SELECT SUM(<Table>.HoursOrDays) FROM <Table> WHERE <Table>.PayTypeCode=1) AS RunHours,
    SUM(Pay1.PayAmt) AS AmtRunTotals,
    SUM(Pay1.LiqAmt) AS LiqRunTotals
FROM
    <Table>
    Inner Join <Table> as Pay1 ON <Table>.AcctCode= Pay1.AcctCode AND Pay1.PayRun=3
WHERE
    PayEmpCkBudget.PayRun = 6
GROUP BY <Table>.AcctCode ORDER BY <Table>.AcctCode;

The problem with this is my RunHours Field is the sum of all HoursOrDays and the same value is used for each row.

Looks like there are more than one TABLE being involved, and The fields too are different from what the structure you have mentioned. So I cannot get you the exact query that works.

For getting the RunHours right, I would suggest you to use CASE... WHEN... THEN to get the expected result. You can checl BOL for the syntax and usage of CASE construct.

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection