Count on text fields

Hello, I works with the reporting services and is thereby reports to
providing. Now the question arises whether one can count into the
reporting services (in the report designer) also on text fields.
Super would be nice if me one help could.You need to clarify the question a little more.
Do you mean a SUM line, or just a text box that has a summation in it? etc
"TR" wrote:
> Hello, I works with the reporting services and is thereby reports to
> providing. Now the question arises whether one can count into the
> reporting services (in the report designer) also on text fields.
> Super would be nice if me one help could.
>

Count of two fields with reference to someother field

Hi,

I have three fields...field1,field2 and field3.
Data fed into field1 and field2 is numeric and
data fed into field3 is string.

I need to count the number of times each of fieild1 and field2 is fed when field3 is equal to "BA Test". Likewise, I have couple of values defined for field3...I want sum of count of field1 and field2.

In other words, I can call it as conditional counting.

Thanks in advance.

Cheers
Sreenath NookalaIn SQL try:

Select field3, count(field1) field1, count(field2) field2
from
{table}
group by field3

You can then use a function to sum count of field1 and field2|||Actually, I am pulling data from Rational ClearQuest and creating a report in Crystal Reports. MS Access is one of the databases supported by Rational ClearQuest.

I am not sure how SQL commands work in MS Access.

Can we tell me how to do in GUI mode in Crystal Reports.

Thanks
Sreenath Nookala|||In Access write a query and save it. When designing the report, design it based on that query|||well u can use a variable to store count value .. means when ever u get condition true then increment this variable with one ... in this u can get count of flds ...
e.g

numbervar countFld1;
if fld3 = 'BA Test' then
countFld1 = countFld1 + 1

then u can display this variable ... u will have to reset ur variable if want to recalculate against each group ... by using

numbervar countFld1 := 0;

this may solve ur poblem upto the query i understand ...sql

Count of is null of datetime field

Hi All,

Im having a problem with count of null values fields and indexes...

I have a table (tb_Propose) with around 8 million lines.

A field Dt_flag -- Datetime

An index Ix_Dt_flag, nonclustered, with field Dt_flag, only

When I do the "select count(*) from tb_Propose where Dt_Flag is null", the results comes so different from real... When I do "reindex", on first time, the select works fine. However, from the second execution the results coming wrong. The database is with option "Auto Update Stats" enabled.

Results:

select count(*) from tb_Propose where Dt_flag is null
select count(*) from tb_Propose where Dt_flag is not null
select count(*) from tb_Propose

----
8405710

(1 row(s) affected)

----
3818428

(1 row(s) affected)

----
8978255

(1 row(s) affected)

[]s
Carlos Eduardo
Bizplace
www.bizplace.com.brThat is really strange. Perhaps you could build a simple test case that exhibits this behavior and post the code so I can try it out on my system?

Count if Conditions Met

I am trying to do a summary SQL query. I have 3 fields. If one filed is
null and the other is not null, I want to count how many records there
are. I also want to count the opposite way then count both fields if
they are both not null. Can I do this within the same query? Help
appreciated.

Thanks,
Steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You could use a CASE expression inside SUM/COUNT etc. Here's an example:

CREATE TABLE #x(i int, j int)
INSERT #x SELECT NULL, 1
INSERT #x SELECT 1, 2
INSERT #x SELECT 2, 3
INSERT #x SELECT NULL, 4
INSERT #x SELECT NULL, 5

SELECT SUM(CASE WHEN i IS NULL THEN 0 ELSE j END) FROM #x

Next time, please post your table structures and some sample data, along
with your desired output.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/

"Steve Bishop" <steveb@.viper.com> wrote in message
news:411a5f99$0$14487$c397aba@.news.newsgroups.ws.. .
> I am trying to do a summary SQL query. I have 3 fields. If one filed is
> null and the other is not null, I want to count how many records there
> are. I also want to count the opposite way then count both fields if
> they are both not null. Can I do this within the same query? Help
> appreciated.
> Thanks,
> Steve
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You could use a CASE expression inside SUM/COUNT etc. Here's an example:

CREATE TABLE #x(i int, j int)
INSERT #x SELECT NULL, 1
INSERT #x SELECT 1, 2
INSERT #x SELECT 2, 3
INSERT #x SELECT NULL, 4
INSERT #x SELECT NULL, 5

SELECT SUM(CASE WHEN i IS NULL THEN 0 ELSE j END) FROM #x

Next time, please post your table structures and some sample data, along
with your desired output.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/

