Finding designation of an employee on a given date

Hi all,

I have two tables

CREATE TABLE [JEMP] (
[EMPID] [int] NOT NULL ,
[DESIGID] [int] NULL , -- CURRENT DESIGNATION OF EMPLOYEE
[DOB] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [JPRO] (
[PromoID] [int] IDENTITY (1, 1) NOT NULL ,
[EmpID] [int] NOT NULL ,
[EffectiveDate] [smalldatetime] NOT NULL ,
[NewDesigID] [int] NOT NULL , -- PROMOTED TO DESIGNATION
[DesigID] [int] NULL -- PROMOTED FROM DESIGNATION
) ON [PRIMARY]
GO

INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(1,1,'1962-03-11 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(2,25,'1980-10-7 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(3,8,'1978-04-05 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(4,7,'1962-07-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(5,22,'1973-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(6,55,'1971-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(7,11,'1973-09-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(8,22,'1975-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(9,22,'1977-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(10,23,'1984-07-11 00:00:00')

INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(3,'2002-15-11 00:00:00',7,20)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(3,'2003-03-01 00:00:00',8,7)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(4,'2002-01-04 00:00:00',20,22)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(4,'2005-05-01 00:00:00',7,20)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(5,'2001-10-01 00:00:00',22,23)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(6,'2001-08-01 00:00:00',55,NULL)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(7,'2003-10-01 00:00:00',11,8)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(8,'2001-09-01 00:00:00',22,23)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(9,'2002-01-05 00:00:00',22,23)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(10,'2002-11-01 00:00:00',24,25)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(10,'2003-11-15 00:00:00',23,24)

--
I wish to find the designation of employee on given date by using
promotion and master table . I am using the following query to get the
result

select isnull( ( select top 1 newdesigid from JPRO where
empid=1 and effectivedate<'anygivendate' order by effectivedate desc )
, (select desigid from empmast where empid=1) )

It did give the result but looking for better method to solve this.

With regards
JatinderHistory is usually modeled in durations with a (start_time, end_time)
pair; this lets you use a BETWEEN predicate for most of your queries.|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> I wish to find the designation of employee on given date by using
> promotion and master table . I am using the following query to get the
> result
> select isnull( ( select top 1 newdesigid from JPRO where
> empid=1 and effectivedate<'anygivendate' order by effectivedate desc )
> , (select desigid from empmast where empid=1) )
>
> It did give the result but looking for better method to solve this.

I don't see anything seriously wrong with that query. Here is an
alternate:

select Top 1 NewDesigID
from (select NewDesigID, EffectiveDate
from JPRO
where EmpID=7 and
EffectiveDate < '20050301'
union
select DESIGID, '19000101'
from JEMP
where EMPID=7) AS x
order by EffectiveDate desc

But I'm not sure in what way it would be "better".

If you are looking for a more ANSI way of doing it, you would have
to get the MAX(EffectiveDate) and then join back to the derived
table again. As you may guess, this is likely to be less effecient.
In SQL 2005, you could use a CTE (Common Table Expresssion) to avoid
repetition of the code for the derived table, but alas the query
plan is likely to be equally ineffecient.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

find value based on max(date)

I know I have done this before, but cannot for the life of me remember how.

I am trying to determine return the current (last added) deduction amount for each deduction type for each employee

Sample Table:
employee|Deduction_type|Date_entered|Amount
1|MED|1/1/2007|50
1|DEPC|1/1/2007|100
1|MED|1/8/2007|50
1|DEPC|1/8/2007|100
1|MED|1/15/2007|150
2|MED|1/1/2007|35
2|DEPC|1/1/2007|100
2|MED|1/8/2007|35
2|DEPC|1/8/2007|75
2|MED|1/15/2007|35

Any suggestions?select t.employee
, t.Deduction_type
, t.Date_entered
, t.Amount
from Sample as t
inner
join (
select employee
, Deduction_type
, max(Date_entered) as max_date
from Sample
group
by employee
, Deduction_type
) as m
on m.employee = t.employee
and m.Deduction_type = t.Deduction_type
and m.max_date = t.Date_enteredsql

find out lock start time

I think it would be great to investigate into system performance and dead
lock if we can find out the start time for the current locks. Is there a way
to do that? thanks!
I tried the following 2 methods:
1) EXEC sp_lock
2) In the enterprice manager | management | current activities |
locks/object
Neither view gives me what I'm looking for.
thanks again!
Maybe these will help:
http://msdn.microsoft.com/library/de...tabse_5xrn.asp
Troubleshooting Deadlocks
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
Andrew J. Kelly SQL MVP
"Zeng" <zeng@.nononospam.com> wrote in message
news:%23ATn9732GHA.2096@.TK2MSFTNGP05.phx.gbl...
> I think it would be great to investigate into system performance and dead
> lock if we can find out the start time for the current locks. Is there a
> way to do that? thanks!
> I tried the following 2 methods:
> 1) EXEC sp_lock
> 2) In the enterprice manager | management | current activities |
> locks/object
> Neither view gives me what I'm looking for.
> thanks again!
>

find out lock start time

I think it would be great to investigate into system performance and dead
lock if we can find out the start time for the current locks. Is there a way
to do that? thanks!
I tried the following 2 methods:
1) EXEC sp_lock
2) In the enterprice manager | management | current activities |
locks/object
Neither view gives me what I'm looking for.
thanks again!Maybe these will help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp
Troubleshooting Deadlocks
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
Andrew J. Kelly SQL MVP
"Zeng" <zeng@.nononospam.com> wrote in message
news:%23ATn9732GHA.2096@.TK2MSFTNGP05.phx.gbl...
> I think it would be great to investigate into system performance and dead
> lock if we can find out the start time for the current locks. Is there a
> way to do that? thanks!
> I tried the following 2 methods:
> 1) EXEC sp_lock
> 2) In the enterprice manager | management | current activities |
> locks/object
> Neither view gives me what I'm looking for.
> thanks again!
>

