uninstall SQL: based

Showing posts with label based. Show all posts

Tuesday, March 27, 2012

CASE Statement - I'm in a bit of a quandry

Hello everyone,

I'm having a problem with trying to check a value from one column and add a value to the next column based on what the first column is. Below are my to CASE statements. What I need is to make field IN_OUT either "IN" for when field UpDnGrd is "Upgrade" and "Out" for when UpDnGrd is "Downgrade"

Kinda like this:

IF UpDnGrd = "U" THEN make IN_OUT = IN

IF UpDnGrd = "D" THEN make IN_OUT = OUT

ELSE keep the original value of IN_OUT which will always be either IN or OUT

see below example of my current CASE statement. Just want to know how to change the value of IN_OUT based on what the value of UpDnGrd is.

================================================== =
CASE sv7.InOut
WHEN 'I' THEN 'IN'
WHEN 'O' THEN 'OUT'

END AS IN_OUT,

CASE sv11.S11M14

WHEN 'D' THEN 'Downgrade'
WHEN 'U' THEN 'Upgrade'
ELSE ' '

END AS UpDnGrd
================================================== =

Thank YOU!How could I merge the 2 case statements?|||You question and code sample are a little confusing, but I think this is what you want:

SELECT
CASE

WHEN sv11.S11M14 = 'D' THEN 'OUT'
WHEN sv11.S11M14 = 'U' THEN 'IN'
WHEN sv7.InOut = 'I' THEN 'IN'
WHEN sv7.InOut = 'O' THEN 'OUT'
ELSE ' '

END AS IN_OUT

FROM ...

Only the first WHEN condition will be processed.

Thursday, March 22, 2012

CASE problems with select statement.