"Steve Bishop" <steveb@.viper.com> wrote in message
news:411a5f99$0$14487$c397aba@.news.newsgroups.ws.. .
> I am trying to do a summary SQL query. I have 3 fields. If one filed is
> null and the other is not null, I want to count how many records there
> are. I also want to count the opposite way then count both fields if
> they are both not null. Can I do this within the same query? Help
> appreciated.
> Thanks,
> Steve
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Count Fonts?

Does anyone know of a way in which colors/fonts can be counted? I have a report with numerous fields in the detail section that have been conditionally formatted & would like to count how many reds/greens/yellows appear.
I am well & truly stumped.When you conditional change the color, add a vlue to to other varialbes to keep track thatsql

count fields of a table

How can I find out how many columns in a table ?
Thanksselect * from information_schema.columns where table_name = 'mytable'
or
select count(*) from information_schema.columns where table_name = 'mytable'
hth.
Mark
www.dovetaildatabases.com
"Catelin wang" <cwang@.cemstone.com> wrote in message
news:uEcty0a%23EHA.1604@.TK2MSFTNGP12.phx.gbl...
> How can I find out how many columns in a table ?
> Thanks
>|||Thanks.
"Mark" <field027@.idonotlikejunkmail.umn.edu> wrote in message
news:uEZqZ5a%23EHA.2452@.TK2MSFTNGP14.phx.gbl...
> select * from information_schema.columns where table_name = 'mytable'
> or
> select count(*) from information_schema.columns where table_name =
'mytable'
> hth.
> Mark
> www.dovetaildatabases.com
> "Catelin wang" <cwang@.cemstone.com> wrote in message
> news:uEcty0a%23EHA.1604@.TK2MSFTNGP12.phx.gbl...
>

count fields of a table

How can I find out how many columns in a table ?
Thanks
select * from information_schema.columns where table_name = 'mytable'
or
select count(*) from information_schema.columns where table_name = 'mytable'
hth.
Mark
www.dovetaildatabases.com
"Catelin wang" <cwang@.cemstone.com> wrote in message
news:uEcty0a%23EHA.1604@.TK2MSFTNGP12.phx.gbl...
> How can I find out how many columns in a table ?
> Thanks
>
|||Thanks.
"Mark" <field027@.idonotlikejunkmail.umn.edu> wrote in message
news:uEZqZ5a%23EHA.2452@.TK2MSFTNGP14.phx.gbl...
> select * from information_schema.columns where table_name = 'mytable'
> or
> select count(*) from information_schema.columns where table_name =
'mytable'
> hth.
> Mark
> www.dovetaildatabases.com
> "Catelin wang" <cwang@.cemstone.com> wrote in message
> news:uEcty0a%23EHA.1604@.TK2MSFTNGP12.phx.gbl...
>

count fields of a table

How can I find out how many columns in a table ?
Thanksselect * from information_schema.columns where table_name = 'mytable'
or
select count(*) from information_schema.columns where table_name = 'mytable'
hth.
Mark
www.dovetaildatabases.com
"Catelin wang" <cwang@.cemstone.com> wrote in message
news:uEcty0a%23EHA.1604@.TK2MSFTNGP12.phx.gbl...
> How can I find out how many columns in a table ?
> Thanks
>|||Thanks.
"Mark" <field027@.idonotlikejunkmail.umn.edu> wrote in message
news:uEZqZ5a%23EHA.2452@.TK2MSFTNGP14.phx.gbl...
> select * from information_schema.columns where table_name = 'mytable'
> or
> select count(*) from information_schema.columns where table_name ='mytable'
> hth.
> Mark
> www.dovetaildatabases.com
> "Catelin wang" <cwang@.cemstone.com> wrote in message
> news:uEcty0a%23EHA.1604@.TK2MSFTNGP12.phx.gbl...
> > How can I find out how many columns in a table ?
> >
> > Thanks
> >
> >
>

Count Changing due to 2 values

why when I leave out the Amount fields, does my query stand correct at 15 records? When I add those 2 amouns (dc.amount and p.amount) back in it for some reason adds 3 more unwanted records and the count is then 18?

SELECT m.customer,
c.name,
c.customer,
c.state,
/*(SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code)
AS FeeSchedule, */
m.Branch,
CASE WHEN ph.batchtype = 'PUR' OR ph.batchtype = 'PAR' OR ph.batchtype = 'PCR' OR ph.batchtype = 'DUR' OR ph.batchtype = 'DAR' Then
(-ph.totalpaid + ph.ForwardeeFee)
WHEN ph.batchtype = 'PU' OR ph.batchtype = 'PC' OR ph.batchtype = 'PA' OR ph.batchtype = 'DC' OR ph.batchtype = 'DA' Then
(ph.totalpaid + ph.ForwardeeFee)
END AS [Posted Amount],
ph.systemmonth,
ph.systemyear,
ph.datepaid,
ph.totalpaid,
ph.batchtype,
m.desk,
0 AS [New Old CC],
0 AS [New Old PDC],
'In-House' AS Type,
1 AS Active,
ph.UID,
m.number,
dc.amount CC,
--p.amount AS PDC,
m.original,
CONVERT(money, ph.OverPaidAmt),
0,
0,
''
FROM dbo.Master m (NOLOCK) INNER JOIN dbo.payhistory ph ON m.number = ph.number
LEFT JOIN dbo.DebtorCreditCards dc ON dc.number = m.number
LEFT JOIN dbo.pdc p ON p.number = m.number
LEFT JOIN dbo.Customer c ON c.Customer = m.Customer

GROUP BY m.customer,
c.name,
c.customer,
c.state,
c.FeeSchedule,
m.Branch,
ph.OverPaidAmt,
ph.systemmonth,
ph.systemyear,
ph.datepaid,
ph.totalpaid,
ph.batchtype,
m.desk,
ph.UID,
m.number,
--dc.amount,
--p.amount,
m.original ,
ph.systemmonth,
ph.systemyear,
ph.ForwardeeFee

HAVING ph.systemmonth = 12 AND ph.systemyear = 2005
AND m.customer = '0000002'AND m.branch = '00002'

ORDER BY m.customer, batchtype

If you have different values, then this could imply different groups and thus increase the number of rows returned from the query.

Conor

'could not be converted because of a potential loss of data'

I have a FoxPro dbf that includes From Milepost (f_mp) and To Milepost (t_mp) fields. These fields contain values between -1 and 9999.9999.

I don't have FoxPro installed, but when I attach the dbf to Access, I see the fields defined as datatype Double.

I have a SQL table that I'm trying to import the dbf data into. In that table the two fields are defined as datatype Real.

When I execute the task, it fails at the first milepost value with 4 digits to the left of the decimal point.

I read up on datatypes, then redefined the milepost fields as Floats, but nothing changed.

Any ideas or suggestions would be greatly appreciated.

ginnyk

Which component is givng the error message? If it's the OLE DB Source adapter, you can change its output column data type for the milepost fields that would allow a lossless conversion. If you can't find a suitable type, as a last resort, you could try converting the data to string.|||

Ted,

The full error text is:

"[SQL DcsEx [574]] Error: There was an error with input column "t_mp" (646) on input "OLE DB Destination Input" (587). The column status returned was: "The value could not be converted because of a potential loss of data.".

No matter which FoxPro Ex source dbf I use, the task fails at the destination input, on the first record that has a number greater than or equal to 1000...specifically - 1022, 1341.76 and 1002.849. The 4 digits left of the decimal in the erroring field is the only thing the 3 records seem to have in common.

I have other tasks in the same package that convert Fox doubles to SQL real or float numbers, greater than 1000, with no trouble. I am completely stumped and don't know where to look next.

|||

Could you give us more information on what your package actually does with this data. Do you only copy or have some transforms as well? Take a look at data types used in SSIS components for the "t_mp" column.

Thanks.

Cost of Comparing Dates, ignoring Time part

Hi, i would like to know what is better when trying to compare two data fields one of them has the format yyyymmdd and then the another is yyyymmdd hh:mms

I need to ignore the time part but when I use this ...

where Convert(CHAR(8),Year(date)*10000+ Month(date)*100+Day(date))
between @.date_ini and @.date_fin

I have also tried this....

where convert(varchar(12), date, 112 )
between @.date_ini and @.date_fin

@.date_ini and @.date_fin are varchar '20070301'

The proble is that the time to execute the query is exagerated

would you pleas help me?

Try:

where date >= @.date_ini
and date < convert(datetime, @.date_fin) + 1

The performance of your query should be improved if you have an index on your "date" field. Also, I have assumed that you have stoerd your field as an SQL datetime field and made the assumption that when you say that you wat @.date_ini and @.date_fin to be the same date that you are wanting all records on that date to be selected.

I do not use the BETWEEN operator because BETWEEN would also select data that occurs exactly on midnight of the next day.

|||

Thank you Kent Waldrop

It works better