Create a string with carriage return and line feed

Hello,
Is it possible to create a variable that has carriage return and line feed
embed inside. Say I have 3 fields: field1, field2, field3. I want to return
a
string with carriage returns and line feeds between field1, 2 and field2, 3.
Thanks in advanceLOOK in BOL for CHAR,
TAB Char(9)
LineFeed Char(10)
CarriageReturn Char(13)
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Ed Chiu" <EdChiu@.discussions.microsoft.com> schrieb im Newsbeitrag
news:462D3FCC-28E9-4660-A5F7-559D5CB3CA33@.microsoft.com...
> Hello,
> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to
> return a
> string with carriage returns and line feeds between field1, 2 and field2,
> 3.
> Thanks in advance|||> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to
return a
> string with carriage returns and line feeds between field1, 2 and field2,
3.
Include CHAR(13)+CHAR(10) when you are concatenating the strings.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Try,
select 'Microsoft' + char(13) + char(10) + 'SQL' + char(13) + char(10) +
'Server' + char(13) + char(10) + '2000'
AMB
"Ed Chiu" wrote:

> Hello,
> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to retur
n a
> string with carriage returns and line feeds between field1, 2 and field2,
3.
> Thanks in advance|||DECLARE @.var VARCHAR(50)
SET @.var = 'joe' + CHAR(13) + CHAR(10) + 'blow'
SELECT @.var
It's up to the consumer of the data to display it properly. For example
in Query Analyzer, if you choose to display results in a grid, it
replaces the carriage return, line feed with spaces. If you choose to
display in text mode, it'll put the carriage return, line feed in there
for you.
If you're returning this to a .net dataset, you can display it properly.
HTH...
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
Ed Chiu wrote:
> Hello,
> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to retur
n a
> string with carriage returns and line feeds between field1, 2 and field2,
3.
> Thanks in advancesql

Create a new table from 2 diffrent tables

Hello Newsgroup,
I have two tables(table1, table2) with diffrent fields and I want to create
a new table (newtable) with fields from table1 and table2
Example:
table table1
(
T1field1 char (4),
T1field2 char (4),
)
table table2
(
T2field1 char (4),
T2field2 char (4),
T2field3 char (4),
)
How can I do a query who can give me the result in a new table
like here:
table newtable
(
T1field1 char (4),
T1field2 char (4),
T2field1 char (4),
T2field2 char (4),
T2field3 char (4),
)
Thank you for youre time
WilliWhat are the keys? What do the tow tables have in common? What are the
relationships?
Please either post complete DDL of the tables, or at least include a
description of the relationship(s) between the two tables.
ML
http://milambda.blogspot.com/|||Iam not sure whether i understood your requirement... what iyou are asking
for is simple...
Select * INTO table from table1,table2
This wont make much sense, do u have any connecting feild between these two
tables.
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Willi Kolmbach" wrote:

> Hello Newsgroup,
> I have two tables(table1, table2) with diffrent fields and I want to creat
e
> a new table (newtable) with fields from table1 and table2
> Example:
> table table1
> (
> T1field1 char (4),
> T1field2 char (4),
> )
> table table2
> (
> T2field1 char (4),
> T2field2 char (4),
> T2field3 char (4),
> )
>
> How can I do a query who can give me the result in a new table
> like here:
> table newtable
> (
> T1field1 char (4),
> T1field2 char (4),
> T2field1 char (4),
> T2field2 char (4),
> T2field3 char (4),
> )
> Thank you for youre time
> Willi
>
>|||Thank you for the fast reply !
Willi
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:61B7DBE9-85E0-4C26-9AEA-24BC01E320E7@.microsoft.com...
> Iam not sure whether i understood your requirement... what iyou are asking
> for is simple...
> Select * INTO table from table1,table2
> This wont make much sense, do u have any connecting feild between these
> two
> tables.
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and
> time
> asking back if its 2000 or 2005]
>
> "Willi Kolmbach" wrote:
>

Create a Formula to Represent Shift

I have 4 fields that I need to create a formula on that shows as Shift 1 and Shift 2 the fields are

Type: String

Shift1begins="06:45" Shift1Ends="19:15"

Shift2begins="18:45" Shift2Ends="07:15"

My intention is to create a formula for a report so that I can use the Shift1 and Shift 2 as parameter choices in my report. Can you help?

Thanks!Can you give alittle more information as to what you need the formulas to do? Do you want to pass the work "Shift1" and have Crystal know that it means between 06:45 and 19:15? Or do you want to pass a time and have Crystal tell you what shift it fall under? Or something totally different?|||My report is a productivity report on our Therapists. Some work from 6:45 am to 7:15P this is shift 1. Shift 2 works from 6:45 pm to 7:00 am.

There are 4 fields that represent the times that are in shift 1 and 2 the beginning time and the endtime. My report has a date parameter that allows me to pull a range of data for our Therapists but it doesn't pull the correct data for the night shift because there is no way to specify the shift times. The report consists of

Therapists Name
Dates Worked
Daily Productivity for each day
Productivity Average for each month.

