alias w/IN operator

running this now, but need the names, & not emp ID's.
select ticketid, acct, date, createuser
from ticket
where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
group by createuser
will the 'as' work in the IN operator?
where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
'k1004' as Elf) ?
broski wrote:
>running this now, but need the names, & not emp ID's.
>select ticketid, acct, date, createuser
>from ticket
>where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
>group by createuser
>will the 'as' work in the IN operator?
>where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
>'k1004' as Elf) ?
sorry...just signed up & realized i posted in connectivity....meant to be
under reporting.
thank you.

alias w/IN operator

running this now, but need the names, & not emp ID's.
select ticketid, acct, date, createuser
from ticket
where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
group by createuser
will the 'as' work in the IN operator?
where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
'k1004' as Elf) ?broski wrote:
>running this now, but need the names, & not emp ID's.
>select ticketid, acct, date, createuser
>from ticket
>where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
>group by createuser
>will the 'as' work in the IN operator?
>where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
>'k1004' as Elf) ?
sorry...just signed up & realized i posted in connectivity....meant to be
under reporting.
thank you.

alias

How to get more columns within same alias?

(
select DateOpen AS Date,TestObjectID from RprRepair where TestObjectID = @.AssetID
union all
select DateSent ,TestObjectID from RprRepair where TestObjectID = @.AssetID
union all
select DateRepairFinished,TestObjectID from RprRepair where TestObjectID = @.AssetID
) AS Der

This works fine alone, but when i put it into union i get an error that no more than one value can be in subqueries.

Could you please explain what you are trying to do? I don't quite understand your question. What do you mena by get more columns with same alias?|||

Then use join rather than correlated subquery.

select

...

, Der.* -- here you can use all the cols from Der

from ...

inner/left/right join (

select DateOpen AS Date,TestObjectID from RprRepair where TestObjectID = @.AssetID

union all

select DateSent ,TestObjectID from RprRepair where TestObjectID = @.AssetID

union all

select DateRepairFinished,TestObjectID from RprRepair where TestObjectID = @.AssetID

) AS Der on <conditions>

aggregation using lastchild

Hi there

I'm using the following MDX function and it works perfect.

iif([Ledger Date].CurrentMember.Level.Name = "Year", ", [Ledger Date].CURRENTMEMBER.LASTCHILD, ([Ledger Date].CURRENTMEMBER, [Measures].[Value]))

But now I want to add another dimension namely Accout Type to the scenario containing Asset, Liabilities, Income and Expence. The lastchild must only work for "Year", "Liabilities" and "Assets"

How do I achive this?

Thank you in advance.

Try :

iif( ([Ledger Date].CurrentMember.Level.Name = "Year" and [Account Type].CurrentMember is [Account Type].[Liabilities]) or ([Ledger Date].CurrentMember.Level.Name = "Year" and [Account Type].CurrentMember is [Account Type].[Assets]) , ", [Ledger Date].CURRENTMEMBER.LASTCHILD, ([Ledger Date].CURRENTMEMBER, [Measures].[Value]))

|||

i've adjusted your suggestion to the following:

iif(([Ledger Date].CurrentMember.Level.Name = "Year" and [Ledger Entries].CurrentMember is [Ledger Entries].[Account Type].&[Asset]) ,[Ledger Date].CURRENTMEMBER.LASTCHILD, ([Ledger Date].CURRENTMEMBER, [Measures].[Value]))

but then I get the following error:

Infinite recursion detected during execution of calculated member.

any idea why?

thanks again.

Aggregation table

Hi all

i need to create aggregation table from 2 tables group by date, any one have any idea how to create it by using SSIS

thanks & regards

Use a merge join or union all to bring your two tables together and then use the aggregate component.

Aggregation Problem with Hours and Minutes in Time Dimension

For the first time, I have created a time dimension that includes hours and minutes, in addition to the Year, Quarter, Month, and Date that I am familiar with.

I want to display the average of my measures at whatever level of Time the user chooses. I thus chose "AverageOfChildren" as the aggregation method for these measures.

Unfortunately, SSAS aggregates these measures by Sum over Hours and Minutes, and then by AverageOfChildren over Date, Month, Quarter, and Year.

I am confused.

How can I force it to perform an "AverageOfChildren" at all levels ?

First, have a look at this document(http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx) and think about your design a second time(on page 34-35 in the word version)

Next. This is only a guess but how are your attribute relations between minutes, hours, date,quarter and year defined, if they are included in the same user hierarchy?

HTH

Thomas Ivarsson

Aggregation of Calculated members of form: MEASURE op ATTRIBUTE VALUE

hi there. i do not understand how to use an attribute in a calculation. In the query below, I'm trying to say:

for all members of the date.calendar hierarchy, show me the sum of (sales amount fact * dealer price attribute for the product sold)

with

member [measures].[fact times attribute]

as cdbl([Measures].[Sales Amount]) * cdbl([Product].[Dealer Price].CurrentMember.memberValue)

select [measures].[fact times attribute] oncolumns,

[Date].[Calendar].allmembersonrows

from [Adventure Works]

where [Geography].[City].&[Beaverton]&[OR]

this gives a type mismatch error complaining that "All Products" cannot be cast to double. But I don't understand how to force the calculation to occur at the lowest level and for aggregation to be done subsequently.

Product is currently at the "All Product" member. What you are actually wanting to do is get the price for each product and multiply that by the sales of that product and then aggregate (i'll assume sum) those values. That query looks like the one below. (Actually, the one below is kinduva shortcut to the solution. Instead of going to the individual product, I just went to the product price. If two products have the same price, the query kinda says that we treat them the same. Nit-picky, I know.)

Good luck.

Code Snippet

withmember [measures].[fact times attribute] as

SUM([Product].[Dealer Price].[Dealer Price].Members,

[Measures].[Sales Amount])

select [measures].[fact times attribute] oncolumns,

NONEMPTY [Date].[Calendar].allmembersonrows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

;

|||Dear Bryan,

Thanks for your pointers. I actually work in hong kong so were ~12 hours ahead of you. I'm checking this from home and so can't test it. but i'm reviewing it now, because I'd love to get this right for work tomorrow.

Looking at your solution, one thing I find very conceptually troubling:

>>I don't see a multiplication sign anywhere!!!!<<

So at the very point where I would imagine attribute and fact really connect, the code is silent! Also when you refer to a shortcut and being nitpicky, that is all just going straight over my head - my knowledge of mdx (rather like my knowledge of cantonese just isn't evolved enough to understand the subtleties here. I can just about order lunch with a lot of gesticulation but that's it.

i will of course kick the tires on this tomorrow when I get into work. But [embarrassingly] I've been trying to figure out what the missing link is here for almost 3 days without success! Would you mind just leaving a working "best practice" solution for me? I feel like I sound like a lazy person - I'm not, I just don't grock this yet and its driving me nuts.

Yours,

John G.
|||

Sorry about that. I was pushing to get the code assembled and totally dropped that one critical part of the code. Try this one. I use the CurrentMember of the Dealer Price to get to the value.

Code Snippet

withmember [measures].[fact times attribute] as

SUM(

[Product].[Dealer Price].[Dealer Price].Members,

[Measures].[Sales Amount]*[Product].[Dealer Price].CurrentMember.MemberValue

)

select [measures].[fact times attribute] on columns,

NON EMPTY [Date].[Calendar].allmembers on rows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

;

|||

Jo,

Try to use named calculations in the datasourceview of your factTable... it would be a better performance, because you only run the formulas once when the cube is processing.

Regards!

|||

ok it works fine - THANKS!

I also tried this which returns the same results - is it logically identical?

with

member [measures].[fact times attribute]

asSUM([Product].[Product].[Product].Members,

[Measures].[Sales Amount]*[Product].[Dealer Price].CurrentMember.MemberValue

)

select {[measures].[fact times attribute]} oncolumns,

NONEMPTY [Date].[Calendar].allmembersonrows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

i'm not sure if you'd have the patience of a saint to wade through what follows, but I'm trying to understand the execution of the query. Would you mind critiquing the below, or if it's easier for you just describe the above query in pseudo code for dummies

For my general understanding when you refer to [Product].[Dealer Price].CurrentMember.MemberValue,

what is the context of the CurrentMember? Is it:

1. The current member in the context of traversing the set of members of the product hierarchy as defined by the set argument passed to the SUM function.

or 2. the currentmember in some wider context of the query (actually I'm not sure if that makes sense, so I'll go with answer 1).

Assuming 1. above, then I would interpret the query as follows:

1. slice by beaverton

2. iterate through all members of the date hierarchy.

3. for each sales fact within the intersection of beaverton and the current date, calculate [fact times attribute] as follows:

a) for each member of the product hierarchy ...

i) ... extract the tuple set defined by the intersection of that product within the current region [for many product members this will be a null set]

ii) for that set of tuples, sum up (sales amount * dealer price).

iii) keep running total of that sum, and the final total will be your result of the current date member.

