Antivirus install to db server: state

Monday, March 12, 2012

Question about design practice - Address table

In a db, is it a good practice to have Address info in Address table in uppe
r
case or lower case?
ex: street name, city, state, county, country etc..
How about name [first name, middle name, last name, prefix, suffix...]
If you have any links that talk in detail about this, I'd like to read them
too
[ Some background: I am looking for some strong points so I could make an
impact in decision making...about best practice]
thanks everyone,
_UdayUsing all lower case for data is terrible. All upper case is bad -
takes me back to my mainframe days when we hardly knew there was a
shift key. I strongly prefer data read by people to be in proper
case: John Smith, Main St, Springfield.
State abbreviations are two character upper case: MA, OH, NY, CT, NJ,
WA.
However, when addresses are printed for the US Postal Service, they
prefer all caps. That change can be made during the printing process,
if at all.
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 12:11:02 -0700, Uday
<Uday@.discussions.microsoft.com> wrote:

>In a db, is it a good practice to have Address info in Address table in upp
er
>case or lower case?
>ex: street name, city, state, county, country etc..
>How about name [first name, middle name, last name, prefix, suffix...]
>If you have any links that talk in detail about this, I'd like to read them
>too
>[ Some background: I am looking for some strong points so I could make an
>impact in decision making...about best practice]
>thanks everyone,
>_Uday|||Default server collation is case in-sensitive.
So first step is, if you are worried about the case. check the collation of
the server,database. and upper case or lower case is a display property and
should be handled in the front end and usually based on requirements.
Hope this helps.|||I agree with you Ron. But the people who make decisions don't see data the
way I see it. So I need some strong points to make an impact on the decision
.
If I just say, 'keep the data in its original format' -- I dont think that'l
l
make a strong point. Hope you can understand the position I am in.
thanks to you all...Ron and Omnibuzz
_Uday
"Roy Harvey" wrote:

> Using all lower case for data is terrible. All upper case is bad -
> takes me back to my mainframe days when we hardly knew there was a
> shift key. I strongly prefer data read by people to be in proper
> case: John Smith, Main St, Springfield.
> State abbreviations are two character upper case: MA, OH, NY, CT, NJ,
> WA.
> However, when addresses are printed for the US Postal Service, they
> prefer all caps. That change can be made during the printing process,
> if at all.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 2 May 2006 12:11:02 -0700, Uday
> <Uday@.discussions.microsoft.com> wrote:
>
>|||Uday.
Few points to remember.
1. If you define some rules saying the names should have the first letter
caps, how are you going to enforce it. Can you prevent a user from inserting
a value that doesn't comply to the standards. You can , by doing a check
constraint with a series of string functions (which SQL Server is not exactl
y
built for). But does it really have any sense to do the check.
Think of SQL Server as a datastore. If the format of storage changes at a
later date (say all caps for the names). How will you enforce the new
standards. Its always better to handle the formatting part in the front end.
That would be my standpoint.
Hope this helps.
--
"Uday" wrote:
> I agree with you Ron. But the people who make decisions don't see data the
> way I see it. So I need some strong points to make an impact on the decisi
on.
> If I just say, 'keep the data in its original format' -- I dont think that
'll
> make a strong point. Hope you can understand the position I am in.
> thanks to you all...Ron and Omnibuzz
> _Uday
> "Roy Harvey" wrote:
>|||it would seem to me that you can only start "doing anything" with the
case in SQL server if you change the collation of the database. I
expect that if used well then this wouldn't be a problem, but I've had
numerous headaches from having to move databases between servers, then
finding the collation is different to the master (which means any SPs
with WHERE colA = 'some value' throw collation conflict errors).
I'd keep with the default collation, it makes everything simpler. As
others have stated, use your front end to do all the validation etc.
then you won't need to wrestle with DTS etc. just to change collation.
Incidentally, I haven't looked into it, but does anyone know if they've
made it easier with 2005 to change a database collation?
Cheers
Will|||You should always store data with as much information contained in it as
possible. You can always convert to all lower or upper or any combination
coming out.
It's like using your digital camera, and saving in a robust (granted, large
file size) on the memory card. You can always shrink it later when you want
to email the pic
Jeff
"Uday" <Uday@.discussions.microsoft.com> wrote in message
news:1162A9EF-6E79-431D-9FF4-FB6FC1AD39F3@.microsoft.com...
>I agree with you Ron. But the people who make decisions don't see data the
> way I see it. So I need some strong points to make an impact on the
> decision.
> If I just say, 'keep the data in its original format' -- I dont think
> that'll
> make a strong point. Hope you can understand the position I am in.
> thanks to you all...Ron and Omnibuzz
> _Uday
> "Roy Harvey" wrote:
>|||Omnibuzz (Omnibuzz@.discussions.microsoft.com) writes:
> 1. If you define some rules saying the names should have the first
> letter caps, how are you going to enforce it. Can you prevent a user
> from inserting a value that doesn't comply to the standards. You can ,
> by doing a check constraint with a series of string functions (which SQL
> Server is not exactly built for). But does it really have any sense to
> do the check.
Yeah, think about Ludwig van Beethoven for example.
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|||Thanks Omnibuzz..!! It sure helps.
I'll try the case-insensitive coallation... and let the data be in which
ever format it is entered. I'll change it on the fly in front-end or as
needed.
Thanks everyone,
_Uday

