Count records when RecordSource is a Stored Procedure

I have a stored procedure named mySP that looks basically like this:
Select Field1, Field2
From tblMyTable
Where Field 3 = 'xyz'

What I do is to populate an Access form:
DoCmd.Openform "frmMyFormName"
Forms!myFormName.RecordSource = "mySP"

What I want to do in VBA is to open frmContinuous(a datasheet form) if
mySP returns more than one record or open frmDetail if mySP returns
only one record.

I'm stumped as to how to accomplish this, without running mySP twice:
once to count it and once to use it as a recordsource.
Thanks,
lq"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404260611.33a36012@.posting.google.c om...
> I have a stored procedure named mySP that looks basically like this:
> Select Field1, Field2
> From tblMyTable
> Where Field 3 = 'xyz'
> What I do is to populate an Access form:
> DoCmd.Openform "frmMyFormName"
> Forms!myFormName.RecordSource = "mySP"
> What I want to do in VBA is to open frmContinuous(a datasheet form) if
> mySP returns more than one record or open frmDetail if mySP returns
> only one record.
> I'm stumped as to how to accomplish this, without running mySP twice:
> once to count it and once to use it as a recordsource.
> Thanks,
> lq

I don't know much about VBA, but if you return the results of the procedure
in an ADO RecordSet object, then you should be able to count the rows on the
client side, and format the data accordingly. You might get a better answer
in an Access newsgroup, though.

Simon

count records using a max value in another field

Please help. I am struggling.

table

cards year org

Q123 Q MO

Q236 Q MO

Q569 Q MO

R456 R MO

Q789 Q MO

R482 R MO

First I need to find the max value of the year field then use that max value to count the cards associated with that max value where org is a parameter from the user.

So the result would be 2. I need a SQL query to give me these results.

R year is the max value and there are two of those records. I hope this makes sense.

Something like this (works in SQL 2000 and SQL 2005):

Code Snippet

SELECT TOP 1

Year,

Count = count( Year )

FROM MyTable

GROUP BY Year

ORDER BY Year DESC

|||

Code Snippet

SELECT max(activeyear)AS activeyear, COUNT(cardnum) AS cardCnt
FROM camcards

WHERE org = @.org)

This is my original query but it gives me the count for all the years regardless of my max(year). The user would be entering the value in for org. I should get back one line so there is not need to to have an order by.

so in my original message the result should be

activeyear = R and cardCnt = 2

I am sorry Arnie, but your suggestion does not work or make sense to me. I don't think you can put an equal sign in the SELECT part of the statement.

|||

Try:

select a.year, count(*)

from dbo.t1 as a inner join (select c.org, max(c.year) from dbo.t1 as c where c.org = @.org group by org) as b

on a.org = b.org and a.year = b.year

group by a.year

go

select year, count(*)

from dbo.t1

where org = @.org and year = (select max(b.year) from dbo.t1 as b where org = @.org)

order by year

go

select top 1 year, count(*)

from dbo.t1

where org = @.org

gropu by year

order by year desc

go

AMB

|||

Select top 1 year, (SELECT count(1) from table where year = t.yearr and org = t.org)

from table t

where org = 'mo'

order by year desc

|||

Actually, Theresa, the code I provided you does indeed work. I understand that you my not yet have the exprience to follow how it works, and I'll try to help. I've even added the extra WHERE clause to filter for the Org by user input.

Below is demonstration code that creates a sample table (named @.MyTable), and then populates the table with your data (I tossed in a couple of extra rows to verify that they would not be included in the result.) I suggest that you copy and paste code into your query window and execute it to verify that it works, THEN change the code, substituting the table name for your tablename.

I don't think you can put an equal sign in the SELECT part of the statement.

You are a little 'confused' about using equals signs in a SELECT statement, and that is just from inexperience. Actually, there are a couple of ways that one may choose to use equals signs in the SELECT. First, to assign a column value to a variable (e.g., SELECT @.ClientID = ClientID FROM ... ), and also to assign an 'alias' for a 'missing' column name or to rename a column in the resultset (e.g, Count = count( Year ) ). Without the 'alias', the column representing the aggragate value COUNT() would not have a name. That is often OK, and sometimes for illustration purposes, it is useful.

