Showing posts with label sum. Show all posts
Showing posts with label sum. Show all posts

Monday, March 19, 2012

Assistance on SUM() statement

I have a table #changes with two columns: familyID, Versiontime

For each record in the #changes table I need to compute the income from another table FamilyIncome with colunms: familyID, IncomeType, EffectiveDate, Amt. Example Below:

FamID, IncType, EffDate, Amt
100, 10, 01/01/2003, $50.00
100, 20, 01/01/2003, $50.00
100, 30, 01/01/2003, $50.00
100, 10, 02/02/3003, $100.00
100, 20, 02/02/3003, $100.00
100, 30, 02/02/3003, $100.00
100, 40, 02/02/3003, $100.00
100, 20, 03/03/3003, $75.00
100, 30, 03/03/3003, $75.00
100, 40, 03/03/3003, $75.00

So if I'm looking for the Incomes on the following dates (which are in the #changes table) it should be:

01/02/2003 - $150.00 (The three records effective on 01/01/2003)
02/10/2003 - $400.00 (The four records effective on 02/02/2003)
04/01/2003 - $325.00 (One record from 02/02/2003 is still effective (Type 10) plus the three records effective on 03/03/2003)

Any help is greatly appreciated,

BrentCan you post the DDL?

But it seems like a join between the two and a GROUP by with a SUM

something like

SELECT EFF_DATE, SUM(AMT)
FROM myTable1 a myTable2 b
ON a.key = b.key
GROUP BY EFF_DATE|||Brett,
I have tried a couple variations on this theme and so far they do not deliver the desired results:

1. Select familyID, VersionTime, (Select Sum(Amt) from FamilyIncome where FamilyIncome.familyid = #changes.familyid and EFFECTIVEDATE < cast(#changes.versiontime as datetime)+1) as Income
from #changes
group by familyid, cast(versiontime as datetime),#changes.versiontime
order by familyid, versiontime desc
THIS CODE WORKS FOR THE FIRST DATE AND RETURNS THE $150.00 DESIRED, BUT ON ANY FUTURE DATES IT ADDS THE NEW INCOME AND KEEPS A RUNNING TOTAL (I.E. $550.00 INSTEAD OF $400.00)

2. Select familyID, VersionTime, (Select Sum(Amt) from FamilyIncome where FamilyIncome.familyid = #changes.familyid
HAVING EFFECTIVEDATE < cast(#changes.versiontime as datetime)+1) as Income
from #changes
group by familyid, cast(versiontime as datetime),#changes.versiontime
order by familyid, versiontime desc
THIS CODE ONLY RETURNS A RESULT SET FOR THE LAST DATE IN THE SEQUENCE AND THEN IT RETURNS $225.00 (ONLY THE RECORDS WITH A 03/03/2003) DATE)
At this time I'm just trying to isolate the effdate calculations. Also whoever reads this all the dates should be 2003 the 3003 for the year is a typo.

Thanks,

Brent
Originally posted by Brett Kaiser
Can you post the DDL?

But it seems like a join between the two and a GROUP by with a SUM

something like

SELECT EFF_DATE, SUM(AMT)
FROM myTable1 a myTable2 b
ON a.key = b.key
GROUP BY EFF_DATE|||I don't understand your results...where do these dates come from:

01/02/2003 - $150.00 (The three records effective on 01/01/2003)
02/10/2003 - $400.00 (The four records effective on 02/02/2003)
04/01/2003 - $325.00 (One record from 02/02/2003

They don't exists in your data...|||Originally posted by Brett Kaiser
I don't understand your results...where do these dates come from:

They don't exists in your data...

Those dates come out of the #changes table which I only included the fields not an example. What I have is 43,000 records in the #changes table that are familyID's and Dates on which I need to perform several calculations (size of the family, income, fee schedule, etc) right now I'm hung up on getting the income which I need in order to determine the fee (fee is based on family size and income)

Brent

Thursday, March 8, 2012

Assigning default aggregate function to calculated member

Is there any way to assign a default aggregate function (e.g. SUM) to a calculated member just like we can do this with a regular measure?

What I need to do is assign a custom calculation in my calculated member at the lowest grain (e.g. scoped at Date.Members and Customer.Members). Everything above this should be summed by default, e.g. Sales Territory.

Unfortunatelly, it is not possible to assign aggregation function to calculated members. If you need to aggregate them - you will have to do it manually with MDX.

The workaround is to use regular dimension members without data instead of calculated members, and assign MDX expressions to them.

|||

Hi, Mosha

Assuming the workaround, Is there difference in performance between a calculated member and a regular measure?

Assigning default aggregate function to calculated member

Is there any way to assign a default aggregate function (e.g. SUM) to a calculated member just like we can do this with a regular measure?

What I need to do is assign a custom calculation in my calculated member at the lowest grain (e.g. scoped at Date.Members and Customer.Members). Everything above this should be summed by default, e.g. Sales Territory.

Unfortunatelly, it is not possible to assign aggregation function to calculated members. If you need to aggregate them - you will have to do it manually with MDX.

The workaround is to use regular dimension members without data instead of calculated members, and assign MDX expressions to them.

|||

Hi, Mosha

Assuming the workaround, Is there difference in performance between a calculated member and a regular measure?

Assigning a Text Variable

Here's what I've got:

select sum(item_price) as item_price, sum(item_qty) as item_qty, avg (item_price) / avg(item_qty) as average_price from x_invoic

where item_code = (item from a different table)
and status = 9

The "item from a different table" are items that I'm interesting in compiling this data for. I'm not sure what the best way to "cycle" through these items is but I would like to get data on each one of them. I was thinking of using a variable, if possible, but I'll do whatever works.

Any input is appreciated.

Regards,
TechRickUse IN and GROUP BY|||Originally posted by jzhu
Use IN and GROUP BY

Thanks!

Sunday, February 19, 2012

asp:sqldatasource - sum and contains

I have an asp:sqldatasource which is bound to a gridview

In addition to this I would like it to
a) see if there is a specif row/ item in it (ie item_id = 10 for any of the rows it has received) as I conditionally want to show another item outside of the gridview subject to if it is in the gridview or not

b) show the sum of all the values within a certain column of returned rows

Many thanks

not quite sure I understand your question a) but I for question b)

select sum(quantity) from table where id = 1

will return the sum of the quantity which id column equal to 1