find out lock start time

I think it would be great to investigate into system performance and dead
lock if we can find out the start time for the current locks. Is there a way
to do that? thanks!
I tried the following 2 methods:
1) EXEC sp_lock
2) In the enterprice manager | management | current activities |
locks/object
Neither view gives me what I'm looking for.
thanks again!Maybe these will help:
http://msdn.microsoft.com/library/d...
tabse_5xrn.asp
Troubleshooting Deadlocks
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
Andrew J. Kelly SQL MVP
"Zeng" <zeng@.nononospam.com> wrote in message
news:%23ATn9732GHA.2096@.TK2MSFTNGP05.phx.gbl...
> I think it would be great to investigate into system performance and dead
> lock if we can find out the start time for the current locks. Is there a
> way to do that? thanks!
> I tried the following 2 methods:
> 1) EXEC sp_lock
> 2) In the enterprice manager | management | current activities |
> locks/object
> Neither view gives me what I'm looking for.
> thanks again!
>

Find out if current user is member of a role

I need a stored procedure to find out if the current user is a member of a certain role.

I want to pass the role name and return a bit to tell whether he is a member or not.

I have this code to list the groups the current user is a member of but I don't know how to search or do a "SELECT FROM" on the results.

DECLARE @.usr varchar(32)

SET @.usr = USER

EXEC sp_helpuser @.usr


But if the current user is a member of more than one role it returns multiple rows. So if I could do something like:

DECLARE @.grpName varchar(32)

SELECT * FROM (EXEC sp_helpuser @.usr) WHERE GroupName=@.grpName
IF rowcount > 0 THEN
RETURN 1
ELSE
RETURN 0
END IF

I know that doesn't work so how can I do this?I'm sure that someone out there can do better than this, but you might try:

ALTER PROC spCheckGroup

@.UserName varchar(255), @.GroupName varchar(255)

AS

DECLARE @.Count int

