create a "Yesterday", "This Week", "Last Week" calculated member

Hi all,

I'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.

At the moment, I'm doing this:

Code Snippet

create member [MIA DW].[DATE TIME].[Date].[Yesterday]
as [DATE TIME].[Date].&[2007-01-01T00:00:00]

select [Measures].members on 0,
non empty [DATE TIME].[Date].members on 1
from [MIA DW]

But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members?

So I guess there's two questions:

1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)

2)How come the Yesterday member is not returned by the .members function?

Thanks

Greg
1.

Yes. In the DataSourceView add a named column called SpecialDates with the value 'Normal'. Then add that column as an attribute in the time dimension, and then create the calculated members on that dimensionattribute. Another possibility is to create a Named Set with the same functionality.

2.
i think calculated members are not returned by .MEMBERS. This will work I think

create member [MIA DW].[DATE TIME].[Date].[Yesterday]
as [DATE TIME].[Date].&[2007-01-01T00:00:00]

select [Measures].members on 0,
non empty ADDCALCULATEDMEMBERS([DATE TIME].[Date].members) on 1
from [MIA DW]
|||Awesome thanks.

Crazy selection help

Hi everyone -

I have two tables, one table (A) that holds a field called location that
has the partial path information of the file

and the second table (B) holds the full path including the filename

I only want to display the records from the partial path (A)
table that do not reside in the second (B) table


Table A =
imagefilename, description, directory, companyname, location
"96.jpg","test","Prog\2006_02","Marey, John","Prog\2006_02\96.jpg"
"274.JPG","disney","Prog\2006_07","Amy Gross","Prog\2006_07\274.JPG"
"570.jpg","sdfsdf","Prog\2007_06","Lof3,"Test3","Prog\2007_06\570.jpg"
"850.JPG","222","Prog\2007_08","Malis, Mark","Prog\2007_08\850.JPG"

Table B =
imagefilename
"d:\webdata\web\uploads\qfimages\Prog\2006_02\96.jp g"
"d:\webdata\web\uploads\qfimages\Prog\2006_03\112.p df"
"d:\webdata\web\uploads\qfimages\Prog\2006_03\127.j pg"

I was thinking about using a cross join with a like condition,
but it fails (go figure)

could someone offer a little help to the query that i need to
perform??

thanks
tonyuse a LEFT OUTER JOIN from A (left table) to B (right table)

the ON condition will match columns (using LIKE or string functions or whatever)

the WHERE clause will test the PK of the right table being NULL (indicating no match found)|||Why don't you post the SQL you have tried so far?|||SELECT *
FROM tbl2 INNER JOIN
tbl1 ON tbl2.imagefilename LIKE '%' + tbl1.location|||DECLARE @.TableA table (imagefilename varchar(100), [description] varchar(100)
, directory varchar(100), companyname varchar(100), location varchar(100))

DECLARE @.TableB table (imagefilename varchar(100))

INSERT INTO @.TableA (imagefilename, description, directory, companyname, location)
SELECT '96.jpg','test','Prog\2006_02','Marey, John','Prog\2006_02\96.jpg' UNION ALL
SELECT '274.JPG','disney','Prog\2006_07','Amy Gross','Prog\2006_07\274.JPG' UNION ALL
SELECT '570.jpg','sdfsdf','Prog\2007_06','Lof3,Test3','Pr og\2007_06\570.jpg' UNION ALL
SELECT '850.JPG','222','Prog\2007_08','Malis, Mark','Prog\2007_08\850.JPG'

INSERT INTO @.TableB(imagefilename)
SELECT 'd:\webdata\web\uploads\qfimages\Prog\2006_02\96.j pg' UNION ALL
SELECT 'd:\webdata\web\uploads\qfimages\Prog\2006_03\112. pdf' UNION ALL
SELECT 'd:\webdata\web\uploads\qfimages\Prog\2006_03\127. jpg'

SELECT 'TableA' AS Source, Location
FROM @.TableA
UNION ALL
SELECT 'TableB' AS Source, imagefilename
FROM @.TableB b
WHERE NOT EXISTS (SELECT * FROM @.TableA a
WHERE b.imagefilename LIKE '%'+a.location)|||SELECT *
FROM tbl2 INNER JOIN
tbl1 ON tbl2.imagefilename LIKE '%' + tbl1.locationnow change it to a LEFT OUTER JOIN and add this --

... where tbl1.location IS NULL|||GREAT !!!!

you folks are the best!

thank you x10000

Crashing a server

Hi,
First of all I want to know if there is a manual way of crashing a server.
My second question -
I have two Standard Edition SQL Servers one(S1) running the production databases, and the other(S2) is just a standby. The database files are on a DISK array. Suppose if S1 crashes, can I point to the S2 Server to the same DISK array and use sp_attch sp's
to bring the database up.
Thanks,
Elvis
Have yo tried poring coke on the motherboard? That would crash the server
pretty good... you might want to explain what particular behairor you want
to cause to happen and then perhaps someone could offer good advice about
how to achieve it...
About the sp_attach...
there are a few issues.
1. Is it shared in some kind of cluster or SAN? Databases in SQL need to be
on local drives. So, you can't simply attach if the files are on a drive
that looks like a network drive to SQL.
2. If S1 goes down in an uncontrolled manner... the files themselves may be
damaged. Even if they're not damages... you can't guarantee the ability to
do an attach unless you had first done a detach. So... know... this doesn't
sound like a great solutioin.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Elvis Clinton" <anonymous@.discussions.microsoft.com> wrote in message
news:FA9EB5D2-AB32-4903-B270-DC5F6C2AC09C@.microsoft.com...
> Hi,
> First of all I want to know if there is a manual way of crashing a server.
> My second question -
> I have two Standard Edition SQL Servers one(S1) running the production
databases, and the other(S2) is just a standby. The database files are on a
DISK array. Suppose if S1 crashes, can I point to the S2 Server to the same
DISK array and use sp_attch sp's to bring the database up.
> Thanks,
> Elvis

Crashing a server

Hi,
First of all I want to know if there is a manual way of crashing a server.
My second question -
I have two Standard Edition SQL Servers one(S1) running the production datab
ases, and the other(S2) is just a standby. The database files are on a DISK
array. Suppose if S1 crashes, can I point to the S2 Server to the same DISK
array and use sp_attch sp's
to bring the database up.
Thanks,
ElvisHave yo tried poring coke on the motherboard? That would crash the server
pretty good... you might want to explain what particular behairor you want
to cause to happen and then perhaps someone could offer good advice about
how to achieve it...
About the sp_attach...
there are a few issues.
1. Is it shared in some kind of cluster or SAN? Databases in SQL need to be
on local drives. So, you can't simply attach if the files are on a drive
that looks like a network drive to SQL.
2. If S1 goes down in an uncontrolled manner... the files themselves may be
damaged. Even if they're not damages... you can't guarantee the ability to
do an attach unless you had first done a detach. So... know... this doesn't
sound like a great solutioin.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Elvis Clinton" <anonymous@.discussions.microsoft.com> wrote in message
news:FA9EB5D2-AB32-4903-B270-DC5F6C2AC09C@.microsoft.com...
> Hi,
> First of all I want to know if there is a manual way of crashing a server.
> My second question -
> I have two Standard Edition SQL Servers one(S1) running the production
databases, and the other(S2) is just a standby. The database files are on a
DISK array. Suppose if S1 crashes, can I point to the S2 Server to the same
DISK array and use sp_attch sp's to bring the database up.
> Thanks,
> Elvis