Day shift works fine cause it doesn't span two days. Night shift is inaccurate because it only calculates the time for the one date entered and doesn't allow for the time that scales both days. So I figured that if I set up a parameter based on the shift that this would work. I added the table that has the field in it to the report (which is a canned report anyway) and tried to create a parameter but I am not doing it correctly and that is what I need help with. Thanks!|||Originally posted by Shiloh917
My report is a productivity report on our Therapists. Some work from 6:45 am to 7:15P this is shift 1. Shift 2 works from 6:45 pm to 7:00 am.

There are 4 fields that represent the times that are in shift 1 and 2 the beginning time and the endtime. My report has a date parameter that allows me to pull a range of data for our Therapists but it doesn't pull the correct data for the night shift because there is no way to specify the shift times. The report consists of

Therapists Name
Dates Worked
Daily Productivity for each day
Productivity Average for each month.

Day shift works fine cause it doesn't span two days. Night shift is inaccurate because it only calculates the time for the one date entered and doesn't allow for the time that scales both days. So I figured that if I set up a parameter based on the shift that this would work. I added the table that has the field in it to the report (which is a canned report anyway) and tried to create a parameter but I am not doing it correctly and that is what I need help with. Thanks!

Hi,

If your fields contains datatime datatype you can calculate enddatetime - startdatetime that returns the value of number of seconds difference.|||No that doesn't work. I have date parameters that I have tried changing to "datetime" datatype but it works fine for day shift but on night shift it will not calculate correctly. You see our Therapists on Night shift come in at 6:45 pm and they get off at 7:15 am so they may come in for example on 02/10/04 and their shift will end on 02/11/04. That is why I figured if I use the shift field which is already a pre-filled value at 6:45 pm and 7:15am to represent shift 2 (made up of of two fields which say Shift2Begins and shift2Ends) that I might be able to get the correct data. I guess there is just know way to do this then. So thank you anyway. I appreciate your efforts.

Shiloh|||Originally posted by Shiloh917
No that doesn't work. I have date parameters that I have tried changing to "datetime" datatype but it works fine for day shift but on night shift it will not calculate correctly. You see our Therapists on Night shift come in at 6:45 pm and they get off at 7:15 am so they may come in for example on 02/10/04 and their shift will end on 02/11/04. That is why I figured if I use the shift field which is already a pre-filled value at 6:45 pm and 7:15am to represent shift 2 (made up of of two fields which say Shift2Begins and shift2Ends) that I might be able to get the correct data. I guess there is just know way to do this then. So thank you anyway. I appreciate your efforts.

Shiloh

use this following functions in the crystal report formula field.

datediff ("h", DateTimeValue (2004,04,26,20,0,0),DateTimeValue (2004,04,27,1,20,0))

create a empty DB from a existing DB

how to: create a empty DB from a existing DB keeping all fields and keys intact

A clean way to create an empty database from an existing populated database is to script all the database objects in the database including Stored Procedures, Tables, User-defined data types and Views. Follow these steps in SQL Server 2005:

1. In SQL Server Management Studio, right-click the database and click Tasks > Generate Scripts...

2. Click the Next button on the Welcome dialog box.

3. Check the "Script all objects in the selected database" option and click the Finish button. The script will be generated and dumped into a query window.

4. Save the script as a SQL Server script file.

5. Run the script file on your destination server to recreate the database without the data.

Hope this helps.

|||Is there a way to generate scripts that also contain the data?

create a empty DB from a existing DB

how to: create a empty DB from a existing DB keeping all fields and keys intact

A clean way to create an empty database from an existing populated database is to script all the database objects in the database including Stored Procedures, Tables, User-defined data types and Views. Follow these steps in SQL Server 2005:

1. In SQL Server Management Studio, right-click the database and click Tasks > Generate Scripts...

2. Click the Next button on the Welcome dialog box.

3. Check the "Script all objects in the selected database" option and click the Finish button. The script will be generated and dumped into a query window.

4. Save the script as a SQL Server script file.

5. Run the script file on your destination server to recreate the database without the data.

Hope this helps.

|||Is there a way to generate scripts that also contain the data?

CR 8.5: Help With Formula

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

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

But if the value is 0 i dont show a string

Example:

DB Fields with Values

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

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

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

CR & LF Problems

I'm trying to concatenate three address fields into one for a SELECT
statement. Below is that part:
CASE WHEN ADDR1 IS NULL
THEN ''
ELSE ADDR1 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR2 IS NULL
THEN ''
ELSE ADDR2 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR3 IS NULL
THEN ''
ELSE ADDR3
END AS Address,
But the results do not have the CR and LF in it. What am I doing
wrong?I don't see a problem here:
create table #tmp (addr1 varchar(20), addr2 varchar(20), addr3 varchar(20))
insert #tmp values ('line 1 field 1', 'line 1 field 2', 'line 1 field 3')
insert #tmp values ('line 2 field 1', 'line 2 field 2', NULL)
insert #tmp values (NULL, 'line 3 field 2', NULL)
SELECT
CASE WHEN ADDR1 IS NULL
THEN ''
ELSE ADDR1 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR2 IS NULL
THEN ''
ELSE ADDR2 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR3 IS NULL
THEN ''
ELSE ADDR3
END AS Address
FROM #tmp
output:
Address
---
line 1 field 1
line 1 field 2
line 1 field 3
line 2 field 1
line 2 field 2
line 3 field 2
P.S. It sure woulda been nice if you had taken the time to construct the
demo code I did! :-))
TheSQLGuru
President
Indicium Resources, Inc.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1178224810.009904.225630@.h2g2000hsg.googlegroups.com...
> I'm trying to concatenate three address fields into one for a SELECT
> statement. Below is that part:
> CASE WHEN ADDR1 IS NULL
> THEN ''
> ELSE ADDR1 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR2 IS NULL
> THEN ''
> ELSE ADDR2 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR3 IS NULL
> THEN ''
> ELSE ADDR3
> END AS Address,
> But the results do not have the CR and LF in it. What am I doing
> wrong?
>|||Paul,
If you run this query to return results in a grid in Query Analyzer or the
Management Studio Query editor (depending on version of SQL) the results
appear in a single cell. But if your results return as text you will see
the line breaks.
RLF
"Paul" <pwh777@.hotmail.com> wrote in message
news:1178224810.009904.225630@.h2g2000hsg.googlegroups.com...
> I'm trying to concatenate three address fields into one for a SELECT
> statement. Below is that part:
> CASE WHEN ADDR1 IS NULL
> THEN ''
> ELSE ADDR1 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR2 IS NULL
> THEN ''
> ELSE ADDR2 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR3 IS NULL
> THEN ''
> ELSE ADDR3
> END AS Address,
> But the results do not have the CR and LF in it. What am I doing
> wrong?
>