|||

thanks - this did occur to me, since as i understand it calculated members (even cube scoped ones) are calculated at runtime not at processing time, correct?

however, in my real calculation I'm using another calculated member which doesn't exist in the underlying table so this makes it tricky.

Would you say that in general, one should strive to do as much data cleaning / preparation / precalculation as possible outside the cube, and then just leave the cube to prepare aggregations and pure analysis calculations that are hard to do outside of mdx?

|||Yeah joGo, I'm with you! If you have other calculated members that you cannot replace for named calculation, so you are right...|||

Your code is logically similar and should produce the same result. You may want to test for performance differences, but the only way I can imagine a performance difference would exist would be under a very particular situation I suspect does not exist in the cube. (In other words, they probably have the same performance so don't sweat it.)

Regarding the CURRENTMEMBER question, you have to keep in mind context at all times. In the SUM function, you generate a set. That set definition is in the context of the cube as a whole. If I want to limit that context based on my slicer (WHERE clause), I can use the EXISTING keyword in the set definition.

So, now I have a set. Then, for each member in that set, I will return and/or calculate a value. That value is determined in the context of the member from the set I am currently working with. At this point, how that set came to be is unknown to me. The set has been determined and I'm just working through it blindly. I think this is what you're saying in the section at the bottom of your email.

Sorry I am being slow to respond today. The MSDN forum email seems to be jammed up a bit and I'm not getting alerts like I should.

Thanks,
Bryan

Aggregation against multiple date fields

I have a single table which records calls made to a helpdesk.
Each call has a single row in the table, and there are three relevant date fields recorded: occurrence date, reported date, and resolution date.

So it looks a little like this:

OccDate | RepDate| ResDate | CallRef
21/3/05 |22/3/05 | 01/4/05 | PMR001
22/3/05 | 1/4/05 | 26/5/05 | PMR002
01/4/05 | 2/4/05 | 3/6/05 | PMR003
01/5/05 | 2/5/05 | <Null> | PMR004

Now what I want to do is to create a single SQL statement which will allow me to summarise in each month how many calls occurred, were reported, and resolved, so for this data it looks like this:

Month | Occ. | Rep. | Res.
Mar-05 | 2 | 1 | 0
Apr-05 | 1 | 2 | 1
May-05 | 1 | 1 | 1
Jun-05 | 0 | 0 | 1

I can get each column individually very easily by simply doing:
COUNT(CallRef) GROUP BY (OccDate)
COUNT(CallRef) GROUP BY (RepDate)
COUNT(CallRef) GROUP BY (ResDate)
..but I'm getting in a right pickle when trying to merge these into a single statement.

To start with, I'm guessing that I need to do 2 self-joins so that all three dates are linked to each other.
Then I tried using CASE and COALESCE statements to do conditional aggregations, but that got me nowhere...

I'd be grateful for any help!I tested it on MS SQL Server:

create table t (OccDate datetime, RepDate datetime, ResDate datetime, callRef varchar(10))

insert into t values( cast('3/21/05' AS DATETIME) , cast('3/22/05' as DATETIME), cast('4/1/05' as datetime), 'PMR001')
insert into t values( cast('3/22/05' AS DATETIME) , cast('4/1/05' as DATETIME), cast('5/26/05' as datetime), 'PMR002')
insert into t values( cast('4/1/05' AS DATETIME) , cast('4/2/05' as DATETIME), cast('6/3/05' as datetime), 'PMR003')
insert into t values( cast('5/1/05' AS DATETIME) , cast('5/2/05' as DATETIME), NULL, 'PMR004')

select
mont,
(select count(*) from t t3 where cast(OccDate AS char(3)) + '-' + cast(year(OccDate) AS varchar(4)) = t2.mont) as Occ,
(select count(*) from t t3 where cast(RepDate AS char(3)) + '-' + cast(year(RepDate) AS varchar(4)) = t2.mont) as Rep,
(select count(*) from t t3 where cast(ResDate AS char(3)) + '-' + cast(year(ResDate) AS varchar(4)) = t2.mont) as Res
from
(
select distinct mon as Mont from
(
select cast(OccDate AS char(3)) + '-' + cast(year(OccDate) AS varchar(4)) as mon from t where OccDate is not null
union all
select cast(RepDate AS char(3)) + '-' + cast(year(RepDate) AS varchar(4)) as mon from t where RepDate is not null
union all
select cast(ResDate AS char(3)) + '-' + cast(year(ResDate) AS varchar(4)) as mon from t where ResDate is not null
) t1
) t2

Month Occ Rep Res
-------------
Apr-2005 1 2 1
Jun-2005 0 0 1
Mar-2005 2 1 0
May-2005 1 1 1

to run this on different DB server all you need is replace
cast(ResDate AS char(3)) + '-' + cast(year(ResDate) AS varchar(4))
with other functions which returns
'Month-Year'|||Thankyou so much for the helpful reply - you're a star!|||An other solution is the following:create table t (OccDate date, RepDate date, ResDate date, callRef varchar(10)) ;
insert into t values( '03/21/2005', '03/22/2005', '04/01/2005', 'PMR001') ;
insert into t values( '03/22/2005', '04/01/2005', '05/26/2005', 'PMR002') ;
insert into t values( '04/01/2005', '04/02/2005', '06/03/2005', 'PMR003') ;
insert into t values( '05/01/2005', '05/02/2005', NULL, 'PMR004') ;
SELECT coalesce(m1,m2,m3) AS "month",
coalesce(Occ,0), coalesce(Rep,0), coalesce(Res,0)
FROM ( SELECT m1, COUNT(*) AS Occ
FROM ( SELECT substr(char(OccDate,ISO),1,7) AS m1
FROM t ) AS x1
GROUP BY m1 ) AS y1
FULL OUTER JOIN
( SELECT m2, COUNT(*) AS Rep
FROM ( SELECT substr(char(RepDate,ISO),1,7) AS m2
FROM t ) AS x2
GROUP BY m2 ) AS y2
ON m1 = m2
FULL OUTER JOIN
( SELECT m3, COUNT(*) AS Res
FROM ( SELECT substr(char(ResDate,ISO),1,7) AS m3
FROM t ) AS x3
GROUP BY m3 ) AS y3
ON m2 = m3
ORDER BY 1It has the slight advantage that it works with DB2 V7, and that it might be a bit faster (but I did not verify that ;) )

Aggregate() doesn't aggregate over an expression

We use CTP3 of SQL Server 2005 SP2. We have the following scope expression

Scope
(
[Date].[Calendar Year].[Calendar Year].Members,
[Date].[Month].Members
);

(
{
[Measures].[Profit YTD]
}
) =