I'm trying to build a select statement using the CASE expression. All
I want is to build the WHERE piece of the select statement based on a
parameter value.
I want to somehow CASE the WHERE clause based on a parameter of let's
say, @.DateType
SELECT *
FROM <table>
WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
SELECT *
FROM <table>
WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
I know there is something simple i'm over looking.
thanks folks...
-fdAre you talking about something like this? Note this sample works in the
AdventureWorks sample database:
DECLARE @.start DATETIME;
DECLARE @.end DATETIME;
DECLARE @.dateType VARCHAR(5);
SELECT @.start = '2001-08-01',
@.end = '2001-08-15',
@.dateType = 'Ship';
SELECT *
FROM Sales.SalesOrderHeader
WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
WHEN 'Due' THEN DueDate
WHEN 'Ship' THEN ShipDate
END BETWEEN @.start AND @.end;
"forest demon" <mete.hanap@.gmail.com> wrote in message
news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
> I'm trying to build a select statement using the CASE expression. All
> I want is to build the WHERE piece of the select statement based on a
> parameter value.
> I want to somehow CASE the WHERE clause based on a parameter of let's
> say, @.DateType
> SELECT *
> FROM <table>
> WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
> SELECT *
> FROM <table>
> WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
> I know there is something simple i'm over looking.
> thanks folks...
> -fd|||On Mar 6, 10:23=A0pm, "Mike C#" <x...@.xyz.com> wrote:
> Are you talking about something like this? Note this sample works in the
> AdventureWorks sample database:
> DECLARE @.start DATETIME;
> DECLARE @.end DATETIME;
> DECLARE @.dateType VARCHAR(5);
> SELECT @.start =3D '2001-08-01',
> =A0 @.end =3D '2001-08-15',
> =A0 @.dateType =3D 'Ship';
> SELECT *
> FROM Sales.SalesOrderHeader
> WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
> =A0 WHEN 'Due' THEN DueDate
> =A0 WHEN 'Ship' THEN ShipDate
> =A0 END BETWEEN @.start AND @.end;
> "forest demon" <mete.ha...@.gmail.com> wrote in message
> news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
>
> > I'm trying to build a select statement using the CASE expression. All
> > I want is to build the WHERE piece of the select statement based on a
> > parameter value.
> > I want to somehow CASE the WHERE clause based on a parameter of let's
> > say, @.DateType
> > SELECT *
> > FROM <table>
> > WHERE =A0 <columnA> =A0 >=3D =A0 @.BeginDate =A0AND =A0 <columnA> =A0 <==3D @.EndDate
> > SELECT *
> > FROM <table>
> > WHERE =A0 <columnB> =A0 >=3D =A0 @.BeginDate =A0AND =A0 <columnB> =A0 <==3D @.EndDate
> > I know there is something simple i'm over looking.
> > thanks folks...
> > -fd- Hide quoted text -
> - Show quoted text -
sorry, i should have added a little more info. i use this in a stored
procedure and
i have the following thus far.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDataReaderStoredProcedure]
(
-- Add the parameters for the stored procedure here
@.BeginDate datetime,
@.EndDate datetime,
@.DateType varchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM Permits
WHERE CASE @.dateType
WHEN 'S_DATE' THEN 'S_DATE >=3D @.BeginDate AND S_DATE <=3D @.EndDate'
WHEN 'G_DATE' THEN 'G_DATE >=3D @.BeginDate AND G_DATE <=3D @.EndDate'
END
END
the DateType comes in as one of two different date types. DateType
either comes
in as 'G_DATE' or 'S_DATE'. the columns in the table are also G_DATE
and S_DATE.
thanks for your time...|||On Mar 6, 11:01=A0pm, forest demon <mete.ha...@.gmail.com> wrote:
> On Mar 6, 10:23=A0pm, "Mike C#" <x...@.xyz.com> wrote:
>
>
> > Are you talking about something like this? Note this sample works in the=
> > AdventureWorks sample database:
> > DECLARE @.start DATETIME;
> > DECLARE @.end DATETIME;
> > DECLARE @.dateType VARCHAR(5);
> > SELECT @.start =3D '2001-08-01',
> > =A0 @.end =3D '2001-08-15',
> > =A0 @.dateType =3D 'Ship';
> > SELECT *
> > FROM Sales.SalesOrderHeader
> > WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
> > =A0 WHEN 'Due' THEN DueDate
> > =A0 WHEN 'Ship' THEN ShipDate
> > =A0 END BETWEEN @.start AND @.end;
> > "forest demon" <mete.ha...@.gmail.com> wrote in message
> >news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...=
> > > I'm trying to build a select statement using the CASE expression. All
> > > I want is to build the WHERE piece of the select statement based on a
> > > parameter value.
> > > I want to somehow CASE the WHERE clause based on a parameter of let's
> > > say, @.DateType
> > > SELECT *
> > > FROM <table>
> > > WHERE =A0 <columnA> =A0 >=3D =A0 @.BeginDate =A0AND =A0 <columnA> =A0 <==3D @.EndDate
> > > SELECT *
> > > FROM <table>
> > > WHERE =A0 <columnB> =A0 >=3D =A0 @.BeginDate =A0AND =A0 <columnB> =A0 <==3D @.EndDate
> > > I know there is something simple i'm over looking.
> > > thanks folks...
> > > -fd- Hide quoted text -
> > - Show quoted text -
> sorry, i should have added a little more info. =A0i use this in a stored
> procedure and
> i have the following thus far.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[spDataReaderStoredProcedure]
> (
> =A0 =A0 =A0 =A0 -- Add the parameters for the stored procedure here
> =A0 =A0 =A0 =A0 @.BeginDate datetime,
> =A0 =A0 =A0 =A0 @.EndDate datetime,
> =A0 =A0 =A0 =A0 @.DateType varchar(50)
> )
> AS
> BEGIN
> =A0 =A0 =A0 =A0 SET NOCOUNT ON;
> SELECT *
> FROM Permits
> WHERE CASE @.dateType
> =A0 WHEN 'S_DATE' THEN 'S_DATE >=3D @.BeginDate AND S_DATE <=3D @.EndDate'
> =A0 WHEN 'G_DATE' THEN 'G_DATE >=3D @.BeginDate AND G_DATE <=3D @.EndDate'
> END
> END
> the DateType comes in as one of two different date types. DateType
> either comes
> in as 'G_DATE' or 'S_DATE'. =A0the columns in the table are also G_DATE
> and S_DATE.
> thanks for your time...- Hide quoted text -
> - Show quoted text -
i finally figured it out...damn, i hate making silly mistakes....
thanks for your input mike...
-fd|||There is case, But there is also Between
Select * from table where DateColumn Between @.BeginDate and @.enddate
Case should not be used in a where.
Better to create a Table Variable and load from an index of Quailifing
Primary keys.
Then Join on the Table variable.
!0 fold performance increase.
-R
"forest demon" <mete.hanap@.gmail.com> wrote in message
news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
> I'm trying to build a select statement using the CASE expression. All
> I want is to build the WHERE piece of the select statement based on a
> parameter value.
> I want to somehow CASE the WHERE clause based on a parameter of let's
> say, @.DateType
> SELECT *
> FROM <table>
> WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
> SELECT *
> FROM <table>
> WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
> I know there is something simple i'm over looking.
> thanks folks...
> -fd|||"Randy Pitkin" <RandyPitkin@.ydpages.com> wrote in message
news:eVT$m35gIHA.5088@.TK2MSFTNGP02.phx.gbl...
> There is case, But there is also Between
> Select * from table where DateColumn Between @.BeginDate and @.enddate
> Case should not be used in a where.
> Better to create a Table Variable and load from an index of Quailifing
> Primary keys.
> Then Join on the Table variable.
> !0 fold performance increase.
BETWEEN replaces the >= AND <= but doesn't do anything for the OP's question
of dynamically determining which column to use to limit rows. Whether or
not he needs a performance increase probably depends on how much data he's
querying, etc.sql

CASE problems with select statement.

I'm trying to build a select statement using the CASE expression. All
I want is to build the WHERE piece of the select statement based on a
parameter value.
I want to somehow CASE the WHERE clause based on a parameter of let's
say, @.DateType
SELECT *
FROM <table>
WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
SELECT *
FROM <table>
WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
I know there is something simple i'm over looking.
thanks folks...
-fd
Are you talking about something like this? Note this sample works in the
AdventureWorks sample database:
DECLARE @.start DATETIME;
DECLARE @.end DATETIME;
DECLARE @.dateType VARCHAR(5);
SELECT @.start = '2001-08-01',
@.end = '2001-08-15',
@.dateType = 'Ship';
SELECT *
FROM Sales.SalesOrderHeader
WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
WHEN 'Due' THEN DueDate
WHEN 'Ship' THEN ShipDate
END BETWEEN @.start AND @.end;
"forest demon" <mete.hanap@.gmail.com> wrote in message
news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
> I'm trying to build a select statement using the CASE expression. All
> I want is to build the WHERE piece of the select statement based on a
> parameter value.
> I want to somehow CASE the WHERE clause based on a parameter of let's
> say, @.DateType
> SELECT *
> FROM <table>
> WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
> SELECT *
> FROM <table>
> WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
> I know there is something simple i'm over looking.
> thanks folks...
> -fd
|||On Mar 6, 10:23Xpm, "Mike C#" <x...@.xyz.com> wrote:
> Are you talking about something like this? Note this sample works in the
> AdventureWorks sample database:
> DECLARE @.start DATETIME;
> DECLARE @.end DATETIME;
> DECLARE @.dateType VARCHAR(5);
> SELECT @.start = '2001-08-01',
> X @.end = '2001-08-15',
> X @.dateType = 'Ship';
> SELECT *
> FROM Sales.SalesOrderHeader
> WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
> X WHEN 'Due' THEN DueDate
> X WHEN 'Ship' THEN ShipDate
> X END BETWEEN @.start AND @.end;
> "forest demon" <mete.ha...@.gmail.com> wrote in message
> news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
>
>
>
>
>
> - Show quoted text -
sorry, i should have added a little more info. i use this in a stored
procedure and
i have the following thus far.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDataReaderStoredProcedure]
(
-- Add the parameters for the stored procedure here
@.BeginDate datetime,
@.EndDate datetime,
@.DateType varchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM Permits
WHERE CASE @.dateType
WHEN 'S_DATE' THEN 'S_DATE >= @.BeginDate AND S_DATE <= @.EndDate'
WHEN 'G_DATE' THEN 'G_DATE >= @.BeginDate AND G_DATE <= @.EndDate'
END
END
the DateType comes in as one of two different date types. DateType
either comes
in as 'G_DATE' or 'S_DATE'. the columns in the table are also G_DATE
and S_DATE.
thanks for your time...
|||On Mar 6, 11:01Xpm, forest demon <mete.ha...@.gmail.com> wrote:
> On Mar 6, 10:23Xpm, "Mike C#" <x...@.xyz.com> wrote:
>
>
>
>
>
>
>
>
>
> sorry, i should have added a little more info. Xi use this in a stored
> procedure and
> i have the following thus far.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[spDataReaderStoredProcedure]
> (
> X X X X -- Add the parameters for the stored procedure here
> X X X X @.BeginDate datetime,
> X X X X @.EndDate datetime,
> X X X X @.DateType varchar(50)
> )
> AS
> BEGIN
> X X X X SET NOCOUNT ON;
> SELECT *
> FROM Permits
> WHERE CASE @.dateType
> X WHEN 'S_DATE' THEN 'S_DATE >= @.BeginDate AND S_DATE <= @.EndDate'
> X WHEN 'G_DATE' THEN 'G_DATE >= @.BeginDate AND G_DATE <= @.EndDate'
> END
> END
> the DateType comes in as one of two different date types. DateType
> either comes
> in as 'G_DATE' or 'S_DATE'. Xthe columns in the table are also G_DATE
> and S_DATE.
> thanks for your time...- Hide quoted text -
> - Show quoted text -
i finally figured it out...damn, i hate making silly mistakes....
thanks for your input mike...
-fd
|||There is case, But there is also Between
Select * from table where DateColumn Between @.BeginDate and @.enddate
Case should not be used in a where.
Better to create a Table Variable and load from an index of Quailifing
Primary keys.
Then Join on the Table variable.
!0 fold performance increase.
-R
"forest demon" <mete.hanap@.gmail.com> wrote in message
news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
> I'm trying to build a select statement using the CASE expression. All
> I want is to build the WHERE piece of the select statement based on a
> parameter value.
> I want to somehow CASE the WHERE clause based on a parameter of let's
> say, @.DateType
> SELECT *
> FROM <table>
> WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
> SELECT *
> FROM <table>
> WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
> I know there is something simple i'm over looking.
> thanks folks...
> -fd
|||"Randy Pitkin" <RandyPitkin@.ydpages.com> wrote in message
news:eVT$m35gIHA.5088@.TK2MSFTNGP02.phx.gbl...
> There is case, But there is also Between
> Select * from table where DateColumn Between @.BeginDate and @.enddate
> Case should not be used in a where.
> Better to create a Table Variable and load from an index of Quailifing
> Primary keys.
> Then Join on the Table variable.
> !0 fold performance increase.
BETWEEN replaces the >= AND <= but doesn't do anything for the OP's question
of dynamically determining which column to use to limit rows. Whether or
not he needs a performance increase probably depends on how much data he's
querying, etc.

Tuesday, March 20, 2012

Case function in T-SQL

Wondering if possible to use case function in where clause of T-SQL statement. I have this application that needs to get the recordsets based on the day of the date selected that is @.dDay in one of these set of values {0,7,14,21, more). Though, I know that if I use either of this:

datediff(dd,@.startdate,@.today) = @.dDay

or convert(varchar(12), @.startDate, 101) between convert(varchar(12), @.today, 101) and convert(varchar(12), dateadd(dd,@.dDay, @.today), 101)

It will work for only the values that are specific as in number but what of if "more" is selected which means that from that day upward, can i use this T-SQL to accomplish this. PLease help

Any suggestion is welcome. thanks

I think I got confused.. can you explain a bit more..?

|||

i too am confused by what you want but just keep in mind that if you include a function your where clause you will likely disallow the use of indexes on your date field, if there is one

|||

 SELECT *FROM tblWHERE StartDate>=DATEADD(DAY,DATEDIFF(DAY, 0, @.today), 0)AND StartDate<CASEWHEN @.dday='more'THEN'9999-12-31'ELSEDATEADD(DAY,DATEDIFF(DAY, 0, @.today), @.dday+1)END

|||

I mean this; I have a textbox with a dropdownlist control to search my databasein which the user can select either "Today", "7 days", "14 days", and "More". If a user select either of the list item except "More", i think i can easily use the"between" and"and" to get the recordsets or usedatediff function to get the recordsets, but what of if the user select"more" which is not bounded but means "from that day and on", how can one control this by using the same T-SQL statement to return the recordset from the database. I am not ad-hoc method to access my datastore but stored procedure.

So i am looking at usingcase function to make the decision and return the recordsets. Is it possible or is there any simpler way?.

Please Help. Urgent

|||

If I understand you correctly, I would use a second stored procedure for the "More" case. IOW,

select @.SomeDate = datediff(....)

then

select ...... from..... where DateField < @.SomeDate

If the user selects a specific number of days, select proc1, else select proc2

Another option is to use a big if statement with 2 selects (to handle either case), but that won't optimize as well

|||

If that in the case, I would change the listitem in the dropdown with the "more" text to have an extremely large value, like say 100000 (250ish years). Then you don't have to have anything complicated on the SQL side. That way you can still have "more" displayed in the listbox, but when it actually goes to send it to the SqlDatasource, it will send 100000 instead of the word more.

Case Expression within a Stored Proc

Is it possible? I have a request to create a stored proc that will
dynamically add a range to a WHERE clause based on a numeric value of a
comment type. If the incoming comment type request is say 10, the
where clause needs to be set to IN(10,11,12,13,14,15,16,17,18,19)OR if
a 20 is passed in the clause would read IN(20,21....)
So I was thinking that a CASE expression within the proc would be the
best way to go, but have had no luck in finding an example or any other
related information regarding CASE exp in a proc.
TIA
BillOn 8 Sep 2004 06:44:18 -0700, Bill Willyerd wrote:

>Is it possible? I have a request to create a stored proc that will
>dynamically add a range to a WHERE clause based on a numeric value of a
>comment type. If the incoming comment type request is say 10, the
>where clause needs to be set to IN(10,11,12,13,14,15,16,17,18,19)OR if
>a 20 is passed in the clause would read IN(20,21....)
>So I was thinking that a CASE expression within the proc would be the
>best way to go, but have had no luck in finding an example or any other
>related information regarding CASE exp in a proc.
>TIA
>Bill

Hi Bill,

In this case (no pun intended), I'd simply write it like this:

WHERE MyColumn BETWEEN @.parameter AND @.parameter + 9

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 8 Sep 2004 08:17:57 -0700, Bill Willyerd wrote:

>Sorry I didn't add that if a request for specfic comment type comes in
>like 22 we only return the type 22's.
>I do like the BETWEEN stmt though I haven't seen that before, I will
>remember that one.
>Thx, Bill

Hi Bill,

Is the request for "a specific comment type" passed in through a seperate
parameter? And the other parameter is used to get a range of comment
types?

CREATE PROC MyProc @.Specific int,
@.RangeStart int
AS
IF (@.Specific IS NULL AND @.RangeStart IS NULL)
OR (@.Specific IS NOT NULL AND @.RangeStart IS NOT NULL)
RAISERROR ('Supply exactly one of the two parameters', 16, 1)
ELSE
IF @.Specific IS NOT NULL
SELECT Column List
FROM YourTable
WHERE MyColumn = @.Specific
ELSE
SELECT Column List
FROM YourTable
WHERE MyColumn BETWEE @.RangeStart AND @.RangeStart + 9
go
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 8 Sep 2004 16:28:26 -0700, Bill Willyerd wrote:

>At present it comes as a single request.
(snip)

Hi Bill,

How do you know if the request is for a specific comment type or for a
range? Surely, there has to be some way to distinguish a request for
comment type '20' (meaning just 20) from a request for comment type '20'
(meaning all values 20 through 29).

Maybe this is a good time to explain what information should be included
in newsgroup postings to maximise the chance to get a useful reply:

* Table structure, posted as DDL (CREATE TABLE statements, omitting
irrelevant columns but including all constraints);
* Sample data, posted as INSERT statements (and please verify that the
CREATE TABLE and INSERT statements you post work properly in an empty test
database!);
* Expected output, based on sample data;
* The SQL code you already got (if any), plus the results these give you
and the reason why that is wrong. If you get an error message, copy and
paste the full message;
* A short, concise description of the business problem you're trying to
solve.

Check out these sites as well:
http://www.aspfaq.com/etiquette.asp?id=5006
http://vyaskn.tripod.com/code.htm#inserts

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Monday, March 19, 2012

CASE and DATE statement - Need Help :)