CR & LF Problems

I'm trying to concatenate three address fields into one for a SELECT
statement. Below is that part:
CASE WHEN ADDR1 IS NULL
THEN ''
ELSE ADDR1 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR2 IS NULL
THEN ''
ELSE ADDR2 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR3 IS NULL
THEN ''
ELSE ADDR3
END AS Address,
But the results do not have the CR and LF in it. What am I doing
wrong?
I don't see a problem here:
create table #tmp (addr1 varchar(20), addr2 varchar(20), addr3 varchar(20))
insert #tmp values ('line 1 field 1', 'line 1 field 2', 'line 1 field 3')
insert #tmp values ('line 2 field 1', 'line 2 field 2', NULL)
insert #tmp values (NULL, 'line 3 field 2', NULL)
SELECT
CASE WHEN ADDR1 IS NULL
THEN ''
ELSE ADDR1 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR2 IS NULL
THEN ''
ELSE ADDR2 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR3 IS NULL
THEN ''
ELSE ADDR3
END AS Address
FROM #tmp
output:
Address
line 1 field 1
line 1 field 2
line 1 field 3
line 2 field 1
line 2 field 2
line 3 field 2
P.S. It sure woulda been nice if you had taken the time to construct the
demo code I did! :-))
TheSQLGuru
President
Indicium Resources, Inc.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1178224810.009904.225630@.h2g2000hsg.googlegro ups.com...
> I'm trying to concatenate three address fields into one for a SELECT
> statement. Below is that part:
> CASE WHEN ADDR1 IS NULL
> THEN ''
> ELSE ADDR1 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR2 IS NULL
> THEN ''
> ELSE ADDR2 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR3 IS NULL
> THEN ''
> ELSE ADDR3
> END AS Address,
> But the results do not have the CR and LF in it. What am I doing
> wrong?
>
|||Paul,
If you run this query to return results in a grid in Query Analyzer or the
Management Studio Query editor (depending on version of SQL) the results
appear in a single cell. But if your results return as text you will see
the line breaks.
RLF
"Paul" <pwh777@.hotmail.com> wrote in message
news:1178224810.009904.225630@.h2g2000hsg.googlegro ups.com...
> I'm trying to concatenate three address fields into one for a SELECT
> statement. Below is that part:
> CASE WHEN ADDR1 IS NULL
> THEN ''
> ELSE ADDR1 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR2 IS NULL
> THEN ''
> ELSE ADDR2 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR3 IS NULL
> THEN ''
> ELSE ADDR3
> END AS Address,
> But the results do not have the CR and LF in it. What am I doing
> wrong?
>

CR & LF Problems

I'm trying to concatenate three address fields into one for a SELECT
statement. Below is that part:
CASE WHEN ADDR1 IS NULL
THEN ''
ELSE ADDR1 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR2 IS NULL
THEN ''
ELSE ADDR2 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR3 IS NULL
THEN ''
ELSE ADDR3
END AS Address,
But the results do not have the CR and LF in it. What am I doing
wrong?I don't see a problem here:
create table #tmp (addr1 varchar(20), addr2 varchar(20), addr3 varchar(20))
insert #tmp values ('line 1 field 1', 'line 1 field 2', 'line 1 field 3')
insert #tmp values ('line 2 field 1', 'line 2 field 2', NULL)
insert #tmp values (NULL, 'line 3 field 2', NULL)
SELECT
CASE WHEN ADDR1 IS NULL
THEN ''
ELSE ADDR1 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR2 IS NULL
THEN ''
ELSE ADDR2 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR3 IS NULL
THEN ''
ELSE ADDR3
END AS Address
FROM #tmp
output:
Address
---
line 1 field 1
line 1 field 2
line 1 field 3
line 2 field 1
line 2 field 2
line 3 field 2
P.S. It sure woulda been nice if you had taken the time to construct the
demo code I did! :-))
--
TheSQLGuru
President
Indicium Resources, Inc.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1178224810.009904.225630@.h2g2000hsg.googlegroups.com...
> I'm trying to concatenate three address fields into one for a SELECT
> statement. Below is that part:
> CASE WHEN ADDR1 IS NULL
> THEN ''
> ELSE ADDR1 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR2 IS NULL
> THEN ''
> ELSE ADDR2 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR3 IS NULL
> THEN ''
> ELSE ADDR3
> END AS Address,
> But the results do not have the CR and LF in it. What am I doing
> wrong?
>|||Paul,
If you run this query to return results in a grid in Query Analyzer or the
Management Studio Query editor (depending on version of SQL) the results
appear in a single cell. But if your results return as text you will see
the line breaks.
RLF
"Paul" <pwh777@.hotmail.com> wrote in message
news:1178224810.009904.225630@.h2g2000hsg.googlegroups.com...
> I'm trying to concatenate three address fields into one for a SELECT
> statement. Below is that part:
> CASE WHEN ADDR1 IS NULL
> THEN ''
> ELSE ADDR1 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR2 IS NULL
> THEN ''
> ELSE ADDR2 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR3 IS NULL
> THEN ''
> ELSE ADDR3
> END AS Address,
> But the results do not have the CR and LF in it. What am I doing
> wrong?
>