Aggregate (
PeriodsToDate([Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember)
, StrToMember(Extensibility.Replace([Measures].CurrentMember.UniqueName, " YTD", "")) -- remove " YTD" suffix to aggregate over the corresponding standard measure

);

Format_String ( This ) = "Currency";
End Scope;

However, Aggregate() essentially bypasses the call to the SSAS stored procedure (Extensibiliy.Replace is a custom SSAS stored procedure). Instead Aggregate() uses the [Profit YTD] measure instead. If I replace Aggregate() with SUM() it works correctly. Is this a bug? A workaround?

Is [Profit YTD] a calculated measure itself ? If this is a case, Aggregate would switch solve orders with it in order to try to determine the correct aggregation function. If Sum is what you want to do, then using Sum directly is probably a right thing to do here.

I also want to note, that the expression inside Aggregate (or Sum) is very inefficient. If you have plenty of calc measures with YTD suffix, and you want to compute year to date over corresponding measure without YTD suffix, there are much more efficient ways of doing it.

|||

Mosha,

Thank you so much for looking into this. I really appreciate your help. Yes, Profit YTD is a calculated member.

Here are our requirements. We have a cube with rather large dimensions, e.g. Customer dimension (some 150,000 customers) and Account dimension (represents a customer bank account) with some 1.5 million members. We need to support additive and semi-additive aggregations (e.g. rolling twelve, weighted averages, etc). Considering the fact that the Report Builder doesn't support dimension-level calculated members (time intelligence), we have no other option but to create measure-level calculated members for each calculation, e.g.: Profit R12 for rolling 12 aggregation of the Profit additive measure, Average Balance R12 for rolling 12 aggregation of the Average Balance weighted average, etc. In addition, we have a requirement to provide YTD and QTD calculations.

The idea behind the the script above was to work universally, i.e. to use the corresponding measure underlying function since additional measures will be added to the scope. For example, if we calculate Profit YTD, we need to base our calculation on the Profit measure (hence, we need to remove the YTD suffix).

Here is an example of our script for R12 aggregations:

Scope
(
[Date].[Month].[Month].Members

);

(
{
[Measures].[Interest Paid R12],
[Measures].[Interest Accrued R12],
...
}
) =
Sum (
{
ParallelPeriod(
[Date].[Calendar].[Month],
11,
[Date].[Calendar].CurrentMember
) : [Date].[Calendar].CurrentMember
}
// current measure
, StrToMember(Extensibility.Replace([Measures].CurrentMember.UniqueName, " R12", "")) -- remove R12 suffix to get to the the corresponding regular measure
);

Format_String ( This ) = "Currency";
End Scope;

What will be the recommended approach from a performance standpoint? Should we introduce multiple scopes (for additive and semi-additive measures) that use the respective aggregation function directly? Should we copy the measure before we start aggregating to avoid StrToMember(), e.g.:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Profit YTD] AS [MEASURES].[Profit] , FORMAT_STRING = "Currency";

|||

Considering the fact that the Report Builder doesn't support dimension-level calculated members (time intelligence)

So the root of your problem is that you cannot use utility attribute in Time dimension or separate utility dimension because of Report Builder limitation ? My advice is to do it right way with utility dimension, but instead of creating calculated members, make R12, YTD etc as real members in this dimension, they just won't be associated with any data. Then, you can put the formulas on them either using custom member formulas or inside MDX script.

|||

Thank you.

1. I'v e read your post about this and and David's article but I need to wrap my head about the utility dimension concept. Would mind eleborating a bit more on the utility dimension approach? Are you saying to add the utility dimension to the cube but don't link this dimension to the measure group at all? What changes need to be made to the Time Intelligence script (if any) to re-purpose it to use the utility dimension?

2. In case the end users find the utility dimension approach confusing from an usability standpoint, can we explore a more efficient approach with standalone calculated members?

|||

Are you saying to add the utility dimension to the cube but don't link this dimension to the measure group at all?

Or you can link the "Normal" or "Current" member of this dimension to the fact table by creating a new calculated column in DSV with constant|||

After a few hours of experimenting and brain crunching I am getting nowhere:

1. Introducing an utility dimension that is not linked to the measure group seems to work in the cube browser/Excel but it doesn't work in the Report Builder because the Report Builder model expects a dmension relationship. Otherwise, there is no navigational path in the model and once you drag and drop the dimension, there is nowhere to go to.

2. The utility dimension approach is causing much grief. Here is what I do.

a) I created a named query DimDateCalculations as follows:

SELECT - 1 AS MemberKey, - 1 AS CurrentPeriodID, 'Current Period' AS CalendarDateCalculations, 'Current Period' AS FiscalDateCalculations
UNION
SELECT 1 AS MemberKey, - 1 AS CurrentPeriodID, 'Rolling 12' AS CalendarDateCalculations, 'Rolling 12' AS FiscalDateCalculations
UNION
SELECT 2 AS MemberKey, - 1 AS CurrentPeriodID, 'YTD' AS CalendarDateCalculations, 'YTD' AS FiscalDateCalculations
UNION
SELECT 3 AS MemberKey, - 1 AS CurrentPeriodID, 'QTD' AS CalendarDateCalculations, 'QTD' AS FiscalDateCalculations

b) I added a calculated column CurrentPeriodID to the fact table and defaulted it to -1. In DSV, I joined DimDateCalculations to the fact table on CurrentPeriodID

c) I created a new dimension (Date Calculations) on top of the DimDateCalculations named query. The dimension key is set to MemberKey and it has two attribute hierarchies (CalendarDateCalculations and FiscalDateCalculations) whose default members are set to the Current Period member.

d) I added the following script to the cube (only changed the dimension name in the script that the Time Intelligence Wizard generates):

Scope(
{
[Measures].[Tax],
[Measures].[Profit],
}
);

(
[Date Calculations].[Calendar Date Calculations].[YTD],
[Date].[Calendar Year].[Calendar Year].Members,
[Date].Month.Members
) =

Aggregate(
{[Date].[Calendar Date Calculations].DefaultMember} *
PeriodsToDate([Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember)

);
End Scope;

e) In the cube browser, I created a report which filters the Calendar Date dimension to a given month. I dropped the Calendar Date Calculations on columns, another dimension on rows, and Profit as data.

However, the YTD column is emtpy (after Show Empty Cells is enabled). What am I missing? Again, our requirement is that the utility dimension cannot have calculated members.

Aggregate values based on the value of another field