SELECT @.Count = Count(*)
FROM (
select
s1.name as username,
s2.name as groupname
from
dbo.sysusers s1 left join dbo.sysmembers sm on
s1.uid = sm.memberuid
left outer join dbo.sysusers s2 on
sm.groupuid = s2.uid
where
s1.uid < 16383
) t1
WHERE
t1.userName = @.UserName and
t1.GroupName = @.GroupName

If @.Count > 0
Return 1
ELSE
Return 0

Test it with this code:

[/code]
DECLARE @.return_status int
EXEC @.return_status = spCheckGroup 'OAJO-SQLAdmin', 'db_owner'
SELECT 'Return Status' = @.return_status
[/code]|||check: BOL

IS_MEMBER ( { 'group' | 'role' } )|||I'll just crawl back under the rock where I came from...|||LOL.

Thanks for trying.|||I need a stored procedure to work with .NET so here's what I have:

CREATE PROCEDURE IsGroupMember
(
@.groupName nvarchar(32),
@.retVal bit OUTPUT
)
AS
SET @.retVal = IS_MEMBER(@.groupName)
GO

and in Query Analyzer I run this:

DECLARE @.bt bit

EXEC IsGroupMember 'db_owner', @.bt

IF @.bt = 1 print 'member'
ELSE IF @.bt = 0 print 'non-member'
ELSE print 'undefined'

but I keep getting undefined. What's wrong?|||EXEC IsGroupMember 'db_owner', @.bt output

find out active Sql statements

Hi,
Where does Sql server store the most current active T-SQL
statements? Is there a system table or stored procedure(documented or
undocumented)that will show you the active sql statements being
executed. For eg: in oracle you can query v$sql to see all the sql
statements executed by each session.
I know that you can use Enterprise manager, but was wondering if you
can run a query via query analyzer to look at it.
Any help is appreciated.

Thanks
Geetha<gdabbara@.brownshoe.com> wrote in message
news:1106264397.579113.143730@.c13g2000cwb.googlegr oups.com...
> Hi,
> Where does Sql server store the most current active T-SQL
> statements? Is there a system table or stored procedure(documented or
> undocumented)that will show you the active sql statements being
> executed. For eg: in oracle you can query v$sql to see all the sql
> statements executed by each session.
> I know that you can use Enterprise manager, but was wondering if you
> can run a query via query analyzer to look at it.
> Any help is appreciated.
> Thanks
> Geetha

Check out fn_get_sql() in Books Online - if it isn't there, you'll need to
download the latest BOL version from Microsoft:

http://www.microsoft.com/sql/techin.../2000/books.asp

DBCC INPUTBUFFER is another option, but it can only display the first 255
characters of whatever batch is being executed.

Simon|||[posted and mailed]

(gdabbara@.brownshoe.com) writes:
> Where does Sql server store the most current active T-SQL
> statements? Is there a system table or stored procedure(documented or
> undocumented)that will show you the active sql statements being
> executed. For eg: in oracle you can query v$sql to see all the sql
> statements executed by each session.
> I know that you can use Enterprise manager, but was wondering if you
> can run a query via query analyzer to look at it.

Simon pointed you to the basics. To get it all nicely packaged, I
have stored procedure aba_lockinfo that will give you the information.
Have a look at http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks you all for the information. It was very helpful.

Find Current Date Time Span SQL

Any body have an Idea on this... Last problem of my Calendar Application.

To keep my calendar loading fast and efficent I have a SP that loads data to a datatable to read from c#.

My sp reads this

(
@.ID int,
@.DateStart datetime,
@.DateEnd datetime
)
AS

SELECT JobDateStart, JobDateEnd, EstimateDate, cLastName,
cCity, ContractPrice, JobDescription, Status
FROM Customers
WHERE (ID = @.ID AND JobDateStart Between @.DateStart AND @.DateEnd + 1
OR JobDateEnd Between @.DateStart AND @.DateEnd + 1)

Now My problem is that the DateStart and DateEnd values are used to pull information from a DB. So lets say that I have a job that starts on November 30th and ends on January 3rd.
My calendar will display the record on the 30th day of Nov and on the 1st through 3rd fo Jan, however when I look at December there is NO DATA there. which is the expected result because the dates do not fall within the start and ends dates.