covering Primary Keys.. What's too much

I have a table which I'd like to create a Primary Key that would have to
cover 6 fields to enforce uniqueness.
My question is, is that too much. I know it's common to have tables with
PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields, 2
varchar(5) fields, 1 tinyint]
I know I can use a surrogate "ID" identity field as the PK, but I prefer to
use PK's that are more natural if possible.
Any thoughts on this?
Thanks,
ChrisWhatever has to be unique must be constrained to be unique. IOW, if your
business rules require that this table have a 6-col combination that is
unique, then you should make that unique - either via a PRIMARY KEY or
UNIQUE constraint. That said, if you will have other tables referencing
this table, then it would make sense to create a surrogate key and then use
that key in the FOREIGN KEY constraints.
That said, consider the surrogate key anyway and cluster on it. Make it,
say, your PK. Then, create a UNIQUE constraint on the 6 col combo. Inserts
into the table - if you use an identity for the PK, will perform well and
the table (i.e. clustered index) will not need to be defragged. The
nonclustered index on the UNIQUE constraint would likely need a fill factor
< 100 to mitigate fragmentation.
HTH
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Chris" <rooster575@.hotmail.com> wrote in message
news:%23VyZlhmhGHA.4864@.TK2MSFTNGP05.phx.gbl...
I have a table which I'd like to create a Primary Key that would have to
cover 6 fields to enforce uniqueness.
My question is, is that too much. I know it's common to have tables with
PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields, 2
varchar(5) fields, 1 tinyint]
I know I can use a surrogate "ID" identity field as the PK, but I prefer to
use PK's that are more natural if possible.
Any thoughts on this?
Thanks,
Chris|||This table holds only FK's and could be considered a "detail" table.
It's also almost completely a "read" table. Writing is completed and then
done again very seldomly.
If I add an "ID" column as a PK, it will never be referenced by another
table. [No FK's will exist], which makes me lean more towards the 6-key PK
option. Does this sway your answer at all?
I appreciate your help. After re-reading your response, the solution seems
clear. What do you think?
-Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ORD4WpmhGHA.3896@.TK2MSFTNGP02.phx.gbl...
> Whatever has to be unique must be constrained to be unique. IOW, if your
> business rules require that this table have a 6-col combination that is
> unique, then you should make that unique - either via a PRIMARY KEY or
> UNIQUE constraint. That said, if you will have other tables referencing
> this table, then it would make sense to create a surrogate key and then
> use
> that key in the FOREIGN KEY constraints.
> That said, consider the surrogate key anyway and cluster on it. Make it,
> say, your PK. Then, create a UNIQUE constraint on the 6 col combo.
> Inserts
> into the table - if you use an identity for the PK, will perform well and
> the table (i.e. clustered index) will not need to be defragged. The
> nonclustered index on the UNIQUE constraint would likely need a fill
> factor
> < 100 to mitigate fragmentation.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:%23VyZlhmhGHA.4864@.TK2MSFTNGP05.phx.gbl...
> I have a table which I'd like to create a Primary Key that would have to
> cover 6 fields to enforce uniqueness.
> My question is, is that too much. I know it's common to have tables with
> PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields,
> 2
> varchar(5) fields, 1 tinyint]
> I know I can use a surrogate "ID" identity field as the PK, but I prefer
> to
> use PK's that are more natural if possible.
> Any thoughts on this?
> Thanks,
> Chris
>|||If that is the case, then you're probably fine without the surrogate key.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Chris" <rooster575@.hotmail.com> wrote in message
news:%23P2RCumhGHA.3996@.TK2MSFTNGP03.phx.gbl...
This table holds only FK's and could be considered a "detail" table.
It's also almost completely a "read" table. Writing is completed and then
done again very seldomly.
If I add an "ID" column as a PK, it will never be referenced by another
table. [No FK's will exist], which makes me lean more towards the 6-key PK
option. Does this sway your answer at all?
I appreciate your help. After re-reading your response, the solution seems
clear. What do you think?
-Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ORD4WpmhGHA.3896@.TK2MSFTNGP02.phx.gbl...
> Whatever has to be unique must be constrained to be unique. IOW, if your
> business rules require that this table have a 6-col combination that is
> unique, then you should make that unique - either via a PRIMARY KEY or
> UNIQUE constraint. That said, if you will have other tables referencing
> this table, then it would make sense to create a surrogate key and then
> use
> that key in the FOREIGN KEY constraints.
> That said, consider the surrogate key anyway and cluster on it. Make it,
> say, your PK. Then, create a UNIQUE constraint on the 6 col combo.
> Inserts
> into the table - if you use an identity for the PK, will perform well and
> the table (i.e. clustered index) will not need to be defragged. The
> nonclustered index on the UNIQUE constraint would likely need a fill
> factor
> < 100 to mitigate fragmentation.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:%23VyZlhmhGHA.4864@.TK2MSFTNGP05.phx.gbl...
> I have a table which I'd like to create a Primary Key that would have to
> cover 6 fields to enforce uniqueness.
> My question is, is that too much. I know it's common to have tables with
> PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields,
> 2
> varchar(5) fields, 1 tinyint]
> I know I can use a surrogate "ID" identity field as the PK, but I prefer
> to
> use PK's that are more natural if possible.
> Any thoughts on this?
> Thanks,
> Chris
>|||Thanks again.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eebldFnhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> If that is the case, then you're probably fine without the surrogate key.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:%23P2RCumhGHA.3996@.TK2MSFTNGP03.phx.gbl...
> This table holds only FK's and could be considered a "detail" table.
> It's also almost completely a "read" table. Writing is completed and then
> done again very seldomly.
> If I add an "ID" column as a PK, it will never be referenced by another
> table. [No FK's will exist], which makes me lean more towards the 6-key PK
> option. Does this sway your answer at all?
> I appreciate your help. After re-reading your response, the solution seems
> clear. What do you think?
> -Chris
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ORD4WpmhGHA.3896@.TK2MSFTNGP02.phx.gbl...
>|||>> I have a table which I'd like to create a Primary Key that would have to cove
r 6 fields [sic] to enforce uniqueness. My question is, is that too much. <<
This is rare but possible. Since we do not have any specs or knwo
anything about the problem, all we can do is generalize. I have seen
some people create super-keys (sertial number + manufacturer, not
knowning that serial number has manufacturer in it).
1) it is not a surrogate key because it is exposed to the user; that is
the definition from Dr. Codd.
2) you will need to use a UNIQUE (c1,.. ,c6) constraint anyway, so you
now have two indexes
3) You will need code to assure that the IDENTITY is always validate
and points to the right target.

