Combine City Field With Prov/State

Jun 4, 2007

Hello I am new to SQL Server 2005 and am designing my first database.

In the AdventureWorks sample database the city field is included with the street address where it repeats for each row.



Why would you not put it in a separate table with state/province or a separate table all togeather?



Thanks for your help.

View 5 Replies


ADVERTISEMENT

Selecting First 10 Addresses In Each City In Each State

Oct 4, 2007

Greetings:

I read with great interest a post on getting hte top 75 per cent by month, but still get through this problem. I have a table containing addresses, city names and state names, Each row has a unique address. I want to get the first ten addresses for each city in each state. I've tried something like the following which didn't work. Suggestions on how to solve the (simple) problem would be appreciated.
Select top (10) city,state,address
from address_list
alan

View 3 Replies View Related

Country State And City Table - Best Practices

Oct 2, 2007

Quick questiom, I need to Import a table with City and State Information

What are your recomendations? Should I create a Unique Key for them?
CityID, CityName, StateID?
StateID, StateName?

Or can I just store them in a table with one column?
CityName, State

Thanks for your help/

View 2 Replies View Related

Transact SQL :: Server Script For List Of All Country State And City

Aug 20, 2014

I am searching for the Sql Server Script of all country State and City with the Following type..

For the Country only 2 Columns are there i.e. CountryId and CountryName(CountryId is primary Key)

For the State 3 Columns are there i.e. CountryId, StateId and StateName(StateId is Primary Key and CountryId is Foreign Key)

For the City 3 Columns are there i.e. StateId ,CityId and CityName(CityId is PrimaryKey and StateId is foreign Key).

I need this type of Script with Column name.

View 7 Replies View Related

Combine Multiple Field On The Basis Of Employee ID Field

Sep 30, 2015

I need formulating a view through which I can create a an output like below image -

Monthly Table

NUM STATUS ACTIVITYCODE

HAX603 Completed 0x45845a
HAX317 Completed 0x112z44
HAX465 Completed 0x1155x4
HAX523 Completed 0x124c69

Season Table

NUM STARTMONTH STARTDAY ENDMONTH EMDDAY

HAX603 JULY 1 OCTOBER 31
HAX317 DECEMBER 1 DECEMBER 31
HAX317 MARCH 1 MARCH 31
HAX317 July 1 July 28

[Code] ...

Final Output 

NUM STATUS ACTIVITYCODE <SEASONS>

HAX603 Completed 0x45845a 1 JULY - 31 OCTOBER, 1 DECEMBER - 31 DECEMBER
HAX317 Completed 0x112z44 1 DECEMBER - 31 DECEMBER, 1 MARCH - 31 MARCH, 1 July - 30 July
HAX465 Completed 0x1155x4 1 MARCH - 31 MARCH, 1 July - 28 July, 1 August - 30 August
HAX523 Completed 0x124c69 1 November - 30 November

I have written a query to join the values of multiple field, but lacking in as how will I formulate a view which will check for the duplicate values of Num fields and merge there values in a single field like season.

select num, (CAST(startday AS VARCHAR(3)) + ' ' +  startmonth + ' - ' + CAST(endday AS VARCHAR(3)) + ' ' +  endmonth)AS Season from seasons;

View 10 Replies View Related

Grouping By One Field And Combine Data Of Another Field In SSRS

Apr 2, 2008

Hi all experters,

Please suggest how to build the report in below case:

Raw data:






ID
Member
Functions

1
Alan
A

1
Alan
B

2
Tom
A

2
Tom
B

2
Tom
C

3
Mary
D

3
Mary
E


Report Shows:






ID
Member
Functions

1
Alan
A,B

2
Tom
A,B,C

3
Mary
D,E


I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID.

Any good suggestion?

Thanks in advance,
Steve Wang
2008/4/2

View 6 Replies View Related

Invalid Column Name 'prov'. For Me, Very Very Strange!

Aug 23, 2007

Can someone se whats wrong here!



DECLARE @TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))



INSERT INTO @TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc




Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.


For me, very very strange!