My question is how query the database to pull back my month spanding record without pulling back all the data in the database for performace?

My thought is something like the date span falls into the range between the start and end, but I cannot find anything????? Please help, I know it is probaly simple right??Found the answer, just took THINKING LESS rather than more..

add and OR statement like this, it will get the 1 day events and current month event and the spanning events.

SELECT JobDateStart, JobDateEnd, EstimateDate, cLastName,
cCity, ContractPrice, JobDescription, Status
FROM Customers
WHERE (ID = @.ID AND
/*FIX SPANNING MONTHS / YEAR JOBS*/
@.DateStart BETWEEN JobDateStart AND JobDateEnd +1
/*SELECT 1 DAY OR 1 MONTH JOBS*/
OR JobDateStart Between @.DateStart AND @.DateEnd + 1)

It really was that easy. Less load on the db so I can loop through the DataTable and do my sorting with no effect on the DB.

Find a name of an index in deadlock situation.

Hi
I have a deadlock occurring.
Using MSSQL 2005 current activity lock screens I can see one process is
waiting for a lock to be released on a Database object. with objectId =
1441294802092032 and type = KEY - so I know I have a lock on an Index
and what table the index belongs to,
But how can I find the name of the index that is blocked?
This was part of the default view in SQL2000 activity manager. I cannot
use object_name (1441294802092032 ).
Pain is cannot even export the details of the locks per process to a
file.
Yitzak
http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
"Yitzak" <terryshamir@.yahoo.co.uk> wrote in message
news:1164814916.178901.38620@.16g2000cwy.googlegrou ps.com...
> Hi
> I have a deadlock occurring.
> Using MSSQL 2005 current activity lock screens I can see one process is
> waiting for a lock to be released on a Database object. with objectId =
> 1441294802092032 and type = KEY - so I know I have a lock on an Index
> and what table the index belongs to,
> But how can I find the name of the index that is blocked?
> This was part of the default view in SQL2000 activity manager. I cannot
> use object_name (1441294802092032 ).
> Pain is cannot even export the details of the locks per process to a
> file.
>

Find a name of an index in deadlock situation.

Hi
I have a deadlock occurring.
Using MSSQL 2005 current activity lock screens I can see one process is
waiting for a lock to be released on a Database object. with objectId = 1441294802092032 and type = KEY - so I know I have a lock on an Index
and what table the index belongs to,
But how can I find the name of the index that is blocked'
This was part of the default view in SQL2000 activity manager. I cannot
use object_name (1441294802092032 ).
Pain is cannot even export the details of the locks per process to a
file.Yitzak
http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
"Yitzak" <terryshamir@.yahoo.co.uk> wrote in message
news:1164814916.178901.38620@.16g2000cwy.googlegroups.com...
> Hi
> I have a deadlock occurring.
> Using MSSQL 2005 current activity lock screens I can see one process is
> waiting for a lock to be released on a Database object. with objectId => 1441294802092032 and type = KEY - so I know I have a lock on an Index
> and what table the index belongs to,
> But how can I find the name of the index that is blocked'
> This was part of the default view in SQL2000 activity manager. I cannot
> use object_name (1441294802092032 ).
> Pain is cannot even export the details of the locks per process to a
> file.
>

Find a name of an index in deadlock situation.

Hi
I have a deadlock occurring.
Using MSSQL 2005 current activity lock screens I can see one process is
waiting for a lock to be released on a Database object. with objectId =
1441294802092032 and type = KEY - so I know I have a lock on an Index
and what table the index belongs to,
But how can I find the name of the index that is blocked'
This was part of the default view in SQL2000 activity manager. I cannot
use object_name (1441294802092032 ).
Pain is cannot even export the details of the locks per process to a
file.Yitzak
http://blogs.msdn.com/bartd/archive.../09/747119.aspx
"Yitzak" <terryshamir@.yahoo.co.uk> wrote in message
news:1164814916.178901.38620@.16g2000cwy.googlegroups.com...
> Hi
> I have a deadlock occurring.
> Using MSSQL 2005 current activity lock screens I can see one process is
> waiting for a lock to be released on a Database object. with objectId =
> 1441294802092032 and type = KEY - so I know I have a lock on an Index
> and what table the index belongs to,
> But how can I find the name of the index that is blocked'
> This was part of the default view in SQL2000 activity manager. I cannot
> use object_name (1441294802092032 ).
> Pain is cannot even export the details of the locks per process to a
> file.
>

