Help Writing A SQL Script
Oct 18, 1999
I have a table that has records that have multiple entries with only a slight varitation to accomodate client needs. I want to filter out the duplicates and call only one record row. How can I do this type of thing and make it work; example - SELECT * FROM clients WHERE client_id DISTINCT
I know that is not correct but that is what I need. I need to get all the columns but only one record for each client using the client_id field as the source of information.????
View 2 Replies
ADVERTISEMENT
Jun 21, 2008
I have some experience with MS SQL mostly installation, configuration, maintaining, etc. But I am trying to teach myself some TSQL. I have bought a few beginner books and have some test machines. In advance, I appreciate any feedback!
I have a database with two tables. I want to calc the min salary for a class of employees. The "wage" is in one table and the "status" is in another.
USE Database
GO
SELECT MIN(Wage) AS "Min Salary", Status
FROM Employee, Job
WHERE Status = '1'
GROUP BY Wage, Status
GO
The result set brings back all not the "status" class that I want.
Again, I am relatively new so take it easy on me!
Thanks,
grinch
View 4 Replies
View Related
Mar 3, 2008
Hello,
I wrote a stored procedure in SQL 2K5, and I would like to write to the sql log without using a rasieerror function. I just would like to log the sucessfull steps without exiting the proc. I am not sure how to do this. Any help would be appreciated.
Thanks,
Dave
View 3 Replies
View Related
May 13, 2007
Hi,
I am new to this programming and SQL.
I am following the: Lesson 8: Obtaining Data from a SQL Server 2005 Express Edition Database video series. I cannot get the following to write changes to the SQL Database:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
BindingSource1.EndEdit()
'CustomerTableAdapter1.Update(MyCompanyDataSet1.Customer)
Dim rowsAffected As Integer = 0
rowsAffected = CustomerTableAdapter1.Update(MyCompanyDataSet1.Customer)
MessageBox.Show(rowsAffected.ToString())
End Sub
End Class
I do get the message box to popup showing 1 row of changes when I make one change, however that change does not get written to the database. Is this enough info to have any idea why?
Thanks
View 1 Replies
View Related
May 17, 2007
I was hoping that someone might be able to help a SQL statement for this, if possible. I have two tables, InvoiceDetails and InvoiceLineItems. What I'd like to do is write a query that will return all invoices, but also return a value InvoiceTotal Based on SUM(InvoiceLineItems.qty * InvoiceLineItems.unitPrice). I've tried a few different methods of writing this, but haven't had much luck. Can anyone shed some light on this for me? Thanks.
View 7 Replies
View Related
Dec 20, 2007
Hi.I realize this may have been asked a thousand times before, but it's still not working for me. I would appreciate any help with it. First, I created a GridView, and from it I created a new SqlDataSource and let it point to the database in C:My ProjectApp_DataASPNETDB.MDF. This automatically created a connectionString in the Web.Config file, saying the following: "R1ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename="C:My ProjectApp_DataASPNETDB.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True"
providerName="System.Data.SqlClient" /> Now when I try to programatically use that connection string, the program crashes. Below is my code: <script runat="server"> String sqlConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename="C:My ProjectApp_DataASPNETDB.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True";
protected void Page_Load(object sender, EventArgs e) { using (SqlConnection conn = new SqlConnection(sqlConnectionString)) { conn.Open(); const String selectQuery = "SELECT StatusName FROM Status ORDER BY StatusName"; Label1.Text = ""; using (SqlCommand cmd = new SqlCommand(selectQuery, conn)) { SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { Label1.Text += dr[0]; while (dr.Read()) { Label1.Text += ", "; Label1.Text += dr[0]; } Label1.Text += ". "; } else
Label1.Text = "None."; } if (conn != null) conn.Close(); } } Here's the error message I'm getting: Server Error in '/My Project' Application.
Keyword not supported: 'c:my projectapp_dataaspnetdb.mdf";integrated security'.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.ArgumentException: Keyword not supported: 'c:my projectapp_dataaspnetdb.mdf";integrated security'.
Source Error:
Line 15: protected void Page_Load(object sender, EventArgs e)Line 16: {Line 17: using (SqlConnection conn = new SqlConnection(sqlConnectionString))Line 18: {Line 19: conn.Open();
Source File: c:My Project est.aspx Line: 17
Stack Trace:
[ArgumentException: Keyword not supported: 'c:my projectapp_dataaspnetdb.mdf";integrated security'.] System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) +417 System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules) +99 System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +52 System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) +25 System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) +141 System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value) +38 System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +4 System.Data.SqlClient.SqlConnection..ctor(String connectionString) +21 ASP.test_aspx.Page_Load(Object sender, EventArgs e) in c:My Project est.aspx:17 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45 System.Web.UI.Control.OnLoad(EventArgs e) +80 System.Web.UI.Control.LoadRecursive() +49 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3745 My question is: How should I write the connection string? I have a feeling the problem is with the quotes ("), but I can't figure out how to write it otherwise.Thank you very much.
View 2 Replies
View Related
Jan 22, 2008
Hello, I have two tables: Customer Training Requests has say two columns.Customer - Class Requested a 1b 1c 1a 2b 2c 3a 3 I have another table called packages, which has packages of class.Package No. Class1 11 2 I want to write a query which will return only those customers who have requested exactly the classes in a particular package.so in above example if the package is 1 the query should return only customer b as only customer b has requested class 1 and 2. Any suggestions? Thanks,Paresh.
View 8 Replies
View Related
Feb 4, 2008
hi, I'm trying to write to a database from a web page. The code is throwing back no errors but it just isn't writing to the database. Am I missing something? void SubmitDetails(Object s, EventArgs e) { SqlConnection objConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]); SqlCommand objCmd; objCmd = new SqlCommand( "INSERT INTO Member_Information (Name, Address_Line_1, Address_Line_2, Address_Line_3, Email_Address, Contact_Number, " + "Password, Name_Of_Bank, Account_Number, Sort_Code, Bank_Address_Line_1, Bank_Address_Line_2, Bank_Address_Line_3) " + "VALUES (@Name, @Address_Line_1, @Address_Line_2, @Address_Line_3, @Email_Address, @Contact_Number, " + "@Password, @Name_Of_Bank, @Account_Number, @Sort_Code, @Bank_Address_Line_1, @Bank_Address_Line_2, @Bank_Address_Line_3)", objConn); objCmd.Parameters.AddWithValue("@Name", txtName.Text); objCmd.Parameters.AddWithValue("@Address_Line_1", txtAL1.Text); objCmd.Parameters.AddWithValue("@Address_Line_2", txtAL2.Text); objCmd.Parameters.AddWithValue("@Address_Line_3", txtAL3.Text); objCmd.Parameters.AddWithValue("@Email_Address", txtEmail.Text); objCmd.Parameters.AddWithValue("@Contact_Number", txtContact.Text); objCmd.Parameters.AddWithValue("@Password", txtPassword.Text); objCmd.Parameters.AddWithValue("Name_Of_Bank", txtBank.Text); objCmd.Parameters.AddWithValue("Account_Number", txtAN.Text); objCmd.Parameters.AddWithValue("@Sort_Code", txtSort.Text); objCmd.Parameters.AddWithValue("@Bank_Address_Line_1", txtBA1.Text); objCmd.Parameters.AddWithValue("@Bank_Address_Line_2", txtBA2.Text); objCmd.Parameters.AddWithValue("@Bank_Address_Line_3", txtBA3.Text); objConn.Open(); objCmd.ExecuteNonQuery(); objConn.Close(); }
View 4 Replies
View Related
Feb 23, 2008
Hello, how can do the following using sql:
if exists(select * from siteindex where site_url like '%msn.com')ThenUpdate siteindex Set dmodified = GetDate(), isFullPage = 0, fp_flname = 'filename', fp_iWidth = 800, fp_iheight = 600
Where site_url = '%msn.com'
else
insert into siteindex (site_url, dcreated, dmodified, isFullPage, fp_flname, fp_iWidth, fp_iHeight)
Values ('msn.com', GetDate(), GetDate(), 0, 'filename', 800, 600)
I cant get the syntax right. Thanks!
View 1 Replies
View Related
Apr 28, 2008
Hi all,
I have a problem with retrieving data from lookup tables. Ok here is the issue I have a Tables ...
Tables | Schools StuAccount
Fields | Sch_Id (pk) Student_ID (pk)
School_Name AccountableSch_Id
DistrictName TestedSch_Id
ParticipatedSch_Id
Ok, so i have two tables Schools and StuAccount, each has one primary key and and the StuAccount has three foreign keys that associates to the Schools TABLE which has a the definition of school names wich i want to retrieve for all three foreign key in the stuaccount table. Here is what i want ....
Student_ID
AccountableSch_Id
Shool_Name( this the name of the AccountableSch_Id )
TestedSch_Id
Shool_Name( this the name of the TestedSch_Id)
ParticipatedSch_Id
Shool_Name( this the name of the ParticipatedSch_Id)
I just want to look up the id and put the school name in the final resulting join and i have no idea how to lookup the table School three time and put school name for all three foreign keys.??
Any help will be much appreciated.
Thanks
View 4 Replies
View Related
Jun 5, 2008
Hey everyone,
I'm making a site and I need to write some code that will do the following things:
Write a statement for the Page_Load event to pull the information from the db and load it into a textbox and a textbox with multiline attribute.
View 13 Replies
View Related
Nov 20, 2003
Please find the necessary SQL scripts to generate a small version of my database and some data at the bottom of this post.
Here's a short description of what the database is all about: It's a project tracking and management system. Contracts go into the tblDeals table. Because each project may be different in nature, project phases are defined in tblPhaseType and tblPhase tables. The table used to keep track of what's going on is the tblProduction table.
Here's what I need to do. When a project is completed -- meaning it has gone through all the phases that it needs to go through -- I want a trigger to fire up and change the contract status in the tblDeals table to "Completed" whose value is 1. When a new contract gets entered into the table, the Contract Status is set to 5 by default which means "In Progress" -- as defined in tblContractStatus. The tricky part is that because, each project is different and has different number of phases, the trigger has to make sure that all the phases have been submitted into the tblProduction table for that particular deal.
I'd really appreciate some help here. Thanks in advance for all your help.
---------------------------------------
Here's the script
---------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblCompany]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblCompany
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblContractStatus]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblContractStatus
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblPhaseType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblPhase_tblPhaseType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblPhase] DROP CONSTRAINT FK_tblPhase_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblProduction_tblDeals]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblDeals
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblProduction_tblPhase]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblPhase
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblProduction]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblProduction]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDeals]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDeals]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPhase]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPhase]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCompany]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCompany]
GO
/****** Object: Table [dbo].[tblContractStatus] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblContractStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblContractStatus]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPhaseType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPhaseType]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/20/2003 11:30:50 AM ******/
CREATE TABLE [dbo].[tblCompany] (
[CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblContractStatus] Script Date: 11/20/2003 11:30:50 AM ******/
CREATE TABLE [dbo].[tblContractStatus] (
[StatusID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[Status] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/20/2003 11:30:51 AM ******/
CREATE TABLE [dbo].[tblPhaseType] (
[PhaseTypeID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[Desription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/20/2003 11:30:51 AM ******/
CREATE TABLE [dbo].[tblDeals] (
[DealID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyID] [int] NOT NULL ,
[DealDate] [smalldatetime] NOT NULL ,
[PhaseTypeID] [tinyint] NOT NULL ,
[CashAmount] [smallmoney] NOT NULL ,
[StatusID] [tinyint] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/20/2003 11:30:52 AM ******/
CREATE TABLE [dbo].[tblPhase] (
[PhaseID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[PhaseTypeID] [tinyint] NOT NULL ,
[PhaseDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PhasePercentage] [float] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/20/2003 11:30:52 AM ******/
CREATE TABLE [dbo].[tblProduction] (
[TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
[DealID] [int] NOT NULL ,
[PhaseID] [tinyint] NOT NULL ,
[TransactionTimeStamp] [smalldatetime] NOT NULL ,
[Comments] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompany] WITH NOCHECK ADD
CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED
(
[CompanyID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblContractStatus] WITH NOCHECK ADD
CONSTRAINT [PK_tblContractStatus] PRIMARY KEY CLUSTERED
(
[StatusID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhaseType] WITH NOCHECK ADD
CONSTRAINT [PK_tblPhaseType] PRIMARY KEY CLUSTERED
(
[PhaseTypeID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] WITH NOCHECK ADD
CONSTRAINT [PK_tblDeals] PRIMARY KEY CLUSTERED
(
[DealID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhase] WITH NOCHECK ADD
CONSTRAINT [PK_tblPhase] PRIMARY KEY CLUSTERED
(
[PhaseID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblProduction] WITH NOCHECK ADD
CONSTRAINT [PK_tblProduction] PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] ADD
CONSTRAINT [DF_tblDeals_StatusID] DEFAULT (5) FOR [StatusID]
GO
ALTER TABLE [dbo].[tblProduction] ADD
CONSTRAINT [DF_tblProduction_TransactionTimeStamp] DEFAULT (getdate()) FOR [TransactionTimeStamp]
GO
ALTER TABLE [dbo].[tblDeals] ADD
CONSTRAINT [FK_tblDeals_tblCompany] FOREIGN KEY
(
[CompanyID]
) REFERENCES [dbo].[tblCompany] (
[CompanyID]
),
CONSTRAINT [FK_tblDeals_tblContractStatus] FOREIGN KEY
(
[StatusID]
) REFERENCES [dbo].[tblContractStatus] (
[StatusID]
),
CONSTRAINT [FK_tblDeals_tblPhaseType] FOREIGN KEY
(
[PhaseTypeID]
) REFERENCES [dbo].[tblPhaseType] (
[PhaseTypeID]
)
GO
ALTER TABLE [dbo].[tblPhase] ADD
CONSTRAINT [FK_tblPhase_tblPhaseType] FOREIGN KEY
(
[PhaseTypeID]
) REFERENCES [dbo].[tblPhaseType] (
[PhaseTypeID]
)
GO
ALTER TABLE [dbo].[tblProduction] ADD
CONSTRAINT [FK_tblProduction_tblDeals] FOREIGN KEY
(
[DealID]
) REFERENCES [dbo].[tblDeals] (
[DealID]
),
CONSTRAINT [FK_tblProduction_tblPhase] FOREIGN KEY
(
[PhaseID]
) REFERENCES [dbo].[tblPhase] (
[PhaseID]
)
GO
exec sp_addextendedproperty N'MS_Description', N'Identifier', N'user', N'dbo', N'table', N'tblContractStatus', N'column', N'StatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'Description', N'user', N'dbo', N'table', N'tblContractStatus', N'column', N'Status'
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the type of phase structure this deal will go through', N'user', N'dbo', N'table', N'tblDeals', N'column', N'PhaseTypeID'
GO
exec sp_addextendedproperty N'MS_Description', N'Identifies the current status of deal', N'user', N'dbo', N'table', N'tblDeals', N'column', N'StatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the percentage value of the phase', N'user', N'dbo', N'table', N'tblPhase', N'column', N'PhasePercentage'
GO
exec sp_addextendedproperty N'MS_Description', null, N'user', N'dbo', N'table', N'tblProduction', N'column', N'TransactionTimeStamp'
GO
---------------------------------------
And here's some data
---------------------------------------
INSERT INTO [tblPhaseType] ([Desription])VALUES('TV Commercial - 4 Phases')
INSERT INTO [tblPhaseType] ([Desription])VALUES('Full Campaign - 6 Phases')
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Customer Info',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Write script',2.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Shoot',3.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Edit commercial',2.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Customer info',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Write script',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Design print ad',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Shoot',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Edit',2.000000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Publish',2.000000000000000e-001)
INSERT INTO [tblContractStatus] ([Status])VALUES('Completed')
INSERT INTO [tblContractStatus] ([Status])VALUES('Hold')
INSERT INTO [tblContractStatus] ([Status])VALUES('Collections')
INSERT INTO [tblContractStatus] ([Status])VALUES('Legal')
INSERT INTO [tblContractStatus] ([Status])VALUES('In Progress')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Johnny''s Remodeling')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Perfect Cut Lawncare')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Useless Ideas Unlimited')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Try-It-Again, Inc.')
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(1,'Aug 5 2003 12:00:00:000AM',1,120.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(2,'Sep 9 2003 12:00:00:000AM',2,150.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(3,'Sep 10 2003 12:00:00:000AM',2,130.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(4,'Nov 20 2003 12:00:00:000AM',1,190.0000,5)
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,1,'Nov 10 2003 10:23:00:000AM','Received company logo')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,2,'Nov 10 2003 10:23:00:000AM','Finished writing script')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,5,'Nov 10 2003 10:23:00:000AM','Just received company info')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,7,'Nov 10 2003 10:24:00:000AM','Finished designing ad copy')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,3,'Nov 20 2003 11:29:00:000AM','Did more work')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,4,'Nov 20 2003 11:29:00:000AM','Finally finished the job')
View 3 Replies
View Related
Jun 16, 2004
i have a query and i would like to write the contents of the dataset out directly to a file and not bother with creating another temp table and then exporting it with a command with a dts to a csv file.
it is a type of reporting that i am trying to do
but i just need to export the raw data i retreive from the query
View 3 Replies
View Related
Oct 25, 1999
Is it possible to do the following without cursors or creating an identity column:
I have a table from legacy data with ~ 1 million records. I need to insert this into the new table which has a unique varchar(11) key. For the new system this key is generated by calling a SP that returns the next key in sequence. To put the legacy data records in the same table I want to first create a new column at end of legacy data table and populate this using SQL without going thru using cursor and calling the SP for each and every record to get a unique varchar(11) key.
In short here is what I want:
Field1 Field2 varchar(11)key
------ ------ -----------
jsdhf dsf99 1LEG
878jh whjhj 2LEG
8728jh whjhj 3LEG
8578jh whjhj 4LEG
3878jh whjhj 5LEG
6878jh whjhj 6LEG
8508jh whjhj 7LEG
...
...
...
4878jh whjhj 1000000LEG
How do I generate this key using an SQL stmt?
Thanks in advance,
Nishi
View 4 Replies
View Related
Apr 5, 2006
I have table Products
I have coloumn like ProductName:
ProductName Having two values like Colgate and pepsi
I want to change these coloumn values in the result set
Please write a query
Hi here is clear question?
Table Name is Products
in that One coloumn name is
ProductsName:
and the values for productsName are
colgate
pepsi
the result set should be like following way
ProductsName:
Mike
Johnson
so here I am replacing value Mike instead of colgate
and Johnson instead of pepsi
View 1 Replies
View Related
Apr 14, 2006
Please help with writing query
I have 2 tables:
Table1:
IDDescription
1name1
2name2
3name3...
Table2:
SiDID
1231
4562
789myname
852yourname
if description exisits in table1 get description from table1 in
all other cases get ID from table2
View 5 Replies
View Related
Apr 19, 2006
please help me write a query:
I have a user function dbo.udf_Valid which is return true or false
(@SID ,@PType,@Group,@Date)
Table 1 with this info:
SID PType Group
12 12 123
45 1 456
Table2 with this info:
PType PType2
12 13
12 45
12 8
1 8
1 9
when I pass the data from table1 to the function I am Okay.
The complexity starts when table2 is involved.
I need to pass PType2 to the function if Ptype is exists in table1.
If function returns true for one of the PType2 return true in all other cases false.
View 3 Replies
View Related
Aug 8, 2005
I need assistance on writing a particular query:
I need to be able to filter out duplicates that have different values in a particular field.
For example, I have a table that contains the following:
Code:
WBS1 WBS2 WBS3 Amount Section
123 13 00 475 F
123 13 00 0 L
123 21 01 125 C
123 24 03 50 L
I need to filter out the first two lines because they have the same values for the first three columns but the last column under 'Section' has different values.
Can someone help me?
Thanks
View 2 Replies
View Related
Apr 28, 2006
Hello I'm new on sql server and I wonder if there's an specific order for writing a query's clauses (joins, and other conditions in where clause or even the order of the tables in the from clause), I mean, I have a query that takes 1 minute executing, but if I execute the same query after moving the tables in the from clause (changing the order) It takes 5 seconds, could you please tell me Why ??? and what's the best order from writing a query ?
This is the query I was talking about:
SELECT TREP_VALOR_PRECIOS.CDPRECIO,
TREP_VALOR_PRECIOS.NMANO,
TREP_VALOR_PRECIOS.NMSEMANA,
ISNULL(DBO.FNCREP_VALORPRECIO(TREP_PRECIOS.CDPRECI O,0,TREP_VALOR_PRECIOS.NMSEMANA,TREP_VALOR_PRECIOS .NMANO),0) VRPAGO_COBRO,
UN_TASASCAMBI.TASTASACAMB VRTASA_CAMBIO,
TREP_PRECIOS.CDTIPO_PRECIO,
TREP_VALOR_PRECIOS.SNACTIVO,
'N' SNACTIVAR
FROM
UN_TASASCAMBI, -- I just moved this one to the end of the from clause
T2_CALENDARIO, -- I just moved this one to the end of the from clause
UN_MONEDEXTRA,
TREP_PRECIOS TREP_PRECIOS_BASE,
TREP_VALOR_PRECIOS,
TREP_PRECIOS
WHERE T2_CALENDARIO.CALFECHAINICIAL BETWEEN UN_TASASCAMBI.TASFECHDESD AND UN_TASASCAMBI.TASFECHHAST
AND UN_TASASCAMBI.TASCOMPANIA = UN_MONEDEXTRA.MEXCOMPANIA
AND UN_TASASCAMBI.TASMONECAMB = UN_MONEDEXTRA.MEXCODIGO
AND T2_CALENDARIO.CALSEMANA = TREP_VALOR_PRECIOS.NMSEMANA
AND T2_CALENDARIO.CALANO = TREP_VALOR_PRECIOS.NMANO
AND T2_CALENDARIO.CALTIPOFRUTA = '01'
AND UN_MONEDEXTRA.MEXCOMPANIA = TREP_PRECIOS_BASE.CDCOMPANIA
AND UN_MONEDEXTRA.MEXCODIGO = TREP_PRECIOS_BASE.CDMONEDA
AND TREP_PRECIOS_BASE.CDMONEDA <> 'PESOC'
AND TREP_PRECIOS_BASE.CDPRECIO = TREP_VALOR_PRECIOS.CDPRECIO
AND TREP_VALOR_PRECIOS.SNACTIVO = 'S'
AND TREP_VALOR_PRECIOS.CDPRECIO = DBO.FNCREP_OBTENER_PRECIO_BASE(TREP_PRECIOS.CDPREC IO)
AND TREP_PRECIOS.CDTIPO_PRECIO = 'F'
View 7 Replies
View Related
Apr 15, 2008
Hi Friends,
I need help on writing an optimized query for the following problem..
Consider a table with columns "Date Emp1 Emp2"
Date Emp1 Emp2
1-feb 1 . . 2
2-feb 3 . . 4
3-feb 6 . . 1
4-feb 1 . . 2
5-feb 1 . . 5
6-Feb 5 . . 7
Now, I want search for records with employee id 1 in a way that if column emp1 contains 1 then I want the data in column emp2, and if column emp2 contains 1, then I want data in column emp1.
The output has to be a single column with no duplicate values. In the above example, for employee id 1, the output would be, 2,5 and 6 only.
This table has lakhs of records. I have to scan both columns for a given employee id. What will be the most optimized way to retrieve the data faster. Also, do I need to restructure this table for faster data retrieval?
I have indexes on emp1 and emp2 columns. Do we need union here and if yes, what is the best optimized query for the same?
View 9 Replies
View Related
May 29, 2008
Hi,
Mine Below Query is taking too much execution time. I am using this query in one of my sp.
Is there any other way to write this query?
Declare @p_Location_Code nvarchar(10)
Declare @p_ShowNearFarRate int
DECLARE @p_Data_Show_Flag int
Set @p_ShowNearFarRate = 1
Set @p_Data_Show_Flag = 1
select
X.Main_Party_Role ,
X.SET_ID,
X.TradeVisionId,
X.EntityId,
DBO.GET_ENTITY_TICKER (X.EntityId) Ticker,
X.Done,
X.MatchDate,
X.Main_Party_Id,
X.BUYER_MNEMONIC,
X.SELLER_MNEMONIC,
X.Other_Party_Id,
DBO.GET_DDMONYYYY_DATE_FORMAT(X.Main_Near_Dt) Main_Near_Dt,
DBO.GET_DDMONYYYY_DATE_FORMAT(X.Main_Far_Dt) Main_Far_Dt,
X.Amounts,
'Amount' = Case When ((ISNULL(X.Amount,0) >20) ) then Case When @p_Location_Code = 'NY44' then '20+' Else convert(nvarchar,x.Amount) End Else convert(nvarchar,x.Amount) End,
X.Price,
X.Duration,
X.Other_Party_Bro,
X.Main_Party_Bro,
X.Main_Party_State,
X.Other_Party_State,
'Main_Party_Near_Parse' = Case When (X.Main_Party_State=184 and X.Other_Party_State=184) THEN X.Main_Party_Near_Parse Else Case When @p_ShowNearFarRate = 1 then X.Main_Party_Near_Parse End End,
'Main_Party_Far_Parse' = Case When (X.Main_Party_State=184 and X.Other_Party_State=184) THEN X.Main_Party_Far_Parse Else Case When @p_ShowNearFarRate = 1 then X.Main_Party_Far_Parse End End,
X.Main_Party_Spread BPS,
'Main_Party_Amt' = Case When @p_Location_Code <> 'NY44' then X.Main_Party_Amt Else Case When (ISNULL(X.DONE,0) = 1 or X.Main_Party_State = 184 or X.Main_Party_State = 106) then X.Main_Party_Amt Else Case When ISNULL(X.Main_Party_Amt,0) <= 20 then X.Main_Party_Amt Else 20 End End End,
X.Other_Party_Near_Parse,
X.Other_Party_Far_Parse,
X.Other_Party_Spread,
X.Other_Party_Amt,
X.LOCATION_CODE,
X.Color_Value Org_Color_Value,
'Color_Value' = Case When @p_Location_Code = 'NY44' then
Case When X.Color_Value=500 then 500 + X.CallMe_Btn_Value
Else Case When X.Color_Value>= 284 and Isnull(x.done,0) <> 1 then 284 + X.CallMe_Btn_Value
Else Case When X.Color_Value=201 then 0 + X.CallMe_Btn_Value
Else Case When X.Color_Value=200 then 0 + X.CallMe_Btn_Value
Else Case When X.Color_Value=184 then 0 + X.CallMe_Btn_Value
Else Case When X.Color_Value=106 then 16 + X.CallMe_Btn_Value
Else Case When X.Color_Value=101 then 0 + X.CallMe_Btn_Value
Else Case When X.Color_Value=100 then 0 + X.CallMe_Btn_Value
Else Case When X.Color_Value=16 then 16 + X.CallMe_Btn_Value
Else 0 + X.CallMe_Btn_Value
End
End
End
End
End
End
End
End
End
Else
Case When X.Color_Value>=300 then 300 + X.CallMe_Btn_Value
Else Case When X.Color_Value=284 then 284 + X.CallMe_Btn_Value
Else Case When X.Color_Value=201 then 201 + X.CallMe_Btn_Value
Else Case When X.Color_Value=200 then 200 + X.CallMe_Btn_Value
Else Case When X.Color_Value=184 then 184 + X.CallMe_Btn_Value
Else Case When X.Color_Value=106 then 106 + X.CallMe_Btn_Value
Else Case When X.Color_Value=101 then 101 + X.CallMe_Btn_Value
Else Case When X.Color_Value=100 then 100 + X.CallMe_Btn_Value
Else Case When X.Color_Value=16 then 16 + X.CallMe_Btn_Value
Else 0 + X.CallMe_Btn_Value
End
End
End
End
End
End
End
End
End
End,
X.Main_Party_Strike_State,
X.Other_Party_Strike_State,
'BACK_COLOR' = (DBO.Fn_Get_TradeVisionRR_Back_Color_Value (@p_Location_Code, X.Color_Value)) ,
'FORE_COLOR' = (DBO.Fn_Get_TradeVisionRR_Fore_Color_Value (@p_Location_Code, X.Color_Value))
FROM
(
SELECT
Y.TradeVisionFileIdSET_ID,
'BUYER'Main_Party_Role,
Y.TradeVisionId,
Y.EntityId,
Y.Done,
Y.MatchDate,
Y.BuyerIdMain_Party_Id,
DBO.GET_CUSTOMER_MNEMONIC(Y.BUYERId) BUYER_MNEMONIC,
'SELLER_MNEMONIC' = Case When Y.Done = 1 THEN DBO.GET_CUSTOMER_MNEMONIC(Y.SELLERID)
Else Case When ( @p_Location_Code <> 'NY44' AND Y.BuyerState = 184AND Y.BuyerState = 184AND Y.IsBNearPraceTouched = 1 AND Y.IsSNearPraceTouched = 1 AND Y.IsBFarPraceTouched = 1AND Y.IsSFarPraceTouched = 1 AND Y.IsBSpreadTouched = 1 AND Y.IsSSpreadTouched = 1 AND Y.IsBAmtTouched = 1 AND Y.IsSAmtTouched= 1 AND Y.BuyerNearPrace = Y.SellerNearPrace AND Y.BuyerFarPrace = Y.SellerFarPrace AND Y.BuyerSpread = Y.SellerSpreadAND Y.BuyerAmt = Y.SellerAmT) THEN DBO.GET_CUSTOMER_MNEMONIC(Y.SELLERID)
Else Case When isnull(@p_Data_Show_Flag,1)=3 THEN DBO.GET_CUSTOMER_MNEMONIC(Y.SELLERId)
Else '*'
End
End
End,
Y.SellerIdOther_Party_Id,
Y.NearDateMain_Near_Dt,
Y.FarDateMain_Far_Dt,
Y.AmountsAmounts,
Y.AmountAmount,
Y.PricePrice,
Y.DurationDuration,
Y.SellerBroOther_Party_Bro,
Y.BuyerBroMain_Party_Bro,
Y.BuyerStateMain_Party_State,
Y.SellerStateOther_Party_State,
Y.BuyerNearPraceMain_Party_Near_Parse,
Y.BuyerFarPraceMain_Party_Far_Parse,
Y.BuyerSpreadMain_Party_Spread,
Y.BuyerAmtMain_Party_Amt,
Y.SellerNearPraceOther_Party_Near_Parse,
Y.SellerFarPraceOther_Party_Far_Parse,
Y.SellerSpreadOther_Party_Spread,
Y.SellerAmtOther_Party_Amt,
Y.LOCATION_CODE,
Y.Buyer_Strike_StateMain_Party_Strike_State,
Y.Seller_Strike_StateOther_Party_Strike_State,
(DBO.GET_TRADEVISIONRR_TRADEVISIONID_CUST_STATUS('NY44', 18, Y.TradeVisionId,Y.TRADEVISION_VERSION ,'BUYER' )) Color_Value ,
'CallMe_Btn_Value' = Case When Y.Buyer_Strike_State = 206 then 1000 Else 0 End
from DBO.TradeVisionRR Y
WHERE
Y.LOCATION_CODE = @p_Location_Code
AND Y.TRADEVISION_VERSION = (DBO.GET_TRADEVISIONRR_SET_MAX_VERSION('NY44', '02/05/2008', 169 ,Y.TradeVisionId ))
AND Y.TRADEVISIONFILEID = 169
------------------------------------------------------------
UNION ALL
------------------------------------------------------------
select
Y.TradeVisionFileIdSET_ID,
'SELLER'Main_Party_Role,
Y.TradeVisionId,
Y.EntityId,
Y.Done,
Y.MatchDate,
Y.SellerIdMain_Party_Id,
'BUYER_MNEMONIC' = Case When Y.Done = 1 THEN DBO.GET_CUSTOMER_MNEMONIC(Y.BUYERID)
Else Case When (@p_Location_Code <> 'NY44' AND Y.BuyerState = 184AND Y.BuyerState = 184AND Y.IsBNearPraceTouched = 1 AND Y.IsSNearPraceTouched = 1 AND Y.IsBFarPraceTouched = 1AND Y.IsSFarPraceTouched = 1 AND Y.IsBSpreadTouched = 1 AND Y.IsSSpreadTouched = 1 AND Y.IsBAmtTouched = 1 AND Y.IsSAmtTouched= 1 AND Y.BuyerNearPrace = Y.SellerNearPrace AND Y.BuyerFarPrace = Y.SellerFarPrace AND Y.BuyerSpread = Y.SellerSpreadAND Y.BuyerAmt = Y.SellerAmT) THEN DBO.GET_CUSTOMER_MNEMONIC(Y.BUYERID)
Else Case When isnull(@p_Data_Show_Flag,1)=3 THEN DBO.GET_CUSTOMER_MNEMONIC(Y.BUYERID)
Else '*'
End
End
End,
DBO.GET_CUSTOMER_MNEMONIC(Y.SELLERID) SELLER_MNEMONIC,
Y.BuyerIdOther_Party_Id,
Y.NearDateMain_Near_Dt,
Y.FarDateMain_Far_Dt,
Y.AmountsAmounts,
Y.AmountAmount,
Y.PricePrice,
Y.DurationDuration,
Y.BuyerBroOther_Party_Bro,
Y.SellerBroMain_Party_Bro,
Y.SellerStateMain_Party_State,
Y.BuyerStateOther_Party_State,
Y.SellerNearPraceMain_Party_Near_Parse,
Y.SellerFarPraceMain_Party_Far_Parse,
Y.SellerSpreadMain_Party_Spread,
Y.SellerAmtMain_Party_Amt,
Y.BuyerNearPraceOther_Party_Near_Parse,
Y.BuyerFarPraceOther_Party_Far_Parse,
Y.BuyerSpreadOther_Party_Spread,
Y.BuyerAmtOther_Party_Amt,
Y.LOCATION_CODE,
Y.Seller_Strike_StateMain_Party_Strike_State,
Y.Buyer_Strike_StateOther_Party_Strike_State,
(DBO.GET_TRADEVISIONRR_TRADEVISIONID_CUST_STATUS('NY44', 169, Y.TradeVisionId,Y.TRADEVISION_VERSION ,'SELLER' )) Color_Value ,
'CallMe_Btn_Value' = Case When Y.Seller_Strike_State = 206 then 1000 Else 0 End
from DBO.TradeVisionRR Y
WHERE
Y.LOCATION_CODE = @p_Location_Code
AND Y.TRADEVISION_VERSION = (DBO.GET_TRADEVISIONRR_SET_MAX_VERSION('NY44', '02/05/2008', 169 ,Y.TradeVisionId ))
AND Y.TRADEVISIONFILEID = 169
) X
where
X.EntityId IN ( Select Distinct Entity_Id from Fn_Get_Allowed_Entity_List('NY44' , 169 , '02/05/2008' ,200000 ))
and X.Main_Party_Id = 101901
Order By X.TradeVisionId
-- Regards
Prashant Hirani
View 7 Replies
View Related
Sep 28, 2005
Hi,
I have a table with the below data:
Billnumber
11111
11111
11111
33333
33333
44444
44444
44444
44444
I want to create an additional field lineItem like below
BillNumber LineItem
11111 1
11111 2
11111 3
33333 1
33333 2
44444 1
44444 2
44444 3
44444 4
Could somebody help me to write this query? Thanks
View 4 Replies
View Related
Jun 7, 2006
Help! I need to write a query that looks at one table with two columns. One column has 20K records and the second column has 25K records. I need to compare the two columns and pull out the difference between the two columns. The majority of the two columns data is the same but there are some that do not have a match between the two. The data is not in any current order. HELP! THANKS!
View 8 Replies
View Related
Nov 23, 2006
Hi, I need some help writing a query, and would rather not go into detail inside here. But if there is someone more advanced in SQL that could help me, could you please write me at g35gurl@yahoo.com.
Thanks!
View 2 Replies
View Related
Nov 27, 2007
Hi all,
I am not an expert in writing sql queries. Please help me writing this sql query.
I have a table with two columns like this.
teacher_id --student_id
1 ----------- 10
1 ----------- 11
2 ----------- 12
2 ----------- 13
2 ----------- 14
3 ----------- 15
4 ----------- 16
Now i need only teacher_id or teacher_id's for which minimum number of students assigned.
Thanks in advance
View 11 Replies
View Related
Jan 28, 2008
Hello,
I have a database containing 2 tables:
the first one called "weeks" has 4 columns: id, week number (ex:
2007/01), start date(ex:01/01/2007), end date(ex: 07/01/2007)
the second one is called "tests" and has 5 columns: index, date, name,
test name, duration
my goal: I want to make a query to get rows containing 3 columns:
week number,
name,
dates = date1, date2, etc.,
tests=test1, test2, etc.,
total duration of test for the given week and given person
To be clear, I am trying to get per week a summary of names with the
tests they did and how long total it lasted. The first tables is just
here to get the week number, given the test date.
My current query returns a list of all tests (table 2) with an added
column of the week number and looks like this:
SELECT date, initials, test, [hours outside 8-19], [Week number] FROM
tests, weeks WHERE date BETWEEN [start date] AND [end date]
What I need now is to have one line per person and per week with their total of hours for the week and the list of tests for the same week. I tried lots of things but does not know to get to my goal. Could
someone please help me?
Any help will be appreciated. Thank you in advance
Pierrot
View 3 Replies
View Related
Feb 1, 2008
Hi,
I need to write a stored procedure to take the backup of a particular database.The backup file name must be included with the date and time(ex: work_fb_01312008_21:11.bak).
I got some errors at the time of execution
Msg 3201, Level 16, State 1, Procedure fbackup, Line 11
Cannot open backup device 'D:ackupWork_fb_20080201_00:10:35:090.bak'. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.).
Msg 3013, Level 16, State 1, Procedure fbackup, Line 11
BACKUP DATABASE is terminating abnormally.
View 1 Replies
View Related
Mar 19, 2008
Dear friends!
I'd like to ask your assistance in writing some T-SQL.
I have a table
MYTABLE (THEDATE datetime, TEMPERATURE numeric(4))
3.02 15
4.02 16
5.02 16
8.02 13
10.02 15
11.02 15
12.02 19
I need a T-SQL without using analitic(range) functions like RANK or DENSE-RANK to provide the following output:
3.02 15
4.02 16
8.02 13
10.02 15
12.02 19
In other words I need to obtain only first occurance of the "neibough" adjacent rows where the temperature is equal.
Please advice.
Thanks in advance.
View 3 Replies
View Related
Mar 24, 2008
Guys,
is there any better way of writing the below query. i don't want to use select again and again.. and as my table has millions of records.. can u pls.suggest me the best possible method.
Select comm_Name,comm_CreationDate,
Total_Users = (Select count(*) from User_Community where UC_COMMID = comm_ID),
New_Members = (Select count(*) from User_Community where convert(char(10),uc_timestamp,101) > CONVERT (char (10) ,'01/01/2008' ,101) and UC_CommID = COMM_ID)
from Community
thanks
View 1 Replies
View Related
Apr 14, 2007
Database consists of the following 4 tables with respectiveattributes:CUSTOMER(C#, CUSTOMER NAME, ADDRESS), the key is [C#]ITEM(I#, ITEM NAME, MANUFACTURER, YEAR), the key is [I#]BOUGHT(C#, I#, DATE, QUANTITY), the key is [C#, I#,DATE]PREFER(I#, C#), the key is [I#, C#]I'm trying to construct the following query (in SQL)List of customers that bought all the items that John prefers.I can get the list of all the items that John prefers, but I'm notsure how to check that list against customers who bought ALL thoseitems. I'm assuming it's either a division or some sort of subtractionbut I'm not sure how to formulate the SQL query.Any and all help is appreciated, thanks!
View 21 Replies
View Related
Jul 6, 2007
Hello I'm a newbie to programming and need help writing an ifstatement.I have a database set up in SQL with the following fields:Category Questions AnswersI only want one category to appear for all of the questions andanswers submitted for that category. The way I have it set up now allif a question is submitted for the same category then the categorywill list twice and a one question under each other.How do I write something if I Dim CategoryIf it's the same category but a different question just list thatquestion under that category. If it's a new category list thatcategory and the questions and answers under that
View 3 Replies
View Related
Jul 20, 2005
Hello,I am trying to write a query and getting a little confused.My problem:I send enquiries to partners from 12:00am to 11:59pm each day. I have tenpartners, and each partner has a different daily limit. In my partner table(Table1), I have a columnm for the Daily Enquiry Limit called "DailyCap". Ialso have a second table (Table2) which counts how many enquiries a partnerhas had and assigns a date stamp.So, my query needs to:Select PARTNERwhere Table2.PartnerCount is less than Table1.DailyCapAND where Table2.TimeStamp between 12:00am TODAY and 11:59pm TODAY.This should hopefully then select any partner who hasent yet reached thedaily cap between midnight start and 24 hours later.Any odeas how to write this query using proper SQL? I have tried butfailed.Regards,Gary.
View 3 Replies
View Related
May 2, 2006
Hello I'm new on sql server and I wonder if there's an specific order for writing a query's clauses (joins, and other conditions in where clause or even the order of the tables in the from clause), I mean, I have a query that takes 1 minute executing, but if I execute the same query after moving the tables in the from clause (changing the order) It takes 5 seconds, could you please tell me Why ??? and what's the best order from writing a query ?
This is my specific query:
/**************/
This is the first one:
***************/
SELECT TREP_VALOR_PRECIOS.CDPRECIO,
TREP_VALOR_PRECIOS.NMANO,
TREP_VALOR_PRECIOS.NMSEMANA,
ISNULL(DBO.FNCREP_VALORPRECIO(TREP_PRECIOS.CDPRECI O,0,TREP_VALOR_PRECIOS.NMSEMANA,TREP_VALOR_PRECIOS .NMANO),0) VRPAGO_COBRO,
UN_TASASCAMBI.TASTASACAMB VRTASA_CAMBIO,
TREP_PRECIOS.CDTIPO_PRECIO,
TREP_VALOR_PRECIOS.SNACTIVO,
'N' SNACTIVAR
FROM
UN_TASASCAMBI,
T2_CALENDARIO,
UN_MONEDEXTRA,
TREP_PRECIOS TREP_PRECIOS_BASE,
TREP_VALOR_PRECIOS,
TREP_PRECIOS
WHERE T2_CALENDARIO.CALFECHAINICIAL BETWEEN UN_TASASCAMBI.TASFECHDESD AND UN_TASASCAMBI.TASFECHHAST
AND UN_TASASCAMBI.TASCOMPANIA = UN_MONEDEXTRA.MEXCOMPANIA
AND UN_TASASCAMBI.TASMONECAMB = UN_MONEDEXTRA.MEXCODIGO
AND T2_CALENDARIO.CALSEMANA = TREP_VALOR_PRECIOS.NMSEMANA
AND T2_CALENDARIO.CALANO = TREP_VALOR_PRECIOS.NMANO
AND T2_CALENDARIO.CALTIPOFRUTA = '01'
AND UN_MONEDEXTRA.MEXCOMPANIA = TREP_PRECIOS_BASE.CDCOMPANIA
AND UN_MONEDEXTRA.MEXCODIGO = TREP_PRECIOS_BASE.CDMONEDA
AND TREP_PRECIOS_BASE.CDMONEDA <> 'PESOC'
AND TREP_PRECIOS_BASE.CDPRECIO = TREP_VALOR_PRECIOS.CDPRECIO
AND TREP_VALOR_PRECIOS.SNACTIVO = 'S'
AND TREP_VALOR_PRECIOS.CDPRECIO = DBO.FNCREP_OBTENER_PRECIO_BASE(TREP_PRECIOS.CDPREC IO)
AND TREP_PRECIOS.CDTIPO_PRECIO = 'F'
/*********************************************/
This is the second one: This is much faster than the first one
/*********************************************/
SELECT TREP_VALOR_PRECIOS.CDPRECIO,
TREP_VALOR_PRECIOS.NMANO,
TREP_VALOR_PRECIOS.NMSEMANA,
ISNULL(DBO.FNCREP_VALORPRECIO(TREP_PRECIOS.CDPRECI O,0,TREP_VALOR_PRECIOS.NMSEMANA,TREP_VALOR_PRECIOS .NMANO),0) VRPAGO_COBRO,
UN_TASASCAMBI.TASTASACAMB VRTASA_CAMBIO,
TREP_PRECIOS.CDTIPO_PRECIO,
TREP_VALOR_PRECIOS.SNACTIVO,
'N' SNACTIVAR
FROM
UN_MONEDEXTRA,
TREP_PRECIOS TREP_PRECIOS_BASE,
TREP_VALOR_PRECIOS,
TREP_PRECIOS
UN_TASASCAMBI, -- I just moved this one to the end of the from clause
T2_CALENDARIO, -- I just moved this one to the end of the from clause
WHERE T2_CALENDARIO.CALFECHAINICIAL BETWEEN UN_TASASCAMBI.TASFECHDESD AND UN_TASASCAMBI.TASFECHHAST
AND UN_TASASCAMBI.TASCOMPANIA = UN_MONEDEXTRA.MEXCOMPANIA
AND UN_TASASCAMBI.TASMONECAMB = UN_MONEDEXTRA.MEXCODIGO
AND T2_CALENDARIO.CALSEMANA = TREP_VALOR_PRECIOS.NMSEMANA
AND T2_CALENDARIO.CALANO = TREP_VALOR_PRECIOS.NMANO
AND T2_CALENDARIO.CALTIPOFRUTA = '01'
AND UN_MONEDEXTRA.MEXCOMPANIA = TREP_PRECIOS_BASE.CDCOMPANIA
AND UN_MONEDEXTRA.MEXCODIGO = TREP_PRECIOS_BASE.CDMONEDA
AND TREP_PRECIOS_BASE.CDMONEDA <> 'PESOC'
AND TREP_PRECIOS_BASE.CDPRECIO = TREP_VALOR_PRECIOS.CDPRECIO
AND TREP_VALOR_PRECIOS.SNACTIVO = 'S'
AND TREP_VALOR_PRECIOS.CDPRECIO = DBO.FNCREP_OBTENER_PRECIO_BASE(TREP_PRECIOS.CDPREC IO)
AND TREP_PRECIOS.CDTIPO_PRECIO = 'F'
View 3 Replies
View Related