Can someone help please.

I need to create a new column with a name of "AdvertPurchasePrice" based upon a Date Value which is held in a column called "AdvertCreationDate".

If the date is before the 10th of April 2007 then the column "AdvertPurchasePrice" needs to be 299 else it needs to be 399

Can anyone help?

Steve

Quote:

Originally Posted by opusid

Can someone help please.

I need to create a new column with a name of "AdvertPurchasePrice" based upon a Date Value which is held in a column called "AdvertCreationDate".

If the date is before the 10th of April 2007 then the column "AdvertPurchasePrice" needs to be 299 else it needs to be 399

Can anyone help?

Steve

use the CASE...WHEN...END statement...

select AdvertPurchasePrice = case when AdvertCreationDate is before 04/10/2007 then 299
else 399
end
from mytable

this is a pseudocode, not a working code...but if you search the help on the CASE-WHEN-END syntax, you'll see what i mean

Cascading(?) Parameters Help

I have 2 parameters that are of type string. The user can enter anything they want in them. The third parameter is query based and uses the first 2 parameters to get a list of people. Is there a way I can prevent the third parameter from propigating until the first 2 are both filled in? The first 2 cannot be drop downs however, they are used in a wild card fashion.

Thanks!

Yes. Set the default on the text boxes to NULL.