At the bottom is the actual results from executing all of this sample code in my query window. I think that matches what you said that you wanted.

As you progress in your learning SQL Server, you will discover that it is considered a big faux paux to use reserved words as table and column names (e.g., Year). When you do that you may 'confuse' matters, so to prevent such 'confusion', it is necessary to use square brackets ( [ ] ) around those inappropiately named objects. You may wish to refer to Books Online, Topic: Reserved Words.

I hope that this helps you, and good luck with your travels down the SQL Server 'path'.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
Cards varchar(10),
[Year] char(1),
Org char(2)
)

INSERT INTO @.MyTable VALUES ( 'Q123', 'Q', 'MO' )
INSERT INTO @.MyTable VALUES ( 'Q236', 'Q', 'MO' )
INSERT INTO @.MyTable VALUES ( 'Q569', 'Q', 'MO' )
INSERT INTO @.MyTable VALUES ( 'R456', 'R', 'MO' )
INSERT INTO @.MyTable VALUES ( 'Q789', 'Q', 'MO' )
INSERT INTO @.MyTable VALUES ( 'R482', 'R', 'MO' )
INSERT INTO @.MyTable VALUES ( 'ABC1', 'S', 'NO' )
INSERT INTO @.MyTable VALUES ( 'XYZ2', 'T', 'NO' )

DECLARE @.Org char(2)
SET @.Org = 'MO'

SELECT TOP 1
ActiveYear = [Year],
CardCnt = count( [Year] )
FROM @.MyTable
WHERE Org = @.Org
GROUP BY [Year]
ORDER BY [Year] DESC

ActiveYear CardCnt
- --
R 2

|||Thanks to all who have replied to thread.

Count records manually

Hi everyone, I'm a newbie with this Crystal Reports thing and I was wondering if there is anyone who can help me.

My table has a field named obsolete, depending on the type of "obsolete" I would like to count the number of ocurrencies in my table.

I know I could do this by grouping the field but, I need to group with other 4 fields and I've tried it like that but I guess that the only way that I'll be able to retrieve the data the way I want it is manually. So, is there any if statement or while statement that I could use?

Thanx for any repliesI guess you could try to use if statements in suppressed formulas (look up information on running totals in CR).

