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
Is it possible to do that?
View 8 Replies
ADVERTISEMENT
Jul 23, 2005
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
View 6 Replies
View Related
May 3, 2015
ID A B C AVG
------------------------
1 08 09 10 -
------------------------
2 10 25 26 -
------------------------
3 09 15 16 -
------------------------
I want to calculate the average of the larges two number from the column A,B & C for particular identity and store that average in the AVG column....
View 9 Replies
View Related
Aug 25, 2015
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
View 1 Replies
View Related
Jul 6, 2015
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?
View 5 Replies
View Related
Dec 23, 2013
We have two tables with names X and Y.
X has a,b columns. And Y has c,d columns.
I want to update b column in X table with the values from d column in Y table on condition X.a=Y.c.
View 3 Replies
View Related
Aug 24, 2015
I am new in SQL Server, What to check ,what action do i need to take while adding a new column to a table.
View 5 Replies
View Related
Sep 18, 2015
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;
View 4 Replies
View Related
Apr 21, 2008
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?
Thanks in advance
View 1 Replies
View Related
Feb 24, 2015
I have the following 2 Query's - case when Table has no Identity Column and other with identity Column . I am planning to make it to single Query .
Query 1:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE object_id =
(
SELECT sys.objects.object_id
FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing1' AND sys.schemas.Name ='dbo'
)
Query2:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE is_identity != 1 AND object_id =
(SELECT sys.objects.object_id FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing2' AND sys.schemas.Name ='dbo'
)
View 8 Replies
View Related
Aug 1, 2015
DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)
[Code]....
i want to select the data priority wise
the o/p should look like below
first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22'
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'
View 1 Replies
View Related
Aug 1, 2015
I have a table with following rows.
FY REVCODE Jul Jun
2015 BNQ 1054839 2000000
2015 FNB 89032 1000000
2015 RS 1067299 3000000
I am looking to convert it to
Month BNQ FNB RS
JUL 1054839 89032 1067299
JUN 2000000 1000000 3000000
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
Results:
MONTHBNQ FNB RS
Jul11054839 89032 1067299
View 4 Replies
View Related
May 2, 2008
Hi there
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.
Martyn Fewtrell
View 19 Replies
View Related
Jun 10, 2014
I have created a stored procedure for retrieving column name, as shown below
CM_id, CM_Name, [Transaction_Month], [Transaction_Year], [Invoice raised date],[Payment Received date],[Payout date],[Payroll lock date]
Now I am trying to create a temporary table using the above generated coluimns from Stored Procedure with datatype.
View 3 Replies
View Related
Jul 31, 2015
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.
View 5 Replies
View Related
Apr 7, 2004
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
PageNum ItemNum ItemDescription
----------- ----------- -------------------
1 1 aaaaaaa
1 2 bbbbbbb
1 3 cccccccc
2 1 ddddddd
2 2 eeeeeee
2 3 ffffffffffff
3 1 ggggggg
3 2 hhhhhhh
3 3 iiiiiiiiiiiiiiii
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??
Thanks
Greg
View 3 Replies
View Related
Apr 14, 2015
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.
AB¯ALBERTA ¯93¯AI
AI¯ALBERTA INDIRECT ¯94¯AI
AL¯ALABAMA ¯30¯
[Code]....
View 2 Replies
View Related
Oct 1, 2014
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
View 9 Replies
View Related
May 2, 2001
If there are gaps ( due to deletions ) in indentity column, how do I
resequence the values ? As well as how to reset the last used number ?
Info. is greatly appreciated.
Ivan
View 1 Replies
View Related
May 1, 2007
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 ?
View 3 Replies
View Related
May 1, 2007
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 ?
View 2 Replies
View Related
Feb 28, 2008
Hi,
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.
Thanks for an feedback...
View 5 Replies
View Related
Apr 2, 2008
I have a temp tbl and in col1 row1 i would like to have 1, col1 row2 value 2, col1 row3 value 3 and so on. how can i do this?
View 3 Replies
View Related
Mar 11, 2008
My SQL table has the
following columns:
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…
For Example:
Account_Number = 12345 New_Num(ber_Of Records) = 3
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!
Thanks
View 2 Replies
View Related
Feb 16, 2006
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)?
View 1 Replies
View Related
Aug 6, 2014
I'm trying to do the following and haven't been able to figure it out.
Say there's a table with these records:
Col1 Col2 Col3
a b c
a b c
a b d
e f g
e f g
I want to generate a number that represents the groups of columns like this:
Col1 Col2 Col3 MyNumber
a b c 1
a b c 1
a b d 2
e f g 3
e f g 3
So that each grouping gets its own identifier. I've tried this:
SELECT Col1, Col2, Col3
row_number() OVER (PARTITION BY Col1, Col2, Col3
ORDER BY Col1, Col2, Col3) AS MyNumber
FROM MyTable
But I get this:
Col1 Col2 Col3 MyNumber
a b c 1
a b c 2
a b d 1
e f g 1
e f g 2
View 9 Replies
View Related
Dec 14, 2014
I got this ERD and I have 2 questions about it:
What is the minimum number of times that R2 can appear in the connection? and what is the maximum?
A=5
B=5
C1=1
C2=2
View 1 Replies
View Related
Dec 19, 2014
Sample Data
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE CheckRegistry (
CheckNumber smallint,
[code]...
How can I get the result orderd by the month number?
View 3 Replies
View Related
Oct 3, 2015
I have a function which generate random number in range :
CREATE FUNCTION Func_Rand
(
@MAX BIGINT ,
@UNIQID UNIQUEIDENTIFIER
)
RETURNS BIGINT
AS
BEGIN
RETURN (ABS(CHECKSUM(@UNIQID)) % @MAX)+1
END
GO
If you run this script you always get result between 1 and 4
SELECT dbo.Func_Rand(4, NEWID())
BUT, in this script sometimes have no result !!!!!!! why??
SELECT 'aa'
WHERE dbo.Func_Rand(4, NEWID()) IN ( 1, 2, 3, 4 )
View 2 Replies
View Related
Oct 7, 2015
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.
View 6 Replies
View Related
Jun 6, 2005
Hello,
I am receiving the following error:
Column name or number of supplied values does not match table definition
I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:
SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp
INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @Gender = 'Male', @Culture = 'en-GB'
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.
Thanks,
Greg.
View 2 Replies
View Related
Mar 19, 2013
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:
Customer_Name
Account_number
First_insert_date
Order_net_price
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.
View 9 Replies
View Related
Nov 25, 2013
create table #temp_Alpha_num (
[uniquenum] [int] Not NULL,
[Somenum] [int] Not NULL,
)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values
(1,121)
[Code] ....
For the somenum column I need to add alphabets to make them unique. for example
121a,121b,121c....121z,121aa,121ab,101a and so on...
View 4 Replies
View Related