I'm not having much fun trying to aggregate values based on the value of
another field
My query is returning the following...
DATE, VOLUME, PRODUCT
1/1/07, 22, OIL
1/1/07, 0, WATER
1/2/07, 8, OIL
1/2/07, 12, WATER
I want to sum all the VOLUME values where the product = 'OIL' and another to
sum where the product = 'GAS'. Is this type of conditional aggregation
possible. If so how?Correction: I want I want to sum all the VOLUME values where the product ='OIL' and another to sum where the product = 'WATER'.
"smithcjb" wrote:
> I'm not having much fun trying to aggregate values based on the value of
> another field
> My query is returning the following...
> DATE, VOLUME, PRODUCT
> 1/1/07, 22, OIL
> 1/1/07, 0, WATER
> 1/2/07, 8, OIL
> 1/2/07, 12, WATER
> I want to sum all the VOLUME values where the product = 'OIL' and another to
> sum where the product = 'GAS'. Is this type of conditional aggregation
> possible. If so how?|||Hey Smithcjb,
If you are trying to do this in your SQL statement, then drop the "date"
(you can leave it in if your are filtering by it, just make sure you define
it as part of the WHERE clause only). Write it like this
SELECT PRODUCT, SUM(VOLUME)
FROM MyTable
WHERE Date Between Date1 and Date2
GROUP BY PRODUCT
If your trying to do this in a table cell or matrix you could use a
conditinal that says
=SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
then again for water
=SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
and so on...
Michael C
"smithcjb" wrote:
> I'm not having much fun trying to aggregate values based on the value of
> another field
> My query is returning the following...
> DATE, VOLUME, PRODUCT
> 1/1/07, 22, OIL
> 1/1/07, 0, WATER
> 1/2/07, 8, OIL
> 1/2/07, 12, WATER
> I want to sum all the VOLUME values where the product = 'OIL' and another to
> sum where the product = 'GAS'. Is this type of conditional aggregation
> possible. If so how?|||For performance reasons I wanted to do this in the report and not in the SQL.
I've tried grouping by the following in the report with no success...
=SUM(IIf(Fields!PRODUCT.Value="OIL",Fields!LIQUID_VOL.Value,0))
"Michael C" wrote:
> Hey Smithcjb,
> If you are trying to do this in your SQL statement, then drop the "date"
> (you can leave it in if your are filtering by it, just make sure you define
> it as part of the WHERE clause only). Write it like this
> SELECT PRODUCT, SUM(VOLUME)
> FROM MyTable
> WHERE Date Between Date1 and Date2
> GROUP BY PRODUCT
> If your trying to do this in a table cell or matrix you could use a
> conditinal that says
> =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> then again for water
> =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> and so on...
> Michael C
> "smithcjb" wrote:
> > I'm not having much fun trying to aggregate values based on the value of
> > another field
> >
> > My query is returning the following...
> > DATE, VOLUME, PRODUCT
> >
> > 1/1/07, 22, OIL
> > 1/1/07, 0, WATER
> > 1/2/07, 8, OIL
> > 1/2/07, 12, WATER
> >
> > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > sum where the product = 'GAS'. Is this type of conditional aggregation
> > possible. If so how?|||Just to reiterate. I want to perform this in the report, not the query. I
tried the conditional expressions you provided but the report just returns
"Error" in the field
"Michael C" wrote:
> Hey Smithcjb,
> If you are trying to do this in your SQL statement, then drop the "date"
> (you can leave it in if your are filtering by it, just make sure you define
> it as part of the WHERE clause only). Write it like this
> SELECT PRODUCT, SUM(VOLUME)
> FROM MyTable
> WHERE Date Between Date1 and Date2
> GROUP BY PRODUCT
> If your trying to do this in a table cell or matrix you could use a
> conditinal that says
> =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> then again for water
> =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> and so on...
> Michael C
> "smithcjb" wrote:
> > I'm not having much fun trying to aggregate values based on the value of
> > another field
> >
> > My query is returning the following...
> > DATE, VOLUME, PRODUCT
> >
> > 1/1/07, 22, OIL
> > 1/1/07, 0, WATER
> > 1/2/07, 8, OIL
> > 1/2/07, 12, WATER
> >
> > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > sum where the product = 'GAS'. Is this type of conditional aggregation
> > possible. If so how?|||Is this aggregate happening in a table? Is it happening in a Group footer?
What is the grouping? What error are you getting? By all accounts this
should work.
Michael C.
"smithcjb" wrote:
> For performance reasons I wanted to do this in the report and not in the SQL.
> I've tried grouping by the following in the report with no success...
> =SUM(IIf(Fields!PRODUCT.Value="OIL",Fields!LIQUID_VOL.Value,0))
> "Michael C" wrote:
> > Hey Smithcjb,
> > If you are trying to do this in your SQL statement, then drop the "date"
> > (you can leave it in if your are filtering by it, just make sure you define
> > it as part of the WHERE clause only). Write it like this
> >
> > SELECT PRODUCT, SUM(VOLUME)
> > FROM MyTable
> > WHERE Date Between Date1 and Date2
> > GROUP BY PRODUCT
> >
> > If your trying to do this in a table cell or matrix you could use a
> > conditinal that says
> >
> > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > then again for water
> > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> >
> > and so on...
> >
> > Michael C
> >
> > "smithcjb" wrote:
> >
> > > I'm not having much fun trying to aggregate values based on the value of
> > > another field
> > >
> > > My query is returning the following...
> > > DATE, VOLUME, PRODUCT
> > >
> > > 1/1/07, 22, OIL
> > > 1/1/07, 0, WATER
> > > 1/2/07, 8, OIL
> > > 1/2/07, 12, WATER
> > >
> > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > possible. If so how?|||Tables (also called matrix) exist INSIDE reports and have nothing to do with
Datasets. I fully understand what your saying, so no need to "reiterate"
anything.
Also, the comment "for performance reasons i want to do this in the report".
Are you saying you want the report to run slower? Performance wise , as far
as I've read, would suggest you do this in the SQL. But hey...its up to you.
Michael C.
"smithcjb" wrote:
> Just to reiterate. I want to perform this in the report, not the query. I
> tried the conditional expressions you provided but the report just returns
> "Error" in the field
> "Michael C" wrote:
> > Hey Smithcjb,
> > If you are trying to do this in your SQL statement, then drop the "date"
> > (you can leave it in if your are filtering by it, just make sure you define
> > it as part of the WHERE clause only). Write it like this
> >
> > SELECT PRODUCT, SUM(VOLUME)
> > FROM MyTable
> > WHERE Date Between Date1 and Date2
> > GROUP BY PRODUCT
> >
> > If your trying to do this in a table cell or matrix you could use a
> > conditinal that says
> >
> > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > then again for water
> > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> >
> > and so on...
> >
> > Michael C
> >
> > "smithcjb" wrote:
> >
> > > I'm not having much fun trying to aggregate values based on the value of
> > > another field
> > >
> > > My query is returning the following...
> > > DATE, VOLUME, PRODUCT
> > >
> > > 1/1/07, 22, OIL
> > > 1/1/07, 0, WATER
> > > 1/2/07, 8, OIL
> > > 1/2/07, 12, WATER
> > >
> > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > possible. If so how?|||Michael,
I appreciate your comments. This is my full query. I'm returning 3 rows per
date (1 per product). I've tried to use inner joins on the view I'm pulling
from but performance is woeful for large date ranges. I'm trying to sum the
production for the specified date range for each product - easy for GAS since
it appears in it's own column! Any further help greatly appreciated. I don't
know why the expressions you provided won;t workm - they seem logical to me!
Kind regards,
Colin
SELECT c.ITEM_NAME AS COMPLETION_NAME, c.ITEM_ID AS COMPLETION_ITEM_ID,
p.START_DATETIME, p.GAS_VOL, p.LIQUID_VOL,p.PRODUCT
FROM dbo.VI_COMPLETION_en_US AS c INNER JOIN
dbo.ITEM_LINK AS il ON c.ITEM_ID = il.FROM_ITEM_ID
INNER JOIN
dbo.VT_ACT_DAY_en_US AS p ON c.ITEM_ID = p.ITEM_ID
WHERE (il.LINK_TYPE = 'NET_MEMBER') AND (p.START_DATETIME between
'1/1/04' and '1/1/07') AND (c.ITEM_ID IN
(SELECT COMPLETION_ITEM_ID
FROM dbo.REP_ORG_COMPLETION
WHERE (FIELD_ITEM_ID = @.FieldItemId)))
GROUP BY c.ITEM_NAME, c.ITEM_ID, p.START_DATETIME, p.GAS_VOL,
p.LIQUID_VOL,p.PRODUCT
"Michael C" wrote:
>
> Tables (also called matrix) exist INSIDE reports and have nothing to do with
> Datasets. I fully understand what your saying, so no need to "reiterate"
> anything.
>
> Also, the comment "for performance reasons i want to do this in the report".
> Are you saying you want the report to run slower? Performance wise , as far
> as I've read, would suggest you do this in the SQL. But hey...its up to you.
> Michael C.
> "smithcjb" wrote:
> > Just to reiterate. I want to perform this in the report, not the query. I
> > tried the conditional expressions you provided but the report just returns
> > "Error" in the field
> >
> > "Michael C" wrote:
> >
> > > Hey Smithcjb,
> > > If you are trying to do this in your SQL statement, then drop the "date"
> > > (you can leave it in if your are filtering by it, just make sure you define
> > > it as part of the WHERE clause only). Write it like this
> > >
> > > SELECT PRODUCT, SUM(VOLUME)
> > > FROM MyTable
> > > WHERE Date Between Date1 and Date2
> > > GROUP BY PRODUCT
> > >
> > > If your trying to do this in a table cell or matrix you could use a
> > > conditinal that says
> > >
> > > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > > then again for water
> > > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> > >
> > > and so on...
> > >
> > > Michael C
> > >
> > > "smithcjb" wrote:
> > >
> > > > I'm not having much fun trying to aggregate values based on the value of
> > > > another field
> > > >
> > > > My query is returning the following...
> > > > DATE, VOLUME, PRODUCT
> > > >
> > > > 1/1/07, 22, OIL
> > > > 1/1/07, 0, WATER
> > > > 1/2/07, 8, OIL
> > > > 1/2/07, 12, WATER
> > > >
> > > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > > possible. If so how?|||Okay, so now I do understand your want to do this in report. I'm a little
stumped at why the IIF won't work (Unless the report crosses pages?).
I would suggest trying a CASE statement in your SQL to give both OIL and
WATER their own column at least to benchmark what the added overhead is.
CASE WHEN p.PRODUCT = 'OIL' THEN p.LIQUID_VOL ELSE 0 END as OIL_VOL,
CASE WHEN p.PRODUCT = 'WATER' THEN p.LIQUID_VOL ELSE 0 END as WATER_VOL,
If it is minimal overhead to do this then voila, you now have columns for
both oil and water (unless of course other products like NGL's and
Condensates are being included in your products list in which case you'll
need to expand the case statements).
Michael C.
"smithcjb" wrote:
> Michael,
> I appreciate your comments. This is my full query. I'm returning 3 rows per
> date (1 per product). I've tried to use inner joins on the view I'm pulling
> from but performance is woeful for large date ranges. I'm trying to sum the
> production for the specified date range for each product - easy for GAS since
> it appears in it's own column! Any further help greatly appreciated. I don't
> know why the expressions you provided won;t workm - they seem logical to me!
> Kind regards,
> Colin
> SELECT c.ITEM_NAME AS COMPLETION_NAME, c.ITEM_ID AS COMPLETION_ITEM_ID,
> p.START_DATETIME, p.GAS_VOL, p.LIQUID_VOL,p.PRODUCT
> FROM dbo.VI_COMPLETION_en_US AS c INNER JOIN
> dbo.ITEM_LINK AS il ON c.ITEM_ID = il.FROM_ITEM_ID
> INNER JOIN
> dbo.VT_ACT_DAY_en_US AS p ON c.ITEM_ID = p.ITEM_ID
> WHERE (il.LINK_TYPE = 'NET_MEMBER') AND (p.START_DATETIME between
> '1/1/04' and '1/1/07') AND (c.ITEM_ID IN
> (SELECT COMPLETION_ITEM_ID
> FROM dbo.REP_ORG_COMPLETION
> WHERE (FIELD_ITEM_ID = @.FieldItemId)))
> GROUP BY c.ITEM_NAME, c.ITEM_ID, p.START_DATETIME, p.GAS_VOL,
> p.LIQUID_VOL,p.PRODUCT
> "Michael C" wrote:
> >
> >
> > Tables (also called matrix) exist INSIDE reports and have nothing to do with
> > Datasets. I fully understand what your saying, so no need to "reiterate"
> > anything.
> >
> >
> > Also, the comment "for performance reasons i want to do this in the report".
> > Are you saying you want the report to run slower? Performance wise , as far
> > as I've read, would suggest you do this in the SQL. But hey...its up to you.
> >
> > Michael C.
> >
> > "smithcjb" wrote:
> >
> > > Just to reiterate. I want to perform this in the report, not the query. I
> > > tried the conditional expressions you provided but the report just returns
> > > "Error" in the field
> > >
> > > "Michael C" wrote:
> > >
> > > > Hey Smithcjb,
> > > > If you are trying to do this in your SQL statement, then drop the "date"
> > > > (you can leave it in if your are filtering by it, just make sure you define
> > > > it as part of the WHERE clause only). Write it like this
> > > >
> > > > SELECT PRODUCT, SUM(VOLUME)
> > > > FROM MyTable
> > > > WHERE Date Between Date1 and Date2
> > > > GROUP BY PRODUCT
> > > >
> > > > If your trying to do this in a table cell or matrix you could use a
> > > > conditinal that says
> > > >
> > > > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > > > then again for water
> > > > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> > > >
> > > > and so on...
> > > >
> > > > Michael C
> > > >
> > > > "smithcjb" wrote:
> > > >
> > > > > I'm not having much fun trying to aggregate values based on the value of
> > > > > another field
> > > > >
> > > > > My query is returning the following...
> > > > > DATE, VOLUME, PRODUCT
> > > > >
> > > > > 1/1/07, 22, OIL
> > > > > 1/1/07, 0, WATER
> > > > > 1/2/07, 8, OIL
> > > > > 1/2/07, 12, WATER
> > > > >
> > > > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > > > possible. If so how?|||Well,I hope i'm not being too much of a pain, but you could also use custom
code ( I realize my last answer is exactly what you DIDN"T want to do).
you can create a function that fires on each detail setting static variables
over the group, then a second function that returns the answers at the end of
the group. this is actually quite easy to accomplish too.
Sorry I can't be of more help Colin.
Michael C.
"Michael C" wrote:
> Okay, so now I do understand your want to do this in report. I'm a little
> stumped at why the IIF won't work (Unless the report crosses pages?).
> I would suggest trying a CASE statement in your SQL to give both OIL and
> WATER their own column at least to benchmark what the added overhead is.
> CASE WHEN p.PRODUCT = 'OIL' THEN p.LIQUID_VOL ELSE 0 END as OIL_VOL,
> CASE WHEN p.PRODUCT = 'WATER' THEN p.LIQUID_VOL ELSE 0 END as WATER_VOL,
> If it is minimal overhead to do this then voila, you now have columns for
> both oil and water (unless of course other products like NGL's and
> Condensates are being included in your products list in which case you'll
> need to expand the case statements).
>
> Michael C.
>
>
>
> "smithcjb" wrote:
> > Michael,
> >
> > I appreciate your comments. This is my full query. I'm returning 3 rows per
> > date (1 per product). I've tried to use inner joins on the view I'm pulling
> > from but performance is woeful for large date ranges. I'm trying to sum the
> > production for the specified date range for each product - easy for GAS since
> > it appears in it's own column! Any further help greatly appreciated. I don't
> > know why the expressions you provided won;t workm - they seem logical to me!
> >
> > Kind regards,
> > Colin
> >
> > SELECT c.ITEM_NAME AS COMPLETION_NAME, c.ITEM_ID AS COMPLETION_ITEM_ID,
> > p.START_DATETIME, p.GAS_VOL, p.LIQUID_VOL,p.PRODUCT
> > FROM dbo.VI_COMPLETION_en_US AS c INNER JOIN
> > dbo.ITEM_LINK AS il ON c.ITEM_ID = il.FROM_ITEM_ID
> > INNER JOIN
> > dbo.VT_ACT_DAY_en_US AS p ON c.ITEM_ID = p.ITEM_ID
> > WHERE (il.LINK_TYPE = 'NET_MEMBER') AND (p.START_DATETIME between
> > '1/1/04' and '1/1/07') AND (c.ITEM_ID IN
> > (SELECT COMPLETION_ITEM_ID
> > FROM dbo.REP_ORG_COMPLETION
> > WHERE (FIELD_ITEM_ID = @.FieldItemId)))
> > GROUP BY c.ITEM_NAME, c.ITEM_ID, p.START_DATETIME, p.GAS_VOL,
> > p.LIQUID_VOL,p.PRODUCT
> >
> > "Michael C" wrote:
> >
> > >
> > >
> > > Tables (also called matrix) exist INSIDE reports and have nothing to do with
> > > Datasets. I fully understand what your saying, so no need to "reiterate"
> > > anything.
> > >
> > >
> > > Also, the comment "for performance reasons i want to do this in the report".
> > > Are you saying you want the report to run slower? Performance wise , as far
> > > as I've read, would suggest you do this in the SQL. But hey...its up to you.
> > >
> > > Michael C.
> > >
> > > "smithcjb" wrote:
> > >
> > > > Just to reiterate. I want to perform this in the report, not the query. I
> > > > tried the conditional expressions you provided but the report just returns
> > > > "Error" in the field
> > > >
> > > > "Michael C" wrote:
> > > >
> > > > > Hey Smithcjb,
> > > > > If you are trying to do this in your SQL statement, then drop the "date"
> > > > > (you can leave it in if your are filtering by it, just make sure you define
> > > > > it as part of the WHERE clause only). Write it like this
> > > > >
> > > > > SELECT PRODUCT, SUM(VOLUME)
> > > > > FROM MyTable
> > > > > WHERE Date Between Date1 and Date2
> > > > > GROUP BY PRODUCT
> > > > >
> > > > > If your trying to do this in a table cell or matrix you could use a
> > > > > conditinal that says
> > > > >
> > > > > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > > > > then again for water
> > > > > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> > > > >
> > > > > and so on...
> > > > >
> > > > > Michael C
> > > > >
> > > > > "smithcjb" wrote:
> > > > >
> > > > > > I'm not having much fun trying to aggregate values based on the value of
> > > > > > another field
> > > > > >
> > > > > > My query is returning the following...
> > > > > > DATE, VOLUME, PRODUCT
> > > > > >
> > > > > > 1/1/07, 22, OIL
> > > > > > 1/1/07, 0, WATER
> > > > > > 1/2/07, 8, OIL
> > > > > > 1/2/07, 12, WATER
> > > > > >
> > > > > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > > > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > > > > possible. If so how?|||Michael,
As it happens your SQL has been of great value. I've rebuilt the view and
the CASE statements (which are new to me) - and seem to be doing the trick.
Many thanks,
Colin
"Michael C" wrote:
> Well,I hope i'm not being too much of a pain, but you could also use custom
> code ( I realize my last answer is exactly what you DIDN"T want to do).
> you can create a function that fires on each detail setting static variables
> over the group, then a second function that returns the answers at the end of
> the group. this is actually quite easy to accomplish too.
> Sorry I can't be of more help Colin.
> Michael C.
> "Michael C" wrote:
> >
> > Okay, so now I do understand your want to do this in report. I'm a little
> > stumped at why the IIF won't work (Unless the report crosses pages?).
> >
> > I would suggest trying a CASE statement in your SQL to give both OIL and
> > WATER their own column at least to benchmark what the added overhead is.
> >
> > CASE WHEN p.PRODUCT = 'OIL' THEN p.LIQUID_VOL ELSE 0 END as OIL_VOL,
> > CASE WHEN p.PRODUCT = 'WATER' THEN p.LIQUID_VOL ELSE 0 END as WATER_VOL,
> >
> > If it is minimal overhead to do this then voila, you now have columns for
> > both oil and water (unless of course other products like NGL's and
> > Condensates are being included in your products list in which case you'll
> > need to expand the case statements).
> >
> >
> > Michael C.
> >
> >
> >
> >
> >
> >
> > "smithcjb" wrote:
> >
> > > Michael,
> > >
> > > I appreciate your comments. This is my full query. I'm returning 3 rows per
> > > date (1 per product). I've tried to use inner joins on the view I'm pulling
> > > from but performance is woeful for large date ranges. I'm trying to sum the
> > > production for the specified date range for each product - easy for GAS since
> > > it appears in it's own column! Any further help greatly appreciated. I don't
> > > know why the expressions you provided won;t workm - they seem logical to me!
> > >
> > > Kind regards,
> > > Colin
> > >
> > > SELECT c.ITEM_NAME AS COMPLETION_NAME, c.ITEM_ID AS COMPLETION_ITEM_ID,
> > > p.START_DATETIME, p.GAS_VOL, p.LIQUID_VOL,p.PRODUCT
> > > FROM dbo.VI_COMPLETION_en_US AS c INNER JOIN
> > > dbo.ITEM_LINK AS il ON c.ITEM_ID = il.FROM_ITEM_ID
> > > INNER JOIN
> > > dbo.VT_ACT_DAY_en_US AS p ON c.ITEM_ID = p.ITEM_ID
> > > WHERE (il.LINK_TYPE = 'NET_MEMBER') AND (p.START_DATETIME between
> > > '1/1/04' and '1/1/07') AND (c.ITEM_ID IN
> > > (SELECT COMPLETION_ITEM_ID
> > > FROM dbo.REP_ORG_COMPLETION
> > > WHERE (FIELD_ITEM_ID = @.FieldItemId)))
> > > GROUP BY c.ITEM_NAME, c.ITEM_ID, p.START_DATETIME, p.GAS_VOL,
> > > p.LIQUID_VOL,p.PRODUCT
> > >
> > > "Michael C" wrote:
> > >
> > > >
> > > >
> > > > Tables (also called matrix) exist INSIDE reports and have nothing to do with
> > > > Datasets. I fully understand what your saying, so no need to "reiterate"
> > > > anything.
> > > >
> > > >
> > > > Also, the comment "for performance reasons i want to do this in the report".
> > > > Are you saying you want the report to run slower? Performance wise , as far
> > > > as I've read, would suggest you do this in the SQL. But hey...its up to you.
> > > >
> > > > Michael C.
> > > >
> > > > "smithcjb" wrote:
> > > >
> > > > > Just to reiterate. I want to perform this in the report, not the query. I
> > > > > tried the conditional expressions you provided but the report just returns
> > > > > "Error" in the field
> > > > >
> > > > > "Michael C" wrote:
> > > > >
> > > > > > Hey Smithcjb,
> > > > > > If you are trying to do this in your SQL statement, then drop the "date"
> > > > > > (you can leave it in if your are filtering by it, just make sure you define
> > > > > > it as part of the WHERE clause only). Write it like this
> > > > > >
> > > > > > SELECT PRODUCT, SUM(VOLUME)
> > > > > > FROM MyTable
> > > > > > WHERE Date Between Date1 and Date2
> > > > > > GROUP BY PRODUCT
> > > > > >
> > > > > > If your trying to do this in a table cell or matrix you could use a
> > > > > > conditinal that says
> > > > > >
> > > > > > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > > > > > then again for water
> > > > > > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> > > > > >
> > > > > > and so on...
> > > > > >
> > > > > > Michael C
> > > > > >
> > > > > > "smithcjb" wrote:
> > > > > >
> > > > > > > I'm not having much fun trying to aggregate values based on the value of
> > > > > > > another field
> > > > > > >
> > > > > > > My query is returning the following...
> > > > > > > DATE, VOLUME, PRODUCT
> > > > > > >
> > > > > > > 1/1/07, 22, OIL
> > > > > > > 1/1/07, 0, WATER
> > > > > > > 1/2/07, 8, OIL
> > > > > > > 1/2/07, 12, WATER
> > > > > > >
> > > > > > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > > > > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > > > > > possible. If so how?|||Colin,
My pleasure, Im glad I could be of assistance.
Michael
"smithcjb" wrote:
> Michael,
> As it happens your SQL has been of great value. I've rebuilt the view and
> the CASE statements (which are new to me) - and seem to be doing the trick.
> Many thanks,
> Colin
> "Michael C" wrote:
> > Well,I hope i'm not being too much of a pain, but you could also use custom
> > code ( I realize my last answer is exactly what you DIDN"T want to do).
> >
> > you can create a function that fires on each detail setting static variables
> > over the group, then a second function that returns the answers at the end of
> > the group. this is actually quite easy to accomplish too.
> >
> > Sorry I can't be of more help Colin.
> >
> > Michael C.
> >
> > "Michael C" wrote:
> >
> > >
> > > Okay, so now I do understand your want to do this in report. I'm a little
> > > stumped at why the IIF won't work (Unless the report crosses pages?).
> > >
> > > I would suggest trying a CASE statement in your SQL to give both OIL and
> > > WATER their own column at least to benchmark what the added overhead is.
> > >
> > > CASE WHEN p.PRODUCT = 'OIL' THEN p.LIQUID_VOL ELSE 0 END as OIL_VOL,
> > > CASE WHEN p.PRODUCT = 'WATER' THEN p.LIQUID_VOL ELSE 0 END as WATER_VOL,
> > >
> > > If it is minimal overhead to do this then voila, you now have columns for
> > > both oil and water (unless of course other products like NGL's and
> > > Condensates are being included in your products list in which case you'll
> > > need to expand the case statements).
> > >
> > >
> > > Michael C.
> > >
> > >
> > >
> > >
> > >
> > >
> > > "smithcjb" wrote:
> > >
> > > > Michael,
> > > >
> > > > I appreciate your comments. This is my full query. I'm returning 3 rows per
> > > > date (1 per product). I've tried to use inner joins on the view I'm pulling
> > > > from but performance is woeful for large date ranges. I'm trying to sum the
> > > > production for the specified date range for each product - easy for GAS since
> > > > it appears in it's own column! Any further help greatly appreciated. I don't
> > > > know why the expressions you provided won;t workm - they seem logical to me!
> > > >
> > > > Kind regards,
> > > > Colin
> > > >
> > > > SELECT c.ITEM_NAME AS COMPLETION_NAME, c.ITEM_ID AS COMPLETION_ITEM_ID,
> > > > p.START_DATETIME, p.GAS_VOL, p.LIQUID_VOL,p.PRODUCT
> > > > FROM dbo.VI_COMPLETION_en_US AS c INNER JOIN
> > > > dbo.ITEM_LINK AS il ON c.ITEM_ID = il.FROM_ITEM_ID
> > > > INNER JOIN
> > > > dbo.VT_ACT_DAY_en_US AS p ON c.ITEM_ID = p.ITEM_ID
> > > > WHERE (il.LINK_TYPE = 'NET_MEMBER') AND (p.START_DATETIME between
> > > > '1/1/04' and '1/1/07') AND (c.ITEM_ID IN
> > > > (SELECT COMPLETION_ITEM_ID
> > > > FROM dbo.REP_ORG_COMPLETION
> > > > WHERE (FIELD_ITEM_ID = @.FieldItemId)))
> > > > GROUP BY c.ITEM_NAME, c.ITEM_ID, p.START_DATETIME, p.GAS_VOL,
> > > > p.LIQUID_VOL,p.PRODUCT
> > > >
> > > > "Michael C" wrote:
> > > >
> > > > >
> > > > >
> > > > > Tables (also called matrix) exist INSIDE reports and have nothing to do with
> > > > > Datasets. I fully understand what your saying, so no need to "reiterate"
> > > > > anything.
> > > > >
> > > > >
> > > > > Also, the comment "for performance reasons i want to do this in the report".
> > > > > Are you saying you want the report to run slower? Performance wise , as far
> > > > > as I've read, would suggest you do this in the SQL. But hey...its up to you.
> > > > >
> > > > > Michael C.
> > > > >
> > > > > "smithcjb" wrote:
> > > > >
> > > > > > Just to reiterate. I want to perform this in the report, not the query. I
> > > > > > tried the conditional expressions you provided but the report just returns
> > > > > > "Error" in the field
> > > > > >
> > > > > > "Michael C" wrote:
> > > > > >
> > > > > > > Hey Smithcjb,
> > > > > > > If you are trying to do this in your SQL statement, then drop the "date"
> > > > > > > (you can leave it in if your are filtering by it, just make sure you define
> > > > > > > it as part of the WHERE clause only). Write it like this
> > > > > > >
> > > > > > > SELECT PRODUCT, SUM(VOLUME)
> > > > > > > FROM MyTable
> > > > > > > WHERE Date Between Date1 and Date2
> > > > > > > GROUP BY PRODUCT
> > > > > > >
> > > > > > > If your trying to do this in a table cell or matrix you could use a
> > > > > > > conditinal that says
> > > > > > >
> > > > > > > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > > > > > > then again for water
> > > > > > > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> > > > > > >
> > > > > > > and so on...
> > > > > > >
> > > > > > > Michael C
> > > > > > >
> > > > > > > "smithcjb" wrote:
> > > > > > >
> > > > > > > > I'm not having much fun trying to aggregate values based on the value of
> > > > > > > > another field
> > > > > > > >
> > > > > > > > My query is returning the following...
> > > > > > > > DATE, VOLUME, PRODUCT
> > > > > > > >
> > > > > > > > 1/1/07, 22, OIL
> > > > > > > > 1/1/07, 0, WATER
> > > > > > > > 1/2/07, 8, OIL
> > > > > > > > 1/2/07, 12, WATER
> > > > > > > >
> > > > > > > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > > > > > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > > > > > > possible. If so how?

Aggregate Concatinate in join

I have a two tables:
table1
id
name
table2
id
date
I'd like to produce
table3:
id names date
where names = all names for the id concatinated seperated by ","jobs
You have two ID columns ,which one you want?
select tb1 .id,name,date from tb1 join tb2 on tb1.id=tb2.id
where name in ('a','b','c')
"jobs" <jobs@.webdos.com> wrote in message
news:1162310209.501090.68440@.e3g2000cwe.googlegroups.com...
>I have a two tables:
> table1
> id
> name
> table2
> id
> date
> I'd like to produce
> table3:
> id names date
> where names = all names for the id concatinated seperated by ","
>|||Is table2 supposed to have a name column where a row in table 1 corresponds a
row in table 2 where the id's are equal?
try this it will create the table and populate it with your data from the
other tables.
select rtrim(a.name)+','+b.name as names, a.id as id, b.[date] as [date]
into table3 from table1 as a inner join table2 as b on (a.id=b.id)
if the other tables are being updated or used by apps then you can use a
view... create view as remove the 'into table3'
Hope it helps,
Netmon
"jobs" wrote:
> I have a two tables:
> table1
> id
> name
> table2
> id
> date
> I'd like to produce
> table3:
> id names date
> where names = all names for the id concatinated seperated by ","
>|||Netmon wrote:
> Is table2 supposed to have a name column where a row in table 1 corresponds a
> row in table 2 where the id's are equal?
> try this it will create the table and populate it with your data from the
> other tables.
> select rtrim(a.name)+','+b.name as names, a.id as id, b.[date] as [date]
> into table3 from table1 as a inner join table2 as b on (a.id=b.id)
> if the other tables are being updated or used by apps then you can use a
> view... create view as remove the 'into table3'
> Hope it helps,
> Netmon
>
> "jobs" wrote:
>
> > I have a two tables:
> >
> > table1
> > id
> > name
> >
> > table2
> > id
> > date
> >
> > I'd like to produce
> >
> > table3:
> > id names date
> >
> > where names = all names for the id concatinated seperated by ","
> >
> >
In SQL Server 2005 you can do like this
select distinct a.id , stuff((select ','+name as [text()] from a as b
where a.id = b.id for xml path('')),1,1,'') as names,
b.date
from a inner join b on a.id = b.id
Regards
Amish Shah

Ageing from system date by number of days

Can anyone help with how I write a script that takes a balance and then age
it by its transaction dates into 30, 60 90 days etc?
My script is as follows and the "AS2 on the 2nd line is coming out as
incorrect syntax:
SELECT
GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(MONTH, TransactionDate, GETDATE()) AS no_of_days_since,
SUM(GoodsValueInAccountCurrency) AS THIRTY WHERE (no_of_days_since BETWEEN 1
AND 30) AND
SUM(GoodsValueInAccountCurrency) AS SIXTY WHERE (no_of_days_since between 31
and 60) AND
SUM(GoodsValueInAccountCurrency) AS NINETY WHERE (no_of_days_since between
61 and 90)AND
SUM(GoodsValueInAccountCurrency) AS MORE WHERE (no_of_days_since > 90)
FROM PLPostedSupplierTran
"WendyUK" wrote:

> Can anyone help with how I write a script that takes a balance and then age
> it by its transaction dates into 30, 60 90 days etc?
|||Wendy,
A couple of things:
1 - DATEDIFF (MONTH... ) does not give you what you want since February 28
and March 1 are one month apart. Use DAY instead of MONTH.
2 - WHERE appears after the FROM and only 1 per query. For your columns,
read about the CASE statement. You will need something like the following
untested code:
SELECT GoodsValueInAccountCurrency, TransactionDate,
SUM (CASE ((DATEDIFF(DAY,TransactionDate, GETDATE()) - 1) / 30)
WHEN(0) THEN GoodsValueInAccountCurrency
ELSE 0
END ) AS THIRTY,
SUM (CASE ((DATEDIFF(DAY,TransactionDate, GETDATE()) - 1) / 30)
WHEN(1) THEN GoodsValueInAccountCurrency
ELSE 0
END ) AS SIXTY,
. . . etc
RLF
"WendyUK" <WendyUK@.discussions.microsoft.com> wrote in message
news:681A829D-F604-41D0-A485-B9B92132180C@.microsoft.com...[vbcol=seagreen]
> My script is as follows and the "AS2 on the 2nd line is coming out as
> incorrect syntax:
> SELECT
> GoodsValueInAccountCurrency, TransactionDate,
> DATEDIFF(MONTH, TransactionDate, GETDATE()) AS no_of_days_since,
> SUM(GoodsValueInAccountCurrency) AS THIRTY WHERE (no_of_days_since BETWEEN
> 1
> AND 30) AND
> SUM(GoodsValueInAccountCurrency) AS SIXTY WHERE (no_of_days_since between
> 31
> and 60) AND
> SUM(GoodsValueInAccountCurrency) AS NINETY WHERE (no_of_days_since between
> 61 and 90)AND
> SUM(GoodsValueInAccountCurrency) AS MORE WHERE (no_of_days_since > 90)
>
> FROM PLPostedSupplierTran
>
> "WendyUK" wrote:

age into age band

Hi,

I have a table of members including date of birth value.

I am not finding any easy way to count the number of members per age band.
0-10, 10-20,20-30 etc.

It seems that the decode function works only for equality tests not for other complex conditions.

Until now, I am using as many count queries as age bands and look whether the date of birth of the member falls between two dates

Any thoughts?

Thanks

Edwardwhat i use is

select dob, floor(months_between(sysdate,dob)/120) ag from person;

Bill

Age calucation

Hai frnds can u tell me the simple syntax using one example how caluclate the aage using system date.i am hanving only DOB field in my table.

Quote:

Originally Posted by chaitanyacrn

Hai frnds can u tell me the simple syntax using one example how caluclate the aage using system date.i am hanving only DOB field in my table.

select datediff(year,DOB,getdate()) from table

Again a date problem inside MSSQL server

I am having java code which inserts date (which is in String format) into
MSSQL table.
The format of date is "yyyy-MM-dd hh:mm:ss" (I believe this is a standard
format ?). On my local m/c it was working fine, but when shifted to new
serverit stopped working. On new serverit takes "dd/MM/yyyy hh:mm:ss" format
only. the above format - it gives me Airthmatic overflow error.
1)Is there any standard format which will always work on MSSQL ?
2)btw, how this format is set on SQL server ? Can i change the date format
to be displayed on Enterprise manager ?
regards
Manisha
check out the ISO8601 format; search BOL for convert using style 126. This
format is international and will always work no matter how the SQL Server is
set up.
yyyy-mm-ddThh:mm:ss:mmm(no spaces)
regards,
Mark Baekdal
http://www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"manisha_css" wrote:

> I am having java code which inserts date (which is in String format) into
> MSSQL table.
> The format of date is "yyyy-MM-dd hh:mm:ss" (I believe this is a standard
> format ?). On my local m/c it was working fine, but when shifted to new
> serverit stopped working. On new serverit takes "dd/MM/yyyy hh:mm:ss" format
> only. the above format - it gives me Airthmatic overflow error.
> 1)Is there any standard format which will always work on MSSQL ?
> 2)btw, how this format is set on SQL server ? Can i change the date format
> to be displayed on Enterprise manager ?
> regards
> Manisha
>
>
|||BOL - writing international transact-sql statements
"manisha_css" <manishacss@.discussions.microsoft.com> wrote in message
news:6017A109-369E-48B5-B6B0-3F6DDCF773F3@.microsoft.com...
> I am having java code which inserts date (which is in String format) into
> MSSQL table.
> The format of date is "yyyy-MM-dd hh:mm:ss" (I believe this is a standard
> format ?). On my local m/c it was working fine, but when shifted to new
> serverit stopped working. On new serverit takes "dd/MM/yyyy hh:mm:ss"
format
> only. the above format - it gives me Airthmatic overflow error.
> 1)Is there any standard format which will always work on MSSQL ?
> 2)btw, how this format is set on SQL server ? Can i change the date format
> to be displayed on Enterprise manager ?
> regards
> Manisha
>
>

