CREATE DATABASE failed: values

Showing posts with label values. Show all posts

Sunday, March 25, 2012

Create a script rename value of a column

Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
JosUPDATE Fasesrt
SET fasesrtom =
CASE fasesrtom
WHEN 'value 1' THEN 'new value 1'
WHEN 'value 2' THEN 'new value 2'
WHEN 'value 3' THEN 'new value 3'
WHEN 'value 4' THEN 'new value 4'
WHEN 'value 5' THEN 'new value 5'
WHEN 'value 6' THEN 'new value 6'
END
WHERE fasesrtom IN
('value 1',
'value 2',
'value 3',
'value 4',
'value 5',
'value 6')
David Portas
SQL Server MVP
--|||You have to use an UPDATE statement, and possibly the REPLACE function. See
SQL Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jos" <Jos.Jeurissen@.mail.ing.nl> wrote in message
news:046DE39B-8B5A-4397-9C10-33F89C6FFA36@.microsoft.com...
Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
Jos

Create a script rename value of a column

Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
Jos
UPDATE Fasesrt
SET fasesrtom =
CASE fasesrtom
WHEN 'value 1' THEN 'new value 1'
WHEN 'value 2' THEN 'new value 2'
WHEN 'value 3' THEN 'new value 3'
WHEN 'value 4' THEN 'new value 4'
WHEN 'value 5' THEN 'new value 5'
WHEN 'value 6' THEN 'new value 6'
END
WHERE fasesrtom IN
('value 1',
'value 2',
'value 3',
'value 4',
'value 5',
'value 6')
David Portas
SQL Server MVP
|||You have to use an UPDATE statement, and possibly the REPLACE function. See
SQL Server Books Online for more information.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jos" <Jos.Jeurissen@.mail.ing.nl> wrote in message
news:046DE39B-8B5A-4397-9C10-33F89C6FFA36@.microsoft.com...
Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
Jos
sql

Create a script rename value of a column

Hi
Is there a way, to make a script that changes the values of a colum
I have a tabel called "fasesrt
That tabel has 3 columns
Fase & Fasesrtom & fasegrpn
In the column fasesrtom I have 6 values that I want to change
I know the old value and the new valu
Can Anyone help me to make a script, If I have a start, I think I can do it
I just need a start
Thank
JoUPDATE Fasesrt
SET fasesrtom = CASE fasesrtom
WHEN 'value 1' THEN 'new value 1'
WHEN 'value 2' THEN 'new value 2'
WHEN 'value 3' THEN 'new value 3'
WHEN 'value 4' THEN 'new value 4'
WHEN 'value 5' THEN 'new value 5'
WHEN 'value 6' THEN 'new value 6'
END
WHERE fasesrtom IN
('value 1',
'value 2',
'value 3',
'value 4',
'value 5',
'value 6')
--
David Portas
SQL Server MVP
--|||You have to use an UPDATE statement, and possibly the REPLACE function. See
SQL Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jos" <Jos.Jeurissen@.mail.ing.nl> wrote in message
news:046DE39B-8B5A-4397-9C10-33F89C6FFA36@.microsoft.com...
Hi,
Is there a way, to make a script that changes the values of a column
I have a tabel called "fasesrt"
That tabel has 3 columns
Fase & Fasesrtom & fasegrpnr
In the column fasesrtom I have 6 values that I want to change,
I know the old value and the new value
Can Anyone help me to make a script, If I have a start, I think I can do it.
I just need a start.
Thanks
Jos

Sunday, March 11, 2012

CR not evaluating NULL values in formula

I have a data set where I am looking for a person's name and title. In some cases though either a title or a name was not entered. When this happens CR does not evaluate it in a formula I have built. Is there a trick to getting CR to evaluate Nulls in a particular formula or am I missing somthing in my initial formula?

Thanks in advance.

Formula (finds the persons title but only if the title value is NOT NULL):
----
'**********************
'Psychiatry/Psychology
'**********************
If (InStr({Concurrence.ConcurrenceTitle}, "Psychol", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "psychia", 1) > 0) then
formula = "Psychiatry/Psychology"

'****************
'Radiology
'****************
ElseIf (InStr({Concurrence.ConcurrenceTitle}, "Radiol", 1) > 0) then
formula = "Radiology"