Counting the number of fields that are populated in row

I have 2 large tables that I'm selecting off of. part of my select is
to get the number of stores visited. The table that has this
information has the store names as columns. If a store wasn't visited
there will be a null value. I need to be able to count the number of
fields that don't have the null value in order to get the desired
result. I can't use the Count() function as the select statement is
very large. and I don't want to group by all of the fields that I'm
selecting.
Does anyone know of another way to get this?what data type are the store name columns?
numeric (of some sort) indicating number of visits?
bit indicating visited?
are you returning the store columns as well?
dmagoo22 wrote:
> I have 2 large tables that I'm selecting off of. part of my select is
> to get the number of stores visited. The table that has this
> information has the store names as columns. If a store wasn't visited
> there will be a null value. I need to be able to count the number of
> fields that don't have the null value in order to get the desired
> result. I can't use the Count() function as the select statement is
> very large. and I don't want to group by all of the fields that I'm
> selecting.
>
> Does anyone know of another way to get this?
>|||> to get the number of stores visited. The table that has this
> information has the store names as columns.
Egads. So if you add a store, you change your schema? That's really not
how it should work. The store names are *data* not *metadata*...

> If a store wasn't visited
> there will be a null value. I need to be able to count the number of
> fields that don't have the null value in order to get the desired
> result.
If this returns exactly one row, you can try this:
SELECT
col1,
col2,
NumberOfStoresVisited =
CASE WHEN Store1 = NULL THEN 0 ELSE 1 END
+ CASE WHEN Store2 = NULL THEN 0 ELSE 1 END
+ ...
+ CASE WHEN StoreN = NULL THEN 0 ELSE 1 END
FROM
table
...
Without better specs, that's about as good as I can do. Please see
http://www.aspfaq.com/5006|||> CASE WHEN Store1 = NULL THEN 0 ELSE 1 END
WHOA! I meant WHEN Store1 IS NULL
*smack*|||try the @.@.rowcount Function
run the select statement to select where your column is not null and then
get the @.@.rowcount
"Aaron Bertrand [SQL Server MVP]" wrote:

> WHOA! I meant WHEN Store1 IS NULL
> *smack*
>
>|||> try the @.@.rowcount Function
> run the select statement to select where your column is not null and then
> get the @.@.rowcount
Unfortunately, I think the OP's table looks like this:
Store1 Store2 Store3 Store4 Store5
5 12 NULL 3 1
So @.@.ROWCOUNT will always be 1...|||Oops!!!! Sorry thot the data was in multiple rows
"Aaron Bertrand [SQL Server MVP]" wrote:

> Unfortunately, I think the OP's table looks like this:
> Store1 Store2 Store3 Store4 Store5
> 5 12 NULL 3 1
> So @.@.ROWCOUNT will always be 1...
>
>

Counting NULL columns?

If I had a basic table with 3 VARCHAR fields; let's say A, B, C