View 2 Replies View Related

How To Combine Date And Time Into One Field

Jan 27, 2012

We have a piece of software and database for student registers.

One of the biggest problems is the database has been difficult to work with, as I have had to work out the date using dateadd functions and combine fields from different tables.

I've now got the date correctly, but I want to add the time to the time part of a datetime field.

The time field is already stored in a datetime field, but the date in this field is always 1899-12-30.

This is the function I am using to get the date:

Code:
DATEADD(dd, e.day_of_week, DATEADD(ww, sm.week, '01/08/2011'))

And this is the function I use to get the time (from a datetime field):

Code:
SUBSTRING(CAST(e.start_time AS varchar(20)), 13, 5)

With the DATEADD function it appears you can only add individual elements each time hh, mi, ss.

Would I have to add a DATEADD function for each time element in order to add the time to the date?

View 2 Replies View Related

Combine Text Field Based On Value

Feb 27, 2014

I have one table with text column(varchar50) and 2 value columns(INT)

it looks like this:
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text4

What i want to do is to combine c3 on row 3 and 4 so the output would look like this:
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text3 ext4

I have tried to use CASE with no luck.

edit:
output like this is also fine
c1, c2, c3
1, null, text1
2, null, text2
3, null, text3
4, 3, text4
5, null, text3 ext4

View 6 Replies View Related

Combine Column Fields Together Into One Field

Jan 24, 2007

Hello to All,

I needs help to combine these together but how would I eliminate necessarily zero in front of "PropertyHouseNumber".


Table: DirectHome

Column fields.......
PropertyHouseNumber, PropertyStreetDirection, PropertyStreetName, PropertyMODE

0000001091 , W , 000026TH , RD


Thank you


RV

View 5 Replies View Related

How To Combine Fromdate And Todate Values In One Field

Aug 16, 2007

Hi All,
I have to fetch FromDate and Todate values from the table like this.Suppose Fromdate value is 02-Feb-2007 and Todate Value is 04-Feb-2007,then my need is to get the date value like this......Feb 2-4,2007or 2-4 Feb,2007.Can anybody know the syntax or code?.I am using sql Server and fromdate and todate values are stored in two different feilds in table.
Thanks and Regards

View 1 Replies View Related

Combine And Convert Int Fields To Create One Field, Should Be Simple

Oct 19, 2007

I want to combine two of my fields somewhat like when you combine to char fields and concatenate them. But these two fields are int. How do i do that? Here's my query right now:




