Search Results

Search found 88963 results on 3559 pages for 'server crashes'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 332/3559 | < Previous Page | 328 329 330 331 332 333 334 335 336 337 338 339  | Next Page >

  • Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

    - by ctrlShiftBryan
    Using MSSQL2005, Can I truncate a table with a foreign key constraint if I first truncate the child table(the table with the primary key of the FK relationship)? I know I can use a DELETE without a where clause and then RESEED the identity OR Remove the FK, truncate and recreate but I thought as long as you truncate the child table you'll be OK however I'm getting a "Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint." error.

    Read the article

  • What is happening in this T-SQL code?

    - by Ben McCormack
    I'm just starting to learn T-SQL and could use some help in understanding what's going on in a particular block of code. I modified some code in an answer I received in a previous question, and here is the code in question: DECLARE @column_list AS varchar(max) SELECT @column_list = COALESCE(@column_list, ',') + 'SUM(Case When Sku2=' + CONVERT(varchar, Sku2) + ' Then Quantity Else 0 End) As [' + CONVERT(varchar, Sku2) + ' - ' + Convert(varchar,Description) +'],' FROM OrderDetailDeliveryReview Inner Join InvMast on SKU2 = SKU and LocationTypeID=4 GROUP BY Sku2 , Description ORDER BY Sku2 Set @column_list = Left(@column_list,Len(@column_list)-1) Select @column_list ---------------------------------------- 1 row is returned: ,SUM(Case When Sku2=157 Then Quantity Else 0 End) As [157 -..., SUM(Case ... The T-SQL code does exactly what I want, which is to make a single result based on the results of a query, which will then be used in another query. However, I can't figure out how the SELECT @column_list =... statement is putting multiple values into a single string of characters by being inside a SELECT statement. Without the assignment to @column_list, the SELECT statement would simply return multiple rows. How is it that by having the variable within the SELECT statement that the results get "flattened" down into one value? How should I read this T-SQL to properly understand what's going on?

    Read the article

  • SQL: Recursively get parent records using Common Table Expressions

    - by Martijn B
    Hi there, Suposse you have to following tables where a sale consists of products and a product can be placed in multiple categories. Whereby categories have a hierachly structure like: Man Shoes Sport Casual Watches Women Shoes Sport Casual Watches Tables: Sale: id name 1 Sale1 Product: id saleidfk name 1 1 a 2 1 b 3 1 c 4 1 d 5 1 e ProductCategory : productid categoryid 1 3 2 3 3 4 4 5 5 10 Category: id ParentCategoryIdFk name 1 null Men 2 1 Shoes 3 2 Sport 4 2 Casual 5 1 Watches 6 null Women 7 6 Shoes 8 7 Sport 9 7 Casual 10 6 Watches Question: Now on my website I want to create a control where only the categories are shown of a certain sale and where the categories are filled with the products of the sale. I also want to include the hierachly structure of the categories. So if we have a leave node, recusivly go up to the top node. So with sale1 I should have a query with the following result: Men Shoes Sport Casual Watches Women Watches This thing is driving me crazy :-) Thanks in advance! Gr Martijn

    Read the article

  • stored procedure vs UDF

    - by TheObserver
    I have a select statement and in a couple of the fields, I want to check if an entry for the record exists in another table and if it does, output 1 value and if it doesn't, provide another value. What would be the best way to do it? When would you use a stored procedure and when would you use a UDF?

    Read the article

  • Does query plan optimizer works well with joined/filtered table-valued functions?

    - by smoothdeveloper
    In SQLSERVER 2005, I'm using table-valued function as a convenient way to perform arbitrary aggregation on subset data from large table (passing date range or such parameters). I'm using theses inside larger queries as joined computations and I'm wondering if the query plan optimizer work well with them in every condition or if I'm better to unnest such computation in my larger queries. Does query plan optimizer unnest table-valued functions if it make sense? If it doesn't, what do you recommend to avoid code duplication that would occur by manually unnesting them? If it does, how do you identify that from the execution plan? code sample: create table dbo.customers ( [key] uniqueidentifier , constraint pk_dbo_customers primary key ([key]) ) go /* assume large amount of data */ create table dbo.point_of_sales ( [key] uniqueidentifier , customer_key uniqueidentifier , constraint pk_dbo_point_of_sales primary key ([key]) ) go create table dbo.product_ranges ( [key] uniqueidentifier , constraint pk_dbo_product_ranges primary key ([key]) ) go create table dbo.products ( [key] uniqueidentifier , product_range_key uniqueidentifier , release_date datetime , constraint pk_dbo_products primary key ([key]) , constraint fk_dbo_products_product_range_key foreign key (product_range_key) references dbo.product_ranges ([key]) ) go . /* assume large amount of data */ create table dbo.sales_history ( [key] uniqueidentifier , product_key uniqueidentifier , point_of_sale_key uniqueidentifier , accounting_date datetime , amount money , quantity int , constraint pk_dbo_sales_history primary key ([key]) , constraint fk_dbo_sales_history_product_key foreign key (product_key) references dbo.products ([key]) , constraint fk_dbo_sales_history_point_of_sale_key foreign key (point_of_sale_key) references dbo.point_of_sales ([key]) ) go create function dbo.f_sales_history_..snip.._date_range ( @accountingdatelowerbound datetime, @accountingdateupperbound datetime ) returns table as return ( select pos.customer_key , sh.product_key , sum(sh.amount) amount , sum(sh.quantity) quantity from dbo.point_of_sales pos inner join dbo.sales_history sh on sh.point_of_sale_key = pos.[key] where sh.accounting_date between @accountingdatelowerbound and @accountingdateupperbound group by pos.customer_key , sh.product_key ) go -- TODO: insert some data -- this is a table containing a selection of product ranges declare @selectedproductranges table([key] uniqueidentifier) -- this is a table containing a selection of customers declare @selectedcustomers table([key] uniqueidentifier) declare @low datetime , @up datetime -- TODO: set top query parameters . select saleshistory.customer_key , saleshistory.product_key , saleshistory.amount , saleshistory.quantity from dbo.products p inner join @selectedproductranges productrangeselection on p.product_range_key = productrangeselection.[key] inner join @selectedcustomers customerselection on 1 = 1 inner join dbo.f_sales_history_..snip.._date_range(@low, @up) saleshistory on saleshistory.product_key = p.[key] and saleshistory.customer_key = customerselection.[key] I hope the sample makes sense. Much thanks for your help!

    Read the article

  • Custom View embed in Gallery crashes while key press

    - by tao
    Hi there, I'd like to find some stuff to replace the Tab component, so I'd make a custom View named StringView, which has the ability to display text, and to embed it into a Gallery. But it always crashes with error "NullPointerException at InputMethodManager". I have no idea about this, any help&tip&suggest are appreciate. Detail of my issue: First I'd created a class StringView extends View: public class StringView extends View { protected final Paint mPaint = new Paint(Paint.ANTI_ALIAS_FLAG); protected String mString; protected int mAscent; // Constructor public StringView(Context context, String string) { super(context); mPaint.setARGB(255, 255, 60, 10); mPaint.setTextSize(30); //mPaint.setFakeBoldText(true); mString = string; setPadding(20,15,20,15); } @Override protected void onDraw(Canvas canvas) { super.onDraw(canvas); int w = this.getPaddingLeft(); int h = this.getPaddingTop() - mAscent; canvas.drawText(mString, w, h, mPaint); } public void setString(String str) { mString = str; this.requestLayout(); this.invalidate(); } public String getString() { return mString; } @Override protected void onMeasure(int widthMeasureSpec, int heightMeasureSpec) { setMeasuredDimension(measureWidth(widthMeasureSpec), measureHeight(heightMeasureSpec)); } /** * Determines the width of this view * @param measureSpec A measureSpec packed into an int * @return The width of the view, honoring constraints from measureSpec */ private int measureWidth(int measureSpec) { int result = 0; int specMode = MeasureSpec.getMode(measureSpec); int specSize = MeasureSpec.getSize(measureSpec); if (specMode == MeasureSpec.EXACTLY) { // We were told how big to be result = specSize; } else { // Measure the text result = (int) mPaint.measureText(mString) + getPaddingLeft() + getPaddingRight(); if (specMode == MeasureSpec.AT_MOST) { // Respect AT_MOST value if that was what is called for by measureSpec result = Math.min(result, specSize); } } return result; } /** * Determines the height of this view * @param measureSpec A measureSpec packed into an int * @return The height of the view, honoring constraints from measureSpec */ private int measureHeight(int measureSpec) { int result = 0; int specMode = MeasureSpec.getMode(measureSpec); int specSize = MeasureSpec.getSize(measureSpec); mAscent = (int) mPaint.ascent(); if (specMode == MeasureSpec.EXACTLY) { // We were told how big to be result = specSize; } else { // Measure the text (beware: ascent is a negative number) result = (int) (-mAscent + mPaint.descent()) + getPaddingTop() + getPaddingBottom(); if (specMode == MeasureSpec.AT_MOST) { // Respect AT_MOST value if that was what is called for by measureSpec result = Math.min(result, specSize); } } return result; } } Second I put it in to Gallery through Adapter Gallery gallery = (Gallery) findViewById(R.id.gallery); gallery.setAdapter(new ImageAdapter(this)); ImageAdapter: public class ImageAdapter extends BaseAdapter { int mGalleryItemBackground; private Context mContext; private View[] mImages = genSerielImageViews(); public ImageAdapter(Context c) { mContext = c; TypedArray a = obtainStyledAttributes(R.styleable.Gallery); mGalleryItemBackground = a.getResourceId( R.styleable.Gallery_android_galleryItemBackground, 0); a.recycle(); } private View[] genSerielImageViews() { if (true) { int N = 6; StringView[] views = new StringView[N]; for (int i=0; i<N; i++) { views[i] = new StringView(mContext, "ITEM #" + Integer.toString(i) ); } return views; } else { int N = 6; TextView[] views = new TextView[N]; for (int i=0; i<N; i++) { views[i] = new TextView( mContext ); views[i].setText("CCTV #" + Integer.toString(i) ); } return views; } } public int getCount() { return mImages.length; } public Object getItem(int position) { return position; } public long getItemId(int position) { return position; } public View getView(int position, View convertView, ViewGroup parent) { return mImages[position]; } } Then Compile&Run, press keypad RIGHT and I got a crash, It's turns out a weird InputMethodManager error: 03-18 07:22:33.568: ERROR/AndroidRuntime(958): Uncaught handler: thread main exiting due to uncaught exception 03-18 07:22:33.648: ERROR/AndroidRuntime(958): java.lang.NullPointerException 03-18 07:22:33.648: ERROR/AndroidRuntime(958): at android.view.inputmethod.InputMethodManager.startInputInner(InputMethodManager.java:940) 03-18 07:22:33.648: ERROR/AndroidRuntime(958): at android.view.inputmethod.InputMethodManager.checkFocus(InputMethodManager.java:1114) 03-18 07:22:33.648: ERROR/AndroidRuntime(958): at android.view.ViewRoot.handleMessage(ViewRoot.java:1869) 03-18 07:22:33.648: ERROR/AndroidRuntime(958): at android.os.Handler.dispatchMessage(Handler.java:99) 03-18 07:22:33.648: ERROR/AndroidRuntime(958): at android.os.Looper.loop(Looper.java:123) 03-18 07:22:33.648: ERROR/AndroidRuntime(958): at android.app.ActivityThread.main(ActivityThread.java:4310) 03-18 07:22:33.648: ERROR/AndroidRuntime(958): at java.lang.reflect.Method.invokeNative(Native Method) 03-18 07:22:33.648: ERROR/AndroidRuntime(958): at java.lang.reflect.Method.invoke(Method.java:521) 03-18 07:22:33.648: ERROR/AndroidRuntime(958): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:860) 03-18 07:22:33.648: ERROR/AndroidRuntime(958): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:618) 03-18 07:22:33.648: ERROR/AndroidRuntime(958): at dalvik.system.NativeStart.main(Native Method) Thanks.

    Read the article

  • How to get SQL Function run a different query and return value from either query?

    - by RoguePlanetoid
    I need a function, but cannot seem to get it quite right, I have looked at examples here and elsewhere and cannot seem to get this just right, I need an optional item to be included in my query, I have this query (which works): SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR (1000),Description)) AS LenDesc FROM tblItem WHERE Title = @Title AND Manufacturer = @Manufacturer ORDER BY LenDesc DESC This works within a Function, however the Manufacturer is Optional for this search - which is to find the description of a similar item, if none is present, the other query is: SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR (1000),Description)) AS LenDesc FROM tblItem WHERE Title = @Title ORDER BY LenDesc DESC Which is missing the Manufacturer, how to I get my function to use either query based on the Manufacturer Value being present or not. The reason is I will have a function which first checks an SKU for a Description, if it is not present - it uses this method to get a Description from a Similar Product, then updates the product being added with the similar product's description. Here is the function so far: ALTER FUNCTION [dbo].[GetDescriptionByTitleManufacturer] ( @Title varchar(400), @Manufacturer varchar(160) ) RETURNS TABLE AS RETURN ( SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR (1000),Description)) AS LenDesc FROM tblItem WHERE Title = @Title AND Manufacturer = @Manufacturer ORDER BY LenDesc DESC ) I've tried adding BEGINs and IF...ELSEs but get errors or syntax problems each way I try it, I want to be able to do something like this pseudo-function (which does not work): ALTER FUNCTION [dbo].[GetDescriptionByTitleManufacturer] ( @Title varchar(400), @Manufacturer varchar(160) ) RETURNS TABLE AS BEGIN IF (@Manufacturer = Null) RETURN ( SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR (1000),Description)) AS LenDesc FROM tblItem WHERE Title = @Title ORDER BY LenDesc DESC ) ELSE RETURN ( SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR (1000),Description)) AS LenDesc FROM tblItem WHERE Title = @Title AND Manufacturer = @Manufacturer ORDER BY LenDesc DESC ) END

    Read the article

  • Select dynamic string has a different value when referenced in Where clause

    - by David
    I dynamically select a string built using another string. So, if string1='David Banner', then MyDynamicString should be 'DBanne' Select ... , Left( left((select top 1 strval from dbo.SPLIT(string1,' ')) //first word ,1) //first character + (select top 1 strval from dbo.SPLIT(string1,' ') //second word where strval not in (select top 1 strval from dbo.SPLIT(string1,' '))) ,6) //1st character of 1st word, followed by up to 5 characters of second word [MyDynamicString] ,... From table1 Join table2 on table1pkey=table2fkey Where MyDynamicString <> table2.someotherfield I know table2.someotherfield is not equal to the dynamic string. However, when I replace MyDynamicString in the Where clause with the full left(left(etc.. function, it works as expected. Can I not reference this string later in the query? Do I have to build it using the left(left(etc.. function each time in the where clause?

    Read the article

  • Need some help understanding IO Statistics

    - by Abe Miessler
    I have a query that has a very costly INDEX SEEK operation in the execution plan. In order to track down the cause i set IO STATISTICS on and ran it. In the problem section it gave the following statistics: Table '#TempStudents_Enrollment2_____________________________________000000004D5F'. Scan count 0, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#TempRace2______________________________________________000000004D58'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'RefRace'. Scan count 120, logical reads 240, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'RefFedEnctyRaceCatg'. Scan count 18, logical reads 36, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#43B0BA0F'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#42BC95D6'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#41C8719D'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#40D44D64'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#LEA2_________________________________________________000000004D56'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#39332B9C'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#School2________________________________________________000000004D57'. Scan count 1, logical reads 29164, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#GenderKey______________________________________________000000004D5A'. Scan count 1, logical reads 29164, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#LangAcqKey_____________________________________________000000004D5B'. Scan count 1, logical reads 29164, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#TransferCatKey___________________________________________000000004D5C'. Scan count 1, logical reads 29164, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#ResCatKey______________________________________________000000004D5D'. Scan count 1, logical reads 29164, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'RPT_SnapShot_1_4_StuPgm_Denorm'. Scan count 2344954, logical reads 4992518, physical reads 16, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#3FE0292B'. Scan count 1, logical reads 2344954, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'RPT_SnapShot_1_4_StuEnrlmt_Denorm'. Scan count 20, logical reads 87679, physical reads 0, read-ahead reads 87425, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#GradeKey_______________________________________________000000004D59'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. What should I look for in here when i'm looking to improve the performance? The line with over 2 million for the Scan count looked suspicious to me but I really don't know. Does anyone see anything here that i should look into in more detail?

    Read the article

  • Reporting on data when data is missing (ie. how to report zero activities for a customer on a given

    - by Christian Vik
    I want to create a report which aggregates the number of activities per customer per week. If there has been no activites on that customer for a given week, 0 should be displayed (i.e week 3 and 4 in the sample below) CUSTOMER | #ACTIVITIES | WEEKNUMBER A | 4 | 1 A | 2 | 2 A | 0 | 3 A | 0 | 4 A | 1 | 5 B ... C ... The problem is that if there are no activities there is no data to report on and therefor week 3 and 4 in the sample below is not in the report. What is the "best" way to solve this?

    Read the article

  • Disable all non-clustered indexes

    - by spender
    I select a number of non-clustered indexes from my database with the following: SELECT sys.objects.name tableName, sys.indexes.name indexName FROM sys.indexes JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id WHERE sys.indexes.type_desc = 'NONCLUSTERED' AND sys.objects.type_desc = 'USER_TABLE' I'd like to run the following over each of the results: ALTER INDEX indexName ON tableName DISABLE How would I go about doing this? Is there a better way?

    Read the article

  • Convert query with system objects from SQL 2000 to 2005/2008

    - by Dan
    I have some SQL I need to get working on SQL 2005/2008. The SQL is from SQL 2000 and uses some system objects to make it work. master.dbo.spt_provider_types master.dbo.syscharsets systypes syscolumns sysobjects I know SQL 2005 does no longer use system tables and I can get the same information from views, but I am looking for a solution that will work for both SQL 2000 and 2005/2008. Any ideas? select top 100 percent TABLE_CATALOG = db_name(), TABLE_SCHEMA = user_name(o.uid), TABLE_NAME = o.name, COLUMN_NAME = c.name, ORDINAL_POSITION = convert(int, ( select count(*) from syscolumns sc where sc.id = c.id AND sc.number = c.number AND sc.colid <= c.colid )), IS_COMPUTED = convert(bit, c.iscomputed) from syscolumns c left join syscomments m on c.cdefault = m.id and m.colid = 1, sysobjects o, master.dbo.spt_provider_types d, systypes t, master.dbo.syscharsets a_cha /* charset/1001, not sortorder.*/ where o.name = @table_name and permissions(o.id, c.name) <> 0 and (o.type in ('U','V','S') OR (o.type in ('TF', 'IF') and c.number = 0)) and o.id = c.id and t.xtype = d.ss_dtype and c.length = case when d.fixlen > 0 then d.fixlen else c.length end and c.xusertype = t.xusertype and a_cha.type = 1001 /* type is charset */ and a_cha.id = isnull(convert(tinyint, CollationPropertyFromID(c.collationid, 'sqlcharset')), convert(tinyint, ServerProperty('sqlcharset'))) -- make sure there's one and only one row selected for each column order by 2, 3, c.colorder ) tbl where IS_COMPUTED = 0

    Read the article

  • Different ways to query this search in SQL?

    - by Bart Terrell
    I am teaching myself MS-SQL and I am trying to find different ways to find the Count of Paid and Unpaid Claims for 2012 grouped by Region from these 3 tables. If there is a returned date, the claim is unpaid if the returned date is null then the claim is paid. I will attach the code I have ran, but I am not sure if there are better ways to do it. Thanks. Here is the code: SET dateformat ymd; CREATE TABLE Claims ( ClaimID INT, SubID INT, [Claim Date] DATETIME ); CREATE TABLE Phoneship ( ClaimID INT, [Shipping Number] INT, [Claim Date] DATETIME, [Ship Date] DATETIME, [Returned Date] DATETIME ); CREATE TABLE Enrollment ( SubID INT, Enrollment_Date DATETIME, Channel NVARCHAR(255), Region NVARCHAR(255), Status FLOAT, Drop_Date DATETIME ); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (102, 201, '2011-10-13 00:00:00', '2011-10-14 00:00:00', NULL); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (103, 202, '2011-11-02 00:00:00', '2011-11-03 00:00:00', '2011-11-20 00:00:00'); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (103, 203, '2011-11-02 00:00:00', '2011-11-22 00:00:00', NULL); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (105, 204, '2012-01-16 00:00:00', '2012-01-17 00:00:00', NULL); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (106, 205, '2012-02-15 00:00:00', '2012-02-16 00:00:00', '2012-02-26 00:00:00'); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (106, 206, '2012-02-15 00:00:00', '2012-02-27 00:00:00', '2012-03-06 00:00:00'); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (107, 207, '2012-03-12 00:00:00', '2012-03-13 00:00:00', NULL); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (108, 208, '2012-05-11 00:00:00', '2012-05-12 00:00:00', NULL); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (109, 209, '2012-05-13 00:00:00', '2012-05-14 00:00:00', '2012-05-28 00:00:00'); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (109, 210, '2012-05-13 00:00:00', '2012-05-30 00:00:00', NULL); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (101, 12345678, '2011-03-06 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (102, 12347190, '2011-10-13 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (103, 12348723, '2011-11-02 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (104, 12349745, '2011-11-09 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (105, 12347190, '2012-01-16 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (106, 12349234, '2012-02-15 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (107, 12350767, '2012-03-12 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (108, 12350256, '2012-05-11 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (109, 12347701, '2012-05-13 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (110, 12350256, '2012-05-15 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (111, 12350767, '2012-06-30 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12345678, '2011-01-05 00:00:00', 'Retail', 'Southeast', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12346178, '2011-03-13 00:00:00', 'Indirect Dealers', 'West', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12346679, '2011-05-19 00:00:00', 'Indirect Dealers', 'Southeast', 0, '2012-03-15 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12347190, '2011-07-25 00:00:00', 'Retail', 'Northeast', 0, '2012-05-21 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12347701, '2011-08-14 00:00:00', 'Indirect Dealers', 'West', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12348212, '2011-09-30 00:00:00', 'Retail', 'West', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12348723, '2011-10-20 00:00:00', 'Retail', 'Southeast', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12349234, '2012-01-06 00:00:00', 'Indirect Dealers', 'West', 0, '2012-02-14 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12349745, '2012-01-26 00:00:00', 'Retail', 'Northeast', 0, '2012-04-15 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12350256, '2012-02-11 00:00:00', 'Retail', 'Southeast', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12350767, '2012-03-02 00:00:00', 'Indirect Dealers', 'West', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12351278, '2012-04-18 00:00:00', 'Retail', 'Midwest', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12351789, '2012-05-08 00:00:00', 'Indirect Dealers', 'West', 0, '2012-07-04 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12352300, '2012-06-24 00:00:00', 'Retail', 'Midwest', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12352811, '2012-06-25 00:00:00', 'Retail', 'Southeast', 1, NULL); And Query1 SELECT Count(ClaimID) AS 'Paid Claim', (SELECT Count(ClaimID) FROM dbo.phoneship WHERE [returned date] IS NOT NULL) AS 'Unpaid Claim' FROM dbo.Phoneship WHERE [Returned Date] IS NULL GROUP BY claimid Query2 SELECT Count(*) AS 'Paid Claims', (SELECT Count(*) FROM dbo.Phoneship WHERE [Returned Date] IS NOT NULL) AS 'Unpaid Claims' FROM dbo.Phoneship WHERE [Returned Date] IS NULL; Query3 Select Distinct(C.[Shipping Number]), Count(C.ClaimID) AS 'COUNT ClaimID', A.Region, A.SubID From dbo.HSEnrollment A Inner Join dbo.Claims B On A.SubId = B.SubId Inner Join dbo.Phoneship C On B.ClaimID = C.ClaimID Where C.[Returned Date] IS NULL Group By A.Region, A.Subid, C.ClaimID, C.[Shipping Number] Order By A.Region

    Read the article

  • How I shoud use BIT in MS SQL 2005

    - by adopilot
    Regarding to SQL performance. I have Scalar-Valued function for checking some specific condition in base, It returns BIT value True or False, I now do not know how I should fill @BIT parameter If I write. set @bit = convert(bit,1) or set @bit = 1 or set @bit='true' Function will work anyway but I do not know which method is recommended for daily use. Another Question, I have table in my base with around 4 million records, Daily insert is about 4K records in that table. Now I want to add CONSTRAINT on that table whit scalar valued function that I mentioned already Something like this ALTER TABLE fin_stavke ADD CONSTRAINT fin_stavke_knjizenje CHECK ( dbo.fn_ado_chk_fin(id)=convert(bit,1)) Where is filed "id" primary key of table fin_stavke and dbo.fn_ado_chk_fin looks like create FUNCTION fn_ado_chk_fin ( @stavka_id int ) RETURNS bit AS BEGIN declare @bit bit if exists (select * from fin_stavke where id=@stavka_id and doc_id is null and protocol_id is null) begin set @bit=0 end else begin set @bit=1 end return @bit; END GO Will this type and method of cheeking constraint will affect badly performance on my table and SQL at all ? If there is also better way to add control on this table please let me know.

    Read the article

  • Double of Total Problem

    - by Gopal
    Table1 ID | WorkTime ----------------- 001 | 10:50:00 001 | 00:00:00 002 | .... WorkTime Datatype is *varchar(. SELECT ID, CONVERT(varchar(10), TotalSeconds1 / 3600) + ':' + RIGHT('00' + CONVERT(varchar(2), (TotalSeconds1 - TotalSeconds1 / 3600 * 3600) / 60), 2) + ':' + RIGHT('00' + CONVERT(varchar(2), TotalSeconds1 - (TotalSeconds1 / 3600 * 3600 + (TotalSeconds1 - TotalSeconds1 / 3600 * 3600) / 60 * 60)), 2) AS TotalWork From ( SELECT ID, SUM(DATEDIFF(second, CONVERT(datetime, '1/1/1900'), CONVERT(datetime, '1/1/1900 ' + WorkTime))) AS TotalSeconds1 FROM table1 group by ID) AS tab1 where id = '001' The above Query is showing "double the total of time" For Example From table1 i want to calculate the total WorkTime, when i run the above query it is showing ID WorkTime 001 21:40:00 002..., But it should show like this ID Worktime 001 10:50:00 ..., How to avoid the double total of worktime. How to modify my query. Need Query Help

    Read the article

  • SQL update query using joins

    - by Shyju
    I have to update a field with a value which is returned by a join of 3 tables. Example: select im.itemid ,im.sku as iSku ,gm.SKU as GSKU ,mm.ManufacturerId as ManuId ,mm.ManufacturerName ,im.mf_item_number ,mm.ManufacturerID from item_master im, group_master gm, Manufacturer_Master mm where im.mf_item_number like 'STA%' and im.sku=gm.sku and gm.ManufacturerID = mm.ManufacturerID and gm.manufacturerID=34 I want to update the mf_item_number field values of table master with some other value which is joined in the above condition. How to frame the SQL ?

    Read the article

  • Convert a MSSQL database to MYSQL database

    - by soldieraman
    Alright so I want to convert an already exist MSSQL database (2005) to a MYSQL database. There is nothing extraordinary to be done The only things I need to achieve is Recreate the tables Transfer data Relationships would be nice but not necessary No views, no sprocs, no functions. Any easy way to do this. Also do you know of any DST (Database Synchronization Tool) which would let me do MSSQL to MYSQL MYSQL to MYSQL MSSQL to MSSQL (I know there is SQL Delta for this)

    Read the article

  • Create a user-defined aggregate without SQL CLR

    - by David Pfeffer
    I am planning on deploying a database to SQL Azure, so I cannot use the SQL CLR. However, I have a need to create an aggregate function -- in my case, I need to STUnion a bunch of Geography objects together. (Azure is expected to support Spatial by June.) Is there another way to accomplish this, without making use of the CLR, in a query? Or do I have to create a UDF that will take a table as a parameter and return the aggregate?

    Read the article

  • How to create nonclustered index in Create Table.

    - by isthatacode
    Create table FavoriteDish ( FavID int identity (1,1) primary key not null, DishID int references Dishes(DishID) not null , CelebrityName nvarchar(100) nonclustered not null ) This results in - Incorrect syntax near the keyword 'nonclustered'. I referred the MSDN help for create table syntax. I am not sure whats wrong here? Thanks for reading.

    Read the article

  • Is it possible to create a new T-SQL Operator using CLR Code in MSSQL?

    - by Eoin Campbell
    I have a very simple CLR Function for doing Regex Matching public static SqlBoolean RegExMatch(SqlString input, SqlString pattern) { if (input.IsNull || pattern.IsNull) return SqlBoolean.False; return Regex.IsMatch(input.Value, pattern.Value, RegexOptions.IgnoreCase); } It allows me to write a SQL Statement Like. SELECT * FROM dbo.table1 WHERE dbo.RegexMatch(column1, '[0-9][A-Z]') = 1 -- match entries in col1 like 1A, 2B etc... I'm just thinking it would be nice to reformulate that query so it could be called like SELECT * FROM dbo.table1 WHERE column1 REGEXLIKE '[0-9][A-Z]' Is it possible to create new comparison operators using CLR Code. (I'm guessing from my brief glance around the web that the answer is NO, but no harm asking) Thanks, Eoin C

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 328 329 330 331 332 333 334 335 336 337 338 339  | Next Page >