How could I write a query that returns the count of the number of NULL columns for every record in a table?

Ideally, it would be something like:

SELECT
CAST (A IS NULL) AS INTEGER
+ CAST (B IS NULL) AS INTEGER
+ CAST (C IS NULL) AS INTEGER
FROM MyTable

That doesn't work at all. I can't seem to do "IS NULL" in the SELECT area. Should I write a T-SQL user-defined function that takes all three columns as parameters? Would that be performance friendly for large data sets?select 'a is null', count(*) from mytable where a is null
union all
select 'b is null', count(*) from mytable where b is null
union all
select 'c is null', count(*) from mytable where c is null
union all
select 'all are null', count(*) where (a is null or b is null or c is null)|||select sum(case when A is null then 1 else 0 end) as Anulls
, sum(case when B is null then 1 else 0 end) as Bnulls
, sum(case when C is null then 1 else 0 end) as Cnulls
from yourtable|||Rudy, I think he wants to count the nulls for each row:

select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable|||blindman, you are too right

the results of your query, however, are totally useless, as there is nothing to tell you which rows have which numbers of nulls

3
2
0
1
0
0
0
2
0
3
0
2
1

at least my query actually produces something useful

:cool:|||Hey, he only said he wanted the count! Ain't my problem if the project specs are no good... :D

And Roger, yes if you have a lot of columns it might be worthwhile to write a function that returns 1 if a value is Null, and 0 if it is not. Call it "NullBit" or something.

... and make Rudy happy by at least including a primary key in your result set!|||many ways to skin the null|||Rudy, I think he wants to count the nulls for each row:

select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable

Yes, you are right; that is exactly what I wanted. It works perfectly!! Thank you so much!! I'm surprised; no one else in my office could come up with this. I was planning on writing a user-defined function with if statements but this is much more elegant.

Thanks Rudy and blindman!

FYI, I'm using this in a WHERE clause for a a large UPDATE command that merges import data into a production table. I only want to overwrite the existing data if the new data has more non-NULL fields.|||I only want to overwrite the existing data if the new data has more non-NULL fields.
surely the number of non-nulls is of secondary concern

suppose i had a Three Stooges table with this row:

'curly','larry',null

you're saying it's okay to overwrite this with

'tom','dick','harry'

but i've done many merges myself, and i'm sure there's more to your example than just three fields...|||Consider using this instead:

Update A
set A.Stooge1 = coalesce(A.Stooge1, B.Stooge1),
A.Stooge2 = coalesce(A.Stooge2, B.Stooge2),
A.Stooge3 = coalesce(A.Stooge3, B.Stooge3),
..etc..
from A inner join B on A.PKey = B.Pkey

This merges the two datasets together, giving priority to data in table A.|||Follow the blindman, follow the blindman!!!

-PatP|||Only when you are in the dark...|||nice

might also want to add a WHERE clause so you don't unnecessarily update every row, just the ones which actually have changed|||Good point. It's a tough call on what proportion of the rows need to be updated to justify the overhead of the where clause. Maybe for a one-time shot a SELECT INTO followed by renaming the resulting table would be fastest?

Counting Group fields

Is there a way I can count the number of grouped items in a textbox in a
report?
When I try to use =count(Fields!UserID.Value), the report gives me the
amount of records in the query.
I also tried using count distinct but that returned the same results.
Is there anyway I can just count the grouped listings? Or maybe count the
number of table rows?
TIA,
JacksonI think you can specify a scope for the count method ie
=count(Fields!UserID.Value, "GroupName")
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_9bp0.asp|||Thanks Thomo,
Where would I be able to find the group name? The only group names I've
found is called table1_UserID and table1_details_group but they don't seem
to work.
Thanks again for the help,
Jackson
"Thomo" <greg.thomson@.gmail.com> wrote in message
news:1147102016.645069.295180@.e56g2000cwe.googlegroups.com...
>I think you can specify a scope for the count method ie
> =count(Fields!UserID.Value, "GroupName")
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_9bp0.asp
>|||Those sound like legit group names. You can find group names by right
clicking on the beginning of a group row and selecting edit group from
the context menu. The name will be on the top portion of the general
tab of the Grouping and Sorting Properties window. The names work fine
as the scope argument for the count statement, making sure that the
group name is in double quotes since it needs to be a string argument.
You didn't say what kind of error you're getting, but maybe its the
placement of your aggregate field. Try putting it in the group footer,
for example, and see if it works.|||Putting it into the group footer worked. I was trying it in the Page
footer. But the problem is that when I do a count it counts all of the
records.
I just want to include a count of how many Users are included in the report.
In my report I have UserID's grouped and within the group is the details of
their previous transactions. And for some reason it's counting the records
instead of just the groups.
Any suggestions?
Thanks again,
Jackson
"dba56" <louvig4@.att.net> wrote in message
news:1147115725.622812.317540@.y43g2000cwc.googlegroups.com...
> Those sound like legit group names. You can find group names by right
> clicking on the beginning of a group row and selecting edit group from
> the context menu. The name will be on the top portion of the general
> tab of the Grouping and Sorting Properties window. The names work fine
> as the scope argument for the count statement, making sure that the
> group name is in double quotes since it needs to be a string argument.
> You didn't say what kind of error you're getting, but maybe its the
> placement of your aggregate field. Try putting it in the group footer,
> for example, and see if it works.
>|||doh, nevermind. I took a look at the link that Thomo had sent and I tried
using countdistinct again in the footer and it worked.
I could have sworn I tried countdistinct and it came out different.
Oh well thanks for all of the help.
Jackson
"Jackson" <jackson_num5@.yahoo.com> wrote in message
news:eK%23I1ntcGHA.3632@.TK2MSFTNGP02.phx.gbl...
> Putting it into the group footer worked. I was trying it in the Page
> footer. But the problem is that when I do a count it counts all of the
> records.
> I just want to include a count of how many Users are included in the
> report.
> In my report I have UserID's grouped and within the group is the details
> of their previous transactions. And for some reason it's counting the
> records instead of just the groups.
> Any suggestions?
> Thanks again,
> Jackson
> "dba56" <louvig4@.att.net> wrote in message
> news:1147115725.622812.317540@.y43g2000cwc.googlegroups.com...
>> Those sound like legit group names. You can find group names by right
>> clicking on the beginning of a group row and selecting edit group from
>> the context menu. The name will be on the top portion of the general
>> tab of the Grouping and Sorting Properties window. The names work fine
>> as the scope argument for the count statement, making sure that the
>> group name is in double quotes since it needs to be a string argument.
>> You didn't say what kind of error you're getting, but maybe its the
>> placement of your aggregate field. Try putting it in the group footer,
>> for example, and see if it works.
>