Code Block
SELECT AutoID,
CONVERT(Varchar(Mars_Calender_Year )+ CONVERT(Varchar(Mars_Calender_Period_Code) as MarsId
FROM NavisionReplication.dbo.Tbl_Mars_Calender
ORDER BY Mars_Calender_Year DESC








what am i doing wrong?

View 5 Replies View Related

Combine Separate Date && Time Fields Into One Datetime Field?

Dec 26, 2006

Good morning.I am importing an XLS file into one of my tables. The fields are:Date Id Time IO12/22/2006 2 12:48:45 PM 912/22/2006 16 5:40:55 AM 112/22/2006 16 12:03:59 PM 2When I do the import, I get the following:Date Id Time IO12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 212/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 112/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2Here are my doubts:1. Would it be better to combine the Date & Time fields into onecolumn? If so, how?2. What issues or problems might I have when I program SQL reports, ifI leave the fields as they are?Any comments or suggestions will be very much welcomed.Cheers mates.

View 2 Replies View Related

Combine Separate Date &&amp; Time Fields Into One Datetime Field?

Dec 26, 2006

Good morning.

I am importing an XLS file into one of my tables. The fields are:

Date Id Time IO







12/22/2006
2
12:48:45 PM
9


12/22/2006
16
5:40:55 AM
1


12/22/2006
16
12:03:59 PM
2


When I do the import, I get the following:

Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2

Here are my doubts:

1. Is it be better to combine the Date & Time fields into one column? Advantages/Disadvantages?
2. If I don't combine them, should I use varchar or datetime data type?
2. What issues or problems might I have when I program SQL reports, if I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.

View 3 Replies View Related

Unknown Column 'state' In Field List

Feb 25, 2006

I installed a script that is suppose to accept paypal, however on trying totest a payment, I get this error msg:ErrorDatabase access errorand I get this error msg emailed to me:Unknown column 'State' in 'field list'Query: 'INSERT INTO `TransactionsMembership` (ID, Sum, State ) VALUESwhat could be swrong, importantly how do I fix it?

View 2 Replies View Related

SQL Server 2014 :: Combine Records That Are Identical And SUM Values In Price Field

Sep 30, 2015

I have a robust query that returns a dataset and the data is good, however some of the records contain the exact same data with the exception of the 'Price' field. I want to combine the records that are identical and SUM the values in the 'Price' field. My query and example return dataset is below.

Query:
--------
select distinct
'On-Demand' as 'Business Line',
o.OrderID as 'Order #',
isnull(d.DisplayCode,'UNK') as Hub,
isnull(rz.RouteID,'UNK') as 'Default Route',
'On-Demand' as 'Assigned Route',

[Code] ....

View 4 Replies View Related

How To Combine Multiple Rows Data Into Single Record Or String Based On A Common Field.

Nov 18, 2007

Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department:                                            Sells:
1                                                              Meat
1                                                              Rice
1                                                              Orange
2                                                              Orange
2                                                              Apple
3                                                             Pears
The Data I would like read separated by Semi-colon:
Department:                                            Sells:
1                                                             Meat;Rice;Orange
2                                                             Orange;Apple
3                                                             Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
 
 

View 2 Replies View Related

List A City Only Once

Nov 13, 2006

Hi...I want one listbox showing cities but I dont want to list a city more than one time....

I know that DISTINCT maybe could work...But I dont get it to work correctly....

The code:

<asp:ListBox ID="ListBox1" runat="server" AutoPostBack="True" DataSourceID="DataSource1" DataTextField="City" DataValueField="City"></asp:ListBox>

<asp:SqlDataSource ID="DataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT DISTINCT [City] FROM [Location]">

</asp:SqlDataSource>

I got the message:

The text data type cannot be selected as DISTINCT because it is not comparable

 The database have a table called "Location" with the column "ID" (int) as primary key and the column "City" (text)

View 14 Replies View Related

Transact SQL :: Convert City Name To Upper / Lower

Jul 7, 2015

In the database, most of our cities are stored in all upper case.  For reporting purposes, I need to have them returned as upper/lower.   I used the below function, which works great for one word cities.   However I can’t figure out how to get it to capitalize the 1st letter of each word, for addresses containing multiple names such as Rancho Santa Margarita. 

Upper(left(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY,1))+lower(substring(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY, 2, LEN(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY)))As ADDRESSCITY

This returns back: ‘Rancho santa margarita’; I need it to return ‘Rancho Santa Margarita’.  Is this possible to do at the query level?

View 10 Replies View Related

Store Procedure To Find City And Zip Code By Radius

Mar 20, 2007

Hi,
 
Is anyone has store procedure that find city and zipcode by radius (10 miles, 30 miles, 50 miles.......)
and also table that has all zipcodes. I finded one, but it's look like very old one, not updated since 2005
 
Thanks
Kate
 
 

View 1 Replies View Related

Selecting Nearby Distinct City/zipcode Issue

Mar 12, 2004

I have the following code, which returns a list of nearby cities based on a city name as input. Most cities have multiple zipcodes per city name, thus it can list multiple rows with the same city name, but with different zipcodes like below:

Zip | Cityname
111 belmont
112 belmont
113 belmont
114 san francisco
115 san francisco

---------------- etc----------------

I do not really care about each group of zipcodes. I only need one pair of zipcode/city name like the following:

ZIP | City name
111 belmont
114 San Francisco


How do I change my select to only return a distinct city name. I do not care which if the city/zipcodes it returns from the similar city.

The select statement is below:




CREATE PROCEDURE ZipSearchByCity
@city varchar(40),
@State varchar(5),
@distance int


AS


SELECT distinct o.City AS City, o.zip_code, o.State AS State,
(3956 * (2 * ASIN(SQRT(
POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
COS(z.RADlatitude) *
COS(o.RADlatitude) *
POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
)))) dist

FROM zipcodes z,
zipcodes o,
zipcodes a




WHERE z.city = @city AND
z.State = @State AND
z.zip_code=a.zip_code AND
(3956 * (2 * ASIN(SQRT(
POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
COS(z.RADlatitude) *
COS(o.RADlatitude) *
POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
)))) < @distance
GO





Thanks,

Christian

View 7 Replies View Related

Need To Produce A Query That Show The Cities And The Number Of People Per City?

Jan 31, 2008



Please help!!!!
Need to produce a query that show the cities and the number of people per city?
simple table
firstname
lastname
age
location

location shows the dif cities and the sql query will show cities will a count of people per city

how do you do this?

View 3 Replies View Related

T-SQL (SS2K8) :: Returning Rows Within Certain Distance In Miles From City Using Longitude And Latitude

Nov 19, 2014

I've got a working query which returns all leads within a supplied proximity to a city. I followed a tutorial I googled a couple months ago (can't find it now). It works, but would love others to look the query over (provided DDL and sample data) and tell me if it's as it should be.

Two things I don't like about query:

1. I have to do a UNION to another query that retrieves everything that is in the same city in order to have complete results.
2. very slow to retrieve results (> 1 minute)

Sample DDL: 2 tables
create table dim_lead
(
date_created datetime,
[contact_first_name] varchar(20),
[contact_last_name] varchar(20),
lead_id int,

[Code] .....

View 9 Replies View Related

SQL Server Session State - Using A 1.1 Database Schema For 2.0 Session State Storage

Aug 3, 2006

The 2.0 version of ASPSTATE is slightly different than the 1.1 version in that one table has one additional column and another table uses a different data type and size for the key. The 2.0 version also has a couple additional stored procedures.

We'd like to manage just one session state database if possible so we're trying to figure out if Microsoft supports using the new schema for 1.1 session state access (it seems to work, but our testing has been very light).

Is there any official support line on this? If not, can anyone comment on whether or not you'd expect it to work and why?

Thanks.

View 1 Replies View Related

Principal Db In Principal, Synchronizing State, The Mirror In Restoring State

Sep 26, 2007

We have mirroring setup for 5 dbs, 4 of which are synchronized and 1 which is in "synchronizing" state on the principal and "restoring" state on the mirror. Mirroring for all dbs has been working fine for the past several months and we have a witness that has allowed automatic failover in the past without problems.

This database has several bulk inserts performed throughout the day and am sure there is some latency due to the size of these transactions.

Not sure as to why this is happening all of a sudden, but the db in question has been in this state for the past 12 hrs. I checked the mirroring status on the principal and it states that it is "synchronizing: data is being transferred from principal to mirror", but the mirror server states that db is in "restoring" state. Can anyone suggest as to how I can get the database on the mirroring server to get back to "mirror, synchronizing/restoring..." state? Or suggest on how I can troubleshoot this?

Thanks in advance.

View 3 Replies View Related

Transact SQL :: Return Field When A Field Contains Text From Another Field

Aug 25, 2015

I'm new to SQL and I'm trying to write a statement to satisfy the following:

If [Field1] contains text from [Field2] then return [Field3] as [Field4].

I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.

View 8 Replies View Related

How To Add Date Field And Time Field (not Datetime Field )

May 4, 2006

Good morning...

I begin with SQL, I would like to add a field that will be date like 21/01/2000.

Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.

How to do for having date and time in two different field.

Sorry for my english....

Cordially

A newbie

View 3 Replies View Related

LIKE And IN Can I Combine

Oct 10, 2005

I can use the IN with the WHERE clause as example:SELECT * FROM NORTHWIND WHERE LASTNAME IN ('FULLER','KING')I want to use the IN and LIKE at the same time:SELECT * FROM NORTHWIND WHERE LASTNAME LIKE ('A%','B%')I know this is a simplistic example, but the members for the IN will bemany, 5 to 10.I'm trying to avoid:SELECT * FROM NORTHWIND WHERE LASTNAME LIKE 'A%' OR LASTNAME LIKE 'B%'OR LASTNAME LIKE 'FU%' OR LASTNAME LIKE 'JON%' <...>and so forth.Any Ideas?TIARob

View 3 Replies View Related

How To Combine IN &&amp; LIKE

Aug 23, 2007

Hai All,
Could some one help me how to combine IN & LIKE in the query selection,
i'v try many syntac and i just have no find the way out

this is what I would like to write:

Select DSCRIPTN,ORMSTRID,ACTINDX
from T50001
where ACTINDX = 350
and DSCRIPTN Like in '%'+(select distinct rtrim(ORMSTRID) ORMSTRID from L10001)+'%'

View 3 Replies View Related

Best Way To Combine Columns

Feb 20, 2007

Hi all,
 
I have a table with multiple rows with the same ID.
a) How do I combine all columns into one row with the same ID?
b) Is this better to do the combine in the store procedure/trigger or a sub that involked by  some sort of datarepeater, datagrid controls?
Since I am trying to brush up my sql. I appreciate any examples on top of conceptual solution.
 