Then it shouldnt try to populate until both are not null.

I just created a test report and it did not populate until both names were entered.

BobP

|||

In RS2005? I just did the same thing, and the 3rd Parameter became available after I entered something in the 1st parameter (no values because the query is where firstname = @.param1 and lastname = @.param2). However, the real query is using firstname LIKE @.param1 and lastname LIKE param2 and thus populates the list as soon as I enter the first name. Which is confusing because param2 is empty. Any ideas?

|||

Ah.. I see. I had the 'Allow blank value' selected. However, not pretty, it works.

|||

So it IS working for you, right?

BobP

|||Yes.. thanks.

Cascading Parameters based off Analysis Services

Hello,

I was trying to do cascading parameters based off my cube and I wasn't able to do this. Is it possible?

For example, I have a dimension that has Products so I first select the parameter for product type (Dairy, Frozen, Candy) and then I have another dropdown listbox that has the name of each product (Milk, Ice Cream, Lemon Drops). The second dropdown listbox should only contain the products that match what parameter was selected in the first dropdown.

When I couldn't get that to work, I went to the source system containing the Dimension tables and just did nice and easy SQL statements from there. It worked but I, for some reason that I can't explain, think this is not the proper way to do it.

Also, is there a way to have a default on the second parameter based on the first parameter selected? I would assume that default would be [All].

Thank you.

-Gumbatman

Yes it is possible. When you use the query designer, by default each parameter depends on the one before it having a value, even if the parameters are not really related. For this, you want to maintain that link. Then, in your MDX that creates the dataset for parameter #2 you can refer to parameter #1 (it will be called something like @.MyParameterName), i.e. you can use it as part of a STRTOSET or STRTOMEMBER function.|||

Sluggy,

Thank you so much for this answer. I was banging my head against the wall trying to figure out how to do this.

-Gumbatman

|||

Could you please post an example of this? My head also aches a lot

I just need the MDX syntax of the second dataset, the one of the parameter that depends on the first one selected.

Is there any way, from within SSRS to "filter" based on the previous parameter inside the MDX sentence?

Using SQL tables datasources and cascading parameters is quite straightforward, not the same with Olap cubes as datasources.

Thank you

Mike

Sunday, March 11, 2012

Cascading Parameters

I have a report that is based on 2 listboxes, the second one's values dependent on the value selected in the first box. How would I display all values in both list boxes on the report if I so decided?

Thanks,

The Rook

I'd be very interested to see how you got one parameter's available values to be dependant on another's. Every way I've tried, I've had a forward dependency error.

Do you mean all available values, or the selected values?

|||I meant the "selected values." MSDN has a walkthrough that's very helpful and it's URL is: http://msdn2.microsoft.com/en-us/library/aa337426(d=printer).aspx - good luck!|||

Thanks for the link.

As for displaying the selected values on the report, that depends. If you are using just single-valued parameters, you can just drop a textbox for each parameter on the page, and set its value to the parameter. If you have a multi-valued parameter, you can try =Join(Parameters!Foo, ", ") for a comma-separated list of selected values. If you wanted something fancier, the only way I can think of is to use that same join syntax to pass the selected parameters to a query that returns the rowset that can populate, say, a table.

Cascading Parameters

