The tricky part is resolving the many to many issue. A value in ColA
can belong to multiple values in ColB and a value in ColB can have
multiple values in ColA.
We are integrating all our applications/databases into one application/database. During the transition phase, I need to create a number of views based on the new database that mimic the old tables of the old databases, so the old programs can continue to function until they are gradually replaced.
In one of the views, I need to generate a sequential number. The value is unimportant, as long as it is unique in the dataset; strictly spoken, it even doesn't need to be sequential:
eg: SELECT * FROM myView
should give
Code: col1col2...id lalacar..1 bababike..2 .... zsrdpen..896 ghrtink..897 SELECT * FROM myView ORDER BY col2
this is a slight change to a fequently asked question around here. Ihave a table which contains a "sortorder" column where a user canspecify some arbitrary order for records to be displayed in. Usershave sometimes ordered records the way we used to number lines in aBASIC program (10,20,30, etc.). I'd like to do an update query and fixthis so that every record is in sequential order. I found an examplein this newsgroup of how to do this.However, I have a slight problem! Sometimes, the users duplicated thesortorders. So for example, I might have two records were thesortorder is 20. The query I found in this newsgroup does not work inthat case. Here is some code so that you can see what I mean.create table sorttest (label char(5),sortorder int)goinsert sorttest values ('joe',20)insert sorttest values ('dan',10)insert sorttest values ('jim',44)insert sorttest values ('tom',20)insert sorttest values ('jan',50)-- data dumpselect label, sortorder from sorttest order by sortorder-- I'd like to fix all of the sortorder fields so that they aresequentialupdate sorttestset sortorder = (select count(*)from sorttest subquerywhere sorttest.sortorder <= subquery.sortorder)-- note that tom and joe BOTH HAVE SORTORDER = 4select label, sortorder from sorttest order by sortorderdrop table sorttestThanks in advance for any help.
Hello, I have a table where I'm deleting the contents before populating the table with new data. I have an ID column that is autogenerating a sequential number. I would like to reset this number back to 1 when I delete the contents of the table. How can this be accomplished?
I have a stored procedure that supplies rows for a front-end DataGrid that allows custom paging. The stored procedure must return the requested "page" of rows as identified by a sproc argument. Currently, I'm loading the the query's result set into a temporary table that has an identity column as primary key. I then run a second query against the temp table that uses the identity column value to strip out the requested "page" of rows and return them to the front-end DataGrid.
I'd like to eliminate the temporary table. To do so I would need to create the equivalent of an identity column in the query's sorted results and reference this value in the WHERE clause in order to return only the requested rows.
Does anyone know of a way to generate a sequential number (starting at 1) within a query (and after the rows have been sorted by the ORDER BY)? I don't think this can be done but I put it out for those who may know better.
Thanks for your help. If you know this is impossible, that would be helpful feedback as well.
Po Table is having a datatime field called "ShipDate", OrderNo and a field called PoNo (which is having number of PO).
PoDet is child table having PoNo and PoQty
Suppose following records are in both tables
Po:-
OrderNo : 1 PoNo :- Po No 1 ShipDate :- Oct 5, 2007
OrderNo : 1 PoNo :- Po No 2 ShipDate :- Dec 5, 2007
OrderNo : 1 PoNo :- Po No 3 ShipDate :- Oct 5, 2007
PoDet:
PoNo :- Po No 1 PoQty :- 2000
PoNo :- Po No 2 PoQty :- 3000
PoNo :- Po No 3 PoQty :- 4000
I want to generate a Delivery No. which will be generated in this way :-
1. Earlier Shipdates should be assigned a Lower Number 2. If Shipdates are same, like in case of PoNo 1 and PoNo3, the higher Qty will be assigned a lower number
So meeting the above two conditions, a single SQL should return
PoNo :- Po No 3 DelNo : 1 (As Dates are same for PoNo1 and PoNo3 but PoNo3 Qty is higher, so this will come first)
How to get the result similar to Ex2, instead of Ex1. (ie., case-number is in sequential order then no need to break), And it should suit large dataset, I will finetune, if any performance issue.
Ok I have upgraded my works database from a poorly designed Access database to a SQL database. The previous system allowed NULL values and duplicates to be inserted into a field that should NOT ALLOW NULL Values or duplicates. Therefore, this issue has now been moved across to my new system as I cannot set these constraints on the field that has multiple NULL values.
My solution would be to use a sequential operator, so whatever = NULL would be changed to a sequential number that us as administrators would know was a bogus number starting at something like = 999999900 counting up from that. There are only 250 records that would require updating.
To make things more interesting this field is not a integer type, its a Nvarchar type as its a Hardware ID. Both numerical and characters are require.
Hello! Got a problem I hope some clever people can help me out with..
I have a web form that displays a set of records in a grid. The grid is "paged" according to a PageNum column, with a dropdown box to change pages and buttons allowing the items to be moved up or down a list within a page or moved between pages. So the backend table (simplified) looks something like this
The problem is when I want to delete a page - I need the page numbers to automatically resequence themselves, so for example, If I delete "Page 2" (i.e. delete rows where PageNum = 2), all items on "Page 3" become "Page 2" (and any items on "Page 4" become "Page 3" etc).
This has proved straightforward to when deleting an item from a particular page, and can resequence ItemNum thanks to a clever bit of code found on SQLteam.com:
DECLARE @intCounter int SET @intCounter = 0 UPDATE <Item Table> @intCounter = ItemNum = @intCounter + 1 WHERE Pagenumber = <Currently Selected Page>
However I haven't been able to adapt this to resequence the Page number, as this involves resequencing blocks of numbers. The closest I can get is:
DECLARE @intCounter int SET @intCounter = 1 UPDATE <Itemtable> SET @intCounter = PageNum = CASE WHEN @intCounter = PageNum - 1 THEN @intCounter + 1 WHEN @intCounter = PageNum - 2 THEN @intCounter + 1 ELSE @intCounter END
But this doesn't quite work. Anyone got any other ideas??
I have a table that holds notes for item's. I'm want to do a select statement where one of my columns assigns a sequential value to each row based on the item number. Would like data to look like this where doc_no would be my row_number function:
item_no seq_no note doc_no ABC 1 blah 1 ABC 2 blahh 1 ABC 3 bla3 1 XYZ 1 more n 2 XYZ 2 another 2 EFG 1 blahhh 3
How to identify different fields with in a group of records?
Example: create table #test (ID int, Text varchar(10)) insert into #test select 1, 'ab' union all select 1, 'ab'
[Code] ...
I want to show additional field as Matched as ID 1 has same Text field on both the records, and for the ID 2 I want to show Unmatched as the Text fields are different but with the same ID.
I am in middle of my transformation where I have to assign records equally among 3 different groups. I can do that in SQL using NTILE() Over() function. How do I do that in SSIS package. I have applied different business rules during transformation to get unique records and now I have to assign those records to 3 group in and generate excel report.Basically, I will need to have another column which will have those group numbers.
I have a report where certain columns have values that get repeated, but the client has a rigid requirement for not wanting these columns as groups in the reports (other programs and exports doing specific tasks with those values). In these reports, the "hide duplicates" value is checked, so as to give an aesthetic sense of grouping.
The data for the report, in raw form is:
Value 1 A
Value 1 B
Value 1 C
Value 2 D
Value 2 E
Value 2 F
Value 3 G
Value 3 Hetc...
In its aesthetic form, it is:
Value 1 A
B
C
Value 2 D
E
F
Value 3 G
H
My question is, how can I use RunningValue() to give me the following row numbers based upon those repeated values acting as grouping, since I'm not using formal grouping in the report design?
I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.
I want to group the records on the time difference
declare @tbl as table(id int,intid int,val int,dt datetime) insert into @tbl select 1,1,10,'03/31/2006 15:05:22' union all select 2,1,12,'03/31/2006 15:10:22' union all select 3,1,15,'03/31/2006 15:15:22' union all select 4,1,12,'03/31/2006 15:25:22' union all select 5,1,8,'03/31/2006 15:30:22' union all select 6,1,6,'03/31/2006 15:35:22' union all select 7,1,4,'03/31/2006 15:40:22' union all select 8,1,3,'03/31/2006 15:45:22' union all select 9,1,10,'03/31/2006 15:50:22'
declare @tbl1 as table(intid int,Tm int,val int) insert into @tbl1 select 1,5,10
I want a output such that when the val in @tbl goes below the val in @tbl1 for the Tm mentioned in @tbl1 then the time difference should be shown.For example record 1 it starts with 10 the records remain more than 10 till record number 5.From 5 the records remains lower than 10 till record number 9.So I need to show the the time difference from record number 5 till 9. But there is a catch.In @tbl1 there is column named Tm.The time difference sould be calculated only if the diff more than Tm value in @tbl1. For example if the value of Tm is changed to say 25 then the there is no need to show the time difference since the time difference value from record 5 to record 9 is less than 25. Hope I am clear.
All,Given multiple records with identical values in all fields except asingle varchar field, is there an efficient query that will group therecords into a single record and concatenate the aforementionedvarchar field into a single field with each of the source records'values separated by commas?Example:Record 1 'Doug' , '1'Record 2 'Doug' , '2'Output record 'Doug' , '1,2'Thanks in advance,Doug
I have a report that requires SubTotals for each page.
To do this successfully, I had to introduce the following grouping:
Code Snippet=Int((RowNumber(Nothing)-1)/5
This groups every 5 records.
Unfortunately, the length of data in my records is not uniform. For example, certain records have long(over 4 lines) comment field while others are short(1 line).
This means that even though I have an artificial grouping, the group may still be split up over two or more pages.
Is there a way to Group records PER PAGE dynamically?
I have results from a query that have anywhere from 1-4 results. I have a subid of 1-4 which is grouped by a certain criteria. so for example...
id subid text 1 processing A records 2 1000 records processing 3 importing A records 4 1000 records processed 1 processing B records 2 500 records processing 3 importing B records 4 1000 records processed
Here is what my desired output is giving each group of subids 1-4 an id to be grouped together.
id subid text 1 1 processing A records 1 2 1000 records processing 1 3 importing A records 1 4 1000 records processed 2 1 processing B records 2 2 500 records processing 2 3 importing B records 2 4 1000 records processed
I have tried to process > 3 million Fuzzy grouping records on two different servers with no success. 3 mill works but anything above 4 mill doesn't. Some background:
We are trying to de-dup our customer table on: name (.5 min), address1 (.5 min), city (.5 min), state (exact). .8 overall record min score. Output includes additional fields: customerid, sourceid, address2, country, phonenumber Without SP1 installed I couldn't even get a few hundred thousand records to process Two different servers - same problems. Note that SSIS and SQL Server are running locally on both The higher end server has 4GB RAM, the other 2.5 GB RAM. Plenty of free disk space on both SQL Server is configured to use 2 GB of RAM max The page file is currently at 15GB
After running a number of test on both servers trying different batch sizes etc. the one thing I noticed is that it seems to always error out when SSIS takes over and starts chewing up all the available RAM. This happens after the index is created and SSIS starts "warming caches". On both servers SQL Server uses up about 1.6GB of RAM at this point while SSIS keeps taking over RAM until all physical RAM is used up.
Some questions:
Has anyone been able to process more then 3 million records and if so what is your hardware configuration? Should we try running SSIS from a different server so it has access to the full amount of physical RAM? (so it doesn't have to fight for RAM with SQL Server) Should we install Win 2003 Enterprise Server so we can add more RAM? Any ideas why switching to the page file might be causing errors?
I tried to ask a similar question yesterday and got shot down, so I'll try again in a different way. I have been looking online at the gaps and islands approach, and it seems to always be referencing a singular field, so i can't find anything which is clear to get my head around it.In the context of a hotel (people checking in and out) I would like to identify how long someone has been staying at the hotel (The Island?) regardless if they checked out and back in the following day.
Data example: DECLARE @LengthOfStay TABLE ( PersonVARCHAR(8) NOT NULL, CheckInDATE NOT NULL, CheckOutDATE NULL
HI EVERYBODY This is my procedure " CREATE PROCEDURE SP_SAMPLE_SEARCH @Title nvarchar(256) AS
SELECT ID,Title,Price FROM [tbl_Sim] WHERE ([Title] LIKE '%' + @Title + '%') Order by Price desc GO " I exec procedure and it returns 12 results with diffirents ID and want to get these values 1,2,3....12 How do I get these... I am a beginner. Thanks for help..
Okay..i have this problem ...i am using SQL server 2005 standard ,C#,VS2005 --i am inserting some record in DB .. using ExecuteNonQuery...i want to know how many records are getting inserted..so in my DB class i did something like this : numRecords = commandObject.ExecuteNonQuery() ,assuming that ExecuteNonQuery returns the number of affected records.i am retriving this numOfRecords in my code behind and printing it but it always prints 1,even though more then one records are inserted.What is wrong here? -i also have returnValue defiend like this.Could this tell me anything about how many records are inserted or affected during update,select ?if so,how? cmd.Parameters.Add(new SqlParameter("@returnVal", SqlDbType.Int)); cmd.Parameters["@returnVal"].Direction = ParameterDirection.ReturnValue; Please help me out with this.Thanks
hi can anyone tell me how to count number of records(rows) in a table without using "COUNT" function.for practise iam trying to implement it through queries.
Hi... I just begining to use the asp.net 2.0 and have tow littel problems... in my project the user makes a "list of problems on a house" when hi post the house number the page shoud to generete a master recod with a keynumber, the date an hour and the state of problems... and a form to insert a list of problems... but if the house dosn´t exist this forms are hidden... then i need to know the nomber of records that gets a recordsource if its more than cero show the forms else hiden it... if show the forms i need to store in a variable the keynumber to store it in the problem details table... ¿How i can khow the number of records of a RecordSource gets?... and ¿How I can store in a variable a field value retrived for a RecordSource?... I´m using VWD and SQL Express that means ADO.NET 2.0... cheers.
I want find out Total number records in one table without using select statment. Some body as told to me there is system table you can find total number of records. Any body give me systable name. Thanks Jack
I want find out Total number records in one table without using select statment. Some body as told to me there is system table you can find total number of records. Any body give me systable name. Thanks Jack
My manager ask me to provide him with the total number of records which have been added, deleted or modified on a certain database in SQL Server 2000 during the month of September. Is there away to get that information from the transaction log or by any how?