find # of days passed

Hello,

I need to take one of my date column and compare it with the current date and see how many day difference? How can I do this?

Thanks,

SELECT
DATEDIFF( day, myDateColumn, GetDate()
FROM
myTable
DATEDIFF is a built in sql function, the first parameters tells what "value" to return,
day = number of days,
the second two parameters tell it what dates to make calculations against.
bill

|||in some function...
public TimeSpan DateDifference(DateTime date1, DateTime date2)
{
return new TimeSpan(date2.Ticks - date1.Ticks);
}
..i didn't test for negative, so if you just want the difference (where could be that 1 < 2 or 2 < 1) then maybe something like
public TimeSpan DateDifference(DateTime date1, DateTime date2)
{
return new TimeSpan(Math.Abs(date2.Ticks - date1.Ticks));
}
|||

You need the DATEDIFF function, if you write VB you are in luck but it is not available in C# so you can use the T-SQL version. Try the links below to get started. Hope this helps.

http://www.stanford.edu/~bsuter/sql-datecomputations.html
http://www.15seconds.com/issue/041013.htm

|||I can bring it as a new column, however main objective is to some amounts
and give three totals such as "Current Amount", "30-60 days amount", "> 60
days amount". How can I do this in Reporting Services?
Thanks,
|||This will pull each of the aging into its own column. myDateColumn is the date you want to check. When the DateDiff is in given amount, the amount will be summed. When it falls outside the (ie < 30 ) range, it will sum 0.
Select
Sum( Case When DATEDIFF( day, myDateColumn, GetDate() ) < 30 Amount else 0 End ) as Curent,
Sum( Case When DATEDIFF( day, myDateColumn, GetDate() ) between 30 and 60 Amount else 0 End ) as Thirty,
Sum( Case When DATEDIFF( day, myDateColumn, GetDate() ) > 60 Amount else 0 End ) as Over
...
'rest of your query.
bill|||

Thank you very much for your help. Is it possible to this in RS, My query is already so complex, I am trying to see if I can do it in RS.

|||Are you looking for a "check box" that will do this for you? I do not believe one exists.
bill|||

:-)))). I am sure there is a way to do it in RS, there are lots of ability to write code there, I did not get training yet, that is why I am asking. Have you ever written a function in Code window? Is there a good reference that can explain it to me.

Find "current database"

what is the SQL to find
1) current database
2) server name
like one can do in Oracle like:
select user from dua;
select * from v$instance;
thanks
klabu wrote:
> what is the SQL to find
> 1) current database
> 2) server name
> like one can do in Oracle like:
> select user from dua;
> select * from v$instance;
>
> thanks
SELECT DB_NAME() AS db_name;
SELECT SERVERPROPERTY('MachineName') AS MachineName;
SELECT SERVERPROPERTY('InstanceName') AS InstanceName;
SELECT SERVERPROPERTY('ServerName') AS ServerName;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||thanks
how to concat them values ?[vbcol=seagreen]
|||klabu wrote:[vbcol=seagreen]
>thanks
>how to concat them values ?
Select @.@.Servername returns current server as well.

Find "current database"