I am using DB2 ... how do I tie the parameter from one dataset to another. I
have a query for dataset 1 based on metrics. The metric they choose needs
to be a parameter in the second dataset query I have. I have created a
parameter for the metric and have it tied to a query (in the report
parameters window) ... but if I put metric (which of course has to be a ? for
unnamed parameters) as a parameter in the second dataset then I get an error.
How does reporting services know that the metric is a parameter to the
second dataset?In the dataset click on the ... go to the parameters tab. Map the query
parameter to the parameter that the cascading parameter is based on.
In layout tab go to Report Menu -> Report Parameters. Make sure the
parameter the second dataset is dependent on is before the depenent
parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:F796C0FB-6834-4D06-A875-003809DA36F9@.microsoft.com...
>I am using DB2 ... how do I tie the parameter from one dataset to another.
>I
> have a query for dataset 1 based on metrics. The metric they choose
> needs
> to be a parameter in the second dataset query I have. I have created a
> parameter for the metric and have it tied to a query (in the report
> parameters window) ... but if I put metric (which of course has to be a ?
> for
> unnamed parameters) as a parameter in the second dataset then I get an
> error.
> How does reporting services know that the metric is a parameter to the
> second dataset?|||Bruce,
I'm not sure if I was clear in my post. The first dataset does not have a
parameter but the selection from that result set needs to BE the parameter
for the second dataset
dataset 1:
select distinct metric_code, metric_name
from grsinst1.metric met
join grsinst1.availability_def availdef
on availdef.derived_metric_code = met.metric_code with ur
dataset 2:
SELECT res_name, start_lcl_dt, res_grp_name, res.res_type, value AS
Percent, value_cnt, metric_name, client_name, coalesce(thresh.threshd_target,
grpthresh.threshd_target) AS threshd_target,
coalesce(thresh.threshd_warning, grpthresh.threshd_warning) AS
threshd_warning,
coalesce(thresh.threshd_critical,
grpthresh.threshd_critical) AS threshd_critical, MAX(start_lcl_dt) OVER() AS
MaxDate,
CASE WHEN start_lcl_dt = MAX(start_lcl_dt) OVER()
THEN(((100.0 - VALUE) / 100) * VALUE_CNT) / 60 ELSE 0 END AS DTMin
FROM grsinst1.availability_monthly_vw am JOIN
grsinst1.metric met ON met.metric_code =am.metric_code JOIN
grsinst1.restree_resgrp_client_vw res ON res.res_id =am.res_id AND res.res_grp_id = am.res_grp_id LEFT JOIN
grsinst1.threshold thresh ON thresh.res_code =res.res_code AND thresh.metric_id = met.metric_id AND thresh.res_type =res.res_type JOIN
grsinst1.grp_threshold_vw grpthresh ON
grpthresh.res_grp_id = res.res_grp_id AND grpthresh.metric_id = met.metric_id
WHERE res.client_code LIKE ? AND DATE (start_lcl_dt) >= ? AND DATE
(start_lcl_dt) <= ? AND res.res_code LIKE ? AND group_path LIKE ? AND
res.res_type = ?
GROUP BY res_name, start_lcl_dt, res_grp_name, res.res_type, value,
value_cnt, metric_name, client_name, coalesce(thresh.threshd_target,
grpthresh.threshd_target),
coalesce(thresh.threshd_warning, grpthresh.threshd_warning),
coalesce(thresh.threshd_critical, grpthresh.threshd_critical)
with ur
Dataset 2 used to have metric_code in the WHERE clause - however ... I
needed to make metric code dynamic so I took it out and put it in its own
dataset. If I have in the where clause "where metric_code = ? ... and "
then I get an error saying there are too many parameters for Dataset 2. I
have metric_code (the parameter) mapped to the query for dataset 1 on the
report parameters tab. What am I missing from what you said?
"Bruce L-C [MVP]" wrote:
> In the dataset click on the ... go to the parameters tab. Map the query
> parameter to the parameter that the cascading parameter is based on.
> In layout tab go to Report Menu -> Report Parameters. Make sure the
> parameter the second dataset is dependent on is before the depenent
> parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:F796C0FB-6834-4D06-A875-003809DA36F9@.microsoft.com...
> >I am using DB2 ... how do I tie the parameter from one dataset to another.
> >I
> > have a query for dataset 1 based on metrics. The metric they choose
> > needs
> > to be a parameter in the second dataset query I have. I have created a
> > parameter for the metric and have it tied to a query (in the report
> > parameters window) ... but if I put metric (which of course has to be a ?
> > for
> > unnamed parameters) as a parameter in the second dataset then I get an
> > error.
> > How does reporting services know that the metric is a parameter to the
> > second dataset?
>
>|||Is the user supposed to pick the metric? If so, then the first dataset is
being used as the source for the first parameter. Then everything else I
said holds true. If not, then you should do remove dataset 1 and change the
second one to this:
select ... where ... metric_code in (select distinct metric_code from ...)
Note that you only want one field being returned if you are using an IN
clause.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:2254419F-D76F-4161-BA4D-FD6FFE7316B0@.microsoft.com...
> Bruce,
> I'm not sure if I was clear in my post. The first dataset does not have
> a
> parameter but the selection from that result set needs to BE the parameter
> for the second dataset
> dataset 1:
> select distinct metric_code, metric_name
> from grsinst1.metric met
> join grsinst1.availability_def availdef
> on availdef.derived_metric_code = met.metric_code with ur
> dataset 2:
> SELECT res_name, start_lcl_dt, res_grp_name, res.res_type, value AS
> Percent, value_cnt, metric_name, client_name,
> coalesce(thresh.threshd_target,
> grpthresh.threshd_target) AS threshd_target,
> coalesce(thresh.threshd_warning, grpthresh.threshd_warning) AS
> threshd_warning,
> coalesce(thresh.threshd_critical,
> grpthresh.threshd_critical) AS threshd_critical, MAX(start_lcl_dt) OVER()
> AS
> MaxDate,
> CASE WHEN start_lcl_dt = MAX(start_lcl_dt) OVER()
> THEN(((100.0 - VALUE) / 100) * VALUE_CNT) / 60 ELSE 0 END AS DTMin
> FROM grsinst1.availability_monthly_vw am JOIN
> grsinst1.metric met ON met.metric_code => am.metric_code JOIN
> grsinst1.restree_resgrp_client_vw res ON res.res_id => am.res_id AND res.res_grp_id = am.res_grp_id LEFT JOIN
> grsinst1.threshold thresh ON thresh.res_code => res.res_code AND thresh.metric_id = met.metric_id AND thresh.res_type => res.res_type JOIN
> grsinst1.grp_threshold_vw grpthresh ON
> grpthresh.res_grp_id = res.res_grp_id AND grpthresh.metric_id => met.metric_id
> WHERE res.client_code LIKE ? AND DATE (start_lcl_dt) >= ? AND DATE
> (start_lcl_dt) <= ? AND res.res_code LIKE ? AND group_path LIKE ? AND
> res.res_type = ?
> GROUP BY res_name, start_lcl_dt, res_grp_name, res.res_type, value,
> value_cnt, metric_name, client_name, coalesce(thresh.threshd_target,
> grpthresh.threshd_target),
> coalesce(thresh.threshd_warning, grpthresh.threshd_warning),
> coalesce(thresh.threshd_critical, grpthresh.threshd_critical)
> with ur
>
> Dataset 2 used to have metric_code in the WHERE clause - however ... I
> needed to make metric code dynamic so I took it out and put it in its own
> dataset. If I have in the where clause "where metric_code = ? ... and
> "
> then I get an error saying there are too many parameters for Dataset 2.
> I
> have metric_code (the parameter) mapped to the query for dataset 1 on the
> report parameters tab. What am I missing from what you said?
> "Bruce L-C [MVP]" wrote:
>> In the dataset click on the ... go to the parameters tab. Map the query
>> parameter to the parameter that the cascading parameter is based on.
>> In layout tab go to Report Menu -> Report Parameters. Make sure the
>> parameter the second dataset is dependent on is before the depenent
>> parameter.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:F796C0FB-6834-4D06-A875-003809DA36F9@.microsoft.com...
>> >I am using DB2 ... how do I tie the parameter from one dataset to
>> >another.
>> >I
>> > have a query for dataset 1 based on metrics. The metric they choose
>> > needs
>> > to be a parameter in the second dataset query I have. I have created a
>> > parameter for the metric and have it tied to a query (in the report
>> > parameters window) ... but if I put metric (which of course has to be a
>> > ?
>> > for
>> > unnamed parameters) as a parameter in the second dataset then I get an
>> > error.
>> > How does reporting services know that the metric is a parameter to the
>> > second dataset?
>>|||Bruce,
I am a little confused but I believe I have it correct. Dataset 1 does
not have a parameter - it is a query that is providing the selections for a
parameter (metric_code) Dataset 2 has a query with a where clause that
includes Where metric_code like ? and I want the ? to be the selection
that was made from the choices provided from Dataset 1. In Dataset 2 - on
the parameters tab for the dataset, I have the ? representing metric_code
tied to Parameters!metric_code. On the report parameters tab, I have the
parameter metric_code tied to Dataset 1 for it's available values. So I
believe that I am running the query for Dataset 2 with whatever has been
supplied as the choice from Dataset 1. I put the parameter back in the WHERE
clause for the Dataset 2 query and I didnt get the error I was getting before
... about too many parameters for Dataset 2 ... so I hope that I have this
correct now.
"Bruce L-C [MVP]" wrote:
> Is the user supposed to pick the metric? If so, then the first dataset is
> being used as the source for the first parameter. Then everything else I
> said holds true. If not, then you should do remove dataset 1 and change the
> second one to this:
> select ... where ... metric_code in (select distinct metric_code from ...)
> Note that you only want one field being returned if you are using an IN
> clause.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:2254419F-D76F-4161-BA4D-FD6FFE7316B0@.microsoft.com...
> > Bruce,
> > I'm not sure if I was clear in my post. The first dataset does not have
> > a
> > parameter but the selection from that result set needs to BE the parameter
> > for the second dataset
> >
> > dataset 1:
> > select distinct metric_code, metric_name
> > from grsinst1.metric met
> > join grsinst1.availability_def availdef
> > on availdef.derived_metric_code = met.metric_code with ur
> >
> > dataset 2:
> >
> > SELECT res_name, start_lcl_dt, res_grp_name, res.res_type, value AS
> > Percent, value_cnt, metric_name, client_name,
> > coalesce(thresh.threshd_target,
> > grpthresh.threshd_target) AS threshd_target,
> > coalesce(thresh.threshd_warning, grpthresh.threshd_warning) AS
> > threshd_warning,
> > coalesce(thresh.threshd_critical,
> > grpthresh.threshd_critical) AS threshd_critical, MAX(start_lcl_dt) OVER()
> > AS
> > MaxDate,
> > CASE WHEN start_lcl_dt = MAX(start_lcl_dt) OVER()
> > THEN(((100.0 - VALUE) / 100) * VALUE_CNT) / 60 ELSE 0 END AS DTMin
> > FROM grsinst1.availability_monthly_vw am JOIN
> > grsinst1.metric met ON met.metric_code => > am.metric_code JOIN
> > grsinst1.restree_resgrp_client_vw res ON res.res_id => > am.res_id AND res.res_grp_id = am.res_grp_id LEFT JOIN
> > grsinst1.threshold thresh ON thresh.res_code => > res.res_code AND thresh.metric_id = met.metric_id AND thresh.res_type => > res.res_type JOIN
> > grsinst1.grp_threshold_vw grpthresh ON
> > grpthresh.res_grp_id = res.res_grp_id AND grpthresh.metric_id => > met.metric_id
> > WHERE res.client_code LIKE ? AND DATE (start_lcl_dt) >= ? AND DATE
> > (start_lcl_dt) <= ? AND res.res_code LIKE ? AND group_path LIKE ? AND
> > res.res_type = ?
> > GROUP BY res_name, start_lcl_dt, res_grp_name, res.res_type, value,
> > value_cnt, metric_name, client_name, coalesce(thresh.threshd_target,
> > grpthresh.threshd_target),
> > coalesce(thresh.threshd_warning, grpthresh.threshd_warning),
> > coalesce(thresh.threshd_critical, grpthresh.threshd_critical)
> > with ur
> >
> >
> > Dataset 2 used to have metric_code in the WHERE clause - however ... I
> > needed to make metric code dynamic so I took it out and put it in its own
> > dataset. If I have in the where clause "where metric_code = ? ... and
> > "
> > then I get an error saying there are too many parameters for Dataset 2.
> > I
> > have metric_code (the parameter) mapped to the query for dataset 1 on the
> > report parameters tab. What am I missing from what you said?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> In the dataset click on the ... go to the parameters tab. Map the query
> >> parameter to the parameter that the cascading parameter is based on.
> >>
> >> In layout tab go to Report Menu -> Report Parameters. Make sure the
> >> parameter the second dataset is dependent on is before the depenent
> >> parameter.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:F796C0FB-6834-4D06-A875-003809DA36F9@.microsoft.com...
> >> >I am using DB2 ... how do I tie the parameter from one dataset to
> >> >another.
> >> >I
> >> > have a query for dataset 1 based on metrics. The metric they choose
> >> > needs
> >> > to be a parameter in the second dataset query I have. I have created a
> >> > parameter for the metric and have it tied to a query (in the report
> >> > parameters window) ... but if I put metric (which of course has to be a
> >> > ?
> >> > for
> >> > unnamed parameters) as a parameter in the second dataset then I get an
> >> > error.
> >> > How does reporting services know that the metric is a parameter to the
> >> > second dataset?
> >>
> >>
> >>
>
>|||I guess what confused me about what you wrote is this "make sure the
parameter the second dataset is dependent on is before the dependent
parameter" ? there is only one parameter shared between the 2 datasets and
it is the metric_code. The first dataset does not have a parameter ... it is
just a query to supply values FOR a parameter (metric_code) . The second
dataset has a parameter called metric_code which is a *value* chosen from the
first dataset. Therefore ... I have metric_code as the first parameter in
the list of parameters on the report parameter window ... that is followed by
client, start_date, end_date ... etc etc which are all parameters for the
second datatset. So if you meant to say "make sure the parameter the second
dataset is dependent on appears at the top of the parameter list" then that
makes sense. If you are implying that I somehow need 2 parameters for
metric_code then I guess I dont understand. Thanks, Bruce for helping with
this.
"Bruce L-C [MVP]" wrote:
> In the dataset click on the ... go to the parameters tab. Map the query
> parameter to the parameter that the cascading parameter is based on.
> In layout tab go to Report Menu -> Report Parameters. Make sure the
> parameter the second dataset is dependent on is before the depenent
> parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:F796C0FB-6834-4D06-A875-003809DA36F9@.microsoft.com...
> >I am using DB2 ... how do I tie the parameter from one dataset to another.
> >I
> > have a query for dataset 1 based on metrics. The metric they choose
> > needs
> > to be a parameter in the second dataset query I have. I have created a
> > parameter for the metric and have it tied to a query (in the report
> > parameters window) ... but if I put metric (which of course has to be a ?
> > for
> > unnamed parameters) as a parameter in the second dataset then I get an
> > error.
> > How does reporting services know that the metric is a parameter to the
> > second dataset?
>
>