I had a similar requirement and found it easier to decode the values in my view (I'm using Oracle PL/SQL).

decode(classId,1,trafficcount,0) class_1,
decode(classId,2,trafficcount,0) class_2,

Ie. If the classId is 1, put the trafficcount in column called class_1, else put 0.|||Thanx for your reply kristyw. Im using informix, I'm not quite sure I can use the decode function, or is it a CR function?

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 records greater than?

Hello All,

Trying to set up a column in a grouped matrix that displays a count of all record over a specificed number.

The field I am counting are response time of transaction and I want to count how many were over 500 milliseconds. I though it would be something like this...

Code Snippet

=Count(Fields!ResponseTime.Value > "500")

However, this appears to just return the count of all rows and ignores the "500" part.

Am I missing something? If someone could post a alternate code snippet, that would be great.

Thanks in advance,

Clint

Hi Clint,

Try this expression

Code Snippet

=Count(iif (Fields!ResponseTime.Value > 500,1,nothing))

Best Regards,

Rajiv

|||

Thanks that works well..as an after thought, I need to add something in that would separate on of the field that has a different threshhold. Its grouped up and all but one has the 500 threshold count but one has 1000. Any ideas on how to separate it out?

I was thinking

Code Snippet

=Count(iif (Fields!ResponseTime.Value="QMEN" > 1000,1,nothing)) or Count(iif (Fields!ResponseTime.Value > 500,1,nothing))

But that errored out.|||

Need a second iff. Try just wrapping it around the timeout value

(I can't see your original post, so I'm just going to alter the innermost part of it. I don't think you meant the responsetime = QMEN..)

. . . > iif(XXXX.Value="QMEN", 1000, 500) . . .

|||

How would I incorporate that into?...

=Count(iif (Fields!ResponseTime.Value > 500,1,nothing))

Thanks.

|||

=Count(iif (Fields!ResponseTime.Value > iif(XXXX.Value="QMEN", 1000, 500) , 1, nothing))

Where XXXX is whatever field has the value QMEN

|||Awsome. Thanks so much. the worked perfectlysql

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 of criteria like Male and Female in One field data

Hi,

Please solve this problem using instead of subreport in Crystal Reports

I think using only running filed.
My Porblem is .......

In One field data has Male and Female in different rows ( in different dates)
I want calculate the count of Male and female
Here we are using database is in Access.

Display Format is

Date count of Male Count of Female

Here i want display the count of male or female depend on data ( so Date is group field)

Please Help me......Hi,

You could create a formula to your report as follows. This should be placed in the details section.

shared numbervar cntfem;
shared numbervar cntmale;

If {table.field} = 'Male' Then
cntmale := cntmale + 1;

If {table.field} = 'Female' Then
cntfem := cntfem + 1;

Create a formula for both male and female in the summary section showing the results

e.g. formula male
shared numbervar cntmale;

and forula female
shared numbervar cntfem;

If you are not interested in the grand total, but a sub total use a function to clear clear the count at a section header. Use the supress property on the function to prevent it from printing on your report.

shared numbervar cntfem := 0;
shared numbervar cntmale := 0;

This works if you are displaying the detailed data in your report, i.e. you dont have a query like
select c, d, sum(a), sum(b)
from table
group by c, d

- Jukkasql

count occurrence of character in a field using SQL

Is there a function that will enable me to count the number of instances a
particular character is in a feild? For instance...if I have a field named
number with a record with characters such as 00000111100000 and I what to do
a function that tells me the number of times the number 1 shows up in the
number field of that record. The result would be 4. Is this possible?SELECT LEN(column) - LEN(REPLACE(column, '1', '')) FROM table;
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:819C28E2-76E5-4392-BA01-A7BDDF2BA57A@.microsoft.com...
> Is there a function that will enable me to count the number of instances a
> particular character is in a feild? For instance...if I have a field
> named
> number with a record with characters such as 00000111100000 and I what to
> do
> a function that tells me the number of times the number 1 shows up in the
> number field of that record. The result would be 4. Is this possible?|||Perfect! Thank you!
"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT LEN(column) - LEN(REPLACE(column, '1', '')) FROM table;
>
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:819C28E2-76E5-4392-BA01-A7BDDF2BA57A@.microsoft.com...
>
>

Count occurence of a word

How can I count the rows in wich a specific word occurs in a field in the
detail row of a report?
Thanks
HThere are at least two approaches that come to my mind.
1. Add a caculated field to your dataset that calls a custom VB function
(either embedded in the report or external) which counts the words per row.
Then use the Sum() function to get the overall total.
2. Add a class-level variable to the report embedded code that keeps a
running total. Call a VB function from the field that needs to be counted.
HTH
--
----
Teo Lachev, MVP, MCSD, MCT
Home page and blog: http://www.prologika.com/
----
"Hennie" <spam_me@.linux.com> wrote in message
news:3F608B1B-5F27-4249-912E-0360B60C93FD@.microsoft.com...
> How can I count the rows in wich a specific word occurs in a field in the
> detail row of a report?
> Thanks
> Hsql

count iif

Hope someone can help.
I am trying to do a count where a field is equal to a certain number of
values.
The example below doesn't work but this is the idea.
=Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
Fields!JobNo.Value, Nothing))
I wondered if someone could point me in the right direction.
Thanks PaulSELECT SUM(CASE WHEN VisitType IN ('RTF', 'ARF', 'ERF', 'WOK') THEN 1 ELSE 0
END)
FROM your_table
--
Jacco Schalkwijk
SQL Server MVP
"pcalv" <pcalv@.discussions.microsoft.com> wrote in message
news:6D921197-A432-4C19-B5AE-1958B94CE795@.microsoft.com...
> Hope someone can help.
> I am trying to do a count where a field is equal to a certain number of
> values.
> The example below doesn't work but this is the idea.
> =Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
> Fields!JobNo.Value, Nothing))
> I wondered if someone could point me in the right direction.
> Thanks Paul|||Try the posted case statement first, but if that doesn't work, you have to
spell out your fields again.
Like:
=Count(iif(Fields!VisitType.Value = "RTF" or Fields!VisitType.Value = "ARF"
or Fields!VisitType.Value = "ERF" or Fields!VisitType.Value = "WOK",
Fields!JobNo.Value, Nothing))
Hope that helps!
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"pcalv" wrote:
> Hope someone can help.
> I am trying to do a count where a field is equal to a certain number of
> values.
> The example below doesn't work but this is the idea.
> =Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
> Fields!JobNo.Value, Nothing))
> I wondered if someone could point me in the right direction.
> Thanks Paul|||Thanks for the replies.
Catadmin i used your suggestion which worked great.
Cheers Paul
"Catadmin" wrote:
> Try the posted case statement first, but if that doesn't work, you have to
> spell out your fields again.
> Like:
> =Count(iif(Fields!VisitType.Value = "RTF" or Fields!VisitType.Value = "ARF"
> or Fields!VisitType.Value = "ERF" or Fields!VisitType.Value = "WOK",
> Fields!JobNo.Value, Nothing))
> Hope that helps!
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)
>
> "pcalv" wrote:
> > Hope someone can help.
> >
> > I am trying to do a count where a field is equal to a certain number of
> > values.
> >
> > The example below doesn't work but this is the idea.
> >
> > =Count(iif(Fields!VisitType.Value = "RTF" or "ARF" or "ERF" or "WOK",
> > Fields!JobNo.Value, Nothing))
> >
> > I wondered if someone could point me in the right direction.
> >
> > Thanks Paul|||Glad I could help. @.=)
Catadmin
"pcalv" wrote:
> Thanks for the replies.
> Catadmin i used your suggestion which worked great.
> Cheers Paul
>

Count for each word in a field

Is it possible to create a SELECT query that will give me the count of all the words in a single field?

SELECT field1, count(field1) FROM table1 GROUP BY field1

won't do it because field1 has multiple words and I want them broken out and counted individually.

Data example:

Field1
-------
dog
dog ate my homework
cat
dog and cat
tail
dog tail
...

I want to get a count of every occurance of each word (e.g. "dog") whether in the field by itself or with other words.

Hope this makes sense.

AlNo, you would require a function that takes a text string as input, and counts the number of occurences of another string in it so that you could write:

select field1, word_count(field1,'dog')
from table1;

Depending on your DBMS's capabilities, such a function may already exist or you may be able to write one for yourself. In Oracle for example, you could certainly write one yourself and maybe one already exists in the Oracle Text tool (I don't know).|||Tony,

Thanks for the response. I forgot to mention the DB engine I'm using: MySQL.

I'm not sure I explained it well. Per your function example you're passing the word "dog" to the function. I need it to count all of the words in each field. Using my original example, the results would look like this (with an ORDER BY added):

field1 qty
-- --
dog 4
cat 3
tail 2
and 1
ate 1
my 1

It probably still requires a function to accomplish this.

Al|||Oh, I see - that's rather different. What you need is first to split out all the words into one per row like this:

word
--
dog
dog
dog
dog
cat
cat
cat
tail
tail
and
ate
my

Then of course it is easy to group and count the words. But how to split them up? If you could be sure there were no more than N words in any sentence then you could use a brute-force approach with a user-defined function like this:

select get_word(field1,1) as word from table1
union all
select get_word(field1,2) as word from table1
union all
...
union all
select get_word(field1,N) as word from table1

