Search Results

Search found 86974 results on 3479 pages for 'visualsvn server'.

Page 317/3479 | < Previous Page | 313 314 315 316 317 318 319 320 321 322 323 324  | Next Page >

• How to find level of employee position using RECURSIVE COMMON TABLE EXPRESSION

  - by user309381

  ;with Ranked(Empid,Mngrid,Empnm,RN,level) As ( select Empid,Mngrid ,Empnm ,row_number() over (order by Empid)AS RN , 0 as level from dbo.EmpMngr ), AnchorRanked(Empid,Mngrid,Empnm,RN,level) AS(select Empid,Mngrid,Empnm,RN ,level from Ranked ), RecurRanked(Empid,Mngrid,Empnm,RN,level) AS(select Empid,Mngrid,Empnm,RN,level from AnchorRanked Union All select Ranked.Empid,Ranked.Mngrid,Ranked.Empnm,Ranked.RN,Ranked.level + 1 from Ranked inner join RecurRanked on Ranked.Empid = RecurRanked.Empid AND Ranked.RN = RecurRanked.RN+1) select Empid,Empnm,level from RecurRanked

  Read the article

• Can Visual Studio.NET 2008 integrate SQL 2008?

  - by burntsugar

  Visual Studio 2008 ships with SQL 2005. I would like to integrate SQL 2008. Are there significant reasons why one could not/ can not do this?

  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

• SQL View with Data from two tables

  - by Alex

  Hello! I can't seem to crack this - I have two tables (Persons and Companies), and I'm trying to create a view that: 1) shows all persons 2) also returns companies by themselves once, regardless of how many persons are related to it 3) orders by name across both tables To clarify, some sample data: (Table: Companies) Id Name 1 Banana 2 ABC Inc. 3 Microsoft 4 Bigwig (Table: Persons) Id Name RelatedCompanyId 1 Joe Smith 3 2 Justin 3 Paul Rudd 4 4 Anjolie 5 Dustin 4 The output I'm looking for is something like this: Name PersonName CompanyName RelatedCompanyId ABC Inc. NULL ABC Inc. NULL Anjolie Anjolie NULL NULL Banana NULL Banana NULL Bigwig NULL Bigwig NULL Dustin Dustin Bigwig 4 Joe Smith Joe Smith Microsoft 3 Justin Justin NULL NULL Microsoft NULL Microsoft NULL Paul Rudd Paul Rudd Bigwig 4 As you can see, the new "Name" column is ordered across both tables (the company names appear correctly in between the person names), and each company appears exactly once, regardless of how many people are related to it. Can this even be done in SQL?! P.S. I'm trying to create a view so I can use this later for easy data retrieval, fulltext indexing and make the programming side simpler by just querying the view.

  Read the article

• Combining SQL Rows

  - by lumberjack4

  I've got SQL Compact Database that contains a table of IP Packet Headers. The Table looks like this: Table: PacketHeaders ID SrcAddress SrcPort DestAddress DestPort Bytes 1 10.0.25.1 255 10.0.25.50 500 64 2 10.0.25.50 500 10.0.25.1 255 80 3 10.0.25.50 500 10.0.25.1 255 16 4 75.48.0.25 387 74.26.9.40 198 72 5 74.26.9.40 198 75.48.0.25 387 64 6 10.0.25.1 255 10.0.25.50 500 48 I need to perform a query to show 'conversations' going on across a local network. Packets going from A - B is part of the same conversations as packets going from B - A. I need to perform a query to show the on going conversations. Basically what I need is something that looks like this: Returned Query: SrcAddress SrcPort DestAddress DestPort TotalBytes BytesA->B BytesB->A 10.0.25.1 255 10.0.25.50 500 208 112 96 75.48.0.25 387 74.26.9.40 198 136 72 64 As you can see I need the query (or series of queries) to recognize that A-B is the same as B-A and break up the byte counts accordingly. I'm not a SQL guru by any means but any help on this would be greatly appreciated.

  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

• 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

• Controlling Trigger Execution When Bulk Importing Data

  - by Vishwa

  Hi In bulk insertion only in the last record trigger is executing. If I bulk insert 10 records the trigger is running only for the 10th record. But I need trigger to run on all 10 records. Please help me with an example

  Read the article