cascading parameter help - solution

I a report that will have two parameters (Year and Account name), I'm having trouble setting up the second parameter to be filtered based on the value of the first parameter.

here is the second parameter with a value hard coded.(see bold text) this works fine when I run the report.

WITH MEMBER [Measures].[ParameterCaption] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,FILTER([CUSTOMER JOB].[ACCOUNT NAME].ALLMEMBERS,([Job Complete Date].[Year].&[2006],[Measures].[Billed Sales Amount])) ON ROWS FROM [DW PREP ARCHIVE]

When I try the following code where I have added in the equal sign and double quotes I get an error message (see below)

= "WITH MEMBER [Measures].[ParameterCaption] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,FILTER([CUSTOMER JOB].[ACCOUNT NAME].ALLMEMBERS,(" & Parameter!JobCompleteDateYear.value & ",[Measures].[Billed Sales Amount])) ON ROWS FROM [DW PREP ARCHIVE]"

error message

TITLE: Microsoft Visual Studio

The query cannot be retrieved from the query builder.
Check the query for syntax errors.
Reporting Services will continue to use the most recent valid query.

ADDITIONAL INFORMATION:

Query preparation failed. (Microsoft.AnalysisServices.Controls)

Query (1, 1) Parser: The syntax for '=' is incorrect. (msmgdsrv)