'****************
'Patient Safety
'****************
ElseIf (InStr({Concurrence.ConcurrenceTitle}, "Patient Safety", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "PSM", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "PSO", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "PSC", 1) > 0 _
or (InStr({Concurrence.ConcurrenceTitle}, "PAT", 1) > 0 _
and InStr({Concurrence.ConcurrenceTitle}, "SAFETY", 1) > 0) _
or (InStr({Concurrence.ConcurrenceTitle}, "PT", 1) > 0 _
and InStr({Concurrence.ConcurrenceTitle}, "SAFETY", 1) > 0) _
or (InStr({Concurrence.ConcurrenceTitle}, "PS", 0) > 0 _
and InStr({Concurrence.ConcurrenceTitle}, "MANAGER", 1) > 0)) then
formula = "Patient Safety"

'*************************
'All other entries
'*************************
ElseIf (IsNull({Concurrence.ConcurrenceTitle}) _
or Trim({Concurrence.ConcurrenceTitle}) = "") then
formula = "No entry"

Else
formula = "Other"

End IfTry putting the NULL test as the very first test. Crystal seems to be funny about this sometimes.

CR in VB.Net

I have one dataset of having values I wants to Create one Report.
How to Create a Crystal Report in Vb.net?You can try Crystal Report available in .NET. It's much easier and has lots of options.|||How to Connect one dataSet to one Crystal Report in VB.net?

CPU, Physical IO, & Memory Usage

I was wondering about what the values in Management->Current
Activity->Process Info represent. Are the values for CPU, Physical IO, &
Memory Usage cumulative or do they represent a max or a current value?
The reason I'm wondering is that I saw a large value for Memory Usage by a
process recently. The value was in the millions. Most other processes had
values < 100 and some went up to 4000 or so.
Should I be worrying about such a large value for any of these parameters?
Thanks
MG
They are cumulative values from the time the user logged on. For the most
part they are pretty useless.
Andrew J. Kelly SQL MVP
"MGeles" <michael.geles@.thomson.com> wrote in message
news:A0B603E2-0C4A-4A5A-839F-DE3CB94FCE42@.microsoft.com...
>I was wondering about what the values in Management->Current
> Activity->Process Info represent. Are the values for CPU, Physical IO, &
> Memory Usage cumulative or do they represent a max or a current value?
> The reason I'm wondering is that I saw a large value for Memory Usage by a
> process recently. The value was in the millions. Most other processes
> had
> values < 100 and some went up to 4000 or so.
> Should I be worrying about such a large value for any of these parameters?
> Thanks
> --
> MG

Thursday, March 8, 2012

CPU, Physical IO, & Memory Usage

I was wondering about what the values in Management->Current
Activity->Process Info represent. Are the values for CPU, Physical IO, &
Memory Usage cumulative or do they represent a max or a current value?
The reason I'm wondering is that I saw a large value for Memory Usage by a
process recently. The value was in the millions. Most other processes had
values < 100 and some went up to 4000 or so.
Should I be worrying about such a large value for any of these parameters?
Thanks
--
MGThey are cumulative values from the time the user logged on. For the most
part they are pretty useless.
Andrew J. Kelly SQL MVP
"MGeles" <michael.geles@.thomson.com> wrote in message
news:A0B603E2-0C4A-4A5A-839F-DE3CB94FCE42@.microsoft.com...
>I was wondering about what the values in Management->Current
> Activity->Process Info represent. Are the values for CPU, Physical IO, &
> Memory Usage cumulative or do they represent a max or a current value?
> The reason I'm wondering is that I saw a large value for Memory Usage by a
> process recently. The value was in the millions. Most other processes
> had
> values < 100 and some went up to 4000 or so.
> Should I be worrying about such a large value for any of these parameters?
> Thanks
> --
> MG

CPU, Physical IO, & Memory Usage

