create an incremental counter in the stored procedure

Hello, I have a following SP
I want to add an extra field "ranking" that just increments the row number.
Another feature would be: if several users have an equal totalvalue, they
should have an equal ranking number. the rankings following users would have
to be adjusted as well. thanks

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [dbo].[Rankings]
@.iErrorCode int OUTPUT
AS

SELECT top 30
###COUNTER##,
[user],
[totalvalue], [cash], [stocksvalue]

FROM [dbo].[users]
ORDER BY totalvalue DESC

SELECT @.iErrorCode=@.@.ERROR

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOThere is more than one way to rank a set with tied values.

CREATE TABLE Users (userid INTEGER PRIMARY KEY, totalvalue NUMERIC(10,2) NOT
NULL, cash NUMERIC(10,2) NOT NULL, stocksvalue NUMERIC(10,2) NOT NULL)

INSERT INTO Users VALUES (101,1010,100,99)
INSERT INTO Users VALUES (102,2020,100,99)
INSERT INTO Users VALUES (103,3030,100,99)
INSERT INTO Users VALUES (104,3030,100,99)
INSERT INTO Users VALUES (105,1002,100,99)
INSERT INTO Users VALUES (106,1002,100,99)
INSERT INTO Users VALUES (107,1002,100,99)
INSERT INTO Users VALUES (108,1002,100,99)
INSERT INTO Users VALUES (109,1000,100,99)

See if this gives the result you expect:

SELECT COUNT(U2.totalvalue)+1 AS ranking,
U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
FROM Users AS U1
LEFT JOIN Users AS U2
ON U1.totalvalue < U2.totalvalue
GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
ORDER BY ranking, U1.userid

Result:

ranking userid totalvalue cash stocksvalue
---- ---- ---- ---- ----
1 103 3030.00 100.00 99.00
1 104 3030.00 100.00 99.00
3 102 2020.00 100.00 99.00
4 101 1010.00 100.00 99.00
5 105 1002.00 100.00 99.00
5 106 1002.00 100.00 99.00
5 107 1002.00 100.00 99.00
5 108 1002.00 100.00 99.00
9 109 1000.00 100.00 99.00

(9 row(s) affected)

Or maybe this:

SELECT COUNT(DISTINCT U2.totalvalue) AS ranking,
U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
FROM Users AS U1
LEFT JOIN Users AS U2
ON U1.totalvalue <= U2.totalvalue
GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
ORDER BY ranking, U1.userid

Result:

ranking userid totalvalue cash stocksvalue
---- ---- ---- ---- ----
1 103 3030.00 100.00 99.00
1 104 3030.00 100.00 99.00
2 102 2020.00 100.00 99.00
3 101 1010.00 100.00 99.00
4 105 1002.00 100.00 99.00
4 106 1002.00 100.00 99.00
4 107 1002.00 100.00 99.00
4 108 1002.00 100.00 99.00
5 109 1000.00 100.00 99.00

(9 row(s) affected)

--
David Portas
SQL Server MVP
--|||thank you! it works fine.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1pmdnW4hl-GFFt3dRVn-uA@.giganews.com...
> There is more than one way to rank a set with tied values.
> CREATE TABLE Users (userid INTEGER PRIMARY KEY, totalvalue NUMERIC(10,2)
NOT
> NULL, cash NUMERIC(10,2) NOT NULL, stocksvalue NUMERIC(10,2) NOT NULL)
> INSERT INTO Users VALUES (101,1010,100,99)
> INSERT INTO Users VALUES (102,2020,100,99)
> INSERT INTO Users VALUES (103,3030,100,99)
> INSERT INTO Users VALUES (104,3030,100,99)
> INSERT INTO Users VALUES (105,1002,100,99)
> INSERT INTO Users VALUES (106,1002,100,99)
> INSERT INTO Users VALUES (107,1002,100,99)
> INSERT INTO Users VALUES (108,1002,100,99)
> INSERT INTO Users VALUES (109,1000,100,99)
> See if this gives the result you expect:
> SELECT COUNT(U2.totalvalue)+1 AS ranking,
> U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> FROM Users AS U1
> LEFT JOIN Users AS U2
> ON U1.totalvalue < U2.totalvalue
> GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> ORDER BY ranking, U1.userid
> Result:
> ranking userid totalvalue cash stocksvalue
> ---- ---- ---- ---- ----
> 1 103 3030.00 100.00 99.00
> 1 104 3030.00 100.00 99.00
> 3 102 2020.00 100.00 99.00
> 4 101 1010.00 100.00 99.00
> 5 105 1002.00 100.00 99.00
> 5 106 1002.00 100.00 99.00
> 5 107 1002.00 100.00 99.00
> 5 108 1002.00 100.00 99.00
> 9 109 1000.00 100.00 99.00
> (9 row(s) affected)
> Or maybe this:
> SELECT COUNT(DISTINCT U2.totalvalue) AS ranking,
> U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> FROM Users AS U1
> LEFT JOIN Users AS U2
> ON U1.totalvalue <= U2.totalvalue
> GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> ORDER BY ranking, U1.userid
> Result:
> ranking userid totalvalue cash stocksvalue
> ---- ---- ---- ---- ----
> 1 103 3030.00 100.00 99.00
> 1 104 3030.00 100.00 99.00
> 2 102 2020.00 100.00 99.00
> 3 101 1010.00 100.00 99.00
> 4 105 1002.00 100.00 99.00
> 4 106 1002.00 100.00 99.00
> 4 107 1002.00 100.00 99.00
> 4 108 1002.00 100.00 99.00
> 5 109 1000.00 100.00 99.00
> (9 row(s) affected)
> --
> David Portas
> SQL Server MVP
> --

Create an array in a result field