BUTTONS:

Any suggestions ?

thanks

I found my solution. I needed to use the Strtomember function and inserted the parameter name as @.parametername

here is the final code

before

after

WITH MEMBER [Measures].[ParameterCaption] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,FILTER([CUSTOMER JOB].[ACCOUNT NAME].ALLMEMBERS,(Strtomember(@.JobCompleteDateYear),[Measures].[Billed Sales Amount])) ON ROWS FROM [DW PREP ARCHIVE]

cascading parameter help

I a report that will have two parameters (Year and Account name), I'm having trouble setting up the second parameter to be filtered based on the value of the first parameter.

here is the second parameter with a value hard coded.(see bold text) this works fine when I run the report.

When I try the following code where I have added in the equal sign and double quotes I get an error message (see below)

error message

TITLE: Microsoft Visual Studio

The query cannot be retrieved from the query builder.
Check the query for syntax errors.
Reporting Services will continue to use the most recent valid query.

ADDITIONAL INFORMATION:

Query preparation failed. (Microsoft.AnalysisServices.Controls)

Query (1, 1) Parser: The syntax for '=' is incorrect. (msmgdsrv)

BUTTONS:

Any suggestions ?

thanks

I found my solution. I needed to use the Strtomember function and inserted the parameter name as @.parametername

here is the final code

before

after

WITH MEMBER [Measures].[ParameterCaption] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,FILTER([CUSTOMER JOB].[ACCOUNT NAME].ALLMEMBERS,(Strtomember(@.JobCompleteDateYear),[Measures].[Billed Sales Amount])) ON ROWS FROM [DW PREP ARCHIVE]

Wednesday, March 7, 2012

Carry Over Hours Monthend

I was wondering if anyone knew if this was possible in T-SQL. I need to
calculate the total number of hours carried over from a previous month,
based on a 6 hours per day rate. Some examples,
Job has 12 hours total and starts on 2/28/2005 it would carry over 6 hours
into March.
Job has 5 hours total and starts on 2/28/2005 it would carry over 0 hours in
March.
Job has 30 hours total and starts on 2/25/2005 it would carry over 18 hours
(30-12).
Note that I have to ignore w

ends as they are not work days. Thanks.
Davidhttp://www.aspfaq.com/etiquette.asp?id=5006
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:%23TzCw%23ZHFHA.1096@.tk2msftngp13.phx.gbl...
>I was wondering if anyone knew if this was possible in T-SQL. I need to
>calculate the total number of hours carried over from a previous month,
>based on a 6 hours per day rate. Some examples,
> Job has 12 hours total and starts on 2/28/2005 it would carry over 6 hours
> into March.
> Job has 5 hours total and starts on 2/28/2005 it would carry over 0 hours
> in March.
> Job has 30 hours total and starts on 2/25/2005 it would carry over 18
> hours (30-12).
> Note that I have to ignore w

ends as they are not work days. Thanks.
> David
>|||Roji,
Your link took me to a DDL example. Are you sure that is correct?
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||David,
What Roji is trying to tell you is to post some DDL, sample data and
expected result if you need help with this problem.
AMB
"David" wrote:

> Roji,
> Your link took me to a DDL example. Are you sure that is correct?
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>|||Below is what I have done so far:
SELECT dbo.RepairOrder.RecordID, dbo.vw_LaborTotalsAll.TotalHours,
dbo.RepairOrder.RepairStartDate,
dbo.RepairOrder.RepairCompleteDate
FROM dbo.RepairOrder INNER JOIN
dbo.vw_LaborTotalsAll ON dbo.RepairOrder.RecordID
= dbo.vw_LaborTotalsAll.RecordID
WHERE (dbo.RepairOrder.RepairStartDate > CONVERT(DATETIME,
'2005-02-01 00:00:00', 102)) AND (dbo.RepairOrder.RepairCompleteDate IS
NULL) AND
(dbo.RepairOrder.RepairStartDate <
CONVERT(DATETIME, '2005-03-01 00:00:00', 102))
I assume I need something else in WHERE clause as I only need to get
back records that have more hours left than 6 x the number of work days
left in the month.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Saturday, February 25, 2012

Capturing SQL code that generates error