However, that probably isn't what you need. I don't know MySQL at all, but in Oracle you could achieve this (without the "N" words limit) by writing a function that returns a collection (like an array), and then selecting from the results of the function - a fairly complex operation.

It may be that this can't be done in MySQL using just a select statement - you may have to write a program that populates a temporary table with all the words, and then select from that.|||I'm thinking I'll create a new table that will house the individual words and after writing the "phrase" to field1, I'll parse the words and write them to the new table for future counting.

Thanks for your feedback!
Al

count distinct

Hi... hereby i hv a problem

At my database, I hv a field "component_detail_key",
and the data is :
T002811_1
T002811_2
T002812_1
T002812_2
T002813_1
T002813_2

I get the data before _ :
T002811
T002811
T002812
T002812
T002813
T002813

Now I hv to count distinct, the output should be 3.
I use 2 for loop in this function but can't do that.
Below is my coding :

int distinct = 0;

for (int i = 1; i < a; i++){
for (int j = 1; j < a; j++)
{
if (swkey[i]==swkey[j])
{
distinct = distinct;
} else {
distinct ++;
}
} // End for
}

The answer is :5 , 10 , 15 , 20 , 25 , 30

Anyone can help?pls~Why don't you just do this in SQL something like this:

SELECT COUNT( DISTINCT( SUBSTR( code, 1, INSTR(code,'_')-1 )))
FROM table
...;

I'm using Oracle SUBSTR and INSTR functions, but most DBMSs have something similar.|||May I know what is the meaning for :INSTR(code,'_')-1?
The "code" is field or...?

Thx~|||INSTR(string1, string2) returns the position of string2 within string1. Therefore, SUBSTR(string1, 1, INSTR(string1, string2)-1) will return a new string containing all the chacracters of string1, prior to the occurence of string2.

Example,

String1('123456');
String2('4');

SUBSTR(string1, 1, INSTR(string1, string2)-1) = "123"|||oh i c~
Thanks very much!!!|||BUt still hv error : invalid command for INSTR...
I am using jsp... is it can't support this code?|||I would recommend that the DBMS perform the complete operation.

Java:
IndexOf(string)|||sorry not understand this.....
I am fresh with Java

is it
String sqlsw = "SELECT SUBSTRING_INDEX(component_detail_key, '_', 1) AS sw FROM component_detail WHERE component_key=?";
?
but still can't...|||I solve the problem by CHARINDEX,
thx very much for your help :)

count and update syntax help

Field Names: NOs Code Code1a UniqueID
61 10 888 10
62 10 888 11
63 10 888 12

Logic: If Count(code >1) & Count (Code1a >1)
Update the (Nos) to EQUAL the same Value.
ALL the Nos for the above examble should be the same value for
all three records whether it's 61 for all three records of any
of the other two numbers, it doesn't matter as long as the equal the same value.
How can this be done via sql?Hi!
I' didn't really understood what you mean, but I'm sure you can use:

select code from tabx group by code having count(*)>1
select min(NOs) from tabx where ....
/Bjrn

"Spencer" <spencey@.mindspring.com> wrote in message
news:673a4d41.0311120525.3203e609@.posting.google.c om...
> Field Names: NOs Code Code1a
UniqueID
> 61 10 888
10
> 62 10 888
11
> 63 10 888
12
> Logic: If Count(code >1) & Count (Code1a >1)
> Update the (Nos) to EQUAL the same Value.
> ALL the Nos for the above examble should be the same value for
> all three records whether it's 61 for all three records of any
> of the other two numbers, it doesn't matter as long as the equal the same
value.
> How can this be done via sql?|||Replied in microsoft.public.sqlserver.programming:

> UPDATE Sometable
> SET nos =
> (SELECT MIN(nos)
> FROM Sometable AS S
> WHERE S.code = Sometable.code
> AND S.code1a = Sometable.code1a)

Please don't multi-post.

--
David Portas
----
Please reply only to the newsgroup
--|||Hi Spencer,