I am between the "newbie" and "intermediate" stages of writing SQL code and I am wondering if there is a way to capture multiple results into one field so I can basically create a "set" for a unique identifier. Here is few result samples I receive from this code I am using now.

ReqNo ProcID

7102005 1409

7102005 1796

7139003 1411

7139003 6097

7261030 1409

7261030 1796

7268303 3998

7268303 4000

I would like to create a single row for each "ReqNo" and have a field that will an array of the "ProcID" results I receive. In other words, for the first "ReqNo" 7102005, can I create a field that will combine the 1409, 1796 into one field? I am trying to capture an array of integers used for that "ReqNo" so I can use that as a unique identifier in a join for another table.

So, ideally my result would be:

ReqNo ProcSet

7102005 1409, 1796

7139003 1411, 6097

7261030 1409, 1796

7268303 3998, 4000

Is this possible?

declare

@.startdate smalldatetime,

@.enddate smalldatetime ,

@.month int,

@.year int

select

@.startdate = dateadd (dd, -7, getdate())

SELECT

@.month = datepart (month, @.startdate),

@.year = datepart (year, @.startdate)

SELECT

@.startdate = convert (smalldatetime, convert(varchar(2), @.month) + "/1/" + convert (varchar(4), @.year))

select

@.enddate = dateadd (dd, 1 , @.startdate)

select distinct

pp_req_no as ReqNo,

pp_cproc_id_r as ProcID

from

risdb_rch08_stag..performed_procedure

(index pp_serv_time_r_ndx)

where

pp_service_time_r between @.Startdate and @.Enddate

and pp_status_v = 'CP'

and pp_rep_id > 0

order by

pp_req_no, pp_cproc_id_r

You could create a function that would concatenate the fields together and return them as a string. This is not really a recommended practice, but can be occassionally useful for presentation. One trick is to use a local variable to accumulate the results. Of course, your function would be specific to this table.

I am not up on 2005 features, but it seems like there is a new unpivot operator that might work.

This demonstrates how to do the accumulation into a local variable.

use Northwind

go

Declare @.result varchar(1000)

Select @.result = Case When @.result Is Not Null Then @.result + ', ' Else '' End + Convert(varchar(10),OrderID)

From Orders

Where CustomerID = 'ALFKI'

Select @.result|||

Confused?

So are you saying that this syntax @.result + ', ' will build the results in the field? This looks like it will concatenate one result followed by the OrderID in your example (123456, 99999).

How will that build multiple results in one field? I understand how to declare and select, but this does not seem to make sense to me. I will be the first to admit if I am missing something here...which could very well be the case. Bear with me.

|||

Hi,

SELECT t3.ReqNo, MAX(case t3.seq when 1 then t3.ProcID end)

+ MAX(case t3.seq when 2 then ', ' + t3.ProcID else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.ProcID else '' end) AS ProcID

FROM ( SELECT ReqNo, ProcID, (SELECT COUNT(*) FROM yourTable AS t2 WHERE t2.ReqNo = t1.ReqNo and t2.ProcID <= t1.ProcID) AS seq

FROM yourTable AS t1

) as t3

GROUP BY t3.ReqNo

Or you can use this for SQL Server 2005:

SELECT t3.ReqNo, t3.[1] + coalesce(', ' + t3.[2], '') + coalesce(', ' + t3.[3], '') AS ProcID

FROM (SELECT ProcID, ReqNo, ROW_NUMBER() OVER(PARTITION BY ReqNo ORDER BY ProcID) AS seq FROM yourTable) as t1

PIVOT (MAX(ProcID) for seq in ([1], [2], [3])) AS t3

Or using CTE in SQL Server 2005:

With MyCTE(ReqNo, ProcID, ProcIDs, myNum) AS

(SELECT a.ReqNo, CONVERT(varchar(50), MIN(a.ProcID)) as col1, CONVERT(varchar(50),(a.ProcID)) as ProcIDs, 1 as myNum

FROM yourTable a GROUP BY a.ReqNo, CONVERT(varchar(50),(a.ProcID))

UNION ALL

SELECT b.ReqNo, CONVERT(varchar(50), b.ProcID), CONVERT(varchar(50), (c.ProcIDs + ',' + b.ProcID)), c.myNum+1 as myNum

FROM yourTable b INNER JOIN MyCTE c ON b.ReqNo=c.ReqNo

WHERE b.ProcID>c.ProcID

)

SELECT a.ReqNo, ProcIDs FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.ReqNo FROM MyCTE a1

group by a1.ReqNo) b on a.ReqNo=b.ReqNo AND a.myNum= b.myNumMax ORDER BY a.ReqNo

|||

Sorry that I wasn't very clear.

You would build a User Defined Function that would be specific to the table you are working with and would accept a parameter that would identify the records -- like the customerID in the Northwind case. The function would concatenate the values into the local variable and return that variable. You could then call this function as part of your Select clause.

Performance would likely be a challenge.

Here is a NorthWind example

Drop Function OrderList

go

Create Function OrderList( @.cust varchar(10) )

Returns varchar(1000)

As

Begin

Declare @.result varchar(1000)

Select@.result =

Case When @.result Is Not Null

Then @.result + ', '

Else ''

End + Convert(varchar(10), OrderID )

FromOrders

WhereCustomerId = @.cust

return @.result

End

go

SelectCustomerID,

CompanyName,

dbo.OrderList( CustomerID )

FromCustomers

Create a table with a field with only Time datatype

Hello experts,
I want to create a table to store only time in a fileld. There is "DateTime" for my purpose but i dont want to save the Date part only the time part as "12:30:44 AM". I know i can select only time part from Datetime field but i want to save only time.Can anybody help me how can i create that kinda table ?Hello experts,
I want to create a table to store only time in a fileld. There is "DateTime" for my purpose but i dont want to save the Date part only the time part as "12:30:44 AM". I know i can select only time part from Datetime field but i want to save only time.Can anybody help me how can i create that kinda table ?