I was wondering about what the values in Management->Current
Activity->Process Info represent. Are the values for CPU, Physical IO, &
Memory Usage cumulative or do they represent a max or a current value?
The reason I'm wondering is that I saw a large value for Memory Usage by a
process recently. The value was in the millions. Most other processes had
values < 100 and some went up to 4000 or so.
Should I be worrying about such a large value for any of these parameters?
Thanks
--
MGThey are cumulative values from the time the user logged on. For the most
part they are pretty useless.
--
Andrew J. Kelly SQL MVP
"MGeles" <michael.geles@.thomson.com> wrote in message
news:A0B603E2-0C4A-4A5A-839F-DE3CB94FCE42@.microsoft.com...
>I was wondering about what the values in Management->Current
> Activity->Process Info represent. Are the values for CPU, Physical IO, &
> Memory Usage cumulative or do they represent a max or a current value?
> The reason I'm wondering is that I saw a large value for Memory Usage by a
> process recently. The value was in the millions. Most other processes
> had
> values < 100 and some went up to 4000 or so.
> Should I be worrying about such a large value for any of these parameters?
> Thanks
> --
> MG

Wednesday, March 7, 2012

CPU unit in Profiler

What do the values for the CPU column in Profiler represent ? So if see 1000
, what exactly does it mean .. I believe its in ms.. but what does 1000 ms
for a CPU mean. I can understand 1000 ms for duration..
ThanksHassan,
CPU time is time spent on the CPU, running CPU cycles. Duration will be CPU
time plus time spent waiting for data reads or writes or other wait types.
Jon Jahren
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uePoHH8xEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What do the values for the CPU column in Profiler represent ? So if see
1000
> , what exactly does it mean .. I believe its in ms.. but what does 1000 ms
> for a CPU mean. I can understand 1000 ms for duration..
> Thanks
>|||Yes it is millis. And it is the number of milliseconds that the operation
spent on the processor.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uePoHH8xEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What do the values for the CPU column in Profiler represent ? So if see
1000
> , what exactly does it mean .. I believe its in ms.. but what does 1000 ms
> for a CPU mean. I can understand 1000 ms for duration..
> Thanks
>

CPU unit in Profiler

What do the values for the CPU column in Profiler represent ? So if see 1000
, what exactly does it mean .. I believe its in ms.. but what does 1000 ms
for a CPU mean. I can understand 1000 ms for duration..
Thanks
Hassan,
CPU time is time spent on the CPU, running CPU cycles. Duration will be CPU
time plus time spent waiting for data reads or writes or other wait types.
Jon Jahren
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uePoHH8xEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What do the values for the CPU column in Profiler represent ? So if see
1000
> , what exactly does it mean .. I believe its in ms.. but what does 1000 ms
> for a CPU mean. I can understand 1000 ms for duration..
> Thanks
>
|||Yes it is millis. And it is the number of milliseconds that the operation
spent on the processor.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uePoHH8xEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What do the values for the CPU column in Profiler represent ? So if see
1000
> , what exactly does it mean .. I believe its in ms.. but what does 1000 ms
> for a CPU mean. I can understand 1000 ms for duration..
> Thanks
>

CPU unit in Profiler

What do the values for the CPU column in Profiler represent ? So if see 1000
, what exactly does it mean .. I believe its in ms.. but what does 1000 ms
for a CPU mean. I can understand 1000 ms for duration..
ThanksHassan,
CPU time is time spent on the CPU, running CPU cycles. Duration will be CPU
time plus time spent waiting for data reads or writes or other wait types.
Jon Jahren
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uePoHH8xEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What do the values for the CPU column in Profiler represent ? So if see
1000
> , what exactly does it mean .. I believe its in ms.. but what does 1000 ms
> for a CPU mean. I can understand 1000 ms for duration..
> Thanks
>|||Yes it is millis. And it is the number of milliseconds that the operation
spent on the processor.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uePoHH8xEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What do the values for the CPU column in Profiler represent ? So if see
1000
> , what exactly does it mean .. I believe its in ms.. but what does 1000 ms
> for a CPU mean. I can understand 1000 ms for duration..
> Thanks
>

Sunday, February 19, 2012

counting values in aresult set