Counting filtered fields

I have a field for the number of items. I need to count how many of these items contain the word "hello" and I need to express this as a percentage of the total number of items. What would be the best way to do this?
Thankswhileprintingrecords;
numbervar a;
if field='hello' then
a:=a+1

i haven't tested it, give it try...

good luck

Counting fields with data

I call a Crystal Reports 8 report from VB6. In the report there is eight fields as part of each record. Some of those eight fields will contain data and some won't. Random order.

How can I keep count of how many fields has data in them for each record. I need to display a count at the end of the record i.e. 3/8 or 0/8 etc.

I think the idea would be to create a counter that can be initialised to 0 for each record and as each field is displayed it must increment the counter but only if there is data displayed in the field so if 3 fields (any 3 of the 8 fields) contain data the counter will be 3 for that particular record and the counter can then be displayed.

Can this be accomplished and how?

Bezziewrite a formula like

numbervar a;
a:=0;
if not isnull(databasefield1) then
a:=a+1;
if not isnull(databasefield2) then
a:=a+1;
a;|||Thanks sraheem.|||Another way is to create a Running Total for each field and add them together at the end of the report.

Counting consecutive # of days a condition is true

Hello,
I have a table. Among the fields are ServerName, ProcessDate and ErrorNumber. What I'd like is a select query which will generate a new field which counts back the consecutive number of days that ErrorNumber \= 0. I'd like the record set to include all Se
rverNames for each ProcessDate arguement.
In other words, the field would show a 5 if the Error Number is 1 for 5 days in a row.
I've been pulling my hair out about this.
Thanks!
Stewart,
Could you post some DDL and sample data? Also, by 'consecutive', do you
mean actual days or only business days?
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!
|||Hope this works for you... It's not the most elegant code but if I
understand your problem it should return what you need:
create table #process(servername char(1), servicedate datetime, errornum
int)
GO
insert #process values ('A', '20040101', 1)
insert #process values ('A', '20040102', 1)
insert #process values ('A', '20040103', 0)
insert #process values ('A', '20040104', 0)
insert #process values ('A', '20040105', 1)
insert #process values ('B', '20040101', 0)
insert #process values ('B', '20040102', 1)
insert #process values ('B', '20040103', 1)
insert #process values ('B', '20040104', 0)
insert #process values ('B', '20040105', 0)
insert #process values ('B', '20040106', 1)
insert #process values ('B', '20040107', 1)
insert #process values ('B', '20040108', 1)
insert #process values ('B', '20040109', 1)
GO
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and CASE WHEN EXISTS
(SELECT * FROM #process d
where d.servicedate < a.servicedate
and d.servername=a.servername
and d.errornum=0) THEN
CASE WHEN b.servicedate >
(select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername) THEN 1
else 0 end
else 1 end = 1) end
from #process a
GO
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!
|||Wow Adam! Thanks a tonne for this. In lookin at this, I understand the jist of what its doin. But I'm no database dynamo, so I'm gonna have to work with this for a bit to see what it'll actually return.
Thanks again!
|||Here's a slightly simplified version of the query:
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and b.servicedate >
coalesce((select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername),
b.servicedate - 1)
) end
from #process a
GO
"Stewart" <anonymous@.discussions.microsoft.com> wrote in message
news:2BC7AFB0-03E9-413B-8733-FABA0170CFFD@.microsoft.com...
> Wow Adam! Thanks a tonne for this. In lookin at this, I understand the
jist of what its doin. But I'm no database dynamo, so I'm gonna have to work
with this for a bit to see what it'll actually return.
> Thanks again!
|||Thats exactly it! I gotta be able to pass a parameter for each servicedate, but I'll be able to figure that out.
Thanks - I appreciate it!

Counting consecutive # of days a condition is true

