Previous YTD

Hi all

I am trying to perform Pervious Year To Date Total (which is like YTD but for the previous year. ie. ytd would work for this year's jan -> whatever month. But I need a total from Last year from jan -> whatever year to do comparision), However I've ran into the following issues.

Since Year is a variable and it can change depending on which year you select. I tried to Return the first member of the month which in theory should be January of a Year. However when I performed the below query I got Dec which was the overall period through out the entire Month this include 2002 dec -> 2005 May period so the head returned Dec.

SELECT { head(Descendants(
[Period].CurrentMember,
[Month]),1)} ON COLUMNS , { [BrandModel].[All BrandModel] } ON ROWS FROM [Running Report] WHERE ( [Measures].[Unit] )
So then I tried to return the Head of the Set of the current year using the below query. But no matter what I do I can not get it working.

SELECT { head(Descendants(
{[Period].CurrentMember, [Period].[Month]},
[Month]),1)} ON COLUMNS , { [BrandModel].[All BrandModel] } ON ROWS FROM [Running Report] WHERE ( [Measures].[Unit] )

How would you guys usually do a previous year to date comparision ? since year is a variable

Thanks
Tom

This isn't clear. Can you be more explicit?|||hi vector

I think you've answered my question on the other thread. I will test this when I get into office first thing in the morning.

thanks alot !!

Preventing Deadlock

Trace 1204 has narrowed the cause of deadlock to these delete statements.
There are a total of 10 delete statements in this SP.
create procedure del_emp @.empid int
Delete from tbl_employees
where empid in (Select empid from tbl_location where empid = @.empid)
Delete from tbl_management
where mge_ID in (Select mge_ID from tbl_sunmanagement where empid = @.empid)
......
......
Questions:
1. should i add (nolock) within the select statement? ex: Select empid from
tbl_location WITH (NOLOCK)
2. should i add (updlock) within the delete statement? ex: Delete from
tbl_employees WITH (UPDLOCK)
Any suggestions highly appreciated.Hi
You don't say what is being blocked and what it causing the blocking! You
may be missing an index, which would help, check out the query plan to see if
this is so. Will the empid's in tbl_location be anything other than @.empid?
Do you explicitly start a transaction outside this procedure?
John
"morphius" wrote:
> Trace 1204 has narrowed the cause of deadlock to these delete statements.
> There are a total of 10 delete statements in this SP.
> create procedure del_emp @.empid int
> Delete from tbl_employees
> where empid in (Select empid from tbl_location where empid = @.empid)
> Delete from tbl_management
> where mge_ID in (Select mge_ID from tbl_sunmanagement where empid = @.empid)
> ......
> ......
> Questions:
> 1. should i add (nolock) within the select statement? ex: Select empid from
> tbl_location WITH (NOLOCK)
> 2. should i add (updlock) within the delete statement? ex: Delete from
> tbl_employees WITH (UPDLOCK)
> Any suggestions highly appreciated.
>
>|||Hi John,
Thanks for the reply. The empid in tbl_location is not anything other than
the empid. Yes a transaction is explicitly started outside this procedure and
between 100-300 users could be using the transaction simultaneously.
"John Bell" wrote:
> Hi
> You don't say what is being blocked and what it causing the blocking! You
> may be missing an index, which would help, check out the query plan to see if
> this is so. Will the empid's in tbl_location be anything other than @.empid?
> Do you explicitly start a transaction outside this procedure?
> John
> "morphius" wrote:
> > Trace 1204 has narrowed the cause of deadlock to these delete statements.
> > There are a total of 10 delete statements in this SP.
> >
> > create procedure del_emp @.empid int
> >
> > Delete from tbl_employees
> > where empid in (Select empid from tbl_location where empid = @.empid)
> >
> > Delete from tbl_management
> > where mge_ID in (Select mge_ID from tbl_sunmanagement where empid = @.empid)
> >
> > ......
> > ......
> >
> > Questions:
> > 1. should i add (nolock) within the select statement? ex: Select empid from
> > tbl_location WITH (NOLOCK)
> > 2. should i add (updlock) within the delete statement? ex: Delete from
> > tbl_employees WITH (UPDLOCK)
> >
> > Any suggestions highly appreciated.
> >
> >
> >
> >|||Hi
If I understand you correctly, then there is no need to query table location
and you could just use
Delete from tbl_employees
where empid = @.empid
or is was there a typo or misunderstanding?
If no other code is in the transaction other than the stored procedure call
(i.e. two delete statements) then you would need to look at the query plans.
John
"morphius" wrote:
> Hi John,
> Thanks for the reply. The empid in tbl_location is not anything other than
> the empid. Yes a transaction is explicitly started outside this procedure and
> between 100-300 users could be using the transaction simultaneously.
> "John Bell" wrote:
> > Hi
> >
> > You don't say what is being blocked and what it causing the blocking! You
> > may be missing an index, which would help, check out the query plan to see if
> > this is so. Will the empid's in tbl_location be anything other than @.empid?
> > Do you explicitly start a transaction outside this procedure?
> >
> > John
> >
> > "morphius" wrote:
> >
> > > Trace 1204 has narrowed the cause of deadlock to these delete statements.
> > > There are a total of 10 delete statements in this SP.
> > >
> > > create procedure del_emp @.empid int
> > >
> > > Delete from tbl_employees
> > > where empid in (Select empid from tbl_location where empid = @.empid)
> > >
> > > Delete from tbl_management
> > > where mge_ID in (Select mge_ID from tbl_sunmanagement where empid = @.empid)
> > >
> > > ......
> > > ......
> > >
> > > Questions:
> > > 1. should i add (nolock) within the select statement? ex: Select empid from
> > > tbl_location WITH (NOLOCK)
> > > 2. should i add (updlock) within the delete statement? ex: Delete from
> > > tbl_employees WITH (UPDLOCK)
> > >
> > > Any suggestions highly appreciated.
> > >
> > >
> > >
> > >