SQL Server 2014 :: Incrementing Number In Table And Then Resetting Column
Jul 15, 2015
I have four columns in my table, the first one is the identity column
col1 Col1 col2 col3
1 12 1 This is Test1
2 12 2 This is Test1
3 12 3 This is Test3
4 12 4 This is Test4
5 12 5 @@@@@
When, I see, @@@ sign in my col4, I need to restart the col3 from 1 again so it will look like this
col1 Col2 col3 col4
1 12 1 This is Test1
2 12 2 This is Test1
3 12 3 This is Test3
4 12 4 This is Test4
5 12 5 @@@@@
6 12 1 This is another test1
7 12 2 This is another Test2
Sorry to bother you guys with what I though would be an easy task. Ihave a table in my database were I would like one of the rows toincrement a number for each row. I want the first row to start at 1000and keep on incrementing by 1 'till the end of the rows (about 2.7million rows). I though this would be a piece of cake, but I just can'tseem to find anything like it on the internet...weird.Anyways, I'm just a rookie in sql, any help would be appreciatedThanksJMT
There are 3 columns in the result set - part num, Qty and MO num. Each MO num has part numbers.So there might be same part numbers in MO's. Each part num has qty. So, if I group by part num, I get Qty.
1.There are duplicates of part.num and I want to remove duplicates and add quantities of those duplicates into one single quantity. For example, xxxx is a part num, then xxxx=1,xxxx=3,xxxx=5. I want xxxx=9. I want to sum those. Another question is, each MO has a user. I want to join user and MO num in MO.
Heres the code,
part.num , (woitem.qtytarget/wo.qtytarget) AS woitemqty, (SELECT LIST(wo.num, ',') FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id WHERE moitem.moid = mo.id) AS wonums FROM mo INNER JOIN moitem ON mo.id = moitem.moid LEFT JOIN wo ON moitem.id = wo.moitemid LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10 LEFT JOIN (Select sum(woitem.qtytarget) as labor, woitem.woid, uom.code as uom from woitem JOIN part on woitem.partid = part.id and part.typeid = 21 JOIN uom on woitem.uomid = uom.id group by 2,3) as labor on wo.id = labor.woid LEFT JOIN part ON woitem.partid = part.id
I have a CTE query against a table with 32K rows that runs fine in 2008R2. I am running it in 2014 Std Ed. against the same data and it runs very slowly. Looking at the execution plan I think I see what's contributing to the slowness.
Note that the "actual number of rows" is some 351M...how is this possible?
the query:
declare @amts table (claim int,allowed decimal(12,2),copay decimal(12,2),deductible decimal(12,2),coins decimal(12,2)); ;with unpaid (claimID) as (select claimID from claim where amt+copay + disct+mm + ded=0) insert @amts select lineID, sum(rc), sum(copay), sum(deduct), case when sum(mm)>0 and (sum(mm)<sum(mmamt)) then sum(mm) else 0 end from claimln where status is null and lineID not in (select claimID from unpaid) group by lineID
it's like there's some massively recursive process going on?
I have a table with some rows and columns what i want is i want to Show sum of particular column in the last row. This is my code.
SELECT DISTINCT Cluster.ClusterName, Gruppe.GruppeName, Arbeitspaket.ArbeitspaketName, BMWProjekt, AnzahlAP, Abgerechnet, InBearbeitung, Billanz FROM Bestellung INNER JOIN Cluster ON Bestellung.Cluster = Cluster.rowid INNER JOIN Arbeitspaket ON Bestellung.Arbeitspaket = Arbeitspaket.rowid INNER JOIN Gruppe ON Bestellung.Gruppe = Gruppe.rowid WHERE Projekt ="EA-284-Nxx" AND AnzahlAP <> 0 AND Abgerechnet is 1 AND InBearbeitung is NULL AND Billanz is NULL;
Hi, I have a report where i do a page break for each supplier. I want to reset my page number to '1' when there is a group break.When i googled for this functionality i found the following chris Hay's blog which provides the solution. http://blogs.msdn.com/chrishays/archive/2006/01/05/ResetPageNumberOnGroup.aspx But if i follow the same, I get #Error instead of page number during the run time.How do i resolve the error? Also, I want to display the "page 1 of totalpages in group" like that. Is that possible?
I tried with the following and result is coming for one month i.e. JUL but not with the second Month i.e Jun
SELECT 'Jul1' AS MON, [BNQ], [FNB], [RS] FROM (SELECT REVENUECODE, SUM(ROUND(((Jul/31)*30),0)) AS JUL FROM RM_USERBUDGETTBL WHERE USERNAME='rahul' AND FY=2015 GROUP BY REVENUECODE, USERNAME ) AS SourceTable PIVOT (SUM(JUL) FOR REVENUECODE IN ([BNQ], [FNB], [RS])) AS PivotTable
I need a bit of generic advice about a project I am currently working on which is slightly out of my comfort zone. I€™m using SQL 2005 and VB.Net to develop an application that produces invoices for a number of different countries. The issue is that the data is arriving from an AS400 system so the tables I am working from are a little strange! This results in a fair degree of work tweaking the data into a format that is useful for my application.
To simplify the explanation each invoice in its basic form consists of the invoice data and the line items (so there is nothing new here). What is unusual is that the invoice data has an invoice number but then an additional invoice number specific to the country it is destined for. The initial invoice number arrives with the data from the AS400 but the secondary number is generated according to a number of business rules.
I have written an update query in a stored procedure which updates the invoice data with a number of values from a secondary table and a number of calculated values. There are several functions, one in particular which looks up the new invoice number from a secondary table based on specific criteria (country of destination being one of them) and I had intended to write an update query, called from within this function which then increments the invoice number but I have since found out that you cant do an update/delete from a function?
I can€™t add an update query to increment the value after the initial update query as this results in the stored procedure running through and adding all the invoice numbers (which are the same) and then the number being incremented at the end.
I assume I can€™t call an update query from within the initial update query so that the increment takes place after each loop of the initial update (if I can what would the syntax be?).
I have tried writing a trigger which completes the increment when the invoice data is updated. I haven€™t used triggers before but once again it appears as if the first update query is running through and completing all the updates and then the trigger fires incrementing the number. Or have I just got this wrong?
I guess what I need is a trigger which fires after each row of the table is updated which was how I assumed it should work!
Ironically I could write this in VB.Net with ease but for speed we need it within the database.
What is the best way to increment my invoice number?
I€™m sorry of this is general in nature but im really looking for some advice as to the best approach to deal with this issue.
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT) INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT) INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H SET CHANNEL1= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=1 -- updates only channel1 UPDATE H SET CHANNEL2= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=2 -- updates only channel2 SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
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 am having one store procedure which use to load data from flat file to staging table dynamically. everything is working fine.Staging_temp table have single column.All the data stored in that single column below is the sample Data.
I need to auto increment an alphanumeric field in a table.
The process is as follows:
1. First position is static letter C for contact or static letter A for account - field contact_id 2. The following 6 positions are numeric - example of the string would be C004658 3. When new contact is entered, it should look up the highest numeric value (in this case 004658) and increment it by one - resulting in C004659
I used a lot of fictitious data in order to test the code of my web site. But I noticed that when I deleted all fictitious data from the tables, the primary key columns keep creating automatically numbers that are above the last one before all data was erased instead of starting again from number 1. How do I reset this automatic numeration in order to restart from 1 again ?
I used a lot of fictitious data in order to test the code of my web site. But I noticed that when I deleted all fictitious data from the tables, the primary key columns keep creating automatically numbers that are above the last one before all data was erased instead of starting again from number 1. How do I reset this automatic numeration in order to restart from 1 again ?
Im not sure if this can/should be done through sql...but i have a sql statement and i need the first column returned to start at 1 - and then increment for each row.
I am then using the returned values in another app.
I tried using count(columnname) but this just stays at 1 for each row.
ID – Auto IncrementingNumber_of_Records – IntAccount_Number – Varchar (Format 1234)Account_Number_Instance – Varchar (Format e.g. 1234-01)Other_Field1Other_Field2…etc.
When the Account_Number was initially inserted into the table, a Stored procedure (not written be me) was used to concatenate the Account_Number and Number_of_Records. As a result the Account_Number_Instance became, e.g. 1234-01, etc.
From the ASPX form we retrieve each of the fields above, and based on the Number_of_Records an appropriate number of records were inserted for the Account_Number.
For Example:
Account_Number = 12345Number_of_Records = 4
The result inserted into the table were as follows:
ID # of Records Acct_Number New_Num Acct_Number_Instance …
1 4 12345 12345-01
2 4 12345 12345-02
3 4 12345 12345-03
4 4 12345 12345-04
Now, I would like to UPDATE this table, and based on the original Number_of_ Records (4) I want to ADD, for Account_Number (1234) add an appropriate Number_of_Instances, beginning with the next incremental number and adding what the New Number of Records input is…
ID # of Records Acct_Number New_Num Acct_Number_Instance …
1 4 12345 12345-01
2 4 12345 12345-02
3 4 12345 12345-03
4 4 12345 12345-04
5 12345 3 12345-05
6 12345 3 12345-06
7 12345 3 12345-07
I do realize that the next time I have to update this Account_Number I am also going to have to somehow ADD only one of the #_of_Records and New_Num for the Acct_Number instances to obtain the next starting number, which in the example above would be (8)…
Any and all suggestions on how to accomplish this would be greatly appreciated!
Hi, I have an auto incrementing int column setup which serves as my unique primary key. Just wondering what happens when the auto increment reaches the limit? Will it recycle numbers from the begining (who's rows have obviously been deleted by this stage)?
I have heard that high numbers of VLF's aren't good. It can impact performance and can delay recovery time, so I wanted to test that.
I created 2 DBs with 100MB datafile and 50MB logfile.
TestDB log file had 100MB autogrowth TestDB2 log file had 1% growth.
I inserted 1048576 records, took the backup
Ran DBCC loginfo and TestDB had 40 VLFs and TestDB2 had 165 VLFs
But when I restored both DBs, this is what I got.
TestDB: RESTORE DATABASE successfully processed 42258 pages in 4.420 seconds (74.691 MB/sec). SQL Server Execution times: CPU Time = 125ms, elapsed time = 8323 ms.
TestDB2: RESTORE DATABASE successfully processed 42257 pages in 3.943 seconds (83.724 MB/sec). SQL Server Execution Times: CPU time = 109 ms, elapsed time = 8314 ms.
Question is: Where is the difference? How TestDB which has 40 VLFs are better than TestDB22 which has 165 VLFs.
The SP UserPersist_GetByCriteria does a "SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture", so why am I receiving this error when both tables have the same structure?
The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.
I'm using SQL Server 2000 sp2...I have created a view that gives me customer info from which I need to create a view and or table that gives me a 24 monthly columns of the sum of each account_number monthly revenues (going back 24 months from this month)..The columns I'm pulling from are these:
Here's what I have so far: ---------------------------- SELECT TOP 100 PERCENT Account_number, Customer_Name, SUM(Order_net_price) AS 'CM - 24' FROM dbo.Customer_Feed WHERE (First_insert_date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 24, 0)) AND (First_insert_date < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 23, 0)) GROUP BY Account_number, Customer_Name ORDER BY Account_number
I've basically hacked out a way to get the monthly totals for each account. What I would like to do is so be able to repeat the query but increment the date range 1 month until reaching the present or last FULL month and display these sums in individual columns named CM - n (where CM means current month and 'n' is the # of months back from current.how to make this query run over again the 23 other times I need it to and display those months sum totals in individual columns.