See this enigma's post link (http://sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk)
Its a good one ...|||i dont understand why you are not thinking in terms of a datetime/smalldatetime field by ignoring the date part and whats wrong in it. however, if you are rigid you can store it either as char field or as numeric field (float/decimal). remember you do not actually save anything by doing so.

storing as char field is straight forward. if you want to store as number (remember that each date+time has a numeric representation) can take hints from the following queries

--1 minute as number
select cast(cast('20060101 11:01 AM' as smalldatetime) as float)- cast(cast('20060101 11:00 AM' as smalldatetime) as float)
--it returns approx 0.0006944

--use that to get a date time 20060101 11:00 AM back
select cast( cast(cast('20060101' as smalldatetime) as float) + (0.0006944*60*11) as smalldatetime)

quite unnecessary...;-)sql

Create a Rolling summary field

I need to make a report that takes a rolling 12 month period.
Each row needs to show the sum of the current months value and all the 11 months prior.

For example, I have a count of items in their respective Months

2 - Jan 06
5 - Feb 06
2 - Mar 06
4 - April 06
2 - May 06
1 - June 06
2 - July 06
5 - Aug 06
2 - Sep 06
2 - Oct 06
4 - Nov 06
2 - Dec 06
2 - Jan 07
3 - Feb 07
1 - Mar 07

What I need as an output is

Jan 07 = 35
Feb 07 = 33
Mar 07 = 32

So each row sums the current and previous 11 rows.

I am using Crystal Report v10 and SQL Database.

Thanks,
DavidThe quick solution in my mind is.

Create a subreport and store the whole data in an array.
Now come to the actual report and while showing the current record you can sum the previous 11 months records from the array and show it there.|||Great thanks for the Idea, I got it working with an array and then another formula summing the 12 in the array. I haven't played with the chart function yet, i have to get all these fields totaling first. Any suggestions getting all the fields in the details section ploted into a chart (the ultimate goal. Each displayed point is a date and the summed 12 month total.|||Keep one thing in mind while dealing with the charts.

You can show/draw more then one values on change of some value. e.g on change of date you can show the sum of previous 11 months.

Also you can get the accumulative .

You have to explore the charts for your project.|||Thanks so much for your help. Sometimes I just need a little bump into the right direction and I can figure it out. I got it working now.

Create a comma delimited *field*