I have an application that imports application data based on the Windows Installer .MSI schema into a SQL database. If it possible that the data in the tables exceeds the size allowed in our database. When that happens, an error message is generated. This
is fine as the user has the option to 'Ignore.'
The problem is that I cannot tell what application is being imported when the error message is produced. Is there a method to capturing the 'parent' SQL insert statements so that I can get the application name?
Thanks
Have you considered using Profiler? Let us know if you need help with using
Profiler to capture the statement and the error.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DanetteB" <DanetteB@.discussions.microsoft.com> wrote in message
news:E5E14CFC-8F1C-49F5-AEE5-40C14448FC86@.microsoft.com...
I have an application that imports application data based on the Windows
Installer .MSI schema into a SQL database. If it possible that the data in
the tables exceeds the size allowed in our database. When that happens, an
error message is generated. This is fine as the user has the option to
'Ignore.'
The problem is that I cannot tell what application is being imported when
the error message is produced. Is there a method to capturing the 'parent'
SQL insert statements so that I can get the application name?
Thanks
|||Vyas -
Thanks for the reply. I have tried the Profiler, but I am not getting the data I need. I'm probably not capturing the correct information. Any suggestions would be much appreciated.
"Narayana Vyas Kondreddi" wrote:

> Have you considered using Profiler? Let us know if you need help with using
> Profiler to capture the statement and the error.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
|||Vyas -
I got it - thank you for the Profiler suggestion. I went back into the tool and added the TSQL events.
DanetteB
"DanetteB" wrote:

> Vyas -
> Thanks for the reply. I have tried the Profiler, but I am not getting the data I need. I'm probably not capturing the correct information. Any suggestions would be much appreciated.
>
> "Narayana Vyas Kondreddi" wrote:
>

Capturing SQL code that generates error

I have an application that imports application data based on the Windows Ins
taller .MSI schema into a SQL database. If it possible that the data in the
tables exceeds the size allowed in our database. When that happens, an error
message is generated. This
is fine as the user has the option to 'Ignore.'
The problem is that I cannot tell what application is being imported when th
e error message is produced. Is there a method to capturing the 'parent' SQL
insert statements so that I can get the application name?
ThanksHave you considered using Profiler? Let us know if you need help with using
Profiler to capture the statement and the error.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DanetteB" <DanetteB@.discussions.microsoft.com> wrote in message
news:E5E14CFC-8F1C-49F5-AEE5-40C14448FC86@.microsoft.com...
I have an application that imports application data based on the Windows
Installer .MSI schema into a SQL database. If it possible that the data in
the tables exceeds the size allowed in our database. When that happens, an
error message is generated. This is fine as the user has the option to
'Ignore.'
The problem is that I cannot tell what application is being imported when
the error message is produced. Is there a method to capturing the 'parent'
SQL insert statements so that I can get the application name?
Thanks|||Vyas -
Thanks for the reply. I have tried the Profiler, but I am not getting the da
ta I need. I'm probably not capturing the correct information. Any suggestio
ns would be much appreciated.
"Narayana Vyas Kondreddi" wrote:

> Have you considered using Profiler? Let us know if you need help with usin
g
> Profiler to capture the statement and the error.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>|||Vyas -
I got it - thank you for the Profiler suggestion. I went back into the tool
and added the TSQL events.
DanetteB
"DanetteB" wrote:

> Vyas -
> Thanks for the reply. I have tried the Profiler, but I am not getting the
data I need. I'm probably not capturing the correct information. Any suggest
ions would be much appreciated.
>
> "Narayana Vyas Kondreddi" wrote:
>
>

Thursday, February 16, 2012

Capacity Planning

Dear All
My company has asked me to come up with the amount of
space a new database will use based upon X number of
records in tables.
Is there some sort of recognised matrix I can follow, or
will I have to wing it based upon my own interpretation of
the tables and relationships ?
Thanks
PeterThis information is in SQL Server 2000 Books Online. Look up the chapter:
"Estimating the size of a database"
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:f97601c3f222$4697f700$a001280a@.phx.gbl...
Dear All
My company has asked me to come up with the amount of
space a new database will use based upon X number of
records in tables.
Is there some sort of recognised matrix I can follow, or
will I have to wing it based upon my own interpretation of
the tables and relationships ?
Thanks
Peter|||Thank you
Peter
>--Original Message--
>This information is in SQL Server 2000 Books Online. Look
up the chapter:
>"Estimating the size of a database"
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f97601c3f222$4697f700$a001280a@.phx.gbl...
>Dear All
>My company has asked me to come up with the amount of
>space a new database will use based upon X number of
>records in tables.
>Is there some sort of recognised matrix I can follow, or
>will I have to wing it based upon my own interpretation of
>the tables and relationships ?
>Thanks
>Peter
>
>.
>

Capacity Planning

Dear All
My company has asked me to come up with the amount of
space a new database will use based upon X number of
records in tables.
Is there some sort of recognised matrix I can follow, or
will I have to wing it based upon my own interpretation of
the tables and relationships ?
Thanks
PeterThis information is in SQL Server 2000 Books Online. Look up the chapter:
"Estimating the size of a database"
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:f97601c3f222$4697f700$a001280a@.phx.gbl...
Dear All
My company has asked me to come up with the amount of
space a new database will use based upon X number of
records in tables.
Is there some sort of recognised matrix I can follow, or
will I have to wing it based upon my own interpretation of
the tables and relationships ?
Thanks
Peter|||Thank you
Peter

>--Original Message--
>This information is in SQL Server 2000 Books Online. Look
up the chapter:
>"Estimating the size of a database"
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f97601c3f222$4697f700$a001280a@.phx.gbl...
>Dear All
>My company has asked me to come up with the amount of
>space a new database will use based upon X number of
>records in tables.
>Is there some sort of recognised matrix I can follow, or
>will I have to wing it based upon my own interpretation of
>the tables and relationships ?
>Thanks
>Peter
>
>.
>

Tuesday, March 27, 2012

CASE Statement - I'm in a bit of a quandry

Thursday, March 22, 2012

CASE problems with select statement.

CASE problems with select statement.

Tuesday, March 20, 2012

Case function in T-SQL

Case Expression within a Stored Proc

Monday, March 19, 2012

CASE and DATE statement - Need Help :)

Cascading(?) Parameters Help

Cascading Parameters based off Analysis Services

Sunday, March 11, 2012

Cascading Parameters

Cascading Parameters

cascading parameter help - solution

cascading parameter help

Wednesday, March 7, 2012

Carry Over Hours Monthend

Saturday, February 25, 2012

Capturing SQL code that generates error

Capturing SQL code that generates error

Thursday, February 16, 2012

Capacity Planning

Capacity Planning

uninstall SQL

Blog Archive

About Me