Thanks

View 6 Replies View Related

Combine 3 Databases

Jun 14, 2007

Combine 3 Databases, Not tables.
Let me spell this out -- I have 3 databases (they are in isolation when in use, its a field app) that need to be merged into 1 "masterDB" database.  I've discovered I can use something like this to get to each DB in a query...
1    USE [database1]2    SELECT     table1.Name, table1.Location, table1.Date, table2.Blog3    FROM         table2 INNER JOIN4                          table1 ON table2.ID = table1.ID5    ORDER BY table1.Date
and then just repeat for database2 and database3.  Ok, fine, rah rah.  My question is how do I "merge" all of these into 1.  No data on each db will be identical, at all, ever so that is not a concern.  I just need all the data from db1, 2 and 3 into masterDB.
Ideas? Direction?

View 2 Replies View Related

Combine Sql Queries

Feb 19, 2008

Hello, I have these variables on my page:
userid = "10101"
RequestHost = "example.com"
What would be the best way performace wise to first check if the userid 10101 exists in my sql server db.  If it does exist I would then need to check if "example.com" exists for the userid in the userdomains table.  If these both exist I would then like to query some additional data.  I was hoping its possible to combine this into one query somehow.  I dont think this is the best solution:
 sqlcommand.CommandText = "SELECT UserId From Users Where UserID = '10101'"