Hi,
I have the following query:
SELECT ahd.call_req.ref_num AS Incident
FROM ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
which returns a single column called Incidents containing integer values.
4323
4316
4316
4309
4309
4309
What I want is a list of the Integer values and the coresponding number of
times
it appears in the result set.For example;
4323 1
4316 2
4309 3
--
roryI forot to say thank you in advance for any help!
Best wishes
--
rory
"Rory" wrote:
> Hi,
> I have the following query:
> SELECT ahd.call_req.ref_num AS Incident
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> which returns a single column called Incidents containing integer values.
> 4323
> 4316
> 4316
> 4309
> 4309
> 4309
> What I want is a list of the Integer values and the coresponding number of
> times
> it appears in the result set.For example;
> 4323 1
> 4316 2
> 4309 3
> --
> rory|||this is untested but try
SELECT ahd.call_req.ref_num AS Incident, count(*)
FROM ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
group by Incident
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rory" <rory@.discussions.microsoft.com> wrote in message
news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> Hi,
> I have the following query:
> SELECT ahd.call_req.ref_num AS Incident
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> which returns a single column called Incidents containing integer values.
> 4323
> 4316
> 4316
> 4309
> 4309
> 4309
> What I want is a list of the Integer values and the coresponding number
> of
> times
> it appears in the result set.For example;
> 4323 1
> 4316 2
> 4309 3
> --
> rory|||Hi Hilary,
Thanks for that, unortunately it does not work.
Regards,
--
rory
"Hilary Cotter" wrote:
> this is untested but try
> SELECT ahd.call_req.ref_num AS Incident, count(*)
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> group by Incident
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Rory" <rory@.discussions.microsoft.com> wrote in message
> news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> > Hi,
> > I have the following query:
> > SELECT ahd.call_req.ref_num AS Incident
> > FROM ahd.call_req JOIN ahd.act_log
> > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > WHERE ahd.act_log.type = 'TR'
> >
> > which returns a single column called Incidents containing integer values.
> > 4323
> > 4316
> > 4316
> > 4309
> > 4309
> > 4309
> > What I want is a list of the Integer values and the coresponding number
> > of
> > times
> > it appears in the result set.For example;
> >
> > 4323 1
> > 4316 2
> > 4309 3
> > --
> > rory
>
>|||Hi Hilary,
Thanks for your reply, unfortunately it doesn't work. Back to
the "drawing board" for me I'm afraid!
Thanks again
--
rory
"Hilary Cotter" wrote:
> this is untested but try
> SELECT ahd.call_req.ref_num AS Incident, count(*)
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> group by Incident
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Rory" <rory@.discussions.microsoft.com> wrote in message
> news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> > Hi,
> > I have the following query:
> > SELECT ahd.call_req.ref_num AS Incident
> > FROM ahd.call_req JOIN ahd.act_log
> > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > WHERE ahd.act_log.type = 'TR'
> >
> > which returns a single column called Incidents containing integer values.
> > 4323
> > 4316
> > 4316
> > 4309
> > 4309
> > 4309
> > What I want is a list of the Integer values and the coresponding number
> > of
> > times
> > it appears in the result set.For example;
> >
> > 4323 1
> > 4316 2
> > 4309 3
> > --
> > rory
>
>|||Hi Hilary,
I modified the query slightly from your suggestion( just column
names) and hey presto! Success!
Thanks you've made it a very nice Friday for me! Thanks again.
SELECT ahd.call_req.ref_num, count(*)
FROM ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
group by ahd.call_req.ref_num
--
rory
"Hilary Cotter" wrote:
> this is untested but try
> SELECT ahd.call_req.ref_num AS Incident, count(*)
> FROM ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> group by Incident
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Rory" <rory@.discussions.microsoft.com> wrote in message
> news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> > Hi,
> > I have the following query:
> > SELECT ahd.call_req.ref_num AS Incident
> > FROM ahd.call_req JOIN ahd.act_log
> > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > WHERE ahd.act_log.type = 'TR'
> >
> > which returns a single column called Incidents containing integer values.
> > 4323
> > 4316
> > 4316
> > 4309
> > 4309
> > 4309
> > What I want is a list of the Integer values and the coresponding number
> > of
> > times
> > it appears in the result set.For example;
> >
> > 4323 1
> > 4316 2
> > 4309 3
> > --
> > rory
>
>|||Rory,
Change the "group by" clause and use the correct column.
SELECT
ahd.call_req.ref_num AS Incident, count(*)
FROM
ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
group by ahd.call_req.ref_num
go
AMB
"Rory" wrote:
> Hi Hilary,
> Thanks for your reply, unfortunately it doesn't work. Back to
> the "drawing board" for me I'm afraid!
> Thanks again
> --
> rory
>
> "Hilary Cotter" wrote:
> > this is untested but try
> >
> > SELECT ahd.call_req.ref_num AS Incident, count(*)
> > FROM ahd.call_req JOIN ahd.act_log
> > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > WHERE ahd.act_log.type = 'TR'
> > group by Incident
> >
> >
> >
> > --
> > Hilary Cotter
> > Director of Text Mining and Database Strategy
> > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> >
> > This posting is my own and doesn't necessarily represent RelevantNoise's
> > positions, strategies or opinions.
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> > Looking for a FAQ on Indexing Services/SQL FTS
> > http://www.indexserverfaq.com
> >
> >
> >
> > "Rory" <rory@.discussions.microsoft.com> wrote in message
> > news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> > > Hi,
> > > I have the following query:
> > > SELECT ahd.call_req.ref_num AS Incident
> > > FROM ahd.call_req JOIN ahd.act_log
> > > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > > WHERE ahd.act_log.type = 'TR'
> > >
> > > which returns a single column called Incidents containing integer values.
> > > 4323
> > > 4316
> > > 4316
> > > 4309
> > > 4309
> > > 4309
> > > What I want is a list of the Integer values and the coresponding number
> > > of
> > > times
> > > it appears in the result set.For example;
> > >
> > > 4323 1
> > > 4316 2
> > > 4309 3
> > > --
> > > rory
> >
> >
> >|||Hi Alejandro,
I modified the query and it works fine,
SELECT ahd.call_req.ref_num, count(*) AS Hops
FROM ahd.call_req JOIN ahd.act_log
ON ahd.act_log.call_req_id=ahd.call_req.persid
WHERE ahd.act_log.type = 'TR'
group by ahd.call_req.ref_num
thanks for your response it is very much appreciated.
--
rory
"Alejandro Mesa" wrote:
> Rory,
> Change the "group by" clause and use the correct column.
> SELECT
> ahd.call_req.ref_num AS Incident, count(*)
> FROM
> ahd.call_req JOIN ahd.act_log
> ON ahd.act_log.call_req_id=ahd.call_req.persid
> WHERE ahd.act_log.type = 'TR'
> group by ahd.call_req.ref_num
> go
>
> AMB
>
> "Rory" wrote:
> > Hi Hilary,
> > Thanks for your reply, unfortunately it doesn't work. Back to
> > the "drawing board" for me I'm afraid!
> > Thanks again
> > --
> > rory
> >
> >
> > "Hilary Cotter" wrote:
> >
> > > this is untested but try
> > >
> > > SELECT ahd.call_req.ref_num AS Incident, count(*)
> > > FROM ahd.call_req JOIN ahd.act_log
> > > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > > WHERE ahd.act_log.type = 'TR'
> > > group by Incident
> > >
> > >
> > >
> > > --
> > > Hilary Cotter
> > > Director of Text Mining and Database Strategy
> > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > >
> > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > positions, strategies or opinions.
> > >
> > > Looking for a SQL Server replication book?
> > > http://www.nwsu.com/0974973602.html
> > >
> > > Looking for a FAQ on Indexing Services/SQL FTS
> > > http://www.indexserverfaq.com
> > >
> > >
> > >
> > > "Rory" <rory@.discussions.microsoft.com> wrote in message
> > > news:50034587-D2A5-4CFE-A629-09636FED3E0C@.microsoft.com...
> > > > Hi,
> > > > I have the following query:
> > > > SELECT ahd.call_req.ref_num AS Incident
> > > > FROM ahd.call_req JOIN ahd.act_log
> > > > ON ahd.act_log.call_req_id=ahd.call_req.persid
> > > > WHERE ahd.act_log.type = 'TR'
> > > >
> > > > which returns a single column called Incidents containing integer values.
> > > > 4323
> > > > 4316
> > > > 4316
> > > > 4309
> > > > 4309
> > > > 4309
> > > > What I want is a list of the Integer values and the coresponding number
> > > > of
> > > > times
> > > > it appears in the result set.For example;
> > > >
> > > > 4323 1
> > > > 4316 2
> > > > 4309 3
> > > > --
> > > > rory
> > >
> > >
> > >

