March 9, 2014 at 1:55 am
Hi
I am developing asp.net application
with sql server database
I have table FinancialYear
caseno(unique) finyear
1 2013-2014
2 2013-2014
3 2013-2014
1 2012-2013
2 2012-2013
3 2012-2013
For financial year-2013 to 2014. if i insert record ( select max(caseno) +1).
insert into finanancialYear values(max(caseno) +1,2013-2014)
if i delete record caseno:3 from the table,
select max(caseno)+1 will be 3
which is already deleted and again that record with caseno 3 will be inserted
How to solve the issue.
for every financial year in 'FinancialYear' table,.caseno will be reset to 1
and if i add new record in finyear 2012-1013
then max(caseno) will be last value of caseno of that financial year 2012-2013
Again insert becomes
insert into finanancialYear values(max(caseno) +1,2012-2013)
i have given the data .Please help me.
Thanks Chandran.
March 10, 2014 at 1:52 am
sorry, what is the issue, again :unsure:?
Please provide statements to create sample set and what exactly is expected ?
March 10, 2014 at 1:59 am
You say caseno is unique, but in your sample data it certainly is not.
Either use an IDENTITY constraint on the columns, or use the SEQUENCE object (since you posted this in a 2012 forum, I can assume you are working with SQL Server 2012).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 10, 2014 at 2:08 am
Boy donot use Max(ID) +1, you will get into a very big and stinky hole if your application run in a multi-user environment. trust me on this one,
just think of it of a data entry user who just entered a complete data in 15 to 20 min after digging hard from the paper he have in hand and faced Primary Key violation error on the screen. 😉
March 10, 2014 at 7:34 am
twin.devil (3/10/2014)
Boy donot use Max(ID) +1, you will get into a very big and stinky hole if your application run in a multi-user environment. trust me on this one,just think of it of a data entry user who just entered a complete data in 15 to 20 min after digging hard from the paper he have in hand and faced Primary Key violation error on the screen. 😉
+1000
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 10, 2014 at 7:37 am
Don't get stuck in the trap of thinking your caseno needs to be sequential with no gaps. I would do as Koen suggested and either use an identity or a sequence. Gaps in a primary key are perfectly normal.
To be honest, you should try to avoid reusing primary keys for this type of thing anyway. You have case numbers and financial years. This sounds like some sort of financial application. What happens when a customer calls in with case #3 and the current case 3 is not theirs?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply