select atleast one record where the date different is > 5 or 10 years.
I could only figure out for 5 years. so this is what I have to do.
Select top 1 Claims# where DateDiff(yy,FromDate,ThroughDate) >5
get the CalimsId of the result and do a query on that id to select all the values of the outcome id.
how i do that.
thanks
Hello~
I am wondering if anyone can recommend a basic, "How To" article, column or book for someone who has never worked with Access but is familiar with DB programming.
I need to know how to submit queries, and print reports.
Any help is appreciated.
Thank you!
Shelli
Hello, I really have a problem writing queries for the dynamic values. i follow the below mentioned method to write the queries but its really confusing. ex: str = "SELECT SO.Description,SO.DiscountPct,SO.Type,SO.Category,SO.StartDate,SO.EndDate,SO.MinQty,SO.MaxQty," + "S.Name AS ProductSubCategory,P.Name AS ProductName, C.Name AS ProductCategory FROM Production.Product P " + "INNER JOIN Production.ProductSubcategory S ON P.ProductSubcategoryID = S.ProductSubcategoryID " + "INNER JOIN Production.ProductCategory C ON S.ProductCategoryID = C.ProductCategoryID " + "INNER JOIN Sales.SpecialOfferProduct SOP ON P.ProductID = SOP.ProductID " + "INNER JOIN Sales.SpecialOffer SO ON SOP.SpecialOfferID = SO.SpecialOfferID " + "WHERE '" + txtStartDate.Text + "' between SO.StartDate AND SO.EndDate AND '" + txtEndDate.Text + "' BETWEEN SO.StartDate AND SO.EndDate " + "AND SO.Description Like '" + txtSpecialDesc.Text + "%'"; can anybody help me in writing the queries for dynamic values in an easy way. Thank you Sandeep Chavva
View 3 Replies View RelatedHiI am trying to write two Select * statements to the same text fileusing bcp (from a stored procedure).But cannot find a way of appending to a file using bcp.Does anyone know if this is possible or is there another way of writingmultiple queries to a file from a stored procedure?ThanksCaro
View 2 Replies View RelatedHi,
I have Table called performance where i what to get the AsOfDate(char 10) from that table... and i want to get the most recent based on this function.
ALTER FUNCTION [dbo].[udf_Quarter] (@Date datetime)
RETURNS char(10)
AS
BEGIN
DECLARE @month int
SET @month = Month(@Date)
RETURN
CASE
WHEN @month BETWEEN 4 AND 6 THEN 5
WHEN @month BETWEEN 7 AND 10 THEN 6
WHEN @month BETWEEN 11 AND 12 THEN 7
WHEN @month BETWEEN 1 AND 3 THEN 8
END
END
and i want this to return 3/31/2008 but i want to make it dynamic like if it is "3/31' and year can be depending on the year it passes
Any help will be appreciated.
Thanks
Karen
Hi, I need to write a simple function to format the contents of the fields in my table. I bascially want to say that if the value in a field is below '0' then format the text in colour red and if the value in the field is 0 or above then format it in black. Obviously this can be done by writing an expression in each field but i would prefer to write a function - any ideas.....
View 1 Replies View Related
I would like to write a user defined function that takes as an input a varchar and returns an xml. If the string is valid xml the function would just return it. If the string is not valid xml then the function would encode the value before returning it.
The function would look something like this:
Code Snippet
CREATE FUNCTION dbo.EncodeXMLIfNotValid (
@InputString varchar(max)
)
RETURNS xml
AS
BEGIN
DECLARE @xml xml
BEGIN TRY
SET @xml = CAST(@InputString as xml)
END TRY
BEGIN CATCH
SET @xml = '<![CDATA[' + @InputString + '>]]>'
END CATCH
RETURN( @xml )
END
However, after writting this I figured out that using BEGIN TRY and CATCH is not allowed in a UDF.
Any thoughts or ideas how I could write a function with this behavior?
Bob
I debug SPS on a daily basis and I use SQL profiler to help me trace where the problem is.
Once I have established which SP is the main problem I need to debug the line of code.
What I do is Cut and Paste the SQL Profiler details and populate all the parameters,sometimes that can be 30 and more..
Now what i thought is to write an SP or Function where I pass :
SP name and Parameters that profiler genererates
and returns me Declare Statements and Set Statements with parameters filled.
EG
Profiler Returns
Customer_INSERT,20,'JO',BLOGG','5 LONDON ROAD'
I would call my new SP =PopulateSPParams and cut and paste the profiler's string
PopulateSPParams 'Customer_Insert,20,'JO',BLOGG','5 LONDON ROAD'
this will RETURN THE FOLLOWING THAT WILL IMMENSILY HELP MY DAILY PROGRAMMING.
DECLARE
@CustomerID int,
@CustomerName varchar(50),
@CustomerSurname varchar(50),
@CustomerAddress varchar(100)
SET @CustomerID =1
SET @CustomerName='JO'
SET @CustomerSurname='BLOGG'
SET @CustomerAddress='5 London Road'
Can you help in writing something that generates and populate parameters?
suppose,the type of the stored procedure's paramters is varchar .I hate to add parameterNames and types.If i can read the string of the stored procedure the get the paramterNames by operating text?
public void storeOperate(string stringParameter,string name) { string[] strs=stringParameter.Split('&'); SqlConnection conn = new SqlConnection(getConnectionString.getconnectionString()); SqlCommand cmd=new SqlCommand(name,conn); cmd.CommandText=name; cmd.CommandType=CommandType.StoredProcedure; foreach(string str in strs) { cmd.Parameters.Add(".....",SqlDbType........).Value=str; //my trouble } conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); cmd.Dispose(); }
Hello,
We have created several Table Valued User Defined Functions in a Production SQL Server 2005 DB that are returning large (tens of thousands of) rows obtained through a web service. Our code is based on the MSDN article Extending SQL Server Reporting Services with SQL CLR Table-Valued Functions .
What we have found in our implementations of variations of this code on three seperate servers is that as the rowset grows, the length of time required to return the rows grows exponentially. With 10 columns, we have maxed out at approximately 2 500 rows. Once our rowset hit that size, no rows were being returned and the queries were timing out.
Here is a chart comparing the time elapsed to the rows returned at that time for a sample trial i ran:
Sec / Actual Rows Returned
0 0
10 237
20 447
30 481
40 585
50 655
60 725
70 793
80 860
90 940
100 1013
110 1081
120 1115
130 1151
140 1217
150 1250
160 1325
170 1325
180 1430
190 1467
200 1502
210 1539
220 1574
230 1610
240 1645
250 1679
260 1715
270 1750
280 1787
290 1822
300 1857
310 1892
320 1923
330 1956
340 1988
350 1988
360 2022
370 2060
380 2094
390 2094
400 2130
410 2160
420 2209
430 2237
440 2237
450 2274
460 2274
470 2308
480 2342
490 2380
500 2380
510 2418
520 2418
530 2451
540 2480
550 2493
560 2531
570 2566
It took 570 seconds (just over 9 1/2 minutes to return 2566 rows).
The minute breakdown during my trial is as follows:
1 = 655 (+ 655)
2 = 1081 (+ 426)
3 = 1325 (+244)
4 = 1610 (+285)
5 = 1822 (+212)
6 = 1988 (+166)
7 = 2160 (+172)
8 = 2308 (+148)
9 = 2451 (+143)
As you can tell, except for a few discrepancies to the resulting row count at minutes 4 and 7 (I will attribute these to timing as the results grid in SQL Management Studio was being updated once every 5 seconds or so), as time went on, fewer and fewer rows were being returned in a given time period. This was a "successful" run as the entire rowset was returned but on more than several occasions, we have reached the limit and have had 0 new rows per minute towards the end of execution.
Allow me to explain the code in further detail:
[SqlFunction(FillRowMethodName = "FillListItem")]
public static IEnumerable DiscoverListItems(...)
{
ArrayList listItems = new ArrayList();
SPToSQLService service = new SPToSQLService();
[...]
DataSet itemQueryResult = service.DoItemQuery(...); // This is a synchronous call returning a DataSet from the Web Service
//Load the DS to the ArrayList
return listItems;
}
public static void FillListItem(object obj, out string col1, out string col2, out string col3, ...)
{
ArrayList item = (ArrayList) obj;
col1 = item.Count > 0 ? (string) item[0] : "";
col2 = item.Count > 0 ? (string) item[1] : "";
col3 = item.Count > 0 ? (string) item[2] : "";
[...]
}
As you will notice, the web service is called, and the DataSet is loaded to an ArrayList object (containing ArrayList objects), before the main ArrayList is returned by the UDF method. There are 237 rows returned within 10 seconds, which leads me to believe that all of this has occured within 10 seconds. The method GetListItems has executed completely and the ArrayList is now being iterated through by the code calling the FillListItem method. I believe that this code is causing the result set to be returned at a decreasing rate. I know that the GetListItems code is only being executed once and that the WebService is only being called once.
Now alot of my larger queries ( > 20 000 rows) have timed out because of this behaviour, and my workaround was to customize my web service to page the data in reasonable chunks and call my UDF's in a loop using T-SQL. This means calling the Web Service up to 50 times per query in order to return the result set.
Surely someone else who has used Table Valued UDFs has come accross this problem. I would appreciate some feedback from someone in the know, as to whether I'm doing something wrong in my code, or how to optimize an SQL Server properly to allow for better performance with CLR functions.
Thanks,
Dragan Radovic
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies View RelatedI have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View 3 Replies View RelatedHi all,
I executed the following sql script successfuuly:
shcInLineTableFN.sql:
USE pubs
GO
CREATE FUNCTION dbo.AuthorsForState(@cState char(2))
RETURNS TABLE
AS
RETURN (SELECT * FROM Authors WHERE state = @cState)
GO
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
shcInlineTableFNresult.sql:
USE pubs
GO
SELECT * FROM shcInLineTableFN
GO
I got the following error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'shcInLineTableFN'.
Please help and advise me how to fix the syntax
"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
Thanks in advance,
Scott Chang
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
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
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
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 RelatedHi.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.
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 Relatedhi, 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 RelatedHello, 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!
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
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.
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')
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
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
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.????
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
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
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.
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
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'
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?