distinct query

Hi
I have a query as below:
select [transaction].[M_GIFTCARDNO], [T_GIFTCARDS].[M_SERIALNO],
[T_GIFTCARDS].[M_CARDNUMBER], [transaction].[req_login_time]
from [T_GIFTCARDS], [transaction]
where req_login_time between '5 October,2005' and '6 December, 2005'
and [T_GIFTCARDS].[M_CARDNUMBER]=[transaction].[M_GIFTCARDNO]
Explanation
[T_Giftcards].[M_Cardnumber] is a unique number,
[transaction].[M_GIFTCARDNO] is the matching filed in another table but this
transaction table has may hundreds of transactions per T_Giftcard number, Ho
w
can I just display 1 T_Giftcards number and one matching transaction'
Help much appreciatedIvo (Ivo@.discussions.microsoft.com) writes:
> I have a query as below:
> select [transaction].[M_GIFTCARDNO], [T_GIFTCARDS].[M_SERIALNO],
> [T_GIFTCARDS].[M_CARDNUMBER], [transaction].[req_login_time]
> from [T_GIFTCARDS], [transaction]
> where req_login_time between '5 October,2005' and '6 December, 2005'
> and [T_GIFTCARDS].[M_CARDNUMBER]=[transaction].[M_GIFTCARDNO]
> Explanation
> [T_Giftcards].[M_Cardnumber] is a unique number,
> [transaction].[M_GIFTCARDNO] is the matching filed in another table but
> this transaction table has may hundreds of transactions per T_Giftcard
> number, How can I just display 1 T_Giftcards number and one matching
> transaction'
select [T_GIFTCARDS].[M_SERIALNO], [T_GIFTCARDS].[M_CARDNUMBER],
MIN([transaction].[req_login_time])
from [T_GIFTCARDS], [transaction]
where req_login_time between '5 October,2005' and '6 December, 2005'
and [T_GIFTCARDS].[M_CARDNUMBER]=[transaction].[M_GIFTCARDNO]
GRUOP BY [T_GIFTCARDS].[M_CARDNUMBER], [T_GIFTCARDS].[M_SERIALNO]
Or use MAX() if you think that is better.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

DISTINCT not returning data in sorted order after specific no. of

Hi
I have the problem with the DISTINCT keyword. I used the following statement
to get the distinct values from the table:
SELECT DISTINCT trade_name FROM Customer
This works absolutely perfect getting the distinct values and getting in
sorted order. But my requirement needs the statement to be as :
SELECT DISTINCT RTRIM(trade_name) FROM Customer
With the above statement, it works fine only if the table contains few 100
of records. My table contains around 6000+ records and the above statement
returns DISTINCTINCT values but does not get them in sorted order.
The same statement works fine if the there are around say 500+ records. I
see that the results displayed are distinct and sorted as well.
The field in question (trade_name) of 30 chars (char(30))
Why is this happening.Without ORDER BY the sort order is undefined for any SQL query. Add
ORDER BY:
SELECT DISTINCT trade_name
FROM Customer
ORDER BY trade_name
SELECT DISTINCT RTRIM(trade_name)
FROM Customer
ORDER BY RTRIM(trade_name)
David Portas
SQL Server MVP
--

Distinct issue

Hi

I wanna write a proc that returns Distinct CustomerID's in a table and returns result in output parameter

When i try this, i get error - incorrect syntax near distinct. Any ideas??

ALTER PROCEDURE proc_Report_CountCustomers_Sept
(
@.CustCount int OUTPUT
)
AS
SET NOCOUNT ON
select
@.CustCount = distinct(CustomerID)
from
Orders
Where
OrderDate > '2006-09-01' and OrderDate < '2006-10-01'

hi,

what you want to return, count of distinct ids or distinct ids itself ?

regards,

satish

|||

hehe.. er ofcourse. I wanted to count the distinct ids, but i didnt count

i worked it out

cheers brother

ALTER PROCEDURE proc_Report_CountCustomers_Sept
(
@.CustCount int OUTPUT
)
AS
SET NOCOUNT ON
SELECT
@.CustCount =Count(DISTINCT(CustomerID))
FROM
Orders
WHERE
OrderDate > '2006-09-01' and OrderDate < '2006-10-01'

|||

good you got it working :),

cheers ,

satish

