Advice on SQL statement please.

I have a query that generates the dataset below, based on the year being
filtered I get the sum of an amount Group By the type. What I would like to
do is use the exact qry using a differnet date, to generate a third column
called Prior12Mnths. How would I use my qry to accomplish this task.
I appreciate the help.
Here's my qry:
Select GroupType, Sum(SumRevAmt) as Last12Mnths
from MyQRY
Where Period = '200006'
Group by GroupType
Type Last12Mnths_200006 Prior12Mnths_199906
Airlines 1234.50 '
Concessions 73854.00 '
etc......Russell Verdun wrote:
> I have a query that generates the dataset below, based on the year being
> filtered I get the sum of an amount Group By the type. What I would like t
o
> do is use the exact qry using a differnet date, to generate a third column
> called Prior12Mnths. How would I use my qry to accomplish this task.
> I appreciate the help.
> Here's my qry:
> Select GroupType, Sum(SumRevAmt) as Last12Mnths
> from MyQRY
> Where Period = '200006'
> Group by GroupType
>
>
> Type Last12Mnths_200006 Prior12Mnths_199906
> Airlines 1234.50 '?
?
> Concessions 73854.00 '
> etc......
>
>
It's not clear from your example what this "other date" would be, so
I'll use a different example. Say I have a table containing
transactions, and each transaction consists of an account, a transaction
date, and an amount:
SELECT
account,
SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) <= 12 THEN amount
ELSE 0) AS Last12Months,
SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) BETWEEN 13 AND 24
THEN amount ELSE 0) AS Prev12Months
FROM table
GROUP BY account
Is that enough to get you started?|||Tracy McKibben wrote:
> Russell Verdun wrote:
> It's not clear from your example what this "other date" would be, so
> I'll use a different example. Say I have a table containing
> transactions, and each transaction consists of an account, a transaction
> date, and an amount:
> SELECT
> account,
> SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) <= 12 THEN amount
> ELSE 0) AS Last12Months,
> SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) BETWEEN 13 AND 24
> THEN amount ELSE 0) AS Prev12Months
> FROM table
> GROUP BY account
> Is that enough to get you started?
>
Sorry, those CASE statements are missing ENDs...|||Hi Russell,
I believe you could do something like this:
Select GroupType, Sum(CASE Period=200006 THEN SumRevAmt ELSE 0 END) as
Last12Mnths, Sum(CASE Period=199906 THEN SumRevAmt ELSE 0 END) as
Prior12Mnths
from MyQRY
Where Period = '200006' or Period = '199906'
Group by GroupType
I Dont know if thats the best way, but that is what first comes to
mind.
Paul T.|||>> I have a query that generates the dataset below, based on the year being
filtered I get the sum of an amount Group By the type. What I would like to
do is use the exact qry using a differnet date, to generate a third column
called Prior12Mnths. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is also helpful if the data elements have good
names.
CREATE TABLE Revenues -- guess at meaningful name
(grp_type INTEGER NOT NULL
REFERENCES GroupTypes(grp_type),
rev_amt DECIMAL(12,2) NOT NULL,
rev_date DATETIME NOT NULL PRIMARY KEY);
In the vague pseudo-code you posted, only some kind of vague date can
be a key
The best trick for this kind of summary is to build a reporting range
table
CREATE TABLE ReportRanges
(range_name CHAR() NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (range_name, start_date));
INSERT INTO ReportRanges
VALUES ('2006-06: Prior12' , '2005-06-01', '2006-06-31' );
INSERT INTO ReportRanges
VALUES ('2006-06: ytd' , '2006-01-01', '2006-06-31' );
SELECT grp_type,
SUM (CASE WHEN R.range_name = '2006-06: ytd'
THEN rev_amt ELSE 0.00 END) AS ytd,
SUM (CASE WHEN R.range_name = '2006-06: Prior12'
THEN rev_amt ELSE 0.00 END) AS Prior12,
etc.
FROM Revenues
GROUP BY grp_type;
Adjust the table as needed.

AdomdConnection.GetSchemaDataSet - using restrictions

I'm attempting to use AdomdConnection.GetSchemaDataSet to see what information is in the AdomdSchemaGuid.PartitionStat dataset. I am setting the restrictions parameter to null.

The error I get is "Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException : XML for Analysis parser: The DATABASE_NAME restriction is required but is missing from the request."

All well and good, except that I can't see in the documentation what form the restrictions should be, the parameter is of type object[]. How are the restrictions defined? Is there any documentation that says what restrictions apply to which dataset? Is there any documentation that says what information is returned in the dataset for each AdomdSchemaGuid?

John.

If you were to connect to Analysis Server using SQL Profiler you would see that any call you make in AMO to obtain some schema rowset is translated to a Discover request. For instance

You can read more about schema rowsets in books online: http://msdn2.microsoft.com/en-us/library/ms126233(SQL.90).aspx