counting values in aresult set

Counting values for transportation mode

I have a table for tracking transportation mode to work for each pay period. There are five modes

1=Transit
2=carpool
3=bicycle
4=walk
5=vanpool

The pay period database fields are below. A number is entered for each day representing the mode (the field type in the SQL db is char) . I would like my report to count the modes for each pay period.

I have create a group on the field "PayPeriod". It seems that I should be able to create a formula like this for each mode.

Wk_1_Sun
Wk_1_Mon
Wk_1_Tues
Wk_1_Wed
Wk_1_Thurs
Wk_1_Fri
Wk_1_Sat
Wk_2_Sun
Wk_2_Mon
Wk_2_Tues
Wk_2_Wed
Wk_2_Thurs
Wk_2_Fri
Wk_2_Sat

NumberVar tot1sun1 := 0;
NumberVar tot1mon1 := 0;

If ({ECAPParticipate.Wk_1_Sun}= "1") Then
tot1sun1 := count({ECAPParticipate.Wk_1_Sun})
else
If ({ECAPParticipate.Wk_1_Mon}= "1") Then
tot1mon1 := count({ECAPParticipate.Wk_1_Mon})

etc.

I am getting scewed results. I'm new at this so I'm sure that my programming is not correct. Can someone give me some guidance. Thank you.If your report is grouped by the payperiod, then add a summary that counts the modes for each one. Place that summary in your Group header or footer where ever you want it visible.

GJ

Counting Unique Values?

I have a database that holds information about the location of our members. Their location is held as an ID that it gets from another table in the databse.

Is there an SQL SELECT query I can write that will return a result listing the ID and the number of times it appears? I am sure this is rather simple but I just am unaware of the code?

thanks.Assuming that you are using SQL, you could use the GROUP BY clause to do what you've described.

What tool(s) are you using, and what is your table design?

-PatP|||I am using MS Access. The table has fields holding member data. All items that will potentially be repeated ie gender, location, member package, member category, etc are populated with a unique ID referencing another table containing the data that is pertinent to those. All the IDs are held in number fields.

I assume that is the info you were after?|||yes, Access supports COUNT(*) and GROUP BYselect ID,count(*) as rows from sometable group by ID

Counting the occurence of a string ...

Hi ...
I have a weblog database where I want to count the occurences of a
table of string values that appear in all the urls viewed.

My tblWebLog as a field that contains the url ...
tblWebLog.[cs-uri-stem]

I have another table ... tblStrings ... that has a field [strSearch]
for a string value and an integer field [intViewCount] to count the
occurence of the string in tblWebLog.[cs-uri-stem]

I've been trying ...

Update tblStrings
Set [intViewCount] = (Select Count(*) From tblWebLog Where
[cs-uri-stem] Like '%_' + tblStrings.[strSearch] + '.htm%')

... but it doesn't fly and I'm stumped. Any thoughts?

Cheers.DaFerg (dave@.myalarm.com) writes:

Quote:

Originally Posted by

I have a weblog database where I want to count the occurences of a
table of string values that appear in all the urls viewed.
>
My tblWebLog as a field that contains the url ...
tblWebLog.[cs-uri-stem]
>
I have another table ... tblStrings ... that has a field [strSearch]
for a string value and an integer field [intViewCount] to count the
occurence of the string in tblWebLog.[cs-uri-stem]
>
I've been trying ...
>
Update tblStrings
Set [intViewCount] = (Select Count(*) From tblWebLog Where
[cs-uri-stem] Like '%_' + tblStrings.[strSearch] + '.htm%')
>
... but it doesn't fly and I'm stumped. Any thoughts?

Your query make sense in relation to your description, but then again I
may be misunderstanding something.

For this sort of question, it helps if you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This helps to clarify what you are looking for, and it makes it possible
to copy and paste and develop a tested solution.

--
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.mspx|||If tblStrings.[strSearch] is defined as fixed length (char) instead of
varying length (varchar) that would cause problems. Or if trailing
blanks were stored in a varchar column. Try using
RTRIM(tblStrings.[strSearch]) instead.

Roy Harvey
Beacon Falls, CT

On 27 Aug 2006 01:00:04 -0700, "DaFerg" <dave@.myalarm.comwrote:

Quote:

Originally Posted by

>Hi ...
>I have a weblog database where I want to count the occurences of a
>table of string values that appear in all the urls viewed.
>
>My tblWebLog as a field that contains the url ...
>tblWebLog.[cs-uri-stem]
>
>I have another table ... tblStrings ... that has a field [strSearch]
>for a string value and an integer field [intViewCount] to count the
>occurence of the string in tblWebLog.[cs-uri-stem]
>
>I've been trying ...
>
>Update tblStrings
>Set [intViewCount] = (Select Count(*) From tblWebLog Where
>[cs-uri-stem] Like '%_' + tblStrings.[strSearch] + '.htm%')
>
>... but it doesn't fly and I'm stumped. Any thoughts?
>
>Cheers.