Help! (again...)
I have data that looks like this:
date snum
5/9/2007 3064
5/9/2007 3072
5/16/2007 4031
5/16/2007 3856
5/16/2007 4252
(Can recreate with
declare @.temptable table (date smalldatetime, snum int)
insert into @.temptable values('09 May 2007', 3064)
insert into @.temptable values('09 May 2007', 3072)
insert into @.temptable values('16 May 2007', 4031)
insert into @.temptable values('16 May 2007', 3856)
insert into @.temptable values('16 May 2007', 4252)
)
I need to make it look like this:
date snumlist
5/9/2007 3064, 3072
5/16/2007 4031, 3856, 4252
The "snumlist" is being plugged into a javascript function in a
vbscript.asp page. I can easily create the snumlist using while/wend
in vbscript but it seems quite slow! I'm wondering if it wouldn't be
faster to create the comma delimited list in SQL rather than try to
build it with VBScript.
(I can do anything to the temptable that I need to, structure-wise -
it's created on the fly.)
Thanks for any help, a pointer to a web page, a pointer to a previous
post, ANYthing.
Julie
(P.S. This email address forwards to my regular email address. Either
Google or my ISP often marks replies from this newsgroup as spam, so I
never see them. I still haven't gotten it sorted out. If possible,
please reply on list. Thanks, jcls)Sorry - posted this in wrong group. Moving to .programming
Thx, Julie
On May 5, 4:42 pm, Julie <julie.sie...@.gmail.com> wrote:
> Help! (again...)
> I have data that looks like this:
> date snum
> 5/9/2007 3064
> 5/9/2007 3072
> 5/16/2007 4031
> 5/16/2007 3856
> 5/16/2007 4252
> (Can recreate with
> declare @.temptable table (date smalldatetime, snum int)
> insert into @.temptable values('09 May 2007', 3064)
> insert into @.temptable values('09 May 2007', 3072)
> insert into @.temptable values('16 May 2007', 4031)
> insert into @.temptable values('16 May 2007', 3856)
> insert into @.temptable values('16 May 2007', 4252)
> )
> I need to make it look like this:
> date snumlist
> 5/9/2007 3064, 3072
> 5/16/2007 4031, 3856, 4252
> The "snumlist" is being plugged into a javascript function in a
> vbscript.asp page. I can easily create the snumlist using while/wend
> in vbscript but it seems quite slow! I'm wondering if it wouldn't be
> faster to create the comma delimited list in SQL rather than try to
> build it with VBScript.
> (I can do anything to the temptable that I need to, structure-wise -
> it's created on the fly.)
> Thanks for any help, a pointer to a web page, a pointer to a previous
> post, ANYthing.
> Julie
> (P.S. This email address forwards to my regular email address. Either
> Google or my ISP often marks replies from this newsgroup as spam, so I
> never see them. I still haven't gotten it sorted out. If possible,
> please reply on list. Thanks, jcls)|||I'll tell you what they'll tell you over there - what you're trying to do
should be done in the front end. That aside, there are several T-SQL
scripts (some cleverer than others) posted in the .programming newsgroup to
do exactly what you're asking. When you ask over there, be sure to mention
whether or not the ordering of the items in the comma-separated list is
important. From your example it doesn't appear so, but you will probably
want to clarify that.
"Julie" <julie.siebel@.gmail.com> wrote in message
news:1178409829.540816.60210@.p77g2000hsh.googlegroups.com...
> Sorry - posted this in wrong group. Moving to .programming
> Thx, Julie
> On May 5, 4:42 pm, Julie <julie.sie...@.gmail.com> wrote:
>> Help! (again...)
>> I have data that looks like this:
>> date snum
>> 5/9/2007 3064
>> 5/9/2007 3072
>> 5/16/2007 4031
>> 5/16/2007 3856
>> 5/16/2007 4252
>> (Can recreate with
>> declare @.temptable table (date smalldatetime, snum int)
>> insert into @.temptable values('09 May 2007', 3064)
>> insert into @.temptable values('09 May 2007', 3072)
>> insert into @.temptable values('16 May 2007', 4031)
>> insert into @.temptable values('16 May 2007', 3856)
>> insert into @.temptable values('16 May 2007', 4252)
>> )
>> I need to make it look like this:
>> date snumlist
>> 5/9/2007 3064, 3072
>> 5/16/2007 4031, 3856, 4252
>> The "snumlist" is being plugged into a javascript function in a
>> vbscript.asp page. I can easily create the snumlist using while/wend
>> in vbscript but it seems quite slow! I'm wondering if it wouldn't be
>> faster to create the comma delimited list in SQL rather than try to
>> build it with VBScript.
>> (I can do anything to the temptable that I need to, structure-wise -
>> it's created on the fly.)
>> Thanks for any help, a pointer to a web page, a pointer to a previous
>> post, ANYthing.
>> Julie
>> (P.S. This email address forwards to my regular email address. Either
>> Google or my ISP often marks replies from this newsgroup as spam, so I
>> never see them. I still haven't gotten it sorted out. If possible,
>> please reply on list. Thanks, jcls)
>

Create a comma delimited *field*

Help! (again...)
I have data that looks like this:
date snum
5/9/2007 3064
5/9/2007 3072
5/16/2007 4031
5/16/2007 3856
5/16/2007 4252
(Can recreate with
declare @.temptable table (date smalldatetime, snum int)
insert into @.temptable values('09 May 2007', 3064)
insert into @.temptable values('09 May 2007', 3072)
insert into @.temptable values('16 May 2007', 4031)
insert into @.temptable values('16 May 2007', 3856)
insert into @.temptable values('16 May 2007', 4252)
)
I need to make it look like this:
date snumlist
5/9/2007 3064, 3072
5/16/2007 4031, 3856, 4252
The "snumlist" is being plugged into a javascript function in a
vbscript.asp page. I can easily create the snumlist using while/wend
in vbscript but it seems quite slow! I'm wondering if it wouldn't be
faster to create the comma delimited list in SQL rather than try to
build it with VBScript.
(I can do anything to the temptable that I need to, structure-wise -
it's created on the fly.)
Thanks for any help, a pointer to a web page, a pointer to a previous
post, ANYthing.
Julie
(P.S. This email address forwards to my regular email address. Either
Google or my ISP often marks replies from this newsgroup as spam, so I
never see them. I still haven't gotten it sorted out. If possible,
please reply on list. Thanks, jcls)Sorry - posted this in wrong group. Moving to .programming
Thx, Julie
On May 5, 4:42 pm, Julie <julie.sie...@.gmail.com> wrote:
> Help! (again...)
> I have data that looks like this:
> date snum
> 5/9/2007 3064
> 5/9/2007 3072
> 5/16/2007 4031
> 5/16/2007 3856
> 5/16/2007 4252
> (Can recreate with
> declare @.temptable table (date smalldatetime, snum int)
> insert into @.temptable values('09 May 2007', 3064)
> insert into @.temptable values('09 May 2007', 3072)
> insert into @.temptable values('16 May 2007', 4031)
> insert into @.temptable values('16 May 2007', 3856)
> insert into @.temptable values('16 May 2007', 4252)
> )
> I need to make it look like this:
> date snumlist
> 5/9/2007 3064, 3072
> 5/16/2007 4031, 3856, 4252
> The "snumlist" is being plugged into a javascript function in a
> vbscript.asp page. I can easily create the snumlist using while/wend
> in vbscript but it seems quite slow! I'm wondering if it wouldn't be
> faster to create the comma delimited list in SQL rather than try to
> build it with VBScript.
> (I can do anything to the temptable that I need to, structure-wise -
> it's created on the fly.)
> Thanks for any help, a pointer to a web page, a pointer to a previous
> post, ANYthing.
> Julie
> (P.S. This email address forwards to my regular email address. Either
> Google or my ISP often marks replies from this newsgroup as spam, so I
> never see them. I still haven't gotten it sorted out. If possible,
> please reply on list. Thanks, jcls)|||I'll tell you what they'll tell you over there - what you're trying to do
should be done in the front end. That aside, there are several T-SQL
scripts (some cleverer than others) posted in the .programming newsgroup to
do exactly what you're asking. When you ask over there, be sure to mention
whether or not the ordering of the items in the comma-separated list is
important. From your example it doesn't appear so, but you will probably
want to clarify that.
"Julie" <julie.siebel@.gmail.com> wrote in message
news:1178409829.540816.60210@.p77g2000hsh.googlegroups.com...
> Sorry - posted this in wrong group. Moving to .programming
> Thx, Julie
> On May 5, 4:42 pm, Julie <julie.sie...@.gmail.com> wrote:
>

Create a calculated field that gives me the avg 75 percentile

I'm trying to create a calculated field that gives me the avg 75 percentile.

Right now I get this value by doing the following:

Create data set:

Select top 75 percent <field>

from <table>

Then I create the following calculated field

Avg(Fields!<field>.value,"<data_set_name>")

But I wanted to be able to create a calculated field that gives me the avg 75 percentile without creating a separate data set to get the top 75 percent Value.

Is it possible?

Thanks!

Either you can use the existing dataset and have a filter on it (Edit Dataset -> Filters tab) with Top %

OR

Write a custom function in report code (Report -> Report Properties -> Code) that will take an array of values and returns avg of top 75% from that array. To do that just call the function in your textbox expression as:

Code.AvgFunction(Fields!YourField.Value)

When you just sent Fields!YourField.Value, a whole array of values in that fields will be sent and you can receive it in an object array in your function. Then use any of the most optimized logics to calculate top 75% from that array and then calculate the average using any of the vb functions.

Shyam

|||

Shyam,

Thanks for your posting...

I'm having problems displaying correct data using TOP 75 %

I use the following data set:

select top 75 percent <field>

from <table>

order by <field>

My report is grouped by month Jan 2007, Feb 2007, etc...

If I select one month date range, for instance february the TOP 75 % returns 474 rows and that's correct, however if my date range includes other months, for instance range from 01/01/2007 thtough 02/28/2007 the Top 75 % results increases for the month of february and January. It will return 490 rows for february and more rows for January too.

My report only returns correct data if I select one month range.

What am I doing wrong?

Thanks, Susan

|||

Sorry for a delayed reply. The solution for your problem is simple.

Edit your group in the report and in the Filters tab and under Expression type "=Fields!YourField.Value" and under Operator select Top % and under Value type 75.

Shyam

|||

Shyam,

Thanks for your response.

Adding the filter above didn't make a difference. It's returning the same results.

when I display data for the month of February I get the following:

@.Month--Count of records--Accept Top 75%

Feb 2007 --498--231.79

when I run for the month of February and January I get the following:

@.Month--Count of records--Accept Top 75%

Feb 2007--512--260.46

Jan 2007--565--309.43

and so fourth, here I added december:

@.Month--Count of records--Accept Top 75%

February-2007-- 508-- 252.29

January-2007-- 560-- 300.59

December-2006-- 488-- 258.64

I expected results for February to remain the same when add months to my view.

Thanks, Susan

|||Remove the TOP 75% clause from your SQL query and have them only in group filters.|||

Shyam,

I had tried that before but the Top% in the Filter tab of the group does't seem to work. Whem I remove the top 75 percent from the sql statement and just leave the Top% =75 filter it totally ignores the filter and it displays the total row count and total average instead.

Well, thanks for the help.... :-)

|||

Top 75% of feb month alone is always going to be different from top 75% of both jan and feb combined. So first of all, you have to remove the top 75% from your sql query. Then make sure you are adding the filter on the group on the same field which you had used in your query. Also, try to convert the field to string using CStr function in the filter expression.

Shyam

|||

Shyam,

Thanks for all your help so far.

I noticed that the filter works based on the row count of the group it's in. My Month group only returns on row, that's why the filter doesn't seem to work, because it's trying to return 75% of one row, which is one row. If I put the filter in the detail row of the report it works, but the problem is that I have to group the detail row before the filter can work, if I group the detail row I won't return all the detail data I need. Is there a way to make the month group filter look at the field of the detail row?

Also, in the report I have to use Bottom% =75 which is the equivalent of the SQL - top 75 percent.

|||

You can use any field from the dataset that is bound to the table for filyering in your group. Using the appropriate field for filtering also makes a big difference in the results obtained.

Shyam

Create a calculated field that gives me the avg 75 percentile

I'm trying to create a calculated field that gives me the avg 75 percentile.

Right now I get this value by doing the following:

Create data set:

Select top 75 percent <field>

from <table>

Then I create the following calculated field

Avg(Fields!<field>.value,"<data_set_name>")

But I wanted to be able to create a calculated field that gives me the avg 75 percentile without creating a separate data set to get the top 75 percent Value.

Is it possible?

Thanks!

Either you can use the existing dataset and have a filter on it (Edit Dataset -> Filters tab) with Top %

OR

Write a custom function in report code (Report -> Report Properties -> Code) that will take an array of values and returns avg of top 75% from that array. To do that just call the function in your textbox expression as:

Code.AvgFunction(Fields!YourField.Value)

When you just sent Fields!YourField.Value, a whole array of values in that fields will be sent and you can receive it in an object array in your function. Then use any of the most optimized logics to calculate top 75% from that array and then calculate the average using any of the vb functions.

Shyam

|||

Shyam,

Thanks for your posting...

I'm having problems displaying correct data using TOP 75 %

I use the following data set:

select top 75 percent <field>

from <table>

order by <field>

My report is grouped by month Jan 2007, Feb 2007, etc...

If I select one month date range, for instance february the TOP 75 % returns 474 rows and that's correct, however if my date range includes other months, for instance range from 01/01/2007 thtough 02/28/2007 the Top 75 % results increases for the month of february and January. It will return 490 rows for february and more rows for January too.

My report only returns correct data if I select one month range.

What am I doing wrong?

Thanks, Susan

|||

Sorry for a delayed reply. The solution for your problem is simple.

Edit your group in the report and in the Filters tab and under Expression type "=Fields!YourField.Value" and under Operator select Top % and under Value type 75.

Shyam

|||

Shyam,

Thanks for your response.

Adding the filter above didn't make a difference. It's returning the same results.

when I display data for the month of February I get the following:

@.Month--Count of records--Accept Top 75%

Feb 2007 --498--231.79

when I run for the month of February and January I get the following:

@.Month--Count of records--Accept Top 75%

Feb 2007--512--260.46

Jan 2007--565--309.43

and so fourth, here I added december:

@.Month--Count of records--Accept Top 75%

February-2007-- 508-- 252.29

January-2007-- 560-- 300.59

December-2006-- 488-- 258.64

I expected results for February to remain the same when add months to my view.

Thanks, Susan

|||Remove the TOP 75% clause from your SQL query and have them only in group filters.|||

Shyam,

I had tried that before but the Top% in the Filter tab of the group does't seem to work. Whem I remove the top 75 percent from the sql statement and just leave the Top% =75 filter it totally ignores the filter and it displays the total row count and total average instead.

Well, thanks for the help.... :-)

|||

Top 75% of feb month alone is always going to be different from top 75% of both jan and feb combined. So first of all, you have to remove the top 75% from your sql query. Then make sure you are adding the filter on the group on the same field which you had used in your query. Also, try to convert the field to string using CStr function in the filter expression.

Shyam

|||

Shyam,

Thanks for all your help so far.

I noticed that the filter works based on the row count of the group it's in. My Month group only returns on row, that's why the filter doesn't seem to work, because it's trying to return 75% of one row, which is one row. If I put the filter in the detail row of the report it works, but the problem is that I have to group the detail row before the filter can work, if I group the detail row I won't return all the detail data I need. Is there a way to make the month group filter look at the field of the detail row?

Also, in the report I have to use Bottom% =75 which is the equivalent of the SQL - top 75 percent.

|||

You can use any field from the dataset that is bound to the table for filyering in your group. Using the appropriate field for filtering also makes a big difference in the results obtained.

Shyam

sql

Crazy selection help

Hi everyone -

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

you folks are the best!

thank you x10000

Cr9.0

in my pageheader , got country and cityname.
below it, is a section that has district as a group.
below it, is detail section with game field in it.
query:
select country, CityName, district,
case when (population > 200) then game else null end
from country, province, city
group CityName, district
my problem is how to suppress the page header when game is null.You can conditionaly suppress your Page Header (or a PH section).
In the Section Expert, check the Suppress option for your PageHeader, click the 'x+2' button and print such a formula:

Count({table.game},{table.district})=0

CR9 - Link String field to Number Field

Hey,

I have two tables used within a report one therough btrieve and the other odbc.

Within each table there is a field called {emp.no}. However, one field is located within a Jobshop database and is a string field and the other is located within a TimeLOG database and is a number field.

Is there anyway of linking these fieds within crystal? I dont belive its possible with the Database Expert. But maybe elsewhere?

The report feeds through from the Jobshop (string) field and I need to match records within the TimeLOG (number) database to those within Jobshop based on emp.no and a date field.

Any help much appreciated.

Regards

RobSee the help on the ToText and ToNumber functions.
You should be able to use these in the Record Selection formula.

CR/LF in Comment field in emails sent using DataDrivenSubscriptions

I am setting up a data driven subscription to send out an email which includes a report. The Sql for the data driven subscription outputs the EmailTo, Subject, Comment(Body) and some report parameters. My issue is with the Comment (Body) field which I would like to format with CR/LF to make the email body look neat and readable. I've tried many different permutations of inserting the CR/LF, From concatenating Char(13) + Char(10) in which the email does recognize the Cr/LF to concatenating binary 0x0D + 0x0A which after the first concatenation of binary nothing else can be concatenated. Here is the code I've tried:

~~~~~~~~

declare @.iEndDate datetime
set @.iEndDate = dbo.fn_today()

Declare @.Account_group int
Set @.Account_group = 999

/*
DECLARE @.mybin1 binary(5), @.mybin2 binary(5)
SET @.mybin1 = 0x0D
SET @.mybin2 = 0x0A
*/

Declare @.CrLf VarChar(5)
set @.CrLf = CHAR(13) + CHAR(10)

Declare @.Str1 VarChar(255)
Declare @.Str2 VarChar(255)
Declare @.Str3 VarChar(255)
Declare @.Str4 VarChar(255)
Declare @.Str5 VarChar(255)
Declare @.Str6 VarChar(255)
Declare @.Str7 VarChar(255)
Declare @.Str8 VarChar(255)
Declare @.Str9 VarChar(255)
Set @.Str1 = 'Good Morning,'
Set @.Str2 = 'We are exposed by '
Set @.Str3 = 'Attached please find the margin call detail.'
Set @.Str4 = 'Please let us know if you have any questions.'
Set @.Str5 = 'Thank you.'
Set @.Str6 = 'Please respond to:'
Set @.Str7 = ''
Set @.Str8 = ''
Set @.Str9 = ''

declare @.t table
(
EmailTo VarChar(255),
ReplyTo VarChar(255),
Subject VarChar(255),
Comment Text,
Account_group Int,
Trading_Account_Id Int,
Broker_Code_Ky VarChar(255),
EndDate Datetime
)

insert into @.t
select
EmailTo = '', -- isnull(bf.firm_email,''),
ReplyTo = '',
Subject = 'Margin Call Notice: - ' + Trading_account_name + ' - Exposure: ' + '$' + Convert(varchar(20), convert(Money, Mark_amount),1) ,
Comment = @.Str1 + @.CrLf + @.CrLf + @.Str2 + '$' + Convert(varchar(20), convert(Money, Mark_amount),1) + '.' + @.CrLf + @.CrLf + @.Str3 + @.CrLf + @.CrLf + @.Str4 + @.CrLf + @.CrLf + @.Str5 + @.CrLf + @.CrLf + @.Str6 + @.CrLf + @.Str7+ @.CrLf + @.Str8 + @.CrLf + @.Str9,
Account_group = @.Account_group,
Trading_Account_Id = trading_account_Id,
Broker_Code_Ky = EGS.Broker_Code_Ky,
EndDate = @.iEndDate
from fni_ExposureGovernmentSummary (@.iEndDate) EGS

join broker_group bg
on EGS.broker_code_ky = bg.broker_code_ky

Join broker_firm bf
on bg.fbe_firm_id = bf.broker_firm_id
Order by
Broker_Group_Name,
Trading_account_Name

select top 2 * from @.t

--~~~~~~~~~~~~~

The Email my company is using is Lotus Notes.

The Question is how do I code the the Comment field so that my email will recognize the Cr/LF.

Thanks

Elias

Try swapping the values - use Char(10) & Char(13) rather than Char(13) & Char(10).

Thanks.

CR Ver 10 String to Memo Field

How can I convert a String Field to a Memo Field so I can display more then 255 characters on my report? I've tried the "Can Grow" option and it's not working.......After setting the can grow option, did you increase the field height?

CR Parameter field Question

Ok, so here's the thing:

I have a VB(6.0) project and a CR report in it. The report draws it's data from a stored procedure. Now, one of the fileds appearing on the report is called Code1, and in this particular case it has values 010, 100, 110 and 230. These numbers represent the item codes my company uses for something not important to us right now ... On the report, Code1 is located in the details section.

What I can't seem 2 figure out is this:

I made a parameter field called Selection. The value type I want to work with is a String. Code1 field also has the same value type. On the " Set default values" tab I entered earlier mentioned values ( 010, 100, 110, 230 ) and assigned description to them.

Now, when I use my parameter field " Selection " with the Select Expert, the formula looks like this: {spProSastavnicePrintQuery.Code1} = {?@.Selection}

The thing that troubles me is when I call upon my report, the entire Details section is EMPTY. BUT! When I use Select Expert and manualy enter

{spProSastavnicePrintQuery.Code1} = 100 for example, the report comes up the

way it's supposed to .. in the Details Section it generates only the data with the
Code1 containing the value of 100. What is the problem ?

Hope this post isn't too confusing .. Thnx in advance ..is it like this
{spProSastavnicePrintQuery.Code1} = {?@.Selection}

or

{spProSastavnicePrintQuery.Code1} = {?Selection}

?Parameter represent parameter and @.Parameter represent formula

CR 8.5: Help With Formula

HI
I have 5 Fields in the DB:
CON_AFM.AFM_DIABETES, CON_AFM.AFM_TENSION, CON_AFM.AFM_STRESS, CON_AFM.AFM_HIV and CON_AFM.AFM_TIROIDE
every field can have the value 1 or 0. Now i want to look for every field that have the value 1 and change to a string.

if CON_AFM.AFM_DIABETES value is 1 then the string to show is "Diabetes"
if CON_AFM.AFM_TENSION value is 1 then the string to show is "Hipertension"
if CON_AFM.AFM_STRESS value is 1 then the string to show is "Stress"
if CON_AFM.AFM_HIV value is 1 then the string to show is "HIV"
if CON_AFM.AFM_TIROIDE value is 1 then the string to show is "Tiroides"

But if the value is 0 i dont show a string

Example:

DB Fields with Values

CON_AFM.AFM_DIABETES 1
CON_AFM.AFM_TENSION 1
CON_AFM.AFM_STRESS 0
CON_AFM.AFM_HIV 0
CON_AFM.AFM_TIROIDE 1

The formula must return a string like
"Diabetes, Hipertension, Tiroides"Would you prefer to do this server side or in the Crystal Report itself? If in the Server side, what DB are you using?|||Thanks blanch1122, but a friend send me this and now works perfect

stringVar STR;
if {CON_AFM.AFM_DIABETES} = 1 then STR = "Diabetes" else STR = "";
if {CON_AFM.AFM_TENSION} = 1 then STR := STR + ", Hipertensin" else STR := STR;
if {CON_AFM.AFM_STRESS} = 1 then STR := STR + ", Stress" else STR := STR;
if {CON_AFM.AFM_HIV} = 1 then STR := STR + ", HIV" else STR := STR;
if {CON_AFM.AFM_TIROIDE} = 1 then STR := STR + ", Tiroides" else STR := STR;
// Esto elimina la coma si el primero es falso
if left(STR,1) ="," then STR := MID(STR,2,LEN(STR)-1);

Covered Index

Here's our scenario:
User inputs data into app such as fname, lname, birthday, etc. You can
search on only one field if you'd like.
Table has multiple indexes. It has an index on fname, lname, birthday, etc.
Index Tuning Wizard suggests combining the index into a covered index. While
that may help if the user searches on everything, doesn't that slow things
down if they only search on lname since the data isn't only lname but all of
the other stuff too?
I hope that makes sense. I am just trying to see the pros and cons of
taking the wizards advice and switching 4 non-clustered indexes to 1 covered
non-clusterd index.
Thanks
Some indexes can be used to cover multiple queries. For example, if your
index is on (lname, fname, bday), then it will support searches on:
lname
lname, fname
lname, fname, bday
lname, bday
As long as your search criteria includes the first column of a
multiple-column index, you have a shot at using the index. For complete
coverage of a query, though, all elements of the WHERE and SELECT clauses
must appear in the columns of the index. Otherwise, a bookmark lookup will
also be required.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:5DF43AE1-39DA-4021-B00F-4E1AEF0D82C6@.microsoft.com...
Here's our scenario:
User inputs data into app such as fname, lname, birthday, etc. You can
search on only one field if you'd like.
Table has multiple indexes. It has an index on fname, lname, birthday, etc.
Index Tuning Wizard suggests combining the index into a covered index. While
that may help if the user searches on everything, doesn't that slow things
down if they only search on lname since the data isn't only lname but all of
the other stuff too?
I hope that makes sense. I am just trying to see the pros and cons of
taking the wizards advice and switching 4 non-clustered indexes to 1 covered
non-clusterd index.
Thanks

Covered Index

Here's our scenario:
User inputs data into app such as fname, lname, birthday, etc. You can
search on only one field if you'd like.
Table has multiple indexes. It has an index on fname, lname, birthday, etc.
Index Tuning Wizard suggests combining the index into a covered index. While
that may help if the user searches on everything, doesn't that slow things
down if they only search on lname since the data isn't only lname but all of
the other stuff too?
I hope that makes sense. I am just trying to see the pros and cons of
taking the wizards advice and switching 4 non-clustered indexes to 1 covered
non-clusterd index.
ThanksSome indexes can be used to cover multiple queries. For example, if your
index is on (lname, fname, bday), then it will support searches on:
lname
lname, fname
lname, fname, bday
lname, bday
As long as your search criteria includes the first column of a
multiple-column index, you have a shot at using the index. For complete
coverage of a query, though, all elements of the WHERE and SELECT clauses
must appear in the columns of the index. Otherwise, a bookmark lookup will
also be required.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:5DF43AE1-39DA-4021-B00F-4E1AEF0D82C6@.microsoft.com...
Here's our scenario:
User inputs data into app such as fname, lname, birthday, etc. You can
search on only one field if you'd like.
Table has multiple indexes. It has an index on fname, lname, birthday, etc.
Index Tuning Wizard suggests combining the index into a covered index. While
that may help if the user searches on everything, doesn't that slow things
down if they only search on lname since the data isn't only lname but all of
the other stuff too?
I hope that makes sense. I am just trying to see the pros and cons of
taking the wizards advice and switching 4 non-clustered indexes to 1 covered
non-clusterd index.
Thanks

Covered Index

Here's our scenario:
User inputs data into app such as fname, lname, birthday, etc. You can
search on only one field if you'd like.
Table has multiple indexes. It has an index on fname, lname, birthday, etc.
Index Tuning Wizard suggests combining the index into a covered index. While
that may help if the user searches on everything, doesn't that slow things
down if they only search on lname since the data isn't only lname but all of
the other stuff too?
I hope that makes sense. I am just trying to see the pros and cons of
taking the wizards advice and switching 4 non-clustered indexes to 1 covered
non-clusterd index.
ThanksSome indexes can be used to cover multiple queries. For example, if your
index is on (lname, fname, bday), then it will support searches on:
lname
lname, fname
lname, fname, bday
lname, bday
As long as your search criteria includes the first column of a
multiple-column index, you have a shot at using the index. For complete
coverage of a query, though, all elements of the WHERE and SELECT clauses
must appear in the columns of the index. Otherwise, a bookmark lookup will
also be required.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:5DF43AE1-39DA-4021-B00F-4E1AEF0D82C6@.microsoft.com...
Here's our scenario:
User inputs data into app such as fname, lname, birthday, etc. You can
search on only one field if you'd like.
Table has multiple indexes. It has an index on fname, lname, birthday, etc.
Index Tuning Wizard suggests combining the index into a covered index. While
that may help if the user searches on everything, doesn't that slow things
down if they only search on lname since the data isn't only lname but all of
the other stuff too?
I hope that makes sense. I am just trying to see the pros and cons of
taking the wizards advice and switching 4 non-clustered indexes to 1 covered
non-clusterd index.
Thanks

Counting rows in a report

i have a report with 1 group and items under the group.

i want to add a new field called Sl. no. at the group level which keeps the count on groups...by that i mean say if there are 10 groups i want the number from 1 to 10 appear against each group as
1 Grp1
2 Grp2
3 Grp3
.
.
.
10 Grp10

How can i achieve the above. I tried the rownumber but it returns the number of rows the group has. The levels i have is "table1" the main scope and "table1_Group1" the group scope which in the table1 scope.

Any help will be appreciated.

what you are doing is similiar to what I wanted to do to get a generic counter... someone else had posted the below as an answer for me. This should work for you as well.

Put this in your code window:

Dim numGroupRow as Double

Function GetRowNumber() as Double
numGroupRow += 1
Return numGroupRow
End Function

Then put this in your group row textbox

= Code.GetRowNumber()

This will increment every group row. If you need to start over at some point the code will need to change.

|||thanks for ur reply.
It works as desired but i have some sorting defined on a field and i want to this sl.no field to move with the sort.

I will try to alter the code but if the solution is out there i would love to have it.

Thaks
|||

I don't think sorting should matter as this is a generic counter and is only incremented by 1 when you call it.

Since the sort acts before the fields are displayed, it shouldn't matter.

|||I thought so too but it is not sorting with the other fields. This is what happens the initial report comes as:

1....valA.......valB
2....valC.......valD
.
.
.
.
40...valE......valF
41...valG.....valH

now when i sort on first field the output comes as

1...valE......valF
2....valC.......valD
3....valA.......valB
.
.
.
.

41...valG.....valH

instead of
40...valE......valF
2....valC.......valD
1....valA.......valB
.
.
.
.

41...valG.....valH
|||

ah, I see.

That is becuase the counter is working as expected.

you will probobaly have to create a field on the dataset your self, add a new field and refernce the code in your field.

then place that field on your report and try the sort that way. You may have to play around with some grouping.

Counting Results in a field in SQL 2000

I've got a varchar field and I'm trying to count the whole field and obtain a total count for the number of entries in a particular field. Can someone please provide the syntax on how to display a count of all the results in a field?

Thanks

Quote:

Originally Posted by Alpenk

I've got a varchar field and I'm trying to count the whole field and obtain a total count for the number of entries in a particular field. Can someone please provide the syntax on how to display a count of all the results in a field?

Thanks

Please send an example of your table or tables

|||select count(column_name) from table_name

In this case you count all not null values in this column.
If you want to count distinct values in this colun do following:

Select count(distinct column_name) from table_name

Good Luck.