what is the SQL to find
1) current database
2) server name
like one can do in Oracle like:
select user from dua;
select * from v$instance;
thanksklabu wrote:
> what is the SQL to find
> 1) current database
> 2) server name
> like one can do in Oracle like:
> select user from dua;
> select * from v$instance;
>
> thanks
SELECT DB_NAME() AS db_name;
SELECT SERVERPROPERTY('MachineName') AS MachineName;
SELECT SERVERPROPERTY('InstanceName') AS InstanceName;
SELECT SERVERPROPERTY('ServerName') AS ServerName;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||thanks
how to concat them values ?
>> SELECT DB_NAME () + SERVERPROPERTY('ServerName') AS db_on_server;|||klabu wrote:
>thanks
>how to concat them values ?
>> SELECT DB_NAME () + SERVERPROPERTY('ServerName') AS db_on_server;
Select @.@.Servername returns current server as well.

Find "current database"

what is the SQL to find
1) current database
2) server name
like one can do in Oracle like:
select user from dua;
select * from v$instance;
thanksklabu wrote:
> what is the SQL to find
> 1) current database
> 2) server name
> like one can do in Oracle like:
> select user from dua;
> select * from v$instance;
>
> thanks
SELECT DB_NAME() AS db_name;
SELECT SERVERPROPERTY('MachineName') AS MachineName;
SELECT SERVERPROPERTY('InstanceName') AS InstanceName;
SELECT SERVERPROPERTY('ServerName') AS ServerName;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||thanks
how to concat them values ?[vbcol=seagreen]|||klabu wrote:[vbcol=seagreen]
>thanks
>how to concat them values ?
Select @.@.Servername returns current server as well.

Financial Query Help

I am in need of a query that would calculate the balance as of a certain
date, say 12/31/2005 for AccountID = 1.
Current Balance = 100
Balance as of 12/31/05 should be 200.
But how do I write a query like this?
Thanks
Sonny
tblAccount - Shows current balances
ID--Balance--AccountName
1 -- 100 -- Test1
2 -- 200 -- Test2
3 -- 200 -- Test3
tblAccountHistory - Show transactions
ID--AccountID--TransDate--Amount--Action
1 -- 1 -- 12/1/2005 -- 500 -- Credit
1 -- 1 -- 12/5/2005 -- 100 -- Debit
1 -- 1 -- 12/10/2005 -- 100 -- Debit
1 -- 1 -- 12/15/2005 -- 100 -- Debit
1 -- 1 -- 1/5/2006 -- 100 -- DebitSonny
Can you post sample data?
SELECT Current Balance *2 FROM Table WHERE AccountID = 1
"Sonny Sablan" <sonny@.sablan.org> wrote in message
news:ODbjNxnQGHA.2536@.tk2msftngp13.phx.gbl...
>I am in need of a query that would calculate the balance as of a certain
>date, say 12/31/2005 for AccountID = 1.
> Current Balance = 100
> Balance as of 12/31/05 should be 200.
> But how do I write a query like this?
> Thanks
> Sonny
>
> tblAccount - Shows current balances
> ID--Balance--AccountName
> 1 -- 100 -- Test1
> 2 -- 200 -- Test2
> 3 -- 200 -- Test3
>
> tblAccountHistory - Show transactions
> ID--AccountID--TransDate--Amount--Action
> 1 -- 1 -- 12/1/2005 -- 500 -- Credit
> 1 -- 1 -- 12/5/2005 -- 100 -- Debit
> 1 -- 1 -- 12/10/2005 -- 100 -- Debit
> 1 -- 1 -- 12/15/2005 -- 100 -- Debit
> 1 -- 1 -- 1/5/2006 -- 100 -- Debit
>
>|||There is sample data...
After my question.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OUERjZoQGHA.4952@.TK2MSFTNGP09.phx.gbl...
> Sonny
> Can you post sample data?
> SELECT Current Balance *2 FROM Table WHERE AccountID = 1
>
>
>
> "Sonny Sablan" <sonny@.sablan.org> wrote in message
> news:ODbjNxnQGHA.2536@.tk2msftngp13.phx.gbl...
>|||he did. ;-)
select AccountID, sum(Amount*(case Action when 'Credit' then 1 when 'Debit'
then -1 else 0 end)) [total]
from tbAccountHistory
where AccountID=1 and TransDate <= '20051231'
group by AccountID
-oj
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OUERjZoQGHA.4952@.TK2MSFTNGP09.phx.gbl...
> Sonny
> Can you post sample data?
> SELECT Current Balance *2 FROM Table WHERE AccountID = 1
>
>
>
> "Sonny Sablan" <sonny@.sablan.org> wrote in message
> news:ODbjNxnQGHA.2536@.tk2msftngp13.phx.gbl...
>|||Thanks oj...
Sonny
"oj" <nospam_ojngo@.home.com> wrote in message
news:u6heUeoQGHA.4960@.TK2MSFTNGP12.phx.gbl...
> he did. ;-)
> select AccountID, sum(Amount*(case Action when 'Credit' then 1 when
> 'Debit' then -1 else 0 end)) [total]
> from tbAccountHistory
> where AccountID=1 and TransDate <= '20051231'
> group by AccountID
> --
> -oj
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OUERjZoQGHA.4952@.TK2MSFTNGP09.phx.gbl...
>|||Thanks, I really need a cofee.
"oj" <nospam_ojngo@.home.com> wrote in message
news:u6heUeoQGHA.4960@.TK2MSFTNGP12.phx.gbl...
> he did. ;-)
> select AccountID, sum(Amount*(case Action when 'Credit' then 1 when
> 'Debit' then -1 else 0 end)) [total]
> from tbAccountHistory
> where AccountID=1 and TransDate <= '20051231'
> group by AccountID
> --
> -oj
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OUERjZoQGHA.4952@.TK2MSFTNGP09.phx.gbl...
>

