Date Difference

A table has 2 columns (Date and Trans). Date has the date of the
transaction, Trans contains whether the transacion was an invoice or a
payment.
I need to determine how long customers are taking to pay their bills.
How can I find the difference in the invoice date and the payment date
if both dates are part of the same field?Assuming there is some key value which shows that the 2 rows are connected
( and I am calling the field KEY)..
Select datediff(dd,i.[date],p.[date]) from thetable i inner join thetable p
on i.key = p.key
WHere whatever selection criteria you wish to use...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bunckles" <chris.bunch@.gmail.com> wrote in message
news:1117648478.631189.171300@.z14g2000cwz.googlegroups.com...
>A table has 2 columns (Date and Trans). Date has the date of the
> transaction, Trans contains whether the transacion was an invoice or a
> payment.
> I need to determine how long customers are taking to pay their bills.
> How can I find the difference in the invoice date and the payment date
> if both dates are part of the same field?
>

Date Context

How can I get two measures in the same query with different date contexts?

For example, lets say I have two measures, Payment, Taxes and Amount. This query would get me total amount for both measures for 2006.

SELECT {Measures.Payment, Measures.Amount, Measures.Taxes} ON COLUMNS, {Company.Name} ON ROWS FROM MyCube WHERE Date.Year.2006

How can I get the total Payment for 2006, 2007, and 2008 while leaving the generic query date context which is 2006? Thanks in advance.

There are two options, you could specify the required dates using sets of tuples in your query

SELECT {(Date.Year.CurrentMember,Measures.Payment)
, (Date.Year.CurrentMember,Measures.Amount)
, (Date.Year.CurrentMember,Measures.Taxes)
(Date.Year.2007,Measures.Payment)
(Date.Year.2008,Measures.Payment)
} ON COLUMNS,
{Company.Name} ON ROWS
FROM MyCube
WHERE Date.Year.2006

Or you could create a couple of calculated members to return the specified dates, You could even make these calculations a bit more generic and put them in the cubes calculation script.

WITH
MEMBER Measures.PrevYrPayment AS (Date.Year.CurrentMember.Lag(1),Measures.Payment)
MEMBER Measures.PrevYr2Payment AS (Date.Year.CurrentMember.Lag(2),Measures.Payment)
SELECT {Measures.Payment
, Measures.Amount
, Measures.Taxes
,Measures.PrevYrPayment
,Measures.PrevYr2Payment
} ON COLUMNS,
{Company.Name} ON ROWS
FROM MyCube
WHERE Date.Year.2006

|||

Thanks for your reply.

The problem is more like this:

SELECT {Measures.Payment, Measures.Amount, Measures.Taxes} ON COLUMNS, {Company.Name} ON ROWS FROM MyCube WHERE Date.Month.[Feb 2006]

How to get the Payment for the whole 2006, or 2007, or maybe 2006 and 2007?

Regards,

|||

The first query I sent should always work. I think the second query I sent would also work, however this would rely on your attribute relationships being setup correctly and you would need to use a user hierarchy in your where clause that had both the year and month in it. For example, if you had a "Calendar" hierarchy that had the Year and Month, something like the following should work. I don't think that explicitly using the Month attribute will correctly set the Year.

eg

WITH
MEMBER Measures.PrevYrPayment AS (Date.Year.CurrentMember.Lag(1),Measures.Payment)
MEMBER Measures.PrevYr2Payment AS (Date.Year.CurrentMember.Lag(2),Measures.Payment)
SELECT {Measures.Payment
, Measures.Amount
, Measures.Taxes
,Measures.PrevYrPayment
,Measures.PrevYr2Payment
} ON COLUMNS,
{Company.Name} ON ROWS
FROM MyCube
WHERE Date.Calendar.[Feb 2006]

An "older" (but still valid) approach to the calculated member would be to use something like:

WITH MEMBER Measures.PrevYrPayment AS (Ancestor(Date.Calendar.CurrentMember,Date.Calendar.Year).Lag(1),Measures.Payment)

but I am pretty sure that this is redundant in SSAS 2005 as the attribute relationships will take care of this. Sorry, but I can't double check this at the moment as I just got a new laptop and am in the process of re-installing everything.

Cheers
Darren