Again a date problem inside MSSQL server

I am having java code which inserts date (which is in String format) into
MSSQL table.
The format of date is "yyyy-MM-dd hh:mm:ss" (I believe this is a standard
format '). On my local m/c it was working fine, but when shifted to new
serverit stopped working. On new serverit takes "dd/MM/yyyy hh:mm:ss" format
only. the above format - it gives me Airthmatic overflow error.
1)Is there any standard format which will always work on MSSQL ?
2)btw, how this format is set on SQL server ? Can i change the date format
to be displayed on Enterprise manager ?
regards
Manishacheck out the ISO8601 format; search BOL for convert using style 126. This
format is international and will always work no matter how the SQL Server is
set up.
yyyy-mm-ddThh:mm:ss:mmm(no spaces)
regards,
Mark Baekdal
http://www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"manisha_css" wrote:
> I am having java code which inserts date (which is in String format) into
> MSSQL table.
> The format of date is "yyyy-MM-dd hh:mm:ss" (I believe this is a standard
> format '). On my local m/c it was working fine, but when shifted to new
> serverit stopped working. On new serverit takes "dd/MM/yyyy hh:mm:ss" format
> only. the above format - it gives me Airthmatic overflow error.
> 1)Is there any standard format which will always work on MSSQL ?
> 2)btw, how this format is set on SQL server ? Can i change the date format
> to be displayed on Enterprise manager ?
> regards
> Manisha
>
>|||BOL - writing international transact-sql statements
"manisha_css" <manishacss@.discussions.microsoft.com> wrote in message
news:6017A109-369E-48B5-B6B0-3F6DDCF773F3@.microsoft.com...
> I am having java code which inserts date (which is in String format) into
> MSSQL table.
> The format of date is "yyyy-MM-dd hh:mm:ss" (I believe this is a standard
> format '). On my local m/c it was working fine, but when shifted to new
> serverit stopped working. On new serverit takes "dd/MM/yyyy hh:mm:ss"
format
> only. the above format - it gives me Airthmatic overflow error.
> 1)Is there any standard format which will always work on MSSQL ?
> 2)btw, how this format is set on SQL server ? Can i change the date format
> to be displayed on Enterprise manager ?
> regards
> Manisha
>
>