Friday, February 17, 2012

counting occurrences of a value

I have a column in a report with values Y or N or V. In the header, I have to show the number of times each value appeared.

I used RepeatingValue() with IIF() but it's counting all the values as same, e.g., if there are four occurrences of Y, two of N and one of V, I want something like this:

Y Cnt=4, N Cnt=2, V Cnt=1

My assumption was that this will work (white spaces added for readability:

=iif(

Fields!myCol.Value = "Y",

"Y Cnt=" & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),

iif(Fields!myCol.Value = "N",

"N Cnt= & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),

iif(Fields!myCol.Value = "V",

"V Cnt= & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),

"NULL"

)

Please help.

Can we declare variables and use them to keep the total?|||

As before, no one could help. After trying millions of different ways, I found the solution (white spaces for readability):

="Y Cnt=" &

Count(

Switch(Fields!myCol.Value = "Y", 1)

, "dsMyDataSet"

)

& ", N Cnt=" &

Count(

Switch(Fields!myCol.Value = "N", 1)

, "dsMyDataSet"

)

& ", V Cnt=" &

Count(

Switch(Fields!myCol.Value = "V", 1)

, "dsMyDataSet"

)

counting multiple values from the same column and grouping by a another column

This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.

TheTable
Order# Customer Status

STATUS has valid values of PROCESSED and INPROGRESS

The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.

I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?

Select Customer, Count (*) As Status1
FROM TheTable
Where (Status = N'Shipped')
Group By Customeryou can write a stored proc and use output parameters to return the values..and in your stored proc have multiple sql stmts to get the diff counts..

hth|||This might work for you, there is probably a better way, this can become very expensive with the 2 sub queries if there is a lot of data

 SELECT Customer,
 (
 SELECT COUNT(*)
 FROM TheTable
 WHERE Status = N'Shipped'
 ) AS Shipped,
 (
 SELECT COUNT(*)
 FROM TheTable
 WHERE Status = N'SomeOtherStatus'
 ) AS SomeOtherStatus
 FROM TheTable
 ORDER BY Customer

Counting items and returning values

I have a table of product orders. It contains a row for "platform" and I
need to return how many times each platform is listed in the DB

Example data for platform could be:
XBOX
XBOX
XBOX
PLAYSTATION
PLAYSTATION
GAMECUBE
PLAYSTATION

I'd like the data to be returned as

XBOX - 3
PLAYSTATION - 3
GAMECUBE - 1

How would I go about doing this please?if the name of the column is "name":
SELECT name, COUNT(name)
GROUP BY name

HTH,
-Cliff

"Andrew Banks" <banksy@.nojunkblueyonder.co.uk> wrote in message
news:ks%ac.351$lN4.6788392@.news-text.cableinet.net...
> I have a table of product orders. It contains a row for "platform" and I
> need to return how many times each platform is listed in the DB
> Example data for platform could be:
> XBOX
> XBOX
> XBOX
> PLAYSTATION
> PLAYSTATION
> GAMECUBE
> PLAYSTATION
> I'd like the data to be returned as
> XBOX - 3
> PLAYSTATION - 3
> GAMECUBE - 1
> How would I go about doing this please?

Sunday, March 25, 2012

Create a script rename value of a column

Create a script rename value of a column

Create a script rename value of a column

Sunday, March 11, 2012

CR not evaluating NULL values in formula

CR in VB.Net

CPU, Physical IO, & Memory Usage

Thursday, March 8, 2012

CPU, Physical IO, & Memory Usage

CPU, Physical IO, & Memory Usage

Wednesday, March 7, 2012

CPU unit in Profiler

CPU unit in Profiler

CPU unit in Profiler

Sunday, February 19, 2012

counting values in aresult set

counting values in aresult set

counting values in aresult set

Counting values for transportation mode

Counting Unique Values?

Counting the occurence of a string ...

Friday, February 17, 2012

counting occurrences of a value

counting multiple values from the same column and grouping by a another column

Counting items and returning values

CREATE DATABASE failed

Blog Archive

About Me