• Adding Column While Selecting Table in SQl

  - by kmkperumal

  My First Table is ProjectCustomFields CustomFieldId ProjectId CustomFieldName CustomFieldRequired CustomFieldDataType 69 1 User Name 1 0 72 1 City 1 0 74 1 Email 0 0 82 1 Salary 1 2 My Second Table is ProjectCustomFieldValues CustomFieldValueId ProjectId CustomFieldId CustomFieldValue RecordId 35 1 69 kaliya 1 36 1 72 Bangalore 1 37 1 74 [email protected] 1 41 1 69 Yohesh 2 42 1 72 Delhi 2 43 1 74 2 50 1 69 sss 3 51 1 72 Delhi 3 52 1 74 [email protected] 3 57 1 69 Sunil 4 58 1 72 Mumbai 4 59 1 74 [email protected] 4 60 1 82 20000 4 I tried Below Query Select M.CustomFieldName,N.CustomFieldValue,N.RecordId From (Select G.CustomFieldName,H.RecordId From (Select CustomFieldName From ProjectCustomFields Where ProjectId=1) G Cross Join (Select Distinct RecordId From ProjectCustomFieldValues) H) M Left Join (Select CustFiled.CustomFieldName,CustValue.CustomFieldValue,CustValue.RecordId From ProjectCustomFieldValues CustValue Left Join ProjectCustomFields CustFiled On CustValue.CustomFieldId=CustFiled.CustomFieldId Where CustValue.AuctionId=1 ) N On M.CustomFieldName=N.CustomFieldName And M.RecordId=N.RecordId But I got the result below #CustomFieldName# CustomFieldValue RecordId User Name kaliya 1 City Bangalore 1 Email [email protected] 1 Salary NULL **NULL** User Name Yohesh 2 City Delhi 2 Email 2 Salary NULL **NULL** User Name sss 3 City Delhi 3 Email [email protected] 3 Salary NULL **NULL** User Name NULL **NULL** City NULL **NULL** Email NULL **NULL** Salary NULL **NULL** User Name Sunil 4 City Mumbai 4 Email [email protected] 4 Salary 20000 4 But Expected Result is CustomFieldName CustomFieldValue RecordId User Name kaliya 1 City Bangalore 1 Email [email protected] 1 Salary NULL **1** User Name Yohesh 2 City Delhi 2 Email 2 Salary NULL **2** User Name sss 3 City Delhi 3 Email [email protected] 3 Salary NULL **3** User Name Sunil 4 City Mumbai 4 Email [email protected] 4 Salary 20000 4 Please guide me some one,I tried so much but i got null value in recordId,So I need same recordId above one..

  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

• 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

• 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

• 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

• 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

• 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

• 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

• How do I reorder/clear the connection history in SQL Management Studio 2008?

  - by Chris

  I connect to a lot of different servers in SQL Management Studio, but I find that not only is there no way to delete the old connections (dbnames, usernames, etc.), they also start to show up as duplicates. Is there any way to clear them/reorder the history? I can't seem to find any easy way. Thanks! Chris

  Read the article

• full text search on multiple fields in diferrent tables

  - by crisgomez

  Hi, I want to create a a full text search usng ms sql 2005. here is the structure of my table User-contains fields of Id, fname,lname,email,alternativeemail Attachment-contains fields of id, name,category,filenameorurl Certification-contains of fileds of id, title,school,sdate,edate EducationalBackground-contain of fields of, id,qualification,sdate,edate,school,fieldofstudy EmploymentDetails-contain of fields position, positionlevelid,specializationid,description now the relationship of user to the other table is one to many relationship. now how can I create an effective query to have quick full text search and return the values of the different tables?any help please

  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

• Why decimal behave differently?

  - by haansi

  Hello, I am doing this small exercise. declare @No decimal(38,5); set @No=12345678910111213.14151; select @No*1000/1000,@No/1000*1000,@No; Results are: 12345678910111213.141510 12345678910111213.141000 12345678910111213.14151 Why are the results of first 2 selects different when mathematically it should be same?

  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

• 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

• How to improve performance of non-scalar aggregations on denormalized tables

  - by The Lazy DBA

  Suppose we have a denormalized table with about 80 columns, and grows at the rate of ~10 million rows (about 5GB) per month. We currently have 3 1/2 years of data (~400M rows, ~200GB). We create a clustered index to best suit retrieving data from the table on the following columns that serve as our primary key... [FileDate] ASC, [Region] ASC, [KeyValue1] ASC, [KeyValue2] ASC ... because when we query the table, we always have the entire primary key. So these queries always result in clustered index seeks and are therefore very fast, and fragmentation is kept to a minimum. However, we do have a situation where we want to get the most recent FileDate for every Region, typically for reports, i.e. SELECT [Region] , MAX([FileDate]) AS [FileDate] FROM HugeTable GROUP BY [Region] The "best" solution I can come up to this is to create a non-clustered index on Region. Although it means an additional insert on the table during loads, the hit isn't minimal (we load 4 times per day, so fewer than 100,000 additional index inserts per load). Since the table is also partitioned by FileDate, results to our query come back quickly enough (200ms or so), and that result set is cached until the next load. However I'm guessing that someone with more data warehousing experience might have a solution that's more optimal, as this, for some reason, doesn't "feel right".

  Read the article

• nested insert exec work around

  - by stackoverflowuser

  i have 2 stored procedures usp_SP1 and usp_SP2. Both of them make use of insert into #tt exec sp_somesp. I wanted to created a 3rd stored procedure which will decide which stored proc to call. something like create proc usp_Decision ( @value int ) as begin if (@value = 1) exec usp_SP1 -- this proc already has insert into #tt exec usp_somestoredproc else exec usp_SP2 -- this proc too has insert into #tt exec usp_somestoredproc end Later realized I needed some structure defined for the return value from usp_Decision so that i can populate the SSRS dataset field. So here is what i tried: within usp_Decision created a temp table and tried to do "insert into #tt exec usp_SP1". Didn't work out. error "insert exec cannot be nested" within usp_Decision tried passing table variable to each of the stored proc and update the table within the stored procs and do "select * from ". That didnt work out as well. Table variable passed as parameter cannot be modified within the stored proc. Pls. suggest what can de done?

  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

< Previous Page | 313 314 315 316 317 318 319 320 321 322 323 324  | Next Page >