Again a date problem inside MSSQL server

I am having Java code which inserts date (which is in String format) into
MSSQL table.
The format of date is "yyyy-MM-dd hh:mm:ss" (I believe this is a standard
format '). On my local m/c it was working fine, but when shifted to new
serverit stopped working. On new serverit takes "dd/MM/yyyy hh:mm:ss" format
only. the above format - it gives me Airthmatic overflow error.
1)Is there any standard format which will always work on MSSQL ?
2)btw, how this format is set on SQL server ? Can i change the date format
to be displayed on Enterprise manager ?
regards
Manishacheck out the ISO8601 format; search BOL for convert using style 126. This
format is international and will always work no matter how the SQL Server is
set up.
yyyy-mm-ddThh:mm:ss:mmm(no spaces)
regards,
Mark Baekdal
http://www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"manisha_css" wrote:

> I am having Java code which inserts date (which is in String format) into
> MSSQL table.
> The format of date is "yyyy-MM-dd hh:mm:ss" (I believe this is a standard
> format '). On my local m/c it was working fine, but when shifted to new
> serverit stopped working. On new serverit takes "dd/MM/yyyy hh:mm:ss" form
at
> only. the above format - it gives me Airthmatic overflow error.
> 1)Is there any standard format which will always work on MSSQL ?
> 2)btw, how this format is set on SQL server ? Can i change the date format
> to be displayed on Enterprise manager ?
> regards
> Manisha
>
>|||BOL - writing international transact-sql statements
"manisha_css" <manishacss@.discussions.microsoft.com> wrote in message
news:6017A109-369E-48B5-B6B0-3F6DDCF773F3@.microsoft.com...
> I am having Java code which inserts date (which is in String format) into
> MSSQL table.
> The format of date is "yyyy-MM-dd hh:mm:ss" (I believe this is a standard
> format '). On my local m/c it was working fine, but when shifted to new
> serverit stopped working. On new serverit takes "dd/MM/yyyy hh:mm:ss"
format
> only. the above format - it gives me Airthmatic overflow error.
> 1)Is there any standard format which will always work on MSSQL ?
> 2)btw, how this format is set on SQL server ? Can i change the date format
> to be displayed on Enterprise manager ?
> regards
> Manisha
>
>