distinct count

Hi

I have a table which stores the shift information for employees. The table contains 10 columns as Employeename,Employeeno,month,year,shifttimings etc. If an employee works a day in a particular shift, then a row will be inserted in to the above table for that employee.

Now at the end of the month i wanted to calculate the shift details for each employee for a particular month of a given year like employeename,employeeno, noofdays(countof shiftdays).

Can some body help?

Thanks in Advance!

Santhosh

Select

Employee.EmployeeName,

Employee.EmployeeNo,

|||

Select EmployeeName, EmployeeNo, Count(*) As NoOfDays

From EmployeeShift

Where Month = @.Month And Year = @.Year

Group By EmployeeName, EmployeeNo

|||

You could create a view as

Select EmployeeName, EmployeeNo, Month, Year, Count(*) As NoOfDays

From EmployeeShift

Group By EmployeeName, EmployeeNo, Month, Year

and apply Where to it...

sql

Displaying distinct record

Hi
I have two tables, linked together in my report. Report_Master is the master table and Report_Details is the detail table. The following is the SQL Query (I right click on Database Field in the Field Explorer and select Show SQL Query)
SELECT `Report_Master`.`Rpt_No`, `Report_Master`.`Rpt_Date`, `Report_Master`.`Rpt_Supervisor`,
`Report_Details`.`Rpt_Description`, `Report_Details`.`Rpt_Note`
FROM `Report_Details` `Report_Details`
INNER JOIN `Report_Master` `Report_Master`
ON `Report_Details`.`Rpt_No`=`Report_Master`.`Rpt_No`
I'd like to display all this like a normal report where the report will start with the name of the person writing the report, the date and followed by the description of the report. I dont find any problem when generating a report with only one record of description (from the Report_Details) because it will look just like what I wanted. When there are more than one description of a certain date in the details table, say three records, whatever in the master table will be displayed 3 times. How to correct this problem?
Thank you in advanceYou need to add a grouping to your report from one of the fields in the master table.

Linda|||I did.
I right clicked on Group Name Field and selected Report_Master.Rpt_No as the field to group but it shows me the same output. Where did i do wrong?|||Did you put the field(s) you don't want repeated in the group header line and remove them from the detail line in the report?

Displaying a subreport in main report at runtime

Hi

I have a report with a subreport within it. When I look at in the preview facility, the data from the subreport is displayed within the main report. At runtime, the sub report is not visible and has to be opened in a separate window. How can I display it in the main report at runtime?

Thanks.I've found the answer to this one. If anyone is interested, please see:

http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do?cmd=displayKC&docType=kc&externalId=c2007600&sliceId=&dialogID=6924658&stateId=1%200%206926126

displaying % in value

Hi

I am calculating percentage using expression. I want to display % at the end of the result.

If I use P1 or P0 somehow it multiplies the result with 100 I guess.

Here is what my calculated value is

15.384515

and I want to display 15.38%

Using P1 or P0 I am getting 1,538.5%

How can I get the desired result?

Any help would be appreciated.

Regards

Amit

Yeah I always thought that was funny the way RS handles formating %.

You can try dividing your value by 100 and them formating it. I think the percent format requires a number between 0 and 1

e.d =format((field.value)/100,"P0")

that should give you "15%"

|||

You are great Nialhannon.

Thanks for your help. Your solution helped.

Regards

Amit

|||

I remember those issues, and I ended up doing the /100 in the dataset (or Stored Procs)

display short datetime

Hi

I need to set the default value of an "End Date" parameter to the date of today.
Now I use the expression =Now(), but then the timestamp is also displayed. That I don't want to happen!

I tried formatting the datetime but then it becomes a string, and converting it back to a date leaves also a timestamp but with midnight time.

Is there a way to format a date parameter?

Hi,

Did you try : Format(mydate.Value,"dd/MM/yyyy") or Cdate(Format(mydate.Value,"dd/MM/yyyy").ToString) ?

Regards

Ayzan

|||

Both expressions above don't work because my parameter is of the type datetime and the expressions return a string. So I get an error that the parameter has another type than it expected.

Still thanks for pointing me the CDate function. It has alot of functions and that's the function that got me the wanted result.

To display only the date part of a datetime value retrieved by the function Now() you need the following expression:
=CDate(Now()).Today