Saturday, February 25, 2012

Question

Hello all,

I have the following dataset returning top 20 records for a day, changing daily

STATE

USER ID

SALES REP

SALES

QLD

6758

Liam Maddrell

QLD

6677

Lisa-Maree Findlay

QLD

6133

Benjamin Matthews

QLD

6299

Simon Tabet

QLD

6112

Deborah Williams

VIC

3428

Peter Mahar

QLD

5134

Russell Den Engelse

5279

Julie Isitt

NSW

5677

Pranav Agarwal

NSW

6093

Rosa Delizia

QLD

6684

Ron Prasad

7578

Francis Williams

Qld

7511

LigIA Borodi

QLD

6804

Rupert Ryan

QLD

6856

Maxwell MacLean

QLD

6904

Trent Sticklen

QLD

7236

Drazen Knezevic

QLD

7406

Amy Donaldson

NSW

5601

Michael Phillips

QLD

5940

Harnake guraya

I would like too now sum up the figures to get in a separate layout (total sales off the above reps by state)

Queensland 168

South
Australia

Western
Australia

176

Victoria

New South
Wales

Northern
Territory

I have tried a different dataset but it doesnt allow me to do this due to the use off top 20 in the query which needs to be grouped by state this time and to do that it doesnt know the topn 20 records.

...I am trying to figure out a way by using textboxes , tables etc, I have managed to get the results coming out in a table using grouping by state and sum of the values but because there are no records for Sth Australia and Northern Territory these are not on the list...and i need these also in the list...can someone please help

thanks

Can someone help me with some code to achieve the above ?

thanks

|||

I don't have code for you, but in your query, you need to specify something like isnull(NorthernTerritory,0)

This will return a 0 (zero) instead of a null and then the data should show up on your report.

Since you are doing a topN, why don't you use a group by in your report?

|||

I cant do a group by as a rep can have more than 1 sale and to get the top 20 it is adding up all his sales already

wa John 20 item 15

wa john 15 item 20

To get the top 20 i add the sum off the values together to get the rep...

wa john 35

if i group by state john will be treated as 2 records and the numbers wont be for the top 20 sales rep...but just the top 20 sales

thanks for trying can anyone else help?

|||

Hi,

I found it hard to understand why cant you use grouping and othere data sets,

I have a solution but It is an ugly one.... (-:

first change your Data set to return a sum colums
by addind a join in your SQL to a query tha group by the State and sum the sales

after you do that you will have all the results you need in the same data set

now you jost have to add the colums to the feilds and use the First function in the expresions to

get the currect total sales for the state