What I'm trying to do is to build a string that will print all the presences of a user for a session. My problem is that I'd like to put the - value when the pr_presence isn't True or False but right now it only returns pr_presence when it contains a boolean value. This way I can't treat the NULL or blank value. Any help would be really appreciate !
Here is my fonction:
DECLARE strPresence VARCHAR;
sessionID alias for $1;
userID alias for $2;
idr record;
BEGIN
strPresence := '';
For idr in
SELECT CASE When pr_presence = 't' Then 'P'
When pr_presence = 'f' Then 'A'
Else '-'
End as "TypePresence"
FROM seance, InscriptionEtat, inscriptionSession
RIGHT JOIN presence ON inscriptionSession.usr_id = presence.usr_id
LEFT JOIN session ON inscriptionSession.ses_id = session.ses_id
WHERE session.ses_id = sessionID
AND presence.usr_id = userID
AND presence.sea_id = seance.sea_id
AND seance.ses_id = session.ses_id
AND seance.sea_valide = 't'
AND inscriptionSession.usr_id = usager.usr_id
AND inscriptionSession.ie_id = inscriptionEtat.ie_id
AND inscriptionEtat.ie_OK = 't'
ORDER BY seance.sea_datedebut
LOOP
strPresence:= strPresence||', '||idr."TypePresence";
END LOOP;
strPresence:= substring(strPresence,char_length(', ')+1);
RETURN strPresence;
END;I don't quite understand your problem. The CASE statement works OK:
SQL> SELECT pr_presence, CASE When pr_presence = 't' Then 'P'
2 When pr_presence = 'f' Then 'A'
3 Else '-'
4 End as "TypePresence"
5 FROM seance;
P T
- -
t P
f A
-
x -|||Originally posted by andrewst
I don't quite understand your problem. The CASE statement works OK:
SQL> SELECT pr_presence, CASE When pr_presence = 't' Then 'P'
2 When pr_presence = 'f' Then 'A'
3 Else '-'
4 End as "TypePresence"
5 FROM seance;
P T
- -
t P
f A
-
x -
If my session contains 4 seances, and the user only enters 1 presence for these seances, my string should looks like "P,-,-,-" because the other 3 pr_presence would be Null
Right now, my string is returning "P" when I test it... I also tought my case was ok but I'm now wondering why I don't get what I want. Thanks for your help
Showing posts with label function. Show all posts
Showing posts with label function. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Help with SQL Function
Hello All:
From the Following Table, I want to enter a Temperature and then have the
SQL Function Return the Web Color
-- TemperatureIndex --
ID TempMin TempMax WebColor
1 0 9 #E59DCB
2 10 19 #8569FA
3 20 29 #3F9CFB
4 30 39 #73E96F
I would like to Enter a Temperature and return the following WebColor output
15 -> #E59DCB
28 -> #3F9CFB
32 -> #73E96FCREATE TABLE TemperatureIndex
(
ID int NOT NULL,
TempMin int NOT NULL,
TempMax int NOT NULL,
WebColor char(7) NOT NULL
)
GO
INSERT INTO TemperatureIndex VALUES(1, 0, 9, '#E59DCB')
INSERT INTO TemperatureIndex VALUES(2, 10, 19, '#8569FA')
INSERT INTO TemperatureIndex VALUES(3, 20, 29, '#3F9CFB')
INSERT INTO TemperatureIndex VALUES(4, 30, 39, '#73E96F')
GO
CREATE UNIQUE CLUSTERED INDEX TemperatureIndex_cdx
ON TemperatureIndex(TempMin, TempMax)
GO
ALTER TABLE TemperatureIndex
ADD CONSTRAINT PK_TemperatureIndex
PRIMARY KEY NONCLUSTERED (ID)
GO
CREATE FUNCTION dbo.GetWebColorForTemperature(@.Temp int)
RETURNS char(7)
AS
BEGIN
RETURN (SELECT WebColor
FROM TemperatureIndex
WHERE @.Temp BETWEEN TempMin AND TempMax
)
END
GO
SELECT dbo.GetWebColorForTemperature(15)
SELECT dbo.GetWebColorForTemperature(28)
SELECT dbo.GetWebColorForTemperature(32)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Stuart Shay" <sshay@.yahoo.com> wrote in message
news:uzg9rvG%23FHA.160@.TK2MSFTNGP12.phx.gbl...
> Hello All:
> From the Following Table, I want to enter a Temperature and then have the
> SQL Function Return the Web Color
> -- TemperatureIndex --
> ID TempMin TempMax WebColor
> 1 0 9 #E59DCB
> 2 10 19 #8569FA
> 3 20 29 #3F9CFB
> 4 30 39 #73E96F
> I would like to Enter a Temperature and return the following WebColor
> output
> 15 -> #E59DCB
> 28 -> #3F9CFB
> 32 -> #73E96F
>|||Dan:
Thanks for your help !!!
Best
Stuart
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uev6wbH%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> CREATE TABLE TemperatureIndex
> (
> ID int NOT NULL,
> TempMin int NOT NULL,
> TempMax int NOT NULL,
> WebColor char(7) NOT NULL
> )
> GO
> INSERT INTO TemperatureIndex VALUES(1, 0, 9, '#E59DCB')
> INSERT INTO TemperatureIndex VALUES(2, 10, 19, '#8569FA')
> INSERT INTO TemperatureIndex VALUES(3, 20, 29, '#3F9CFB')
> INSERT INTO TemperatureIndex VALUES(4, 30, 39, '#73E96F')
> GO
> CREATE UNIQUE CLUSTERED INDEX TemperatureIndex_cdx
> ON TemperatureIndex(TempMin, TempMax)
> GO
> ALTER TABLE TemperatureIndex
> ADD CONSTRAINT PK_TemperatureIndex
> PRIMARY KEY NONCLUSTERED (ID)
> GO
> CREATE FUNCTION dbo.GetWebColorForTemperature(@.Temp int)
> RETURNS char(7)
> AS
> BEGIN
> RETURN (SELECT WebColor
> FROM TemperatureIndex
> WHERE @.Temp BETWEEN TempMin AND TempMax
> )
> END
> GO
> SELECT dbo.GetWebColorForTemperature(15)
> SELECT dbo.GetWebColorForTemperature(28)
> SELECT dbo.GetWebColorForTemperature(32)
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Stuart Shay" <sshay@.yahoo.com> wrote in message
> news:uzg9rvG%23FHA.160@.TK2MSFTNGP12.phx.gbl...
>
From the Following Table, I want to enter a Temperature and then have the
SQL Function Return the Web Color
-- TemperatureIndex --
ID TempMin TempMax WebColor
1 0 9 #E59DCB
2 10 19 #8569FA
3 20 29 #3F9CFB
4 30 39 #73E96F
I would like to Enter a Temperature and return the following WebColor output
15 -> #E59DCB
28 -> #3F9CFB
32 -> #73E96FCREATE TABLE TemperatureIndex
(
ID int NOT NULL,
TempMin int NOT NULL,
TempMax int NOT NULL,
WebColor char(7) NOT NULL
)
GO
INSERT INTO TemperatureIndex VALUES(1, 0, 9, '#E59DCB')
INSERT INTO TemperatureIndex VALUES(2, 10, 19, '#8569FA')
INSERT INTO TemperatureIndex VALUES(3, 20, 29, '#3F9CFB')
INSERT INTO TemperatureIndex VALUES(4, 30, 39, '#73E96F')
GO
CREATE UNIQUE CLUSTERED INDEX TemperatureIndex_cdx
ON TemperatureIndex(TempMin, TempMax)
GO
ALTER TABLE TemperatureIndex
ADD CONSTRAINT PK_TemperatureIndex
PRIMARY KEY NONCLUSTERED (ID)
GO
CREATE FUNCTION dbo.GetWebColorForTemperature(@.Temp int)
RETURNS char(7)
AS
BEGIN
RETURN (SELECT WebColor
FROM TemperatureIndex
WHERE @.Temp BETWEEN TempMin AND TempMax
)
END
GO
SELECT dbo.GetWebColorForTemperature(15)
SELECT dbo.GetWebColorForTemperature(28)
SELECT dbo.GetWebColorForTemperature(32)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Stuart Shay" <sshay@.yahoo.com> wrote in message
news:uzg9rvG%23FHA.160@.TK2MSFTNGP12.phx.gbl...
> Hello All:
> From the Following Table, I want to enter a Temperature and then have the
> SQL Function Return the Web Color
> -- TemperatureIndex --
> ID TempMin TempMax WebColor
> 1 0 9 #E59DCB
> 2 10 19 #8569FA
> 3 20 29 #3F9CFB
> 4 30 39 #73E96F
> I would like to Enter a Temperature and return the following WebColor
> output
> 15 -> #E59DCB
> 28 -> #3F9CFB
> 32 -> #73E96F
>|||Dan:
Thanks for your help !!!
Best
Stuart
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uev6wbH%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> CREATE TABLE TemperatureIndex
> (
> ID int NOT NULL,
> TempMin int NOT NULL,
> TempMax int NOT NULL,
> WebColor char(7) NOT NULL
> )
> GO
> INSERT INTO TemperatureIndex VALUES(1, 0, 9, '#E59DCB')
> INSERT INTO TemperatureIndex VALUES(2, 10, 19, '#8569FA')
> INSERT INTO TemperatureIndex VALUES(3, 20, 29, '#3F9CFB')
> INSERT INTO TemperatureIndex VALUES(4, 30, 39, '#73E96F')
> GO
> CREATE UNIQUE CLUSTERED INDEX TemperatureIndex_cdx
> ON TemperatureIndex(TempMin, TempMax)
> GO
> ALTER TABLE TemperatureIndex
> ADD CONSTRAINT PK_TemperatureIndex
> PRIMARY KEY NONCLUSTERED (ID)
> GO
> CREATE FUNCTION dbo.GetWebColorForTemperature(@.Temp int)
> RETURNS char(7)
> AS
> BEGIN
> RETURN (SELECT WebColor
> FROM TemperatureIndex
> WHERE @.Temp BETWEEN TempMin AND TempMax
> )
> END
> GO
> SELECT dbo.GetWebColorForTemperature(15)
> SELECT dbo.GetWebColorForTemperature(28)
> SELECT dbo.GetWebColorForTemperature(32)
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Stuart Shay" <sshay@.yahoo.com> wrote in message
> news:uzg9rvG%23FHA.160@.TK2MSFTNGP12.phx.gbl...
>
HELP with SQL Date Function
Is there a Function that would just give me the DATE and not the DATE & TIME? I am trying to group by VRUServiceDate but since this is a smalldatetime data type, the grouping does not work by date because the time shows.
Thank you.
SELECT
VRUServiceDate AS [Service Date],
EmployeeID AS [Rep ID],
SUM(VRUDriveMiles) AS [Miles Traveled]
FROM WorkOrder
WHERE TagToPay=1 AND Paid=0 and vrudrivemiles <> 0
GROUP BY VRUServiceDate,EmployeeID
ORDER BY EmployeeID,VRUServiceDatei would use the CONVERT function --
SELECT
CONVERT(CHAR(10),VRUServiceDate,126) AS [Service Date],
EmployeeID AS [Rep ID],
SUM(VRUDriveMiles) AS [Miles Traveled]
FROM WorkOrder
WHERE TagToPay=1 AND Paid=0 and vrudrivemiles <> 0
GROUP BY CONVERT(CHAR(10),VRUServiceDate,126),EmployeeID
ORDER BY EmployeeID,CONVERT(CHAR(10),VRUServiceDate,126)
if the expression does not work in the ORDER BY, use the ordinal form instead -- ORDER BY 2,1
rudy
http://r937.com/|||It worked great but I had to change 126 to 101. Thanks!!
Thank you.
SELECT
VRUServiceDate AS [Service Date],
EmployeeID AS [Rep ID],
SUM(VRUDriveMiles) AS [Miles Traveled]
FROM WorkOrder
WHERE TagToPay=1 AND Paid=0 and vrudrivemiles <> 0
GROUP BY VRUServiceDate,EmployeeID
ORDER BY EmployeeID,VRUServiceDatei would use the CONVERT function --
SELECT
CONVERT(CHAR(10),VRUServiceDate,126) AS [Service Date],
EmployeeID AS [Rep ID],
SUM(VRUDriveMiles) AS [Miles Traveled]
FROM WorkOrder
WHERE TagToPay=1 AND Paid=0 and vrudrivemiles <> 0
GROUP BY CONVERT(CHAR(10),VRUServiceDate,126),EmployeeID
ORDER BY EmployeeID,CONVERT(CHAR(10),VRUServiceDate,126)
if the expression does not work in the ORDER BY, use the ordinal form instead -- ORDER BY 2,1
rudy
http://r937.com/|||It worked great but I had to change 126 to 101. Thanks!!
Monday, March 26, 2012
Help with SP or Function
I was trying to write this MDX but I am getting errors so maybe I should
create a stored procedure against my CUBE.
I want to return two values : Revenue Avg and Product Price Avg by a
specific time and Sales Region.
can someone provide an example?
My cube provides the total revenue for any date. I thought I could take the
avg(Sales Region.members,Time.members, total revenue amount) AS 'Revenue Avg
'
avg(Sales Region.members, Time.members, Product price) AS 'Product Price Avg
'
and get a value but I am getting an error message.What error message? Where?
ML
http://milambda.blogspot.com/
create a stored procedure against my CUBE.
I want to return two values : Revenue Avg and Product Price Avg by a
specific time and Sales Region.
can someone provide an example?
My cube provides the total revenue for any date. I thought I could take the
avg(Sales Region.members,Time.members, total revenue amount) AS 'Revenue Avg
'
avg(Sales Region.members, Time.members, Product price) AS 'Product Price Avg
'
and get a value but I am getting an error message.What error message? Where?
ML
http://milambda.blogspot.com/
Wednesday, March 21, 2012
Help with Round( ) function.
Hi,
I am using the round function since i want two decimal places and it doesnt
give me the correct results.
The field in which i apply it is a Real with Auto number of decimal places.
In my querries I have something like:
value1 = 2.3443 and Round(value1,2) = 2.3443 *instead of* 2.34
The funny thing is that I tried it on a copy of the database on Access XP
and I get the *same* result !!!!...
Is it because I have a fench copy of XP? I have changed my regional
settings
to English.
PLEASE HELP!
TIA
-steveTry,
declare @.value1 real
set @.value1 = 2.3443
select @.value1, round(@.value1,2), cast(round(@.value1,2) as numeric(8, 2))
AMB
"Steve" wrote:
> Hi,
> I am using the round function since i want two decimal places and it doesn
t
> give me the correct results.
> The field in which i apply it is a Real with Auto number of decimal places
.
> In my querries I have something like:
> value1 = 2.3443 and Round(value1,2) = 2.3443 *instead of* 2.34
> The funny thing is that I tried it on a copy of the database on Access XP
> and I get the *same* result !!!!...
> Is it because I have a fench copy of XP? I have changed my regional
> settings
> to English.
> PLEASE HELP!
> TIA
> -steve
>
>|||Regional settings should have nothing to do with this. I do not
think the ROUND function is giving you an incorrect answer, but
in order to help you out, you will have to post the actual statements
or a reproducible example of where you get these results. "Auto
number of decimal places" is not part of any SQL Server type, so
I'm not even sure you are using Microsoft SQL Server. If not, see
if there is a newsgroup for whatever database you are using.
Steve Kass
Drew University
Steve wrote:
>Hi,
>I am using the round function since i want two decimal places and it doesnt
>give me the correct results.
>The field in which i apply it is a Real with Auto number of decimal places.
>In my querries I have something like:
>value1 = 2.3443 and Round(value1,2) = 2.3443 *instead of* 2.34
>The funny thing is that I tried it on a copy of the database on Access XP
>and I get the *same* result !!!!...
>Is it because I have a fench copy of XP? I have changed my regional
>settings
>to English.
>PLEASE HELP!
>TIA
>-steve
>
>
>|||The problem is that you don't understand the round function, nor do you
understand numeric datatypes. Don't feel bad, they are tricky subjects.
declare @.value1 real
set @.value1 = 2.3443
select round(@.value1, 1,2)
returns:
2.2999999999999998
What you want to do is:
select round(@.value1,2) --since you don't want to truncate. This returns:
2.3399999999999999
Which in real datatype terms, is 2.34
If your goal is to get it into a precise datatype, do something like:
select cast( round(@.value1,2) as decimal(5,2))
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Steve" <try@.this.com> wrote in message
news:WWg_d.5424$sC5.53547@.weber.videotron.net...
> Hi,
> I am using the round function since i want two decimal places and it
> doesnt
> give me the correct results.
> The field in which i apply it is a Real with Auto number of decimal
> places.
> In my querries I have something like:
> value1 = 2.3443 and Round(value1,2) = 2.3443 *instead of* 2.34
> The funny thing is that I tried it on a copy of the database on Access XP
> and I get the *same* result !!!!...
> Is it because I have a fench copy of XP? I have changed my regional
> settings
> to English.
> PLEASE HELP!
> TIA
> -steve
>
>|||Steve,
WHy are you trying to round() ?
Just for display purposes ? Or do you actually want to store only two
decimal places in the database ?
If the former, Then let your UI Code control the display... SQL isn;t the
place for that..
If the Latter, just change the datatype of the column in the database to
Decimal 6,2)
(NOTE: If this Store Proc or SQL Is customized specifically for some
report, and you CAN't control the formatting in the reporting tool, then in
that case Convert the output from the real column to text
Use the STR() Function for this... Look it up in BOL
Declare @.V Real Set @.V = 2.3443
Select STR(@.V, 5,2)
"Steve" wrote:
> Hi,
> I am using the round function since i want two decimal places and it doesn
t
> give me the correct results.
> The field in which i apply it is a Real with Auto number of decimal places
.
> In my querries I have something like:
> value1 = 2.3443 and Round(value1,2) = 2.3443 *instead of* 2.34
> The funny thing is that I tried it on a copy of the database on Access XP
> and I get the *same* result !!!!...
> Is it because I have a fench copy of XP? I have changed my regional
> settings
> to English.
> PLEASE HELP!
> TIA
> -steve
>
>|||Thanx! That seems to work ( in SQL SERVER not Access, I know wrong nsgroup
but my application should target both)
I am VERY surprised. So, in SQL server, despite the fact that i have
declared (imported from Access) my val1 as real, number, whatever....
There is NO single function to output two decimal places?....
I do not want to loose precision, although not too important, i just want
to save space when i *display* the result. I want to round-off to two
decimal places.
I actually have to show 24 values ....
Thanx again!
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> a crit dans le message de
news: %23jDg$nxKFHA.2136@.TK2MSFTNGP14.phx.gbl...
> The problem is that you don't understand the round function, nor do you
> understand numeric datatypes. Don't feel bad, they are tricky subjects.
> declare @.value1 real
> set @.value1 = 2.3443
> select round(@.value1, 1,2)
> returns:
> 2.2999999999999998
> What you want to do is:
>
> select round(@.value1,2) --since you don't want to truncate. This returns:
> 2.3399999999999999
> Which in real datatype terms, is 2.34
> If your goal is to get it into a precise datatype, do something like:
> select cast( round(@.value1,2) as decimal(5,2))
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Steve" <try@.this.com> wrote in message
> news:WWg_d.5424$sC5.53547@.weber.videotron.net...
>|||On Thu, 17 Mar 2005 13:26:41 -0500, Steve wrote:
>I am VERY surprised. So, in SQL server, despite the fact that i have
>declared (imported from Access) my val1 as real, number, whatever....
>There is NO single function to output two decimal places?....
Hi Steve,
Yes, there is: CAST.
DECLARE @.Test float
SET @.Test = 12345.6789/876.54321
SELECT @.Test, CAST(@.Test AS NUMERIC(4,2))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I kind of forgot, if it is a formatting thing, you can use STR() to format
it.
declare @.value1 real
set @.value1 = 2.3443
select str(@.value1, 4,2)
Returns:
2.34
I was all caught up in the mathy angle. I would consider using the front
end to change the display, since numbers transmitted in a numeric form are
often smaller than the characters that make them up.
Sorry :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Steve" <try@.this.com> wrote in message
news:6_j_d.6385$sC5.100192@.weber.videotron.net...
> Thanx! That seems to work ( in SQL SERVER not Access, I know wrong nsgroup
> but my application should target both)
> I am VERY surprised. So, in SQL server, despite the fact that i have
> declared (imported from Access) my val1 as real, number, whatever....
> There is NO single function to output two decimal places?....
> I do not want to loose precision, although not too important, i just want
> to save space when i *display* the result. I want to round-off to two
> decimal places.
> I actually have to show 24 values ....
> Thanx again!
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> a crit dans le message
> de news: %23jDg$nxKFHA.2136@.TK2MSFTNGP14.phx.gbl...
>
I am using the round function since i want two decimal places and it doesnt
give me the correct results.
The field in which i apply it is a Real with Auto number of decimal places.
In my querries I have something like:
value1 = 2.3443 and Round(value1,2) = 2.3443 *instead of* 2.34
The funny thing is that I tried it on a copy of the database on Access XP
and I get the *same* result !!!!...
Is it because I have a fench copy of XP? I have changed my regional
settings
to English.
PLEASE HELP!
TIA
-steveTry,
declare @.value1 real
set @.value1 = 2.3443
select @.value1, round(@.value1,2), cast(round(@.value1,2) as numeric(8, 2))
AMB
"Steve" wrote:
> Hi,
> I am using the round function since i want two decimal places and it doesn
t
> give me the correct results.
> The field in which i apply it is a Real with Auto number of decimal places
.
> In my querries I have something like:
> value1 = 2.3443 and Round(value1,2) = 2.3443 *instead of* 2.34
> The funny thing is that I tried it on a copy of the database on Access XP
> and I get the *same* result !!!!...
> Is it because I have a fench copy of XP? I have changed my regional
> settings
> to English.
> PLEASE HELP!
> TIA
> -steve
>
>|||Regional settings should have nothing to do with this. I do not
think the ROUND function is giving you an incorrect answer, but
in order to help you out, you will have to post the actual statements
or a reproducible example of where you get these results. "Auto
number of decimal places" is not part of any SQL Server type, so
I'm not even sure you are using Microsoft SQL Server. If not, see
if there is a newsgroup for whatever database you are using.
Steve Kass
Drew University
Steve wrote:
>Hi,
>I am using the round function since i want two decimal places and it doesnt
>give me the correct results.
>The field in which i apply it is a Real with Auto number of decimal places.
>In my querries I have something like:
>value1 = 2.3443 and Round(value1,2) = 2.3443 *instead of* 2.34
>The funny thing is that I tried it on a copy of the database on Access XP
>and I get the *same* result !!!!...
>Is it because I have a fench copy of XP? I have changed my regional
>settings
>to English.
>PLEASE HELP!
>TIA
>-steve
>
>
>|||The problem is that you don't understand the round function, nor do you
understand numeric datatypes. Don't feel bad, they are tricky subjects.
declare @.value1 real
set @.value1 = 2.3443
select round(@.value1, 1,2)
returns:
2.2999999999999998
What you want to do is:
select round(@.value1,2) --since you don't want to truncate. This returns:
2.3399999999999999
Which in real datatype terms, is 2.34
If your goal is to get it into a precise datatype, do something like:
select cast( round(@.value1,2) as decimal(5,2))
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Steve" <try@.this.com> wrote in message
news:WWg_d.5424$sC5.53547@.weber.videotron.net...
> Hi,
> I am using the round function since i want two decimal places and it
> doesnt
> give me the correct results.
> The field in which i apply it is a Real with Auto number of decimal
> places.
> In my querries I have something like:
> value1 = 2.3443 and Round(value1,2) = 2.3443 *instead of* 2.34
> The funny thing is that I tried it on a copy of the database on Access XP
> and I get the *same* result !!!!...
> Is it because I have a fench copy of XP? I have changed my regional
> settings
> to English.
> PLEASE HELP!
> TIA
> -steve
>
>|||Steve,
WHy are you trying to round() ?
Just for display purposes ? Or do you actually want to store only two
decimal places in the database ?
If the former, Then let your UI Code control the display... SQL isn;t the
place for that..
If the Latter, just change the datatype of the column in the database to
Decimal 6,2)
(NOTE: If this Store Proc or SQL Is customized specifically for some
report, and you CAN't control the formatting in the reporting tool, then in
that case Convert the output from the real column to text
Use the STR() Function for this... Look it up in BOL
Declare @.V Real Set @.V = 2.3443
Select STR(@.V, 5,2)
"Steve" wrote:
> Hi,
> I am using the round function since i want two decimal places and it doesn
t
> give me the correct results.
> The field in which i apply it is a Real with Auto number of decimal places
.
> In my querries I have something like:
> value1 = 2.3443 and Round(value1,2) = 2.3443 *instead of* 2.34
> The funny thing is that I tried it on a copy of the database on Access XP
> and I get the *same* result !!!!...
> Is it because I have a fench copy of XP? I have changed my regional
> settings
> to English.
> PLEASE HELP!
> TIA
> -steve
>
>|||Thanx! That seems to work ( in SQL SERVER not Access, I know wrong nsgroup
but my application should target both)
I am VERY surprised. So, in SQL server, despite the fact that i have
declared (imported from Access) my val1 as real, number, whatever....
There is NO single function to output two decimal places?....
I do not want to loose precision, although not too important, i just want
to save space when i *display* the result. I want to round-off to two
decimal places.
I actually have to show 24 values ....
Thanx again!
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> a crit dans le message de
news: %23jDg$nxKFHA.2136@.TK2MSFTNGP14.phx.gbl...
> The problem is that you don't understand the round function, nor do you
> understand numeric datatypes. Don't feel bad, they are tricky subjects.
> declare @.value1 real
> set @.value1 = 2.3443
> select round(@.value1, 1,2)
> returns:
> 2.2999999999999998
> What you want to do is:
>
> select round(@.value1,2) --since you don't want to truncate. This returns:
> 2.3399999999999999
> Which in real datatype terms, is 2.34
> If your goal is to get it into a precise datatype, do something like:
> select cast( round(@.value1,2) as decimal(5,2))
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Steve" <try@.this.com> wrote in message
> news:WWg_d.5424$sC5.53547@.weber.videotron.net...
>|||On Thu, 17 Mar 2005 13:26:41 -0500, Steve wrote:
>I am VERY surprised. So, in SQL server, despite the fact that i have
>declared (imported from Access) my val1 as real, number, whatever....
>There is NO single function to output two decimal places?....
Hi Steve,
Yes, there is: CAST.
DECLARE @.Test float
SET @.Test = 12345.6789/876.54321
SELECT @.Test, CAST(@.Test AS NUMERIC(4,2))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I kind of forgot, if it is a formatting thing, you can use STR() to format
it.
declare @.value1 real
set @.value1 = 2.3443
select str(@.value1, 4,2)
Returns:
2.34
I was all caught up in the mathy angle. I would consider using the front
end to change the display, since numbers transmitted in a numeric form are
often smaller than the characters that make them up.
Sorry :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Steve" <try@.this.com> wrote in message
news:6_j_d.6385$sC5.100192@.weber.videotron.net...
> Thanx! That seems to work ( in SQL SERVER not Access, I know wrong nsgroup
> but my application should target both)
> I am VERY surprised. So, in SQL server, despite the fact that i have
> declared (imported from Access) my val1 as real, number, whatever....
> There is NO single function to output two decimal places?....
> I do not want to loose precision, although not too important, i just want
> to save space when i *display* the result. I want to round-off to two
> decimal places.
> I actually have to show 24 values ....
> Thanx again!
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> a crit dans le message
> de news: %23jDg$nxKFHA.2136@.TK2MSFTNGP14.phx.gbl...
>
Help with Recursive Function
I am writing a function which I hope to use as a column value in a
select query. The function recursively walks a taxonomic heirarchy,
extracting the name for an organism at the taxonomic level requested
by the user. I'm having trouble figuring out the syntax to call the
function from itself (see **1), and the value returned.
select query. The function recursively walks a taxonomic heirarchy,
extracting the name for an organism at the taxonomic level requested
by the user. I'm having trouble figuring out the syntax to call the
function from itself (see **1), and the value returned.
When I test the funciton, it says 'commands completed successfully',
but nothing is returned. This is in SQL2000, runing on Windows2000.
The table the function acts on is:
CREATE TABLE [dbo].[tblbenthictaxa] (
[tsn] [int] IDENTITY (1, 1) NOT NULL ,
[rank_id] [int] NOT NULL ,
[dir_parent_tsn] [int] NULL ,
[req_parent_tsn] [int] NOT NULL ,
[taxa_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
ReqParentTSN is the recursive link to rows in the table;
Level is the taxonomic level the user requested (an integer
representing Order, Family, Genus or Species).
CREATE FUNCTION dbo.CBN_RecursTaxa (
@.ReqParentTSN int,
@.Level int
)
RETURNS varchar(100) AS
BEGIN
Declare @.Rank int,
@.taxaname varchar(100)
SELECT @.ReqParentTSN = tblbenthictaxa.req_parent_tsn,
@.TaxaName = tblbenthictaxa.taxa_name,
@.Rank = tblbenthictaxa.rank_id
FROM tblbenthictaxa
WHERE tblbenthictaxa.TSN=@.ReqParentTSN
if @.Rank > @.Level
**1 --exec CBN_RecursTaxa @.ReqParentTSN, @.Level
RETURN @.TaxaName
END
Thanks in advance for any help,
TimTim Pascoe (tim.pascoe@.cciw.ca) writes:
> I am writing a function which I hope to use as a column value in a
> select query. The function recursively walks a taxonomic heirarchy,
> extracting the name for an organism at the taxonomic level requested
> by the user. I'm having trouble figuring out the syntax to call the
> function from itself (see **1), and the value returned.
> When I test the funciton, it says 'commands completed successfully',
> but nothing is returned. This is in SQL2000, runing on Windows2000.
> The table the function acts on is:
There are two ways to run a scalar UDF, and I don't know which you are
using. But I think this example, gives you the answer to both of your
questions:
CREATE FUNCTION nisse (@.i int) returns varchar(200) AS
BEGIN
DECLARE @.ret varchar(200),
@.tmp varchar(200)
SELECT @.i = @.i - 1, @.ret = 'nisse '
IF @.i > 0
BEGIN
EXEC @.tmp = dbo.nisse @.i
SELECT @.ret = @.tmp + @.ret
END
RETURN @.ret
END
go
SELECT dbo.nisse(4)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
The example was perfect. I knew it was something small, but the simple
things are sometimes the hardest to track down when you are learning.
The function works perfectly, and is much faster than the original ASP
script approach I had.
Thanks again,
Tim
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql
Monday, February 27, 2012
Help with more Advanced functions
I have been able to get several basic databases to function both in playing
around and functional ones on the web but they have all been pretty simple.
I am now trying to develop a database for the web using Access. What I am
really needing help with is how to actually reduce the number of record sets
that I think i am going to need. Here is what I am trying to do.
around and functional ones on the web but they have all been pretty simple.
I am now trying to develop a database for the web using Access. What I am
really needing help with is how to actually reduce the number of record sets
that I think i am going to need. Here is what I am trying to do.
I am having people sign up and create teams. So you come to the site and you
see Team 1, Team 2... Team 8. (These numbers will later be replaced by real
names given by the team captains and replaced in the database, this will be
done by the captain through the web)You pick a team you want to be on, enter
the info and you are placed on that team (in the database) I have two tables
in the database TeamRoster (everyone on all of the teams) and Teams (List
the captains and the names of their team)
So when I query the database to fill the tables I am confused as to how I
should go about the query. As I see it I need to build a RS (Record Set) for
each team to get the name of the team for the appropriate table, then
populate it with the players for that team (repeating region) if there are
eight teams, that means 16 RS. This does not seem right.
Am I making any sense?
Thanks for trying to understand all of this.
Houston"Houston" <houston@.hbip.com> wrote in message news:<jSgUc.1240$jj.803@.newssvr23.news.prodigy.com>...
> I have been able to get several basic databases to function both in playing
> around and functional ones on the web but they have all been pretty simple.
> I am now trying to develop a database for the web using Access. What I am
> really needing help with is how to actually reduce the number of record sets
> that I think i am going to need. Here is what I am trying to do.
>
> I am having people sign up and create teams. So you come to the site and you
> see Team 1, Team 2... Team 8. (These numbers will later be replaced by real
> names given by the team captains and replaced in the database, this will be
> done by the captain through the web)You pick a team you want to be on, enter
> the info and you are placed on that team (in the database) I have two tables
> in the database TeamRoster (everyone on all of the teams) and Teams (List
> the captains and the names of their team)
>
> So when I query the database to fill the tables I am confused as to how I
> should go about the query. As I see it I need to build a RS (Record Set) for
> each team to get the name of the team for the appropriate table, then
> populate it with the players for that team (repeating region) if there are
> eight teams, that means 16 RS. This does not seem right.
>
> Am I making any sense?
>
> Thanks for trying to understand all of this.
>
>
> Houston
It's not really clear from your description if you're using only
Access, or Access as a front end to MSSQL. If you're using MSSQL, you
would use queries (preferably inside stored procedures) to return data
such as the complete list of teams, the list of team members for a
specified team etc. How you then format and display that data in
Access, I have no idea.
If you're not using MSSQL, or if you need more information about using
RecordSet objects, you should probably post to an Access or ADO group,
with some more details about exactly which software you're using, and
which client libraries. If you are using MSSQL, then it would be
useful if you post the CREATE TABLE statements for your tables, as
well as sample data, so that it's easier to understand what you're
asking.
Simon
Help with modifying my function in order to seek out and return specific values
I'll try to make this simple, but first, a few requests & info for you:
1) Do not ask the "why", just help me with my question, how to change my SQL
2) I will try to explain this in as simple terms and with enough info that I think is needed for this post
3) Do not tell me this should be done more easily in an OOP language..I have already realized this but, I don’t have time to turn back now.I want to use what I have that works, and simply modify it for now to get the results I need with your help
4) I am not using SQL 2005 so I cannot put in nice regex functions.I realized now, I should have used C# for this *** but oh well, too late to turn back, time is of the essence
5) Yes, it’s the stupidest thing, we have ProductIDs in a memo field and we’re now fixing this by inserting these ProductIDs returned back into a bridge table.I was not on this team, I would never design a table like this in the first place, but…with that said, I am assigned to fix the data.So please refrain from any remarks to that…I hear ya!I hate it too.
6) Do not assume that I know SQL well.I do know pretty advanced, but not this extreme, this is getting crazy with all these PATINDEX, CHARINDEX, and in combination/use with SUBSTRING.I am first and foremost a C# programmer, not a SQL guru by all means.
First off, the existing function works great.It takes in a ProductDescription that has ProductIDs embedded in it (again, yes, very stupid) and then this function returns a nice comma deilimited string of ProductIDs like this:
106761,106763,106791,105813
My goal, and what’s hard for me:
1) I realized I need to change this function to return ProductIDs based on product type: WAV vs. MP3
2) I need help with modifying my existing function above, changing the Substring, PatIndex, or whatever so that it checks the current ProductDescription, looks for some strings that tell you whether it’s a WAV or ProductID, and rips out WAV or MP3 productIDs specifically.
Other Information you need:
There are 2 posssible types of @.ProductDescription formats that could be passed to this function.
The # of ProductIDs vary and this function already handles that problem
Example 1:
‘These music tracks invoke the spirit<br><br><span class='product-name-no-link'>You can also purchase the individual tracks:</span><br><br>01. American Plains <a href="ProductInfo.aspx?ProductID=105234"
Example 2:
‘Clapping percussion effects characterize this Hip Hop/Urban piece with train sound effects and strings.<br><br><b>Styles:</b> Dramatic,Reflective,Somber/dark<br><br><
Conclusion
1) So basically I assume the best way to do this would be to somehow check on the following strings in order to determine what ProductIDs to bring back in the @.result:
‘MP3</a>’
‘WAV</a>’
‘for the WAV version of this track’
‘for the MP3 version of this track’
Existing Script:
ALTER FUNCTION [dbo].[GetProductChildIDs] (
@.ProductDescription varchar(5500),
@.FileFormatvarchar(3)
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE@.Location varchar(50),
@.Result varchar(1000)
SET @.Result = ''
SET @.Location = PATINDEX('%ProductID=%',@.ProductDescription)+10
WHILE @.Location > 10
BEGIN
SELECT @.Result = @.Result + SUBSTRING(@.ProductDescription,PATINDEX('%ProductID=%',@.ProductDescription)+10, (CHARINDEX('"',@.ProductDescription,PATINDEX('%ProductID=%',@.ProductDescription)) - (PATINDEX('%ProductID=%',@.ProductDescription)+10))) + ','
SET @.ProductDescription = RIGHT(@.ProductDescription,LEN(@.ProductDescription) - @.Location)
SET @.Location = PATINDEX('%ProductID=%',@.ProductDescription)+10
END
-- Return the comma delimited string of child ProductIDs for Currrent ProductID
-- and get rid of the last comma
return substring(@.Result,1,len(@.Result)-1)
END
My start, a mock-up:
Here’s a mock-up of trying to start this.
ALTER FUNCTION [dbo].[GetProductChildIDs] (
@.ProductDescription varchar(5500),
@.FileFormatvarchar(3)
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE@.Location varchar(50),
@.Result varchar(1000)
SET @.Result = ''
If @.FileFormat = 'MP3'
BEGIN
WHILE @.Location > 10
BEGIN
Get and set only the ProductIDs for MP3
END
-- Return the comma delimited string of MP3 ProductIDs
return substring(@.Result,1,len(@.Result)-1)
END
If @.FileFormat = 'WAV'
BEGIN
WHILE @.Location > 10
BEGIN
Get and set only the ProductIDs for WAV
END
-- Return the comma delimited string of WAV ProductIDs
return substring(@.Result,1,len(@.Result)-1)
END
END
Looking at the first example first...
I think if you run some sort of string split function (like Itzik's at http://www.sql.co.il/books/insidetsql2005/source_code/TechEd%202006%20Israel%20-%20Advanced%20T-SQL%20Techniques.txt - don't worry, you don't need SQL2005 for it, just be prepared to make yourself an auxiliary table of numbers), then you could split your string on "<BR>" to break it into the lines for each song.
Then you could split those lines on "ProductID=", to get something that looks like:
1.1 - 01. American Plains <a
href="ProductInfo.aspx? 2.1 - 02. Sultry Summer Night <a href="ProductInfo.aspx? 3.1 - 03. Ocean Skyline <a href="ProductInfo.aspx? etc. So then you should be able to quite easily pull out the song title, ID and type. My code is here... the stuff in the comment at the start sets it up, and then the rest is to pull the data out. I haven't tried to do the second format... but I'm sure you can look at what I'm doing here and adapt it accordingly. /* create table largetext (id int identity(1,1) primary key, largetext nvarchar(3000)) select * from largetext declare @.rowseparator nvarchar(100) SELECT set @.rowseparator = N'ProductID=' SELECT select left(titles.val,charindex(N'<a href',titles.val)-2) as title, drop table #lines Rob, thank you, and of course I will work on this and take maybe a different approach. I just get head spins sometimes with combining the Substring, Charindex, and Patindex together when it's getting this complex. Thanks a lot, I'll try it from here and also make sure I understand it going forward. Also, checked out your blog, nice! I see you're from Austrailia. My experience with people from Britain and Austrailia is that they are very pleasant people. I wish in general, programmers in the US could be more like you...instead of ego trips like I see every day here for the past 10 years in my profession with many of them. cheers! It's very different to the way you'd do it in C#, because it's taking a set-based approach to the problem - not stepping through it bit by bit. If you insert extra rows into that largetext table, you'll see that it doesn't really take much longer. And please don't call me 'Bob'... No-one calls me that. Rob|||Thank you for explaining. I corrected the Rob.
1.2 - 105234"
1.3 - 105235"
2.2 - 105236"
2.3 - 105237"
3.2 - 105238"
3.3 - 105239"
create table dbo.Nums (n int primary key)
while (select count(*) from nums) < 1000
insert into nums select n + (select count(*) from nums) from nums
insert into largetext values (
'‘These music tracks invoke the spirit<br><br><span class=''product-name-no-link''>You can also purchase the individual tracks:</span><br><br>01. American Plains <a href="ProductInfo.aspx?ProductID=105234">MP3</a> | <a href="ProductInfo.aspx?ProductID=105235">WAV</a><br>02. Sultry Summer Night <a href="ProductInfo.aspx?ProductID=105236">MP3</a> | <a href="ProductInfo.aspx?ProductID=105237">WAV</a><br>03. Ocean Skyline <a href="ProductInfo.aspx?ProductID=105238">MP3</a> | <a href="ProductInfo.aspx?ProductID=105239">WAV</a><br>04. Wistful Lover <a href="ProductInfo.aspx?ProductID=105240">MP3</a> | <a href="ProductInfo.aspx?ProductID=105241">WAV</a><br>’')
*/
set @.rowseparator = N'<br>'
l.id,
(n - LEN(REPLACE(LEFT(l.largetext, n), @.rowseparator, ''))) / len(@.rowseparator) + 1 AS pos,
SUBSTRING(l.largetext, n,
CHARINDEX(@.rowseparator, l.largetext + @.rowseparator, n) - n)
AS val
into #lines
FROM dbo.largetext l JOIN dbo.Nums
ON n <= LEN(l.largetext)
AND SUBSTRING(@.rowseparator + l.largetext, n, len(@.rowseparator)) = @.rowseparator
l.id,
l.pos as lineid,
(n - LEN(REPLACE(LEFT(l.val, n), @.rowseparator, ''))) / len(@.rowseparator) + 1 AS pos,
SUBSTRING(l.val, n,
CHARINDEX(@.rowseparator, l.val + @.rowseparator, n) - n)
AS val
into #lines2
FROM #lines l JOIN dbo.Nums
ON n <= LEN(l.val)
AND SUBSTRING(@.rowseparator + l.val, n, len(@.rowseparator)) = @.rowseparator
where l.val like '%' + @.rowseparator + '%'
left(pid.val,charindex(N'"',pid.val)-1) as productid,
substring(pid.val,charindex(N'>',pid.val)+1,charindex(N'</a>',pid.val)-charindex(N'>',pid.val)-1) as producttype
from #lines2 titles
join
#lines2 pid
on titles.lineid = pid.lineid
and titles.pos = 1
and pid.pos > 1
drop table #lines2|||
Friday, February 24, 2012
Help with measure aggregation functions
Hi, I will rewrite my question.
I'm having trouble to show averages of a measure in a cube, where the normal
aggregation function for a measure is SUM.
I see no AVG aggregation function for measures (I see Min, Max, Count,
Distinct Count and SUM).
If I hide the measure (cost), and create a calculated member based on that
measure, ie Avg(cost), I have te problem of how to average it, since the
cube has two dimensions in the row axis, like:
Time
Product |
Customer | avg of cost
If I use avg(nonemptycrossjoin(product.currentmember.childr en,
customer.currentmember.children), measures.cost) I get the same average for
every cell in the cube, what's not corrrect...
Sorry to bother you all, but this thing is becoming a nightmare.
Hope you can help
Michael Prendergast
Averages are usually handled by summing and counting...and then
dividing the sub by the count in a calculated member.
MPS wrote:
> Hi, I will rewrite my question.
> I'm having trouble to show averages of a measure in a cube, where the
normal
> aggregation function for a measure is SUM.
> I see no AVG aggregation function for measures (I see Min, Max,
Count,
> Distinct Count and SUM).
> If I hide the measure (cost), and create a calculated member based on
that
> measure, ie Avg(cost), I have te problem of how to average it, since
the
> cube has two dimensions in the row axis, like:
> Time
> Product |
> Customer | avg of cost
> If I use avg(nonemptycrossjoin(product.currentmember.childr en,
> customer.currentmember.children), measures.cost) I get the same
average for
> every cell in the cube, what's not corrrect...
> Sorry to bother you all, but this thing is becoming a nightmare.
> Hope you can help
> Michael Prendergast
|||Sometimes, getting back to basics gets the job done
Thank yo very much, problem solved
Michael
"OLAPMonkey" <jjanke@.spss.com> escribi en el mensaje
news:1112202692.231562.128020@.g14g2000cwa.googlegr oups.com...
> Averages are usually handled by summing and counting...and then
> dividing the sub by the count in a calculated member.
> MPS wrote:
> normal
> Count,
> that
> the
> average for
>
I'm having trouble to show averages of a measure in a cube, where the normal
aggregation function for a measure is SUM.
I see no AVG aggregation function for measures (I see Min, Max, Count,
Distinct Count and SUM).
If I hide the measure (cost), and create a calculated member based on that
measure, ie Avg(cost), I have te problem of how to average it, since the
cube has two dimensions in the row axis, like:
Time
Product |
Customer | avg of cost
If I use avg(nonemptycrossjoin(product.currentmember.childr en,
customer.currentmember.children), measures.cost) I get the same average for
every cell in the cube, what's not corrrect...
Sorry to bother you all, but this thing is becoming a nightmare.
Hope you can help
Michael Prendergast
Averages are usually handled by summing and counting...and then
dividing the sub by the count in a calculated member.
MPS wrote:
> Hi, I will rewrite my question.
> I'm having trouble to show averages of a measure in a cube, where the
normal
> aggregation function for a measure is SUM.
> I see no AVG aggregation function for measures (I see Min, Max,
Count,
> Distinct Count and SUM).
> If I hide the measure (cost), and create a calculated member based on
that
> measure, ie Avg(cost), I have te problem of how to average it, since
the
> cube has two dimensions in the row axis, like:
> Time
> Product |
> Customer | avg of cost
> If I use avg(nonemptycrossjoin(product.currentmember.childr en,
> customer.currentmember.children), measures.cost) I get the same
average for
> every cell in the cube, what's not corrrect...
> Sorry to bother you all, but this thing is becoming a nightmare.
> Hope you can help
> Michael Prendergast
|||Sometimes, getting back to basics gets the job done
Thank yo very much, problem solved
Michael
"OLAPMonkey" <jjanke@.spss.com> escribi en el mensaje
news:1112202692.231562.128020@.g14g2000cwa.googlegr oups.com...
> Averages are usually handled by summing and counting...and then
> dividing the sub by the count in a calculated member.
> MPS wrote:
> normal
> Count,
> that
> the
> average for
>
Help with measure aggregation functions
Hi, I will rewrite my question.
I'm having trouble to show averages of a measure in a cube, where the normal
aggregation function for a measure is SUM.
I see no AVG aggregation function for measures (I see Min, Max, Count,
Distinct Count and SUM).
If I hide the measure (cost), and create a calculated member based on that
measure, ie Avg(cost), I have te problem of how to average it, since the
cube has two dimensions in the row axis, like:
Time
---
Product |
Customer | avg of cost
If I use avg(nonemptycrossjoin(product.currentmember.children,
customer.currentmember.children), measures.cost) I get the same average for
every cell in the cube, what's not corrrect...
Sorry to bother you all, but this thing is becoming a nightmare.
Hope you can help
Michael PrendergastAverages are usually handled by summing and counting...and then
dividing the sub by the count in a calculated member.
MPS wrote:
> Hi, I will rewrite my question.
> I'm having trouble to show averages of a measure in a cube, where the
normal
> aggregation function for a measure is SUM.
> I see no AVG aggregation function for measures (I see Min, Max,
Count,
> Distinct Count and SUM).
> If I hide the measure (cost), and create a calculated member based on
that
> measure, ie Avg(cost), I have te problem of how to average it, since
the
> cube has two dimensions in the row axis, like:
> Time
> ---
> Product |
> Customer | avg of cost
> If I use avg(nonemptycrossjoin(product.currentmember.children,
> customer.currentmember.children), measures.cost) I get the same
average for
> every cell in the cube, what's not corrrect...
> Sorry to bother you all, but this thing is becoming a nightmare.
> Hope you can help
> Michael Prendergast|||Sometimes, getting back to basics gets the job done
Thank yo very much, problem solved
Michael
"OLAPMonkey" <jjanke@.spss.com> escribi en el mensaje
news:1112202692.231562.128020@.g14g2000cwa.googlegroups.com...
> Averages are usually handled by summing and counting...and then
> dividing the sub by the count in a calculated member.
> MPS wrote:
> normal
> Count,
> that
> the
> average for
>
I'm having trouble to show averages of a measure in a cube, where the normal
aggregation function for a measure is SUM.
I see no AVG aggregation function for measures (I see Min, Max, Count,
Distinct Count and SUM).
If I hide the measure (cost), and create a calculated member based on that
measure, ie Avg(cost), I have te problem of how to average it, since the
cube has two dimensions in the row axis, like:
Time
---
Product |
Customer | avg of cost
If I use avg(nonemptycrossjoin(product.currentmember.children,
customer.currentmember.children), measures.cost) I get the same average for
every cell in the cube, what's not corrrect...
Sorry to bother you all, but this thing is becoming a nightmare.
Hope you can help
Michael PrendergastAverages are usually handled by summing and counting...and then
dividing the sub by the count in a calculated member.
MPS wrote:
> Hi, I will rewrite my question.
> I'm having trouble to show averages of a measure in a cube, where the
normal
> aggregation function for a measure is SUM.
> I see no AVG aggregation function for measures (I see Min, Max,
Count,
> Distinct Count and SUM).
> If I hide the measure (cost), and create a calculated member based on
that
> measure, ie Avg(cost), I have te problem of how to average it, since
the
> cube has two dimensions in the row axis, like:
> Time
> ---
> Product |
> Customer | avg of cost
> If I use avg(nonemptycrossjoin(product.currentmember.children,
> customer.currentmember.children), measures.cost) I get the same
average for
> every cell in the cube, what's not corrrect...
> Sorry to bother you all, but this thing is becoming a nightmare.
> Hope you can help
> Michael Prendergast|||Sometimes, getting back to basics gets the job done
Thank yo very much, problem solved
Michael
"OLAPMonkey" <jjanke@.spss.com> escribi en el mensaje
news:1112202692.231562.128020@.g14g2000cwa.googlegroups.com...
> Averages are usually handled by summing and counting...and then
> dividing the sub by the count in a calculated member.
> MPS wrote:
> normal
> Count,
> that
> the
> average for
>
Help with Matrix report and design
I've got this data source that returns member information.. great.
I've got a function that when passed the member number and a month/
year returns days participating in the club.
I need to build a report that will output some of the columns for the
member, but then report other information for 6 months for each member
that all calulated based on days particpating for that month. Which 6
months will be dictated by single parameter passed to the report for
starting month/year... basically the next 6 months.
Is a matrix report a good option for this? Or should I just have the
function called 6 times in my data source? Ideally I layout the report
for one month and then *somehow* join in my data source for 6
iterations where month is 1 through 6.
Right now I'm having trouble wrapping my mind around how and where to
join the data? Especially if matrix and I have two data sources. I'm
also not sure I can call my function from cells in the matrix or if I
can even have mulitple data buckets in the matrix. I'm tempted to do
this the only way I know how, but I suspect there is a better way.
I've never built a matrix report in ssrs, any information or help is
appreciated.
Thank you!If you have a table that lists visits for your members with a datetime
field, your query should look like this:
select MemberFullName, Month(ActivityDate), count(*) from MyTable
group by MemberFullName, Month(ActivityDate)
Then put a matrix control on your report's layout. Drag the MemberFullName
field on the top row, the Month(ActivityDate) on the left column and the
count(*) field in the middle cell. Preview your report.
John Smith | Mary Poppins | Joe Shmoe
Jan 12 | 15 | 17
Feb 14 | 19 | 10
Mar 11 | 12 | 12
Apr 12 | 8 | 11
May 9 | 7 | 18
Jun 10 | 3 | 14
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
<wildman@.noclient.net> wrote in message
news:8cb4193c-acf5-405d-b6b3-d03d512bdcfe@.e53g2000hsa.googlegroups.com...
> I've got this data source that returns member information.. great.
> I've got a function that when passed the member number and a month/
> year returns days participating in the club.
> I need to build a report that will output some of the columns for the
> member, but then report other information for 6 months for each member
> that all calulated based on days particpating for that month. Which 6
> months will be dictated by single parameter passed to the report for
> starting month/year... basically the next 6 months.
> Is a matrix report a good option for this? Or should I just have the
> function called 6 times in my data source? Ideally I layout the report
> for one month and then *somehow* join in my data source for 6
> iterations where month is 1 through 6.
> Right now I'm having trouble wrapping my mind around how and where to
> join the data? Especially if matrix and I have two data sources. I'm
> also not sure I can call my function from cells in the matrix or if I
> can even have mulitple data buckets in the matrix. I'm tempted to do
> this the only way I know how, but I suspect there is a better way.
> I've never built a matrix report in ssrs, any information or help is
> appreciated.
> Thank you!
>
I've got a function that when passed the member number and a month/
year returns days participating in the club.
I need to build a report that will output some of the columns for the
member, but then report other information for 6 months for each member
that all calulated based on days particpating for that month. Which 6
months will be dictated by single parameter passed to the report for
starting month/year... basically the next 6 months.
Is a matrix report a good option for this? Or should I just have the
function called 6 times in my data source? Ideally I layout the report
for one month and then *somehow* join in my data source for 6
iterations where month is 1 through 6.
Right now I'm having trouble wrapping my mind around how and where to
join the data? Especially if matrix and I have two data sources. I'm
also not sure I can call my function from cells in the matrix or if I
can even have mulitple data buckets in the matrix. I'm tempted to do
this the only way I know how, but I suspect there is a better way.
I've never built a matrix report in ssrs, any information or help is
appreciated.
Thank you!If you have a table that lists visits for your members with a datetime
field, your query should look like this:
select MemberFullName, Month(ActivityDate), count(*) from MyTable
group by MemberFullName, Month(ActivityDate)
Then put a matrix control on your report's layout. Drag the MemberFullName
field on the top row, the Month(ActivityDate) on the left column and the
count(*) field in the middle cell. Preview your report.
John Smith | Mary Poppins | Joe Shmoe
Jan 12 | 15 | 17
Feb 14 | 19 | 10
Mar 11 | 12 | 12
Apr 12 | 8 | 11
May 9 | 7 | 18
Jun 10 | 3 | 14
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
<wildman@.noclient.net> wrote in message
news:8cb4193c-acf5-405d-b6b3-d03d512bdcfe@.e53g2000hsa.googlegroups.com...
> I've got this data source that returns member information.. great.
> I've got a function that when passed the member number and a month/
> year returns days participating in the club.
> I need to build a report that will output some of the columns for the
> member, but then report other information for 6 months for each member
> that all calulated based on days particpating for that month. Which 6
> months will be dictated by single parameter passed to the report for
> starting month/year... basically the next 6 months.
> Is a matrix report a good option for this? Or should I just have the
> function called 6 times in my data source? Ideally I layout the report
> for one month and then *somehow* join in my data source for 6
> iterations where month is 1 through 6.
> Right now I'm having trouble wrapping my mind around how and where to
> join the data? Especially if matrix and I have two data sources. I'm
> also not sure I can call my function from cells in the matrix or if I
> can even have mulitple data buckets in the matrix. I'm tempted to do
> this the only way I know how, but I suspect there is a better way.
> I've never built a matrix report in ssrs, any information or help is
> appreciated.
> Thank you!
>
Help with math in function
I have a function that reads an unsigned integer and translates it into an IP address. The problem is that it is rounding the numbers so the IP numbers are off by 1 either up or down, i.e., 144.xxx.xxx.xx will show as 145.xxx.... or 144.456.xx... will show as 144.455.xx....Can someone help?
Here is the code:
create or replace function readip(unsint number)
return varchar
is
ip_address varchar(15);
a number(3);
b number(3);
c number(3);
d number(3);
begin
a := mod( ( unsint / 16777216 ), 256);
b := mod( ( unsint / 65536 ), 256);
c := mod( ( unsint / 256 ), 256);
d := mod( ( unsint ), 256);
ip_address := a||'.'||b||'.'||c||'.'||d;
return ip_address;
end readip;
Thank you much!
MonicaIf you want the decimals to be displayed, define the variables as
a number(10,3);
Hope this helps.|||Originally posted by DBW-Monica
I have a function that reads an unsigned integer and translates it into an IP address. The problem is that it is rounding the numbers so the IP numbers are off by 1 either up or down, i.e., 144.xxx.xxx.xx will show as 145.xxx.... or 144.456.xx... will show as 144.455.xx....Can someone help?
Here is the code:
create or replace function readip(unsint number)
return varchar
is
ip_address varchar(15);
a number(3);
b number(3);
c number(3);
d number(3);
begin
a := mod( ( unsint / 16777216 ), 256);
b := mod( ( unsint / 65536 ), 256);
c := mod( ( unsint / 256 ), 256);
d := mod( ( unsint ), 256);
ip_address := a||'.'||b||'.'||c||'.'||d;
return ip_address;
end readip;
Thank you much!
Monica
must be missing the obvious, but I just don't get it. Can you give an example of an input value, and what your function should return ?
Here is the code:
create or replace function readip(unsint number)
return varchar
is
ip_address varchar(15);
a number(3);
b number(3);
c number(3);
d number(3);
begin
a := mod( ( unsint / 16777216 ), 256);
b := mod( ( unsint / 65536 ), 256);
c := mod( ( unsint / 256 ), 256);
d := mod( ( unsint ), 256);
ip_address := a||'.'||b||'.'||c||'.'||d;
return ip_address;
end readip;
Thank you much!
MonicaIf you want the decimals to be displayed, define the variables as
a number(10,3);
Hope this helps.|||Originally posted by DBW-Monica
I have a function that reads an unsigned integer and translates it into an IP address. The problem is that it is rounding the numbers so the IP numbers are off by 1 either up or down, i.e., 144.xxx.xxx.xx will show as 145.xxx.... or 144.456.xx... will show as 144.455.xx....Can someone help?
Here is the code:
create or replace function readip(unsint number)
return varchar
is
ip_address varchar(15);
a number(3);
b number(3);
c number(3);
d number(3);
begin
a := mod( ( unsint / 16777216 ), 256);
b := mod( ( unsint / 65536 ), 256);
c := mod( ( unsint / 256 ), 256);
d := mod( ( unsint ), 256);
ip_address := a||'.'||b||'.'||c||'.'||d;
return ip_address;
end readip;
Thank you much!
Monica
must be missing the obvious, but I just don't get it. Can you give an example of an input value, and what your function should return ?
Subscribe to:
Posts (Atom)