Hello,
I have a table. Among the fields are ServerName, ProcessDate and ErrorNumber
. What I'd like is a select query which will generate a new field which coun
ts back the consecutive number of days that ErrorNumber \= 0. I'd like the r
ecord set to include all Se
rverNames for each ProcessDate arguement.
In other words, the field would show a 5 if the Error Number is 1 for 5 days
in a row.
I've been pulling my hair out about this.
Thanks!Stewart,
Could you post some DDL and sample data? Also, by 'consecutive', do you
mean actual days or only business days?
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!|||Hope this works for you... It's not the most elegant code but if I
understand your problem it should return what you need:
create table #process(servername char(1), servicedate datetime, errornum
int)
GO
insert #process values ('A', '20040101', 1)
insert #process values ('A', '20040102', 1)
insert #process values ('A', '20040103', 0)
insert #process values ('A', '20040104', 0)
insert #process values ('A', '20040105', 1)
insert #process values ('B', '20040101', 0)
insert #process values ('B', '20040102', 1)
insert #process values ('B', '20040103', 1)
insert #process values ('B', '20040104', 0)
insert #process values ('B', '20040105', 0)
insert #process values ('B', '20040106', 1)
insert #process values ('B', '20040107', 1)
insert #process values ('B', '20040108', 1)
insert #process values ('B', '20040109', 1)
GO
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and CASE WHEN EXISTS
(SELECT * FROM #process d
where d.servicedate < a.servicedate
and d.servername=a.servername
and d.errornum=0) THEN
CASE WHEN b.servicedate >
(select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername) THEN 1
else 0 end
else 1 end = 1) end
from #process a
GO
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!|||Wow Adam! Thanks a tonne for this. In lookin at this, I understand the jist
of what its doin. But I'm no database dynamo, so I'm gonna have to work with
this for a bit to see what it'll actually return.
Thanks again!|||Here's a slightly simplified version of the query:
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and b.servicedate >
coalesce((select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername),
b.servicedate - 1)
) end
from #process a
GO
"Stewart" <anonymous@.discussions.microsoft.com> wrote in message
news:2BC7AFB0-03E9-413B-8733-FABA0170CFFD@.microsoft.com...
> Wow Adam! Thanks a tonne for this. In lookin at this, I understand the
jist of what its doin. But I'm no database dynamo, so I'm gonna have to work
with this for a bit to see what it'll actually return.
> Thanks again!|||Thats exactly it! I gotta be able to pass a parameter for each servicedate,
but I'll be able to figure that out.
Thanks - I appreciate it!

Counting based on a field combination

Having a brainfart...

I need a query that returns a record count, based on two distinct fields.

For example:

Order Revision Customer
001 1 Bob
001 2 Bob
002 1 John
003 1 John
004 1 John
005 1 Bob
006 1 Bob
006 2 Bob

The query on the above data should return a count of orders, regardless of
the revision numbers (each order number should only be counted once).

So WHERE Customer = 'Bob', it should return OrderCount = 3

TIA!

Calan> The query on the above data should return a count of orders,
regardless of
> the revision numbers (each order number should only be counted once).
> So WHERE Customer = 'Bob', it should return OrderCount = 3
> TIA!
> Calan

--something like this?

create table #foo (
Ord int,
Revision int,
Customer varchar(10)
primary key (ord,revision)
)
GO

insert into #foo( Ord, Revision, Customer )Values(001, 1, 'Bob' )
insert into #foo( Ord, Revision, Customer )Values(001, 2, 'Bob' )
insert into #foo( Ord, Revision, Customer )Values(002, 1, 'John' )
insert into #foo( Ord, Revision, Customer )Values(003, 1, 'John' )
insert into #foo( Ord, Revision, Customer )Values(004, 1, 'John' )
insert into #foo( Ord, Revision, Customer )Values(005, 1, 'Bob' )
insert into #foo( Ord, Revision, Customer )Values(006, 1, 'Bob' )
insert into #foo( Ord, Revision, Customer )Values(006, 2, 'Bob' )

select customer,count( distinct ord) as 'orders'
from #foo
group by customer

drop table #foo
go

--Strider|||Please give proper DDL with your posts, including the definition of
keys and constraints. They can make a big difference to the answer.
Post sample data as INSERT statements so that we can test out solutions
with your data.

Assuming the key in this case is (order,revision):

SELECT customer, count(*)
FROM Orders
GROUP BY customer

If I'm wrong about the key, try:

SELECT customer, count(DISTINCT order)
FROM Orders
GROUP BY customer

(both untested)

The COUNT(DISTINCT...) version typically runs significantly slower.

--
David Portas
SQL Server MVP
--

Counting Average Value

i want to count average on one of the fields in my table
i am doing a right click on it and expression and am typing the following
but getting 0 as an answer
=(( Fields!valueactiveaccounts.Value+ Fields!valueskipaccounts.Value)/( Fields!numactiveaccounts.Value+ Fields!numskipaccounts.Value))
is there any other way to get average value?Raj wrote:
> i want to count average on one of the fields in my table
> i am doing a right click on it and expression and am typing the
> following
> but getting 0 as an answer
> =(( Fields!valueactiveaccounts.Value+
> Fields!valueskipaccounts.Value)/( Fields!numactiveaccounts.Value+
> Fields!numskipaccounts.Value))
> is there any other way to get average value?
Try the inbuilt avg aggregate
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_10pz.asp
roland