See what schema rowset is requested by AMO and then you can look it up in the BOL.

As for specifying restrictions:

restcoll = New AdomdRestrictionCollection;

restcoll.Add("CATALOG_NAME", "MyDatabase");

dsCubes = AdomdConnection.GetSchemaDataSet("MDSCHEMA_CUBES", restcoll)

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward, thanks for this.

This is generally useful knowledge for me, but...

AdomdSchemaGuid.PartitionStat returns a dataset DISCOVER_PARTITION_STAT for which there is no detail of result columns in the documentation.

Further, the dataset is returned by a call to GetSchemaDataSet(Guid, object[]), where it is not documented what the objects of object[] should be. I assume the parameter is not an AdomdRestrictionCollection, otherwise why is the parameter not of that type in the first place, like the GetSchemaDataset(string, AdomdRestrictionCollection) call?

The documentation does not indicate what restrictions are required for each dataset, and the exception does not explicitly say what the name of the restriction is.

John.

|||

There is a little survey on bottom on page in books online. I would encourage you to fill it. This should give an idea about which topics in documentation should get improved.

As for the using discovers. I was getting at giving you some generic mechanism you can use to figure out how to use any discover request.

AS you have seen in Profiler AMO is sending a Discover request to Analysis Server behind the scenes.

You can compose such request directly in SQL Managemet studio open an XMLA query editor. For example of discovering all cubes in your database you can send following request:

<Envelope xmlns = "http://schemas.xmlsoap.org/soap/envelope/">
<Body>
<Discover xmlns = "urn:schemas-microsoft-com:xml-analysis">
<RequestType>MDSCHEMA_CUBES</RequestType>
<Restrictions>
<RestrictionList/>
</Restrictions>
<Properties>
<PropertyList>
<Catalog>MyDatabase</Catalog>
</PropertyList>
</Properties>
</Discover>
</Body>
</Envelope>

The error messages you are getting back from the server when submitting such request should give you an idea what is missing.

In your case looks like the RestrictionList node is missing DATABASE_NAME restriction.

In AMO all AdomdRestrictionCollection object does: it is appending more elements to the RestrictionList node in the XMLA request.

So the mechanism is pretty generic, you should be able to figure out how to send almost any request and see what is getting returned back to you.

Hope that helps

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

You can find most schema rowsets descriptions for example here: http://msdn2.microsoft.com/en-us/library/ms126233(SQL.90).aspx (proabbly books online have them as well)
Usually you would be able to find the descriptions of returned columns and restrictions columns in the docs. However, i think some schemas are missing, and DISCOVER_PARTITION_STAT seems to be one of those.
In such a case i can suggest executing the Discover_Schema_Rowsets in the SSMS, which should return you the list of supported schemas and their restrictions.
Request can look something liek this:

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Body>
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DISCOVER_SCHEMA_ROWSETS</RequestType>
<Restrictions>
<RestrictionList/>
</Restrictions>
<Properties>
<PropertyList/>
</Properties>
</Discover>
</Body>
</Envelope>

for the DISCOVER_PARTITION_STAT, we get the following restrictions:

<Restrictions>
<Name>DATABASE_NAME</Name>
<Type>xsd:string</Type>
</Restrictions>
<Restrictions>
<Name>CUBE_NAME</Name>
<Type>xsd:string</Type>
</Restrictions>
<Restrictions>
<Name>MEASURE_GROUP_NAME</Name>
<Type>xsd:string</Type>
</Restrictions>
<Restrictions>
<Name>PARTITION_NAME</Name>
<Type>xsd:string</Type>
</Restrictions>

Adomd.Net has a number of overloads for GetSchemaDataSet function.
All GetSchemaDataSet overloads basically require you to provide:
- which schema rowset to retrieve (either by schema guid, or by schema name)
- restrictions to be applied in the schema rowset request (either in the "oledb style" – as object[], or using the AdomdRestrictionCollection)
And return the resulting schema rowset as System.Data.DataSet.
In the "ole db" style case, restrictions are mapped by position, and in the other approach - by name.

Here are couple samples to retrieve the DISCOVER_PARTITION_STAT rowset.

// ole db – like approach
// schema specified by guid;
// restrictions matched by position;

// assuming ‘con’ is an opened connection
object[] rest = new object[]
{
"Adventure Works DW", // DATABASE_NAME
"Adventure Works", // CUBE_NAME
"Internet Sales", // MEASURE_GROUP_NAME
"Internet_Sales_2002" // PARTITION_NAME
};

DataTable partitionStat =
con.GetSchemaDataSet(
AdomdSchemaGuid.PartitionStat,
rest).Tables[0];

foreach (DataColumn column in partitionStat.Columns)
{
Debug.WriteLine(column.ColumnName);
}

// another way – schema specified by name;
// restriction matching done by name;
// using restrictions collection

// assuming ‘con’ is an opened connection
AdomdRestrictionCollection restrictions =
new AdomdRestrictionCollection();
restrictions.Add("DATABASE_NAME", "Adventure Works DW");
restrictions.Add("CUBE_NAME", "Adventure Works");
restrictions.Add("MEASURE_GROUP_NAME", "Internet Sales");
restrictions.Add("PARTITION_NAME", "Internet_Sales_2002");

partitionStat =
con.GetSchemaDataSet(
"DISCOVER_PARTITION_STAT",
restrictions).Tables[0];

foreach (DataColumn column in partitionStat.Columns)
{
Debug.WriteLine(column.ColumnName);
}

hope this helps some,

|||

Thanks, these posts help a lot. As an aside, is there a reason there's no overloaded version of GetSchemaDataSet that takes a Guid and AdomdRestrictionCollection as parameters?

|||

hello John,

There is no particular strict reason. The 2 sets of overloads available (with guid and name) are basically kind of 2 different style. Personally i prefer second one as it seems to be more self descriptive (especially in the restrictions part). But perhaps there are people who like the first one (and it is consistent with System.Data's GetOleDbSchemaTable). I don't think the overloads should be intermixed, but don't think there is any road block to create such overload. On the other hand i don't think there is a strong need to have one. So i guess it is sort of an arbitrary call.

thanks,

ADO.NET works, but data to/from database is truncated

I am using MSDE in a web application. The ADO.NET works, updating the
database correctly. The only thing is when I use the dataset, and when I
check the data in the database using server explorer the data is truncated. I
tried using the various ntext, nvarchar and setting the length to somthing
like a 100, but the data string is still to short somewhere. Only when this
comes to the dataset, and data in the database is this true. What can I do?
Thanks.
Spencer
hi Spencer,
Spencer H. Prue wrote:
> I am using MSDE in a web application. The ADO.NET works, updating the
> database correctly. The only thing is when I use the dataset, and
> when I check the data in the database using server explorer the data
> is truncated. I tried using the various ntext, nvarchar and setting
> the length to somthing like a 100, but the data string is still to
> short somewhere. Only when this comes to the dataset, and data in the
> database is this true. What can I do? Thanks.
did you manually set the datatype size?
check it's settings..
and probably you'll have better luck asking in the .Net heirarchy newsgroups
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" wrote:

> hi Spencer,
> Spencer H. Prue wrote:
> did you manually set the datatype size?
> check it's settings..
> and probably you'll have better luck asking in the .Net heirarchy newsgroups
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
Did you say .NET hierarchy group. I couldn't find that group can you give me
the full name for the group?
|||microsoft.public.dotnet.framework.adonet
"Spencer H. Prue" <SpencerHPrue@.discussions.microsoft.com> wrote in message
news:5E484369-BCA3-4527-96A7-281CE91B01EE@.microsoft.com...
>
> "Andrea Montanari" wrote:
> Did you say .NET hierarchy group. I couldn't find that group can you give
> me
> the full name for the group?

ADO.NET DataSets as a DataSource

Can I use a ADO dataset as a datasource for a report using Reporting Services
2000? Or will I have to implement a custom data extension. Does anyone know?You have to implement an extension until version 2, then there will be new
web and winform controls.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aparna" <Aparna@.discussions.microsoft.com> wrote in message
news:819219C0-B02D-470D-BA74-D15A72DD014C@.microsoft.com...
> Can I use a ADO dataset as a datasource for a report using Reporting
Services
> 2000? Or will I have to implement a custom data extension. Does anyone
know?

ADO.NET & SELECT ... UNION issue

Hi,

I have discovered something weird.

I prepared a dataset that consists of a table adapter which has a select command of type stored proc. My stored procedure performs a select on a table1 and then table2 using UNION. My stored proc is running perfectly in the SQL Management studio (SQL2005) - no questions.

In Visual Studio when I test my dataset querying my tableadapter I get a result that is not just a UNION but a join of empty columns (number of empty columns = number of columns from one of my tables) and then the result of select statement from my stored proc.

And then my asp.net code fails as well because my gridView is expecting only 3 columns but instead I am getting 6 (3 empty + 3 those I was expecting in the first place.)

I think ADO.NET converted you UNION to a JOIN which either means your table was UNION compatible implicitly or ADO.NET internally is using very old SET operation based JOIN syntax. So check your table to see if it is explicitly UNION compatible, I am assuming you know UNION performs implicit distinct to remove duplicates so its requirements are strict compared to UNION ALL which includes duplicate rows. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms180026.aspx

|||

Hey,

That is exactly what happened. ADO.net converted my resultset to some sort of join and I know why.

The thing that caused that abnormal behavior was I used a join on two tables that have different column names. Once I've changed my select statement in both parts of the union to return exactly the same names of columns it's started working properly.