Role Playing Dimensions

What is the proper way to include the date dimension table into your DSV if you plan on using multiple role-playing dimensions? It seems if you can use this dimension as a role playing dimension, and assign the "join" using the Dimension Usage tab, then it doesn't need to be joined to the fact table in the DSV... is that correct? Or should you add the Date Dim table to the DSV and join it to every date-specific fact foreign keys? I see three possibilities for the DSV:

1) Include the Date Dim table but not join to any other table in the DSV

2) Include the Date Dim table and join to at least one fact foreign key

3) Include the Date Dim table and join to all fact foreign keys for dates in the DSV

Which method would be the most appropriate (or what are the consequences of each method)?

Also, if you use a role-playing dimension, is there anyway of manipulating the levels to display the type of date. For example, of the user picks the Order Date dimension, the year level says "Order Year" but if they use the Shipped Date dimension, the year level would say "Shipped Year"... etc.?

Thanks

Kory

Hi KoryS. Check the Adventure Works sample BI project that clearly shows that no 3 is the correct way of doing it. It is a very simple concept, instead of using three tables of views for time you join one table to the different time keys in the fact table. There have been some concerns regarding using role playing dimensions like here(http://mgarner.wordpress.com/2006/06/27/role-playing-dimensions-not-materialized/) but now it is OK regarding performance.

Regards

/Thomas Ivarsson

|||

Thanks- I'll follow the example from the Adventureworks db.

Do you know if I can rename some of the levels in my role-playing dimensions (see second half of my original thread)?

-Kory

|||

I think you will have to use the same names but you name each dimension like DueDate, OrderDate, ShipDate and so on. You can see this is in the Adventure Works AS2005 project.

Regards

/Thomas

sql

Role playing dimension and member naming question.

I have a fact table with invoice information that has multiple date columns.

I had originaly only needed to join my time dimension to this fact table on it's create date, but I have now added a role-playing dimension to join to the invoice date.

When I had 1 date dimension all of it's members where called 'week','year', 'day', etc.
Now that I have the role-playing dimension I have two dimensions with member names like 'Date.week', 'Date.year', 'Date.day', 'Invoice Date.week', 'Invoice Date.year', 'Invoice Date.day'.

So many queries I had written to reference the original date dimension no longer work because of the extra 'Date.' prefix. Is there a way to hide this prefix for my original date dimension?

Thanks in advance.

I had the same problem earlier on. I had to bite the bullet and change my MDX when I had multiple 'date' dimensions. Can you globally change the MDX or are you using a third party tool?

Unless someone knows better.

|||

I am using ProClarity as a front end to the cube. I have found a sort of work around, rather than creating a role-playing dimension. I created a whole new date dimension on the same date table and it doesn't mess up the naming. The only downside I can see to this right now is that if I have one date dimension on rows and one date dimension on columns your result could look like:

may june july august
may
june
july
august

so it's not really clear which dimension is where, but I could get around this by changing the names of the members in the new date dimension.

-Preston

|||

Hi,

Although this solution will ultimately work, I guess we are duplicating process and space by repeating a dimension.

I've not tested it thorougly yet, but, the cube dimension has a property named HierarchyUniqueNameStyle, that allows for two values: IncludeDimensionName and ExcludeDimensionName. Using the later in the "default" dimension, let's say "Date" vs. "Delivery Date", the MDX will run fine as it was originally, without need to edit all of them.

Jordi Rambla

SQL Server MVP

Certia (http://www.certia.net)

SolidQualityLearning (http://www.solidqualitylearning.com)

Right join not working when joining 3 tables

Have 2 tables that are joined by a 3rd. I want to get all the date in both
tables whether they are connected or not.
There can be many policies per role. RolePolicies is the table that
connects the two.
CREATE TABLE [dbo].[Roles] (
[RoleID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Roles] ADD
CONSTRAINT [PK_Roles_1] PRIMARY KEY CLUSTERED
(
[RoleID]
) ON [PRIMARY]
CREATE TABLE [dbo].[Policies] (
[PolicyID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Policies] ADD
CONSTRAINT [PK_Policies] PRIMARY KEY CLUSTERED
(
[PolicyID]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[RolePolicies] (
[RoleID] [int] NULL ,
[PolicyID] [int] NULL
) ON [PRIMARY]
GO
INSERT Roles (Description) VALUES ('Manager')
INSERT Roles (Description) VALUES ('Director')
INSERT Roles (Description) VALUES ('User')
INSERT Policies (Description) VALUES ('Add')
INSERT Policies (Description) VALUES ('Edit')
INSERT Policies (Description) VALUES ('Delete')
INSERT RolePolicies (RoleID,PolicyID) VALUES (1,1)
INSERT RolePolicies (RoleID,PolicyID) VALUES (1,2)
INSERT RolePolicies (RoleID,PolicyID) VALUES (2,1)
INSERT RolePolicies (RoleID,PolicyID) VALUES (2,2)
SELECT *
FROM RolePolicies rp
RIGHT JOIN Roles r ON (rp.RoleID = r.RoleID)
RIGHT JOIN Policies p ON (rp.PolicyID = p.PolicyID)
RoleID PolicyID RoleID Description PolicyID
Description
-- -- -- -- -- --
--
1 1 1 Manager
1 Add
2 1 2 Director
1 Add
1 2 1 Manager
2 Edit
2 2 2 Director
2 Edit
NULL NULL NULL NULL 3
Delete
Here the last RIGHT JOIN works fine and we get the Delete, even though it
isn't in RolePolicies. But why don't we get 'User' from the 1st RIGHT JOIN?
If I do it this way:
SELECT *
FROM RolePolicies rp
RIGHT JOIN Roles r ON (rp.RoleID = r.RoleID)
I get the extra line ("User").
RoleID PolicyID RoleID Description
-- -- -- --
1 1 1 Manager
1 2 1 Manager
2 1 2 Director
2 2 2 Director
NULL NULL 3 User
How do I get both the "User" as well as the "Add"?
Thanks,
TomTshad,
Is it a LEFT JOIN that you want instead?
If not what should the resultset look like?
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e$NdcS3yFHA.3756@.tk2msftngp13.phx.gbl...
> Have 2 tables that are joined by a 3rd. I want to get all the date in
> both tables whether they are connected or not.
> There can be many policies per role. RolePolicies is the table that
> connects the two.
> CREATE TABLE [dbo].[Roles] (
> [RoleID] [int] IDENTITY (1, 1) NOT NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Roles] ADD
> CONSTRAINT [PK_Roles_1] PRIMARY KEY CLUSTERED
> (
> [RoleID]
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[Policies] (
> [PolicyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Policies] ADD
> CONSTRAINT [PK_Policies] PRIMARY KEY CLUSTERED
> (
> [PolicyID]
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[RolePolicies] (
> [RoleID] [int] NULL ,
> [PolicyID] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT Roles (Description) VALUES ('Manager')
> INSERT Roles (Description) VALUES ('Director')
> INSERT Roles (Description) VALUES ('User')
> INSERT Policies (Description) VALUES ('Add')
> INSERT Policies (Description) VALUES ('Edit')
> INSERT Policies (Description) VALUES ('Delete')
> INSERT RolePolicies (RoleID,PolicyID) VALUES (1,1)
> INSERT RolePolicies (RoleID,PolicyID) VALUES (1,2)
> INSERT RolePolicies (RoleID,PolicyID) VALUES (2,1)
> INSERT RolePolicies (RoleID,PolicyID) VALUES (2,2)
> SELECT *
> FROM RolePolicies rp
> RIGHT JOIN Roles r ON (rp.RoleID = r.RoleID)
> RIGHT JOIN Policies p ON (rp.PolicyID = p.PolicyID)
> RoleID PolicyID RoleID Description PolicyID
> Description
> -- -- -- -- -- --
--
> 1 1 1 Manager 1
> Add
> 2 1 2 Director 1
> Add
> 1 2 1 Manager 2
> Edit
> 2 2 2 Director 2
> Edit
> NULL NULL NULL NULL 3 Delete
> Here the last RIGHT JOIN works fine and we get the Delete, even though it
> isn't in RolePolicies. But why don't we get 'User' from the 1st RIGHT
> JOIN?
> If I do it this way:
> SELECT *
> FROM RolePolicies rp
> RIGHT JOIN Roles r ON (rp.RoleID = r.RoleID)
> I get the extra line ("User").
> RoleID PolicyID RoleID Description
> -- -- -- --
> 1 1 1 Manager
> 1 2 1 Manager
> 2 1 2 Director
> 2 2 2 Director
> NULL NULL 3 User
> How do I get both the "User" as well as the "Add"?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fgZcZ3yFHA.2848@.TK2MSFTNGP15.phx.gbl...
> Tshad,
> Is it a LEFT JOIN that you want instead?
>
No.
That is why I showed the 2nd statement which just takes away the Join that
worked. When it was gone it worked correctly and showed the role "User".

> If not what should the resultset look like?
It should look the same as the 1st result set with one more row showing the
role "User":
RoleID PolicyID RoleID Description PolicyID
Description
-- -- -- -- -- --
--
1 1 1 Manager
1 Add
2 1 2 Director
1 Add
1 2 1 Manager
2 Edit
2 2 2 Director
2 Edit
NULL NULL NULL NULL 3
Delete
NULL NULL 3 User NULL
NULL
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e$NdcS3yFHA.3756@.tk2msftngp13.phx.gbl...
>|||tshad,
Try using FULL JOIN:
SELECT *
FROM ROLES R FULL JOIN ROLEPOLICIES RP
ON R.ROLEID = RP.ROLEID
FULL JOIN POLICIES P
ON RP.POLICYID = P.POLICYID
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ehx$oc4yFHA.3864@.TK2MSFTNGP12.phx.gbl...
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fgZcZ3yFHA.2848@.TK2MSFTNGP15.phx.gbl...
> No.
> That is why I showed the 2nd statement which just takes away the Join that
> worked. When it was gone it worked correctly and showed the role "User".
>
> It should look the same as the 1st result set with one more row showing
> the role "User":
> RoleID PolicyID RoleID Description PolicyID
> Description
> -- -- -- -- -- --
--
> 1 1 1 Manager 1
> Add
> 2 1 2 Director 1
> Add
> 1 2 1 Manager 2
> Edit
> 2 2 2 Director 2
> Edit
> NULL NULL NULL NULL 3 Delete
> NULL NULL 3 User NULL
> NULL
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uKlnH94yFHA.3316@.TK2MSFTNGP10.phx.gbl...
> tshad,
> Try using FULL JOIN:
> SELECT *
> FROM ROLES R FULL JOIN ROLEPOLICIES RP
> ON R.ROLEID = RP.ROLEID
> FULL JOIN POLICIES P
> ON RP.POLICYID = P.POLICYID
That worked.
I also could do it with my statement (which really is the same as yours) by
replacing both "RIGHT JOIN"s with "FULL JOIN"s, as you suggested.
Not sure why the outside RIGHT JOIN would work and not the inside one.
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ehx$oc4yFHA.3864@.TK2MSFTNGP12.phx.gbl...
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uKlnH94yFHA.3316@.TK2MSFTNGP10.phx.gbl...
> tshad,
> Try using FULL JOIN:
> SELECT *
> FROM ROLES R FULL JOIN ROLEPOLICIES RP
> ON R.ROLEID = RP.ROLEID
> FULL JOIN POLICIES P
> ON RP.POLICYID = P.POLICYID
This works well, but how do I tell it not to display rows that have Nulls in
the result set in certain columns.
I actually used this with 3 tables and it works fine, but I didn't want to
display rows that Nulls in 2 of the columns of the result set.
This is a little confusing, I know.
But if you take the statement and change it to:
SELECT *
FROM ROLES R FULL JOIN ROLEPOLICIES RP
ON R.ROLEID = RP.ROLEID
FULL JOIN POLICIES P
WHEN P.POLICYID <> NULL
ON RP.POLICYID = P.POLICYID
You will get no results.
This makes sense, because all the PolicyID records in the actual table have
something in them.
But in the full join, there can be a Null in the PolicyID if the there is a
record in Policies, but not in RolePolicies.
This would be a ridiculous statement in this example, but I would like it to
display all the rows that have no nulls.
For example,
RoleID PolicyID RoleID Description PolicyID
Description
-- -- -- -- -- --
--
1 1 1 Manager
1 Add
2 1 2 Director
1 Add
1 2 1 Manager
2 Edit
2 2 2 Director
2 Edit
NULL NULL NULL NULL 3
Delete
NULL NULL 3 User NULL
NULL
I want to test for either PolicyID or RoleID, after the result set is
created.
In my other, Select - I want to test for both being NULL.
The statement would be something like:
SELECT *
FROM ROLES R FULL JOIN ROLEPOLICIES RP
ON R.ROLEID = RP.ROLEID
FULL JOIN POLICIES P
WHEN P.POLICYID <> NULL AND R.ROLEID <> NULL
ON RP.POLICYID = P.POLICYID
In my example, I should get the same result set, but I actually get no
results.
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ehx$oc4yFHA.3864@.TK2MSFTNGP12.phx.gbl...
that
"User".
>
-- -- -- -- -- --
--
,
>
--
>

RIDDLE

I bet this is simple, but I need help!

OK CHECK IT

Table A

UserID Date DateID
===== ==== =====
1 2/2/07 100
2 3/12/07 101
3 4/1/07 102
2 5/10/07 103

Table B

DateID UserID
===== =====
2 100

I am user 2.
I need a query that will bring back everything from table A that has my ID (2) and everything from table A that is associated with my ID from table B.

(so I should get back 3 records)

Make sense?

Anyone have an idea?

No it does not make sense. Which Id are you talking about UserId or DateID? From the data you provided neither matches with what you are expecting the output to be.

|||

Hopefully it is obvious that I messed up.

Table B fields should be reversed... DateID = 100 and UserID=2

|||

cyberwin:

Hopefully it is obvious that I messed up.

Table B fields should be reversed... DateID = 100 and UserID=2

That would only bring in 2 records. You were expecting 3?

|||

Also, can you post the exact output you are expecting since the structure of the 2 tables are different. I am guessing you need the 2 rows from TableA and 1 row from TableB.

|||

What I need is 3 rows from table A.

The 2 that have my ID (2) and the the one that links to my ID according to table B.

|||

Declare @.userIdintSet @.userId = 2Select UserID, DateID, Datefrom TableAWhere UserId= @.userIdUNIONALLSelect DateID, UserID,NULLfrom TableBWhere DateId= @.userId

|||

What you're saying really doesn't make sense. Why don't you put in the data you expect to get back as you would expect to see it

|||

Table A

UserID Date DateID
===== ==== =====
1 2/2/07 100
2 3/12/07 101
3 4/1/07 102
2 5/10/07 103

Table B

UserID DateID
===== =====
2 100

What I expect to get back is (assuming I am passing in UserID=2)

UserID Date DateID
===== ===== =====
2 3/12/07 101
2 5/10/07 103
1 2/2/07 100

I don't think it can be done with a single query. I think I will have to write a recursive function that checks the link table (table B) for the UserID that is passed in and goes back to table A for the record that matches...

|||

You can do it via the UNION ALL method I posted in my previous reply. give it a try and if you cant get it to work post back and we can help you out. Its pretty simple.

|||

Hey ndinakar, that sure did work!

I need to tweak it a bit to get exactky what I need... but that is certainly the right direction!

Thanks a million for the help. I was playing with that for an hour before I posted it!

thanks again

Revision Date

I would like to be able to include the report revision date as part of a
footer. I would like to get this date automatically from the date the report
was last saved or the date of the RDL file itself.
Can any one provide any advice or suggestions about how to do this?
Thanks!Matthew, there are two ways you can do this...
Write a code block to get it from the report itself, OR, if you are using
code to render the report, then just pass in the date as a parameter for the
report itself as the catalogItem will have the creation as well as the
modification date for the report item (or any item for that matter in RS).
=-Chris
"Matthew" <Matthew@.discussions.microsoft.com> wrote in message
news:C8ED6A5B-0FE0-4BA9-A02B-24B0BABDC1D5@.microsoft.com...
>I would like to be able to include the report revision date as part of a
> footer. I would like to get this date automatically from the date the
> report
> was last saved or the date of the RDL file itself.
> Can any one provide any advice or suggestions about how to do this?
> Thanks!|||That gives me some places to start. Thanks for the help.

Revised SP4 Date

Hi
Does anyone know the release date for the revised SP4 (fixing the AWE memory
issue)
Thanks,
There are hotfix for awe. As support to obtain.
Ramunas
"dave222" <dave222@.discussions.microsoft.com> wrote in message
news:48321459-A13F-4AD8-97E0-915D90752952@.microsoft.com...
> Hi
> Does anyone know the release date for the revised SP4 (fixing the AWE
memory
> issue)
> Thanks,
>

Revised SP4 Date

Hi
Does anyone know the release date for the revised SP4 (fixing the AWE memory
issue)
Thanks,There are hotfix for awe. As support to obtain.
Ramunas
"dave222" <dave222@.discussions.microsoft.com> wrote in message
news:48321459-A13F-4AD8-97E0-915D90752952@.microsoft.com...
> Hi
> Does anyone know the release date for the revised SP4 (fixing the AWE
memory
> issue)
> Thanks,
>|||Can someone confirm that they are running SP4 w/this AWE hotfix and
everything is running fine?
"Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
news:1118747296.697763@.loger.vpmarket.int...
> There are hotfix for awe. Ask support to obtain.|||Yes. This hotfix fixes the issue.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:ukpzU7NcFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Can someone confirm that they are running SP4 w/this AWE hotfix and
> everything is running fine?
> "Ramunas Balukonis" <ramblk2@.hotmail.com> wrote in message
> news:1118747296.697763@.loger.vpmarket.int...
>> There are hotfix for awe. Ask support to obtain.
>

Revised SP4 Date

Hi
Does anyone know the release date for the revised SP4 (fixing the AWE memory
issue)
Thanks,There are hotfix for awe. As support to obtain.
Ramunas
"dave222" <dave222@.discussions.microsoft.com> wrote in message
news:48321459-A13F-4AD8-97E0-915D90752952@.microsoft.com...
> Hi
> Does anyone know the release date for the revised SP4 (fixing the AWE
memory
> issue)
> Thanks,
>

Returnung data with a running date timeframe

In Query Analyzer, I would like to design a view that would that would return
data automatically on a running annual, quarterly, monthly or weekly basis.
For example(from the SOP30200 table in Great Plains Dynamics 7.0):
sopnumbe soptype docdate subtotal
-- -- -- --
I would like all data from today going back for either one of:
one year, quarter, month or week.
Thanks.
Hi Hugo...I am not familiar with the Decalre statement. I work in Query
Analyzer exclusively with Select statements. Will Declare and Set update,
delete or change any live data in the tables.
Thank you.
Charlie
"Hugo Kornelis" wrote:

> On Wed, 27 Dec 2006 11:45:02 -0800, chas2006 wrote:
>
> Hi chas2006,
> I'm not familiar with Great Plains, but here's a generic form of a query
> that calculates subtotals for current month, quarter, and year. You can
> adapt this to your specific needs.
> DECLARE @.BeginMonth datetime,
> @.BeginQuarter datetime,
> @.BeginYear datetime;
> SET @.BeginMonth = DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0);
> SET @.BeginQuarter = DATEADD(qq, DATEDIFF(qq, 0, CURRENT_TIMESTAMP), 0);
> SET @.BeginYear = DATEADD(yy, DATEDIFF(yy, 0, CURRENT_TIMESTAMP), 0);
> SELECT Product,
> SUM(CASE WHEN DATEDIFF(mm, @.BeginMonth, SaleDate) = 0
> THEN Amount ELSE 0 END) AS MonthSales,
> SUM(CASE WHEN DATEDIFF(qq, @.BeginQuarter, SaleDate) = 0
> THEN Amount ELSE 0 END) AS QuarterSales,
> SUM(CASE WHEN DATEDIFF(yy, @.BeginYear, SaleDate) = 0
> THEN Amount ELSE 0 END) AS YearSales
> FROM SalesTable
> WHERE SaleDate >= @.BeginYear
> GROUP BY Product;
> --
> Hugo Kornelis, SQL Server MVP
>

returning values in stored proceedures

Helloooooooooooooo,

I've createde a stored proceedure that executes an INSERT.

There is a field [date] and i want to put the current date in there.

This doesn't work but there must be some easy way:
thanks in advance!!!!

-----
CREATE PROCEDURE dbo.sp_InsertSomething
@.something varchar(50),
@.date smalldatetime
AS
-- this is where i get lost
--SELECT getDate() AS mydate
--@.date = mydate

INSERT INTO [dbo].[mytable] (something, [date])
VALUES(
@.something,
@.date
)

GO
------Why not set the default of the field to getDate() in the table ?|||Originally posted by rnealejr
Why not set the default of the field to getDate() in the table ?

oh man that is a really good idea. how dumb am i!|||alternatively...

INSERT INTO [dbo].[mytable] (something, [date])
VALUES(@.something, getDate())

Returning the entered date

My question might be stupid but How to retrieve records for a date that I would like to determine?

Means I would like to have a parameter where I can enter the date I want to retrieve my records.

At the beginning I had it set up as: Between [Beginning Date] And [Ending Date]. Now I just want One parameter

Thanks for your helpHi
I am not sure if I fully understand your question.
you could do something like

select orderid, ordername from ordertable where dt_ordered = to_date('DATE_REQUIRED','YYYY/MM/DD');

Make sure DATE REQUIRED is of the format 'YYYY/MM/DD'.
Thanx and Regards
Aruneesh|||I want SQL to retrieve all the records to the corresponding date that I'll enter.

Means I'll have one pop up parameter where I enter the date I want.

I have a already one pop up parameter in my SQL statement for country which is: Like [Country:].
When I run my query a parameter pop up and I return the country I want.

I would like to do the same thing with the Date. Having a parameter where I enter the date I want.

Thanks|||I wanted to get some clarification on the scenario.
I dont under what popup you are mentioning here.

Are you anyway using VB to link it to some DB.
Please clarify the popup portion.
Thanx and Regards
Aruneesh|||The pop portion is just a parameter that pops up when you run your query.

In access your query can be presented in 3 ways: Design View, Table View and SQL view.

I'm working out of the Design view and when you specify a parameter in the criteria part of a field, it will pop up as a parameter when you run your Table view.

Anyway I found the solution. For the date and to return the date that you want on your parameter you just set the criteria as this:
Like [Date:] in your date field. In SQL it's translated like this:
WHERE Table.OrderDate = [Date:]

And now I bumped into another issue:
How to return the text that I want in my parameter. Means I want to look for a word in the whole title and the query will run the companies that contain that word.
I don't want to specify the word in my SQL statement I want to leave it open like I've done above for the date. (I'm working out of Access Database)

Thanks for the help

returning only first record of partition that exceeds threshold

Hi Gurus
Need some help. I have a account table with the following columns. I want
to
only see the first date the account threshold is >= 105 or 120 under
theOCLWatch column. However, if I have consecutive dates that exceed my
threshold I only want to see the first time it exceeds 105 or 120. I want t
o
evaluate every record in the account table and only return the first time my
account exceeds the treshold. My account could exceed the threshold in day
1
then go under the threshold in day 2, then go back over the threshold in day
3. In this case I ony want to see Day 1 and Day 3. I am thinking I need a
varaible and cursor. I need to partition this by account because my table ma
y
contain multiple accounts. Any help would be greatly appreciated . Example
Below(USING SQL 2005)
SELECT
Date
,AccountId
,CurrentBalance
,CreditBalance
,CurrentBalance/CreditLimt*100 as OCLWatch
FROM Account
ORDER BY Date ASC
Sample Results:
Date Account CurrentBalance CreditLimit OCLWatch
12/1/05 123 208 200 104
12/2/05 123 209 200 104
12/3/05 123 211 200 105 Want to
see this only
12/4/05 123 211 200 105
12/5/05 123 211 200 105
12/6/05 123 150 200 75
12/7/05 123 225 200 125 Want to
see this only
12/8/05 123 225 200 125
12/9/05 123 209 200 104
12/10/05 123 211 200 105 Want to
see this only
12/11/05 123 225 200 125 Want to
see this too!!Can you give the create script and the inserts for sample data. It will be
easier for us to give the query :)
Also tell us if there will be one record per day?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Randy" wrote:

> Hi Gurus
> Need some help. I have a account table with the following columns. I wan
t to
> only see the first date the account threshold is >= 105 or 120 under
> theOCLWatch column. However, if I have consecutive dates that exceed my
> threshold I only want to see the first time it exceeds 105 or 120. I want
to
> evaluate every record in the account table and only return the first time
my
> account exceeds the treshold. My account could exceed the threshold in da
y 1
> then go under the threshold in day 2, then go back over the threshold in d
ay
> 3. In this case I ony want to see Day 1 and Day 3. I am thinking I need
a
> varaible and cursor. I need to partition this by account because my table
may
> contain multiple accounts. Any help would be greatly appreciated . Examp
le
> Below(USING SQL 2005)
>
> SELECT
> Date
> ,AccountId
> ,CurrentBalance
> ,CreditBalance
> ,CurrentBalance/CreditLimt*100 as OCLWatch
> FROM Account
> ORDER BY Date ASC
> Sample Results:
> Date Account CurrentBalance CreditLimit OCLWatch
> 12/1/05 123 208 200 104
> 12/2/05 123 209 200 104
> 12/3/05 123 211 200 105 Want to
> see this only
> 12/4/05 123 211 200 105
> 12/5/05 123 211 200 105
> 12/6/05 123 150 200 75
> 12/7/05 123 225 200 125 Want to
> see this only
> 12/8/05 123 225 200 125
> 12/9/05 123 209 200 104
> 12/10/05 123 211 200 105 Want to
> see this only
> 12/11/05 123 225 200 125 Want to
> see this too!!
>
>
>
>|||Randy,
I am not sure i understand your problem, but try this:
select '20051201' tr_date, 1 account, 104 OCLWatch
into #t
union all
select '20051202' tr_date, 1 account, 104 OCLWatch
union all
select '20051203' tr_date, 1 account, 105 OCLWatch
union all
select '20051204' tr_date, 1 account, 105 OCLWatch
union all
select '20051206' tr_date, 1 account, 104 OCLWatch
union all
select '20051207' tr_date, 1 account, 106 OCLWatch
union all
select '20051208' tr_date, 1 account, 126 OCLWatch
union all
select '20051209' tr_date, 1 account, 127 OCLWatch
go
select * from #t
where
(case when OCLWatch <105 then 0
when OCLWatch > 119 then 2
else 1 end) >
(select top 1
case when t1.OCLWatch <105 then 0
when t1.OCLWatch > 119 then 2
else 1 end
from #t t1
where t1.account=#t.account and t1.tr_date<#t.tr_date
order by t1.tr_date desc
)
tr_date account OCLWatch
-- -- --
20051203 1 105
20051207 1 106
20051208 1 126
(3 row(s) affected)

Returning last months data

I'm running a query in a reporting program which returns all fields for a certain date (using SQL). One of the queries was to find tuples that had a date that equalled yesterday. so to get the correct results just queried date >= sysdate-1 and date < sysdate.

I now need to try and get it to return records for all the previous month. This is run every month so I can't just put in date >= 01-AUG-2005 and date < 01-SEP-2005. Any ideas how i could automate this using sysdate so i dont have to change the fields manually everytime?

Thanks in advance.

last day of prior month:
dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
first day of prior month:
dateadd(mm,DATEDIFF(mm,0,DATEADD(mm,-0-DATEPART(day,0),getdate())),0)

Hope this helps,
Josh|||What about this one?

SELECT *
FROM your_table
WHERE YEAR(date) = YEAR(GETDATE())
AND MONTH(date) = MONTH(GETDATE()) - 1|||That sounds nearly about right but bascially im trying to compare it with a variable.

i.e:

SELECT *
FROM table
WHERE TO_CHAR(var1, 'YYYY/MM') >= '2005/08'
AND TO_CHAR(var1, 'YYYY/MM') < '2005/09'

What i'm wanting to automate is the 2005/08 and the 2005/09, so i can compare value in that format.

Returning First Day of Week

Running SQL 2005 SP2.
I am wondering if there is a simple script or function that, for a given date,
I will get returned the previous Sunday at midnight?
For example, if my given date is today (Wednesday, February 6, 2008), my
return value would be 2/3/2008 00:00:00.000.
Message posted via http://www.droptable.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:7f58a15d04fbc@.uwe...
> Running SQL 2005 SP2.
> I am wondering if there is a simple script or function that, for a given
> date,
> I will get returned the previous Sunday at midnight?
> For example, if my given date is today (Wednesday, February 6, 2008), my
> return value would be 2/3/2008 00:00:00.000.
> --
> Message posted via http://www.droptable.com
>
DECLARE @.dt DATETIME;
SET @.dt = CURRENT_TIMESTAMP;
SELECT DATEADD(DAY,7*FLOOR(DATEDIFF(DAY,'20000102',@.dt)/7.0),'20000102');
David Portas

Returning First Day of Week

Running SQL 2005 SP2.
I am wondering if there is a simple script or function that, for a given date,
I will get returned the previous Sunday at midnight?
For example, if my given date is today (Wednesday, February 6, 2008), my
return value would be 2/3/2008 00:00:00.000.
--
Message posted via http://www.sqlmonster.com"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7f58a15d04fbc@.uwe...
> Running SQL 2005 SP2.
> I am wondering if there is a simple script or function that, for a given
> date,
> I will get returned the previous Sunday at midnight?
> For example, if my given date is today (Wednesday, February 6, 2008), my
> return value would be 2/3/2008 00:00:00.000.
> --
> Message posted via http://www.sqlmonster.com
>
DECLARE @.dt DATETIME;
SET @.dt = CURRENT_TIMESTAMP;
SELECT DATEADD(DAY,7*FLOOR(DATEDIFF(DAY,'20000102',@.dt)/7.0),'20000102');
--
David Portas|||When I am working with dates I always stick in an auxillary calendar
table, this is very useful in quickly finding days of weeks etc.
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

returning date without time part

Hi
I have a simple select like this
Select MyDate, MyID From Sales
The select is returning the date like this:
01/06/2005 12:00:00 a.m.
02/06/2005 12:00:00 a.m.
I'm showing it on a Datagrid in my app, my question is can I modify the
select to return only the date without the time part, like this
01/06/2005
02/06/2005
Thks.
Kenny M.If you're returning a datetime value, then no, you can't strip the date. if
you convert it to char, you can.
SELECT CONVERT(CHAR(10), MyDate, 101) AS MyDate, MyId FROM Sales
What you probably want to do, however, is set the format on the
GridColumnStyle in the client app so that it only displays the date.
"Kenny M." wrote:

> Hi
> I have a simple select like this
> Select MyDate, MyID From Sales
> The select is returning the date like this:
> 01/06/2005 12:00:00 a.m.
> 02/06/2005 12:00:00 a.m.
> I'm showing it on a Datagrid in my app, my question is can I modify the
> select to return only the date without the time part, like this
> 01/06/2005
> 02/06/2005
> Thks.
> --
> Kenny M.

Returning Date without

Hi
I have a simple select like this
Select MyDate, MyID From Sales
The select is returning the date like this:
01/06/2005 12:00:00 a.m.
02/06/2005 12:00:00 a.m.
I'm showing it on a Datagrid in my app, my question is can I modify the
select to return only the date without the time part, like this
01/06/2005
02/06/2005
Thks.See the CONVERT function as its arguments in SQL Server Books Online
Anith

Returning Date Formats in Stored Procedures?

Ok, so you can use MONTH, DATENAME(mm) to return Month properties.
Using MONTH(...) returns the numeric value.
Using DATENAME(mm, ...) returns the full month name.
What if you wanted to get the Month returned to you formatted like:
Apr
Are you forced to return a substring of the return value, or is there
some special function?
On the same token, let's say the getdate() returns:
6/1/2006 11:00:37 AM
What if you wanted just:
Jun 1 11:00am
Note the removal of space afer time and removal of seconds, and
lowercase am/pm.For all intents and purposes, let's just use this ad-hoc Stored
Procedure for an example.
CREATE PROCEDURE pGetUploadRefs
AS
BEGIN
SELECT DATENAME(mm, DateTimeUploaded)
FROM SiteStatus
END
GO|||DECLARE @.dtSampleDt datetime, @.vchDay varchar(2), @.vchFormattedDt
varchar(15)
SELECT @.dtSampleDt = GETDATE() + 30 --FINAL OUTPUT: Jun 1 2:49pm
--SELECT @.dtSampleDt = DATEADD(mi, -167, GETDATE()) --FINAL OUTPUT: May
2 0:0pm
--SELECT @.dtSampleDt = DATEADD(mi, -888, GETDATE()) --FINAL OUTPUT: May
2 0:0am
SELECT @.vchDay = DATENAME(hh, @.dtSampleDt)
SELECT @.vchFormattedDt = LEFT(DATENAME(m, @.dtSampleDt), 3) + ' ' +
DATENAME(d, @.dtSampleDt) + ' '
SELECT @.dtSampleDt, @.vchDay, @.vchFormattedDt
IF (@.vchDay < 12)
BEGIN
SELECT @.vchFormattedDt = @.vchFormattedDt + DATENAME(hh, @.dtSampleDt) +
':' + DATENAME(mi, @.dtSampleDt) + 'am'
END
ELSE
BEGIN
SELECT @.vchFormattedDt = @.vchFormattedDt + CAST((DATEPART(hh,
@.dtSampleDt) - 12) AS VARCHAR) + ':' + DATENAME(mi, @.dtSampleDt) + 'pm'
END
SELECT @.vchFormattedDt
*** Sent via Developersdex http://www.examnotes.net ***|||The CONVERT function has an extra argument for formatting dates but I think
most people are going to tell you to do this client-side. Have a look at
CONVERT in BOL.
SELECT CONVERT ( VARCHAR, GETDATE(), 106 )
SELECT CONVERT ( VARCHAR, GETDATE(), 112 )
Potentially you could build your specially formatted string, but why do it
to yourself? You're going to get a performance hit breaking the strings up.
SELECT
CONVERT ( VARCHAR, GETDATE(), 106 ) + ' ' +
CAST( DATEPART( hour, GETDATE() ) AS VARCHAR ) + ':' +
CAST( DATEPART( minute, GETDATE() ) AS VARCHAR )
Doing it client-side, VBA / VB6 for example provides the Format function eg:
Format(Now(),"mmm d yyyy h:mmam/pm")
As flexible as you want!
You can easily format it in Excel, Crystal Reports etc. Is there a special
reason you need to do this?
Damien
"cider123@.hotmail.com" wrote:

> Ok, so you can use MONTH, DATENAME(mm) to return Month properties.
> Using MONTH(...) returns the numeric value.
> Using DATENAME(mm, ...) returns the full month name.
> What if you wanted to get the Month returned to you formatted like:
> Apr
> Are you forced to return a substring of the return value, or is there
> some special function?
> On the same token, let's say the getdate() returns:
> 6/1/2006 11:00:37 AM
> What if you wanted just:
> Jun 1 11:00am
> Note the removal of space afer time and removal of seconds, and
> lowercase am/pm.
>|||It's actually being interfaced in databound controls for ASP pages. I
want to be able to change the format (if desired by clients) on the fly
in a SP vs making changes in the web site.
I'm used to making such formats client side in code in the C# Apps I
develop, but my knowledge in the the ASP and SQL stuff is rather light.
I know how to do the basic functions and stored procedures, but
nothing really in depth like my current project has brought to the
table.
Thank you for all the help and feedback provided!