SQL Server 2012 :: Select Statement That Take Upper Table And Select Lower Table

Jul 31, 2014

I need to write a select statement that take the upper table and select the lower table.

View 3 Replies


ADVERTISEMENT

Check Value In Table Upper Case Or Lower

Sep 20, 2006

hi i want to select * from table1 where name =petter?now if there is many type of petter in table linke  PETTER , Petter And petter which record will come in display?if i want all this three (PETTER,Petter,petter) will come in display which command is for this ??? regard

View 4 Replies View Related

SQL Server 2012 :: Stored Procedure - How To Join Another Table Into Select Statement

Jan 7, 2014

I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).

The structure of the periods table is as follows:

[ID] [int] NOT NULL,
[Period] [int] NULL,
[Quarter] [int] NULL,
[Year] [int] NULL,
[PeriodStarts] [date] NULL,
[PeriodEnds] [date] NULL

The stored procedure is currently:

USE [International_Forecast_New]
GO
/****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[Code] ....

What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.

For example Period 2 looks like this
Period Quarter Year Period Start Period End
2 1 20142014-01-27 2014-02-23

So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.

View 1 Replies View Related

SQL Server 2012 :: Insert Multiple Rows In A Table With A Single Select Statement?

Feb 12, 2014

I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).

Each row will have the same item, but with a different task type.ie.

TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'

How can I do this with tSQL using a single select statement?

View 6 Replies View Related

SQL 2012 :: Select Statement From History Table?

Jan 26, 2015

Project has 2 tables process(parent) and processchild(child).

Project workflow recorddsany changes to these tables as a history.

I want to find out all the process that are in status = saved(1) where processchild is at status = started(1).

Here is example.

Process table

PK, processid, status , other data
1, 1, 1,...
2, 1, 2,...
3, 2, 1,...
4, 3, 1,...

ProcessChild table

PK, processid, processchildid status, other data
1, 1, 1, 1,..
2, 1, 1, 2,..
3, 1, 2, 1,...
4, 1, 2, 2,...
5, 2, 1, 1,..
6, 2, 1, 2,...
7, 2, 2, 1,...
8, 3, 1, 1,..

I want to find out all the processes where processchildid=2 and processchild.status =1

View 3 Replies View Related

Large Log Table ....SELECT * FROM Statement....killing The Performance Of Server..Help Me Out..

Mar 12, 2008

Hi all

I have a Large log table with large size data(I month only),If I run a query like SELECT * FROM <table_name> Server will go€¦very very slow€¦.

Because of large Data system is going slow€¦..

Please some body helps me with suggestion how get good performance.

View 4 Replies View Related

SQL Server 2012 :: Use Select Within A Table Field In 2nd Query

Aug 27, 2015

I have a table (ScriptTable) which holds a groupID Nvarchar(10) ,SQLStatement Nvarchar (150)

Table Fields =
GroupID SQLStatement
1234 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'AB123'
9876 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'XY*'

What I need is to take each select statement in turn and add the data into a temp table. I can use any method but it needs to be the most efficient. There can also be a varying number of select statements to run through each time my job is run.

View 6 Replies View Related

Using Result Of A Select Statement From One Table To Add Data To A Different Table.

Mar 17, 2008

I have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table? 

View 3 Replies View Related

SQL Server 2012 :: Select Rows With Sum Of Column From Joined Table?

May 2, 2015

I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.

What I tried, but what returns NULL for purchasedamount:

SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2

How can I achieve what I need?

Here are my table definitions and data:

/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,

[code].....

View 0 Replies View Related

SQL Server 2012 :: SELECT INTO - Importing A Text File Into A New Table?

Jun 6, 2015

how to import a text file with a list of NI numbers into a new table with a column to list all the NI numbers? I think I use the Select INTO clause, but not sure how to do this?

View 1 Replies View Related

SQL Server 2012 :: Query To Select Parent Details From Child Table

Mar 3, 2015

I have a scenario,

We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.

Equipment table looks like :

Equipment_ID Code DescriptionTreeLevelParent_Id
6132 S2611aaa 4 6130
11165 V2546bbb 3 1022
15211 PF_EUccc 5 15192
39539 VP266ddd 4 35200
5696 KA273eee 3 3215
39307 VM2611fff 4 35163
39398 IK264ggg 4 35177

There is another table for Equipment_Tree which has got Equipment_Tree_ID,Parent_Id and Equipment_ID does not has the Code here.

Select query where i need to select the Code for all Parent_Id's.

View 8 Replies View Related

Is There A Method To Convert Select * From Table To Select Field1,field2,...fieldn From Table ?

Nov 29, 2007

Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
 
Thanks

View 1 Replies View Related

Declaring A Table Variable Within A Select Table Joined To Other Select Tables In Query

Oct 15, 2007

Hello,

I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.

I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.

This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.

So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?

Thanks in advance,
Andy



Select * from

(

declare @years table (years int);

insert into @years

select

CASE

WHEN month(getdate()) in (1) THEN year(getdate())-1

WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())

END

select

u.fullname

, sum(tx.Dm_Time) LastMonthBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-1

and

year(dm_date) = (select years from @years)

and tx.dm_billable = 1

group by u.fullname

) as A

left outer join

(select

u.FullName

, sum(tx.Dm_Time) Billhours

, ((sum(tx.Dm_Time))

/

((day(getdate()) * ((5.0)/(7.0))) * 8)) perc

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

tx.Dm_Billable = '1'

and

month(tx.Dm_Date) = month(GetDate())

and

year(tx.Dm_Date) = year(GetDate())

group by u.fullname) as B

on

A.Fullname = B.Fullname

Left Outer Join

(

select

u.fullname

, sum(tx.Dm_Time) TwomosagoBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-2

group by u.fullname

) as C

on

A.Fullname = C.Fullname

View 1 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Help With Select Statement - From XML Table

May 6, 2008

I'm trying to get the <Title> node value returned in the select statement below, but cant quite get it to work. I have the <ID> node being returned in the statement, but not the title. Any help is apprecited.

param ids = '<Collection><Content><ID>1</ID><Title>Document 1</Title></Content>< Content><ID>2</ID><Title>Document 2</Title></Content></Collection>'

CREATE PROCEDURE [GetDownloads](@Ids xml) AS

DECLARE @ResearchDocuments TABLE (ID int)

INSERT INTO @ResearchDocuments (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @Ids.nodes('/Collection/Content/ID') as ParamValues(ID)

SELECT * FROM research_downloads INNER JOIN @ResearchDocuments rd ON research_downloads.doc_id = rd.ID Where research_downloads.post_sf = 0

View 5 Replies View Related

Help With Select Statement - From XML Table

May 7, 2008

I'm trying to get the <Title> node value returned in the select statement below, but cant quite get it to work. I have the <ID> node being returned in the statement, but not the title. Any help is apprecited.

param ids = '<Collection><Content><ID>1</ID><Title>Document 1</Title></Content>< Content><ID>2</ID><Title>Document 2</Title></Content></Collection>'

CREATE PROCEDURE [GetDownloads](@Ids xml) AS

DECLARE @ResearchDocuments TABLE (ID int)

INSERT INTO @ResearchDocuments (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @Ids.nodes('/Collection/Content/ID') as ParamValues(ID)

SELECT * FROM research_downloads INNER JOIN @ResearchDocuments rd ON research_downloads.doc_id = rd.ID Where research_downloads.post_sf = 0

View 6 Replies View Related

SQL Statement Select From One Table Where Not In Another Table

Jul 20, 2005

I have two tables with a 1-many relationship. I want to write aselect statement that looks in the table w/many records and comparesit to the records in the primary table to see if there are any recordsthat do not match based on a certain field.Here is how my tables are setup:Task Code TableTCode,TCodeFName,ActiveTracking Table(multiple records)ID,User,ProjectCode,TCode,Hours,DateI want to look at the tracking table and see if there are any TCodelisted here that are not listed in the Task Code table.How do I write a SQL statement to do this?

View 6 Replies View Related

Variable For The Table Name In A SELECT Statement.

Apr 23, 2007

Hi,I'm trying to dynamically assign the table name for a SELECT statement but can't get it to work. Given below is my code: SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE GetLastProjectNumber (@DeptCode varchar(20))
AS
BEGIN TRANSACTION
SET NOCOUNT ON

DECLARE @ProjectNumber int
SET @ProjectNumber = 'ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','')
SELECT MAX(@ProjectNumber)
FROM 'tbl_ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','');
END TRANSACTION  Basically, I have a bunch of tables which were created dynamically using the code from this post and now I need to access the last row in a table that matches the supplied DeptCode. This is the error I get:Msg 102, Level 15, State 1, Procedure GetLastProjectNumber, Line 29Incorrect syntax near 'tbl_ProjectNumber'. Any help would be appreciated.Thanks. 

View 3 Replies View Related

SELECT Statement With An Attendance Table

Feb 4, 2005

I have a table that contains the following structure and information:


Code:

UID Member_Number Time_Stamp Status Reason Event Reg F_Name L_Name
18772054062/3/2005 11:48:27 AMInNonenoneNoneWendyBoud
86930082522/3/2005 12:39:35 PMInNonenoneNoneJeremyAhlman
98772054062/3/2005 12:40:20 PMOutNonenoneNoneWendyBoud
106930082522/3/2005 12:40:45 PMOutNonenoneNoneJeremyAhlman
118772054062/3/2005 12:40:50 PMInNonenoneNoneWendyBoud
128772054062/3/2005 12:46:25 PMOutNonenoneNoneWendyBoud



I need to be able to take this information and display it in a data grid so that on each row I see the Member Number, First and Last Name and the In and Out Time.

I am not sure how to group the In and Out times together so that the query knows which time out corresponds to which time time for the member?

Any help is greatly appreciated!

View 9 Replies View Related

Select Statement - Denormalize Table

Aug 27, 2014

I would like to know if it is possible to do a SELECT statement where I can denormalize my table. I am not sure if it is needed to create a new table at first and insert denormalized values into the created table.

Example

Original table:
ID XXX VALUES
1 a 10
1 b 15
1 c 8

And I want to use SELECT to get this table:
ID a b c
1 10 15 8

View 1 Replies View Related

Creating Table With A Select Statement

Nov 27, 2006

Dear folks,

create table temptable(eno, ename) as select eno, ename from emp.

here the problem is it is asking for the datatype for the temporary table.

is it not possible to create the temp table without providing the datatypes?

thank you very much.

Vinod

View 8 Replies View Related

Select * From Table Is Processed Much Faster Than Select MyField From Table ¿?¿?

Oct 1, 2007



I have a query that has the following structure

Select *
From Table
Where Condition And ... (some 'Exists' conditions)

When I run the query using field names the query gets much slower, and I cannot understand Why!


Select MyField
From Table
Where Condition And ... (some 'Exists' conditions)


I'm talking about three times slower using the Select MyField sintax.

Any ideas???

View 8 Replies View Related

SQL Server 2012 :: Set Field Value In Select Statement

Aug 4, 2015

I have a query that displays a bunch of fields, specifically a createdon field and closedate field.I need to find the diff in days between date1 and date2 and only disiplay those results.For example: where the NumDays = a certain value. For example, 81.I have the NumDays displaying in the query, but I don't know how to reference these values in the where clause.

SELECT
TBU.businessunitidnameAS 'BU Name',
LEADS.statecodenameAS 'Status',
LEADS.statuscodeAS 'Status Code',
LEADS.accountidnameAS 'Account Name',

[code]...

View 5 Replies View Related

Select Statement To Pull Columns From Same Table For Different Ids

Oct 9, 2007

I'm wondering if there is a single statement I can write to pull my data. Let's say my Order table has one field for userId and one field for supervisorId (among other fields) both of which are foreign keys into the Users table where their name, address, etc. are stored. What I'd like to do is to pull all the rows from Order and have a join that pulls the user name and supervisor name from the User table all in one go. Right now I pull all the Orders with just user name joined, and then go back over the objects to add the supervisor name as a separate query.

The reason I'd like to do this is to simplify the objects I'm passing to the GridView by doing a single fetch instead of multiples. I'm using SQL Server, .NET 2.0 and VS.NET 2005.

Thanks

View 1 Replies View Related

How Do I Get The Actual Name Of A Column Or Table In A Sql Select Statement?

Jul 12, 2004

Hello fellow .net developers,

In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist.

Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist.

So, what I need to know is: is there a sql statement that can return this type of information?

Example:

Table Names in Database: Customers, Suppliers

Columns in Customers Table: Name, Phone, Email, Address

I click on the word "Customers" in the first dropdownlist.

I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist.

I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool.

Thanks in advance,

Robert

View 5 Replies View Related

Dynamic CREATE TABLE Or SELECT INTO Statement

Jul 27, 2004

In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind?

Example:

I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query.

I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database.

BTW - this would be a stored procedure...

Any suggestions would be greatly appreciated.

View 1 Replies View Related

Create Temporary Table Through Select Statement

Jul 20, 2005

Hi,I want to create a temporary table and store the logdetails froma.logdetail column.select a.logdetail , b.shmacnocase when b.shmacno is null thenselectcast(substring(a.logdetail,1,charindex('·',a.logde tail)-1) aschar(2)) as ShmCoy,cast(substring(a.logdetail,charindex('·',a.logdeta il)+1,charindex('·',a.logdetail,charindex('·',a.lo gdetail)+1)-(charindex('·',a.logdetail)+1))as char(10)) as ShmAcnointo ##tblabcendfrom shractivitylog aleft outer joinshrsharemaster bon a.logkey = b.shmrecidThis statement giving me syntax error. Please help me..Server: Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'case'.Server: Msg 156, Level 15, State 1, Line 7Incorrect syntax near the keyword 'end'.sample data in a.logdetailBR··Light Blue Duck··Toon Town Central·Silly Street···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00······0234578······· ····· ··········UB··Aqua Duck··Toon Town Central·Punchline Place···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00·····Regards.

View 2 Replies View Related

SQL Query (SELECT Statement) And Table Design

Aug 19, 2007

I am wondering if there is a direct query in this case:


I am developing a program to a company which simply sells services
One service may have different prices for different types of clients
The price of any service for any client can change at any time, and I should be able to trace these changes at any time

I made the following tables (simplified): (asterisk for primary key)

(Table) (Fields)

CLIENT_TYPES : ID*, ClientTypeName
SERVICES : ID*, ServiceName
PRICES : ServiceID*, ClientTypeID*, Price, Date*
ORDERS : ID*, Date, ClientTypeID
ORDER_SERVICES : OrderID*, ServiceID*

The field in bold is the area of the question

This is a sample data in the PRICES table:

ServiceID ClientTypeID Price Date1 1 100 1/1/20072 1 150 1/1/20071 2 90 1/1/20072 2 135 1/1/2007

Now if I want to update a price of service 1 for clienttype 1, I add the following row:

1 1 100 1/1/2008

So one product for one client can have any number of prices with different dates


The following query:

SELECT * FROM PRICES WHERE ClientTypeID = 1

will retrieve all prices with repeats for a specific client (#1 here)

What I want is a query to retrieve the most recent prices for specific client for all products, even if a query on query

If there is commemts on table design please tell me

thanks for any one who provides help

View 6 Replies View Related

SQL 2012 :: How To Select Top 200 To 300 Rows From A Table

Mar 9, 2015

Select top 100 * from tab1 order by col1 ---gives me the top 100 order by col1

Select top 200 * from tab1 order by col1 ---gives me the top 200 order by col1

How do I select the top 100 to 200

View 3 Replies View Related

SQL Server 2012 :: Filter MS Objects Out Of Select Statement

Jul 27, 2015

I am writing a script to get all of the user objects out of a database. What I am having a hard time doing is filtering out the MS replication system objects, such as:

dbo.MSsnapshotdeliveryprogress
dbo.MSreplication_objects
dbo.MSreplication_subscriptions
dbo.MSsavedforeignkeycolumns
dbo.MSsavedforeignkeyextendedproperties
dbo.MSsavedforeignkeys
dbo.MSsnapshotdeliveryprogress
dbo.MSsubscription_agents

View 2 Replies View Related

Comparing Upper And Lower On Like% Query

May 2, 2008

My SQL Server database is not case sensetive.
How can I compare like cluase with search for capital and small letter?
For example
SELECT add1 from xcty_all where add1 like '%AL'%'
I need only
...................
10 ltncewwod way AL
456 Ruio St. AL
NOT

Duci Ral Rd Mexico
Albi Road Hawai CA

I want to ingore this bold letter on search




Sanjeev Shrestha
12/17/1971

View 1 Replies View Related

SELECT Statement To Return Table Column Names

Aug 16, 2004

Is there a SELECT statement that will return a the column names of a given table?

View 5 Replies View Related

Insert Record Into Temporary Table From A Select Statement

Jan 17, 2006

Hi guys,

anyone can help me?
i using sp to select a select statement from a join table. due to the requirement, i need to group the data into monthly/weekly basic.

so i already collect the data for the month and use the case to make a new compute column in the selete statement call weekGroup. this is just a string showing "week 1", "week 2" .... "week 5".

so now i want to group the weekgroup and disply the average mark. so i need to insert all the record from the select statement into the temporary table and then use 2nd select statement to collect the new data in 5 record only. may i know how to make this posible?

regards
terence chua

View 4 Replies View Related







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