Adding Time to Rank

Using FoodMart, how can i add time to this:
WITH MEMBER [Measures].[Rank] AS 'Rank ( Product.CurrentMember,Order( {Product.CurrentMember.Parent.Children} ,[Profit], DESC) ) '
SELECT
{[Profit] , [Rank]} ON COLUMNS,
Drink.Children ON ROWS
FROM SalesWhat platform is this?|||Brett,

This is an MDX statement for OLAP - Hard as nails to understand ..

msenoelo - are you wanting to set this up as a calculated measure or do you just want to see if the MDX is valid

If you want check the MDX then you can open up the sample mdx application from

Programs > SQL Server > Anaysis Services > Sample MDX Application

I think??

You then get a kind IDE that can use to parse MDX.

Or you can goto your cube

New calculated meause > Insert your code

Not totally sure as I don't have anaylsis services installed on my computer and MDX is total bloody mystery to me. Where did you get the MDX from ?

Here is a like to a more relevant forum:

http://www.sql-server-performance.com/forum/forum.asp?FORUM_ID=18

Cheerssql

Adding new content to a table by refering other tables using SQL

I keep product name, id in Table1.

I keep Category name, id in Table2.

I keep relation between product and category (product_id, category_id) in Table3.

I have added some products to the table with proper category.

Work fine

But for some products I did not specified any category

(ie their id is not present in Table3)

But now I want all such products

(ie all products whose category is nothing)

To be associated with category_id 10

Can I do this simply with SQL queries?

Hope u can help me

sujith

Hellosujith!

UPDATE [TableName] -- in your case it will be TABLE3

SET [FieldName] = 10 -- in your case it will be CATEGORY_ID

WHERE [FieldName] IS NULL -- in your case i dont know if it is NULL or ''

http://www.w3schools.com take a look at this site to learn more about SQL STATEMENT

HAVE NICE QUERY'S

|||

But I don't have any entry in table 3 if the category of a product is nothing.

I insert data into table 3 if and only if a product , belonged to acategory(this is how I save space)

|||

ohhhh...

You want to see all products that doesn't has category_Id and isnert into table 3 with category_Id = 10!

Is this that you want?

Sorry if i am not understanding your question!!

|||

sujithukvl@.gmail.com:

I keep product name, id in Table1.

I keep Category name, id in Table2.

I keep relation between product and category (product_id, category_id) in Table3.

I have added some products to the table with proper category.

Work fine

But for some products I did not specified any category

(ie their id is not present in Table3)

But now I want all such products

(ie all products whose category is nothing)

To be associated with category_id 10

Can I do this simply with SQL queries?

Hope u can help me

sujith

Following Sql statement might suite your need:

insert into product_categoryselect id ,'10'fromproductwherenot exists (select *from product_categorywhere product_category.pro_id = product.id)

Adding data to more than one table

Hi there,

I am currently setting up a registration system where customers can registers their details and the details of the product, using ASP.net and MS SQL.

There is a column called customerID in the Custoemrs table, and a column of the same name in the Products table, so that I can have relationships between the tables.

For obvious reasons (ie. people that quit half-way through), I want to hold all the information until the end. The ID in the Customer table is unique, and auto-increasing, and therefore not assigned until the data enters the database.

However, I wish to submit information to the Products table at the same time, but what shall I put in for the custoemrID (which hasn't yet been assigned)

Thank you in advance for your help,

Nathair

It's a Referential Integrity issue. You need a ForeignKey with Cascading on UPDATE/DELETE to maintain the Referential Integrity on UPDATE/DELETE: take CustomerID column on Customer table as PrimaryKey, and CustomerID on Products table as ForeignKey, you can refer to this link:

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

To maintain the Referential Integrity when INSERT, you can create an INSRET Trigger on the Customer table as following:

create trigger trg_Customer on Customer for insert
as
insert into Products select CustomerId,'myProduct' from inserted
go

You can take a look at this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp

|||Thanks for that. UPDATE/DELETE isn't really gonna be an issue.

Normally I hard-core my SQL statement into the actual page - however, this time I'm thinking of using an SP in MS SQL. For both ways, I am unsure on how to incorporate the trigger?

Thanks,
Nathair

Adding data from fields to get a total

EX: I have a table for products, and each product has a quantity. How can I add up the QTY field in all the rows to find out the total QTY of all the products.

Any help would be greatly appreciated.

gkc

You don't say whether you're using a GridView or DataGrid (or anything in particular) -

I'm hoping you're using ASP.Net 2.0 and can use the GridView - because this code sample from ASPNet101.com shows how to create a calculated column - not the exact column you want, but the code's the same :
http://aspnet101.com/aspnet101/aspnet/codesample.aspx?code=GVFooterTotal

|||Thank you for the help!

Actually, I am not using any controls. I know there must be a way to do this in TSQL, I am just not familiar with it.|||check out the SUM function in SQL.|||Yes! That was exactly what I needed.

Thank you!