Display rows as columns

Hi
i have a table like
Id , ColumnName group
1 C1 1
2 C2 1
3 C3 2
these columns are mapped to another table like
Id C1 C2 C3....
so the columns tht must be selected from the transaction table differs
according to the group in the mapping table.
so can i write a query to get the data from transaction table for a group
in mapping table.
I DONT WANT TO USE DYNAMICALLY CREATED QUERY..
plz send the solution as the client is waiting
RenjithHi Renjith
Probably you can use CASE statement for this. This is similar to DECODE
statement available in Oracle.
Please post the expected result so that we can give u a solution
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Renjith" wrote:

> Hi
> i have a table like
> Id , ColumnName group
> 1 C1 1
> 2 C2 1
> 3 C3 2
> these columns are mapped to another table like
> Id C1 C2 C3....
> so the columns tht must be selected from the transaction table differs
> according to the group in the mapping table.
> so can i write a query to get the data from transaction table for a group
> in mapping table.
> I DONT WANT TO USE DYNAMICALLY CREATED QUERY..
> plz send the solution as the client is waiting
> Renjith
>|||> Id , ColumnName group
> 1 C1 1
> 2 C2 1
> 3 C3 2
> these columns are mapped to another table like
> Id C1 C2 C3....
Sounds like a cross-tab / pivot.
http://www.aspfaq.com/2462

> plz send the solution as the client is waiting
So why is the client paying YOU? I wonder what the client would think if
they saw your post and recognized you as their consultant?|||Hi
i need the result as
when i select group = 1
Id C1 C2
the value of c1 , c2 will be transaction table
when i select group = 2
Id C3
the value of C3 will be transaction table
Renjith
"Renjith" wrote:

> Hi
> i have a table like
> Id , ColumnName group
> 1 C1 1
> 2 C2 1
> 3 C3 2
> these columns are mapped to another table like
> Id C1 C2 C3....
> so the columns tht must be selected from the transaction table differs
> according to the group in the mapping table.
> so can i write a query to get the data from transaction table for a group
> in mapping table.
> I DONT WANT TO USE DYNAMICALLY CREATED QUERY..
> plz send the solution as the client is waiting
> Renjith
>|||> i need the result as
> when i select group = 1
> Id C1 C2
> the value of c1 , c2 will be transaction table
> when i select group = 2
> Id C3
> the value of C3 will be transaction table
http://www.aspfaq.com/5006

Display Result in Page Header

Hi!

I've got following problem: I got a formula evaluated whileprintingrecords in page footer. It writes in a global variable the highest number on the page. When I display this variable in page footer it works fine. But I want it to appear on the page header, too! When I display it in page header, it is the old value. Of course, because the variable is written on the end of page.

Is there a possibility to say the page header should be written AFTER the Page Footer? Or that the variable shall be displayed AFTER the page footer?
I tried the EvaluateAfter() function but it didn't work. It just writes the previous value on the top of following page.

I would be glad about some hints.

Greeting and thanks,
MarkusCongratulations, you've run into one of the most unpleasant "features" of Crystal Reports - inability to display running totals in group or page headers. In fact, there are workarounds for squeezing group-level running totals into group headers (by using external views or crosstabs), but for page-level totals, I think you are out of luck.

The reason is, as you correctly mentioned, that pages are formed and all running totals are calculated AND PRINTED during pass 2, but to put the running totals into headers, pass 3 would be required. There is, in fact, pass 3, but it will not calculate anything except page count.|||Thank you for your answer. Although it's not the one I wanted to hear... :-(

Perhaps there is a possibility to locate the field at an absolute position? So I can say: "Okay, you're evaluated in page footer, but displayed 2cm away from top of page"... But I think, this is not possible, too. I've seen that the position relates on section, therefore is no absolute one...

Sad, that CR is far behind freeware tools in this topic... Had one where you could set the evaluation and print time for each field...

display problem

Hi

I m using CR 11.0.

In my report, I want to display the page header details only on the pages where the group header is displayed.

I cannot put the page header details also on the group header details, because it has to be displayed on the top of the page where the group details are displayed. And also I cannot give page break option for hew group.

Please help !!!

Thanking in advancePut the group header on every page :)
or suppress the page header section if it's not a new group.
e.g. not (onfirstrecord or previous(group field) <> group field)