Conn.Open()
dr = sqlcommand.ExecuteReader
if dr.hasrows then
sqlcommand2.CommandText = "SELECT UserDomain From UserDomains Where UserID = 'example.com'"
dr2 = sqlcommand2.ExecuteReader
if dr2.hasrows then
sqlcommand3.CommandText = 'Select Additional Data
dr3 = sqlcommand3.ExecuteReader
'read values
conn.close
else
conn.close
'do something
end if
else
conn.close
'do something
end if  Thanks Very Much!

View 2 Replies View Related

How To Combine Two Columns Into One In An Sql

Dec 7, 2005

I have an stored procedure that returns 3 columns. Month, Date, and Total Number of Calls.
Here is the stored Proc:
SELECT DATEPART(mm, CALLSTARTTIME) ,  DATEPART(dd, CALLSTARTTIME), COUNT(*)
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1') AND (PINCODE IS NOT NULL)
GROUP BY DATEPART(mm, CALLSTARTTIME), DATEPART(dd, CALLSTARTTIME)
ORDER BY DATEPART(mm, CALLSTARTTIME), DATEPART(dd, CALLSTARTTIME)
It returns a table:
MONTH   DATE   TOTAL NUMBER OF CALLS=======   =====   ===========1                  1               10
1                  2               15
My question is: is it possible to combine the Month and Date column into one column. e.g.
Date   Total Number of Calls====   ==============1/1      101/2      15
Please Help, Thanks in advance :)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved