Showing posts with label contains. Show all posts
Showing posts with label contains. Show all posts

Wednesday, March 28, 2012

How to use YTD on a mirror period

Hi friends

I have a funny situation. I have a period dimension which contains Year, Qtr, Month. I also have another Time dimension which contains Mth1 .. Mth25.

The 2nd Time dimension matches that of the 1st Time dimension ie. Mth25 is 2005.April. and Mth24 is 2005.March and so on...

However I need to calculate YTD when MTH25 is selected from the dimension.

I can't exactly do YTD (Mth25) as Mth25 is not a time dimension. Is there a way to Retrieve the equivalent time dimension in the form of 2005.April from Mth25 so that I can use in YTD Calculation ?

Thanks

Tom

Do you have a property saying the month?
If so you can do YTD(STRTOMEMBER("[Time].[Month].["+[MthDim].CURRENTMEMBER.PROPERTIES("Month Name")+"]"))
I hope it helps you.|||Thinking out loud a bit (i.e., have not tried this), but you might be able to do something like this. Use the Rank() function to determine what month number you have within the current member's siblings:

Rank([MthDim].CurrentMember, [MthDim].CurrentMember.Siblings)

This should return 1 for Mth1, 14 for Mth14, and so on.

Then, use this with the Item() function (substracting 1 given that Rank() returns a 1-based position while Item() expects a 0-based position) to decide which member in the time dimension to return:

Descendents(StrToMember("[Time].[Year].[" + [MthDim].CurrentMember.Parent.Name + "]"), [Time].[Month]).Item(<rank function> - 1)

This assumes you have a [Year] level in [MthDim] as well as [Time]. Might work...

Dave Fackler
|||That was helpful... however when this is done

sum(YTD(StrToMember("[Period2].
&[" + [Period].CURRENTMEMBER.PROPERTIES("Year") + "].
&[" + [Period].CURRENTMEMBER.PROPERTIES("Quarter") + "].
&[" + [Period].CURRENTMEMBER.PROPERTIES("Month") + "]"
)), [Measures].[Unit] )

Where Period2 is Month1 .. 25
where Period is Year, Quarter, Month

the sum returned is not for Year To Date ie. April figure + March Figure + Feb.. + Jan..

When I pivot to Month 25 in Period2 which is 2005-April. The value thats displayed is ONLY the figure for APRIL instead of April figure + March Figure + Feb.. + Jan..

do you have any idea why this is the case ?

Thanks
Tom|||sorry made a mistake in the above post

Where Period2 is Month1 .. 25
where Period is Year, Quarter, Month

is actually

Where Period2 is Year, Quarter, Month
where Period is Month1 .. 25 but contains Year, Quarter, Month as properties.

So, sorry but just to repeat the problem is still there if anyone could help I'd appreciate it

Thanks
Tom
|||I think I know.. Because we're returning a Member using StrToMember instead maybe we should use StrToSet. Because we're only returning a member when YTD is performed it is only calculating that member.

Maybe we should use StrToSet ?

though I'm not entirely show how the expression should go.

any help is appreciated

Thankyou
Tom|||Hi Tom,

YTD() does want a member, so I believe using StrToMember is the right approach. Does your [Period] dimension (the one with Mth1...Mth25) contain a [Year] level? I am assuming it does since it sounds as if your time span is greater than one year (since [Period2] has [Year], [Quarter], etc.). If this is the case, you need to make sure the dimension is marked as a "Time" dimension (property of the dimension) and the [Year] level is marked as a "Years" level (property of the level).

The YTD(<member>) function is a shortcut for the PeriodsToDate([Year], <member>) function. The YTD() function, however, needs to be able to determine what your [Year] level is. It does this using the properties of the levels in the dimension to determine which one has been marked as "Years". This allows the YTD() function to know the year parent for the <member> in question and then correctly aggregate all the members from the first child under that year up through the <member> specified.

Dave Fackler
|||

Hi david

You're right YTD() only take member.

The [Period] dimension with Mth1..25 is set as a Time Type and it also contains a [Year] level which currently is hidden with visible set to false and it is marked as a Years level.

Something strange that I've noticed is that the below query only returns the YTD for the month [Period2].&[2005].&[2].&[4], instead of from [Period2].&[2005].&[1].&[1] + [Period2].&[2005].&[1].&[2] + [Period2].&[2005].&[1].&[3] +
[Period2].&[2005].&[2].&[1]...

I think the issue lies here. what is the difference between

1. sum(ytd([Period2].&[2005].&[2].&[4]), [Measures].[Unit])

and

2. sum(ytd([Period2].currentmember), [Measures].[Unit])
2. which when pivoted to 2005 april returns the ytd total of jan + feb+ march +april which is what i want.

however 1. only returns the unit for [Period2].&[2005].&[2].&[4] only

Thanks alot for your help !
Tom

|||I've really ran out of ideas.

Is there another way to do this ? surely it shouldn't be this difficult to do something like it ?

:) any suggestions

Thanks
Tom

Monday, March 26, 2012

How to use value of a variable in defining data type

HI Experts,

I have same table structures in two database and one master table which contains Table id, Table name,primary key, data type of primary key. i have to comapare
Tables in both tha database and as per result i have to do insert,update or delete.

for that i have written query :

DECLARE @.rowcount_mastertable FLOAT
SET @.rowcount_mastertable = (select count(*) from master_table)

DECLARE @.TABLE_ID float,
@.TABLE_NAME varchar (100),
@.primary_key varchar (100),
@.Primarykey_DATATYPE varchar (50),

DECLARE @.COUNTER FLOAT
SET @.COUNTER = 1

WHILE (@.Counter <= @.rowcount_mastertable)

Begin

SET @.TABLE_NAME = (SELECT TABLE_NAME FROM MASTER_TABLE TABLE_ID = @.COUNTER)
SET @.primary_key = (SELECT primary_key FROM MASTER_TABLE WHERE TABLE_ID = @.COUNTER)
SET @.Primarykey_DATATYPE = (SELECT Primarykey_DATATYPE FROM MASTER_TABL WHERE TABLE_ID = @.COUNTER)

--In below line i want to declare a variable and datatype should be same as what we got from master table so that i can use this @.MAX_primary_key to fetch max of primary key from table name where table id is 1

DECLARE @.MAX_primary_key @.Primarykey_DATATYPE
SELECT @.MAX_primary_key = MAX(@.primary_key) FROM @.TABLE_NAME
WHERE TABLE_ID = @.COUNTER

--But by running it i am getting error that "Incorrect syntax near '@.Primarykey_DATATYPE'. and "Must declare the variable '@.MAX_primary_key'.

Please suggest

Thanks in Advance

Quote:

Originally Posted by aviansh

HI Experts,

I have same table structures in two database and one master table which contains Table id, Table name,primary key, data type of primary key. i have to comapare
Tables in both tha database and as per result i have to do insert,update or delete.

for that i have written query :


DECLARE @.rowcount_mastertable FLOAT
SET @.rowcount_mastertable = (select count(*) from master_table)

DECLARE @.TABLE_ID float,
@.TABLE_NAME varchar (100),
@.primary_key varchar (100),
@.Primarykey_DATATYPE varchar (50),

DECLARE @.COUNTER FLOAT
SET @.COUNTER = 1

WHILE (@.Counter <= @.rowcount_mastertable)

Begin

SET @.TABLE_NAME = (SELECT TABLE_NAME FROM MASTER_TABLE TABLE_ID = @.COUNTER)
SET @.primary_key = (SELECT primary_key FROM MASTER_TABLE WHERE TABLE_ID = @.COUNTER)
SET @.Primarykey_DATATYPE = (SELECT Primarykey_DATATYPE FROM MASTER_TABL WHERE TABLE_ID = @.COUNTER)

--In below line i want to declare a variable and datatype should be same as what we got from master table so that i can use this @.MAX_primary_key to fetch max of primary key from table name where table id is 1

DECLARE @.MAX_primary_key @.Primarykey_DATATYPE
SELECT @.MAX_primary_key = MAX(@.primary_key) FROM @.TABLE_NAME
WHERE TABLE_ID = @.COUNTER


--But by running it i am getting error that "Incorrect syntax near '@.Primarykey_DATATYPE'. and "Must declare the variable '@.MAX_primary_key'.


Please suggest

Thanks in Advance



Examine this line more closely...

DECLARE @.MAX_primary_key @.Primarykey_DATATYPE

at what point are you declaring either of these variable datatypes?

Jim :)

Wednesday, March 21, 2012

How to use sorting in an "Order by" function

When I run a stored procedure I get a resultant set which contains a field called "Status". This status can be 'Approved','Declined' or 'Withdrawn'.

Now I want to add a parameter('Approved' or 'Declined' or 'Withdrawn') to the stored procedure which is an input from the external user. Based on the user preference I want to sort the output to display.

i.e. if the user selects 'Withdrawn' then the resultant set of the stored procedure must display all the records with 'Withdrawn' status first then the other records.

Currently I am using "Order by status" but unable to understand how to implement the above logic.

Does any one has suggestions or an approach that would be helpful to me?

You can do below in the ORDER BY clause:

ORDER BY NULLIF(Status, @.Your_Parameter)

This will result in NULL if the status matches the passed parameter and status value otherwise. And since NULL values get sorted first in SQL Server those rows will get ordered first.

Monday, March 12, 2012

How to use Loop statement

I have a table which contains 170K of customer_numbers. How can I write a query (loop) to create 340 tables or excel files which contains 500 customer numbers each.

Thanks for your assistance in advance.

A couple of questions.

1. How do you want to split the data up into groups? Randomly?

2. Tables or excel files are two VERY different things. For excel files, I would suggest you not use SQL to do this. I would probably suggest you use a .NET language to build Excel files. If you really mean tables, then I would suggest you just use one table and assign a number to each customer_number permanently at the start of your process and then you have 340 logical files.

3. What version of SQL Server are you using? In 2005 the query to assign the numbers is pretty easy.

Something like:

SELECT customer_number, NTILE (340) Over ( order by customer_number)
into #hold
from table_which_contains_170K_customer_numbers

Or update your table:

;with tiledCustomers as
(
SELECT customer_number, NTILE (340) Over ( order by customer_number) as groupValue
FROM table_which_contains_170K_customer_numbers
)
update
set group = tiledCustomers.groupValue
from table_which_contains_170K_customer_numbers as t170
join tiledCustomers
on tiledCustomers.customer_number = t170.customer_number

|||

here's a psedou code

1. select disticnt customers

2. write a script to create an empty excel files per customer

3. create a script that will generate the script for bcp out to excel files

step 3. outputs bcp scripts so you dont need to write one for each user

4. group the scripts to 10

5. simultaneously run the 10 groups at the same time if you have a powerful box to reduce your

time to complete the task.

The idea here is to write sql server scripts that will write the scripts that you need to run.

Friday, March 9, 2012

How to use if else

Dear friends,

I have a webpage having a textbox and combobox. Combobox contains messers and country. If a user for instance enter?'India'?in?textbox?and?select?country?in?combobox.?I?want?to?retrieve?records?having?India?in?their?country?field.?
And?if?user?inputs?category?data in?textbox?and?select?category?from?combo?it?should?be?shown?that?category?records.?But?

It is giving error "Procedure or Function 'search' expects parameter '@.messers', which was not supplied."

ALTER PROCEDURE [dbo].[search]
@.messers nvarchar(70),
@.country nvarchar(20)

AS
IF @.country <> ''
BEGIN
select * from vendor where country like '%' +@.country+'%'
END
ELSE
IF @.messers <> ''
BEGIN
select * from vendor where messers like '%' +@.messers+'%'
END

Any help. PleaseYou need to set the default values for the parameters in the proc. Otherwise SQL Server expects you to send in BOTH parameters. Also I would add the condition @.param IS NOT NULL to make sure its not NULL. Checking for empty string is not sufficient.

ALTER PROCEDURE [dbo].[search]@.messersnvarchar(70)NULL,@.countrynvarchar(20)NULLASBEGINIF @.country <>''AND @.countryISNOT NULLBEGINselect *from vendorwhere countrylike'%' +@.country+'%'ENDELSEIF @.messers <>''AND @.messersISNOT NULLBEGINselect *from vendorwhere messerslike'%' +@.messers+'%'ENDEND