Financial Periods YTD & Date Ranges

I am developing a set of reports for a site based on AS2000.

The reports will have columns for each finance period in the current year from period 01 to the current reporting period from the Primary Time dimension.

i.e. If period 4 is the default period, The report should show columns for periods 1,2,3,4 with a total that sums the four columns.

When the default period 5 the columns should be 1,2,3,4,5.

I cannot use the current year and explode that to show all periods in the year, as there are postings in future periods beyond the current accounting period that should not be displayed on the report.

I am setting the default member of the time dimension to the current reporting period. (2005/08 for example)
I am trying to use a named set based on an MDX expression to set the column range for the report.

The first test I tried was to set the MDX for the period range to some fixed values
{[Primary Time].[ACCSCAL].&[200501]:[Primary Time].[ACCSCAL].&[200508]}
This gave the expected result showing columns 01/2005 to 08/2005

I then tried changing the last period to use the default member of the dimension
{[Primary Time].[ACCSCAL].&[200501]:[Primary Time].[ACCSCAL].DefaultMember}
This again gave the expected result range and I could change the default member on the dimension and see the columns on the report update correctly.

I then tried testing that the first period can be found correctly
{[Primary Time].[ACCSCAL].&[200508].FirstSibling:[Primary Time].[ACCSCAL].&[200508]}
This again gave the expected result range from 01/2005 to 08/2005

I then tried combining the default member and first sibling calculations
{[Primary Time].[ACCSCAL].DefaultMember.FirstSibling:[Primary Time].[ACCSCAL].DefaultMember}
This gave a single column "All Primary Time", this was not what I was expecting.

So I tried changing the mdx to test the value of the first sibling of the default member, expecting 01/2005
{[Primary Time].[ACCSCAL].DefaultMember.FirstSibling}
This again gave a single column "All Primary Time".

So I then tried changing the mdx to test that a single period would work in the named set
{[Primary Time].[ACCSCAL].DefaultMember}
This again gave a single column "All Primary Time".

So I tried changing the mdx to test the value of the default member, expecting 08/2005
{[Primary Time].[ACCSCAL].&[200508]}
This again gave a single column for 08/2005 as expected

By now this has me totally confused!

Do you have any idea why the default member works when used as the second part of the range but does not work when used on its own or as the first element of the range?

Is this the correct way of achieving the report I am aiming for or is there a better method to use?

Any suggestions you have would be welcome.

Apologies is this is a FAQ.

What is the actual MDX expression for the Default Member of [Primary Time].[ACCSCAL]?|||The Default Member is [Primary Time].[ACCSCAL].&[200508]