Here's one way of doing it using UPDATE FROM. - Louis

create table #T (n int, codeA int, codeB int, id uniqueidentifier)
insert into #T values(11,1,888,newid())
insert into #T values(12,1,888,newid())
insert into #T values(13,1,888,newid())
insert into #T values(21,10,888,newid())
insert into #T values(22,10,888,newid())
insert into #T values(23,10,888,newid())
insert into #T values(1,1,111,newid())
insert into #T values(2,2,222,newid())
insert into #T values(3,3,333,newid())
insert into #T values(3,4,444,newid())

select codeA,codeB,n=min(n)
into #U
from #T
group by codeA,codeB
having count(*)>1

update #T
set n=b.n
from #T as a
JOIN #U as b
ON a.codeA=b.codeA and a.codeB=b.codeB

select n,codeA,codeB from #T

returns:
n codeA codeB
---- ---- ----
11 1 888
11 1 888
11 1 888
21 10 888
21 10 888
21 10 888
1 1 111
2 2 222
3 3 333
3 4 444|||Wow! Do you have something against using an UPDATE subquery?

It may be worth adding a WHERE clause to my original suggestion in line with
the HAVING COUNT(*)>1 requirement.

UPDATE Sometable
SET nos =
(SELECT MIN(nos)
FROM Sometable AS S
WHERE S.code = Sometable.code
AND S.code1a = Sometable.code1a)
WHERE nos >
(SELECT MIN(nos)
FROM Sometable AS S
WHERE S.code = Sometable.code
AND S.code1a = Sometable.code1a)

Assuming Nos is not nullable.

--
David Portas
----
Please reply only to the newsgroup
--|||> Wow! Do you have something against using an UPDATE subquery?
Hi David,

My little brain can't handle subqueries ;) I'm currently undergoing
brain overload, trying to figure out how to use W3C SVG to create
dynamic charts on the web.

Count All Things Happened Today

Ive got a table of notes people have created, with a field called "timecreated" which has a default value of "GETDATE()" Im trying to write an SQL statement that will count up all of the notes that people have created today/ yesturday etc. i could do it if the timecreated value was a "short date string" styled date, but its set up like : 11/11/2007 18:51:46 is there way of converting it before counting? if theres a simple way of doing this i would appricate any help thanks John

check out the year, month and day or datepart functions in t-sql.

You can assemble the date anyway you want.

|||

forgot to add:

SET DATEFORMAT

CONVERT

|||

i found this : http://support.microsoft.com/kb/q186265/

which helps me get stuff like secounds etc :

 Ms for Milliseconds
 Yy for Year
 Qq for Quarter of the Year
 Mm for Month
 Dy for the Day of the Year
 Dd for Day of the Month
 Wk for Week
 Dw for the Day of the Week
 Hh for Hour
 Mi for Minute
 Ss for Second

how do i get it as a short date string, like "11/10/2007" please John

|||

Try this:

WHERE timecreated>=DATEADD(dd,DATEDIFF(dd, 0,GETDATE()), 0)AND timecreated<DATEADD(dd,DATEDIFF(dd, 0,GETDATE()), 1)

|||

that worked perfectly, is it "read" by like this :

WHERE TimeCreated is equalto or greater than today + 0 days. AND TimeCreated is LessThan today + 1day.

just wondering becuase i wish to adjust this code to count things happening this week / month etc.

Thanks John

|||

Both DATEADD and DATEDIFF are very useful functions to work with datetime in TSQL. You can search to find more information on these two key words. You can modify the code with the same logic to get what you want, but i have read some good articles with a lot usages for these two functions.

|||

yeh they seem to be invalueable when attempting to work with dates / times! thanks for this! John

count 2 value in a table

hi all pls kindly help
need to know how to do a count for 2 value in a column

example :
my table(entries)
has the field name 'selection'
under this selection, data inside have is e.g(apple,apple,pear,pear,pear,orange)
i need to do a COUNT on how many apple and pear there is inside this table

but i seriously have no idea, pls kindly help thanksThis sounds like homework from an "Introduction to databases" course, but I'm willing to give you the benefit of the doubt...SELECT Count(*), selection
FROM entries
GROUP BY selection
ORDER BY Count(*) DESC -- gratis-PatP|||SELECT Count(*), selection
FROM entries
GROUP BY selection
ORDER BY Count(*) DESC -- gratis

sorry for my ignorance..
wat u meant by DESC over here??

wat i want is to count how many pears and apple there is in that table but not orange|||DESC means descending, and you don't have to use it if you don't want to

did you want the total of apples and pears? that's different --

SELECT count(*) as applesandpears
FROM entries
WHERE selection in ('apple','pear')|||thanks so much...
really so basic

need to read up my books again :(|||Sorry, my background is showing :rolleyes:

Gratis is a Latin vulgate term that means "free of charge" or "complimentary". When I used it in a comment, I meant that I expected it to make things easier to use, without actually impacting the solution at all.

As Rudy pointed out, in this case it simply orders the result set so that the most frequently occuring items appear at the top of the list. You can safely ignore it if it doesn't help you.

-PatP|||ok got it ..
but still appreciated
:)

corrupted values in database

I just started working on reports on an SQL 2000 database.
Many field contain numerical values stored as varchar. To make matters worse these strings contain both '.' and ',' as decimal sign.
So far this has really been a great pain :-(

The database and its application are bought as is, I cannot modify fieldtypes or anything else.

Can anyone give a good strategy to convert the strings back to numerical so I can calculate revenue's and such??

P.S.

In extreme cases string contain both ',' AND '.' , like:

5.608,42Use

convert (float, replace(column_name,',',''))

or convert (int,replace(replace(column_name,',',''),'.','') if you are sure the value is an integer|||Note quite what I need,

The string can contain either a . or a ,
With the conversion you give all figures with a ',' come out 100 fold higher than they should be, while those with a '.' come out right.

Some sort of validation seems to be needed to get all them right.

Right now I am focussing on prices which means the highest value is just 900.00 (or 900.00) so I have no problem in this column with both a ',' and a '.'|||The following seems to work:

SELECT
CASE
WHEN
SUBSTRING(REVERSE(RTRIM(LTRIM(PRIJS))),3,1) = ',' THEN
convert(float,replace(PRIJS,',',''))/100
WHEN
SUBSTRING(REVERSE(RTRIM(LTRIM(PRIJS))),3,1) = '.' THEN
convert(dec(9,2),PRIJS)
ELSE 0 END

not getting very good feelings though on having to do these king of conversions :-(|||No, me either! You should consider to clean your data before reporting them. You are not allowed to change the field type, but you should be allowed to update your data. So, define your required format, and update everything, that does not match your format, first. Your format may be "#.###,##" or "####.##"; I would prefer the last, because that can be converted into a number directly. You may even consider to convert everything to cents, which makes it easy for you to detect new values, that are not yet converted.

Another approach would be to identify your different formats, and make a union query, handling each of your formats in a separate branch.|||Can't do that.

Some of the fields are filled by procedures from the AS400 system.
THere is something fundamentaly wrong with the way the data is handled on the SQL database. (For which I just mailed an angry mail to all involved) ,

but doctor maybe you can help me with this one:

I have all the differnent flavors in the DB:

1.203,56
1,203.56
456,67
456.67

You see replacing the comma is okay if it comes as first one in the string.
If there is just a comma replace it by a '.'
If a '.' precedes a ',' replace ','by '.' and get rid of the '.'

Nice challenge ain't it?

Next private message next week, gotta cycle to the north this afternoon
:-)|||No, me either! You should consider to clean your data before reporting them. You are not allowed to change the field type, but you should be allowed to update your data. So, define your required format, and update everything, that does not match your format, first. Your format may be "#.###,##" or "####.##"; I would prefer the last, because that can be converted into a number directly. You may even consider to convert everything to cents, which makes it easy for you to detect new values, that are not yet converted.

I dont uderstand the format "#.###,##" . What does it exactly mean ?|||Originally posted by blom0344
Can't do that.

Some of the fields are filled by procedures from the AS400 system.
THere is something fundamentaly wrong with the way the data is handled on the SQL database. (For which I just mailed an angry mail to all involved) ,


I understand, that you don't have control of the import process, but who is preventing you from cleaning your data afterwards?

Originally posted by blom0344
I have all the differnent flavors in the DB:

1.203,56
1,203.56
456,67
456.67

You see replacing the comma is okay if it comes as first one in the string.
If there is just a comma replace it by a '.'
If a '.' precedes a ',' replace ','by '.' and get rid of the '.'

Nice challenge ain't it?


As I said, determine first your possible flavors. What is imported when your price is 456.- or 456.50? Is is 456[,|.]00 or just 456? So, actually I'm asking whether your decimal point is always on the 3rd last position? Your algorithm depends on that. Another point is whether you can expect a maximum number of digits, or not. So, can you also have, for example 1,234,567.89?

Assuming, your possible flavors are those 4 you gave me, a query like I propose would look like (your price field is called p)

SELECT p FROM T WHERE len(p)=6 AND SubString(p, 4,1)="."
UNION
SELECT replace(p,',','.') FROM T WHERE len(p)=6 AND SubString(p, 4,1)=","
UNION
SELECT replace(p,',','') FROM T WHERE len(p)=8 AND SubString(p, 6,1)="."
UNION
SELECT replace(replace(p,'.',''), ',','.') FROM T WHERE len(p)=8 AND SubString(p, 6,1)=","

Calling this union query U you can do your accumulation like

SELECT sum(cast(p as decimal(10,2))) from (<U>)|||-- replace(replace(p,'.',''), ',','.') --

Yep,

I am working with the replace on replace in my solution as well.
Does not seem to work like it should.
I want to use the CASE instead of UNION solution , cause I am going to assign it to a BO object

We'll continue next week...|||If you know you have only two decimal places, how about something along the lines of eliminating all commas/periods, then dividing by 100?

select convert(numeric (10, 2), replace(replace(value, ',', ''), '.', '')))/100.00

A bit pressed for time here, so I have not tested that code snippet. Hope it helps.|||Elegant solution, MCrowley. :cool:

...but make sure the values don't just only have two decimal places, but that the ALWAYS have two decimal places.

blindman|||Originally posted by blom0344
I just started working on reports on an SQL 2000 database.
Many field contain numerical values stored as varchar. To make matters worse these strings contain both '.' and ',' as decimal sign.
So far this has really been a great pain :-(

The database and its application are bought as is, I cannot modify fieldtypes or anything else.

Can anyone give a good strategy to convert the strings back to numerical so I can calculate revenue's and such??

P.S.

In extreme cases string contain both ',' AND '.' , like:

5.608,42

Dumb question ... but, any chance you are dealing with software that handles multi-currency? Do you have the CCSID translation turned on from the AS400 to the SQL server?|||Most certainly not a dumb question, but in this case we are talking about E-commerce orders from two euro countries, so every order amount is always just in one currency.

McCrowleys solution does indeed work for me, cause every order is calculated down to the euro-cent

Corrupted FormatDateTime Results

Using SQL Server 2005 Developer Edition and reporting services.

My report pulls a datetime field from the database, for example holding a value of 9/9/2006 12:00:00. It places this field by itself in a textbox.

I want it to display as long date (Saturday, September 09, 2006) so I set the format of the textbox to:

=FormatDateTime(Fields!Contest_Dt.Value , 1 )

This displays the results quite oddly as:

SaAur9a6, SepAe0ber 09, 2006.

Any ideas as to what is going on here and how to fix it?

Thanks in advance!

When I try this, it works as expected. Are you sure the value in the database is stored as a datetime value not as a string?

Can you also try this expression: =FormatDateTime(CDate(Fields!Contest_Dt.Value, 1))

-- Robert