auto generate a sql column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

auto generate a sql column

Author

Topic

abuzar
Starting Member

20 Posts

Posted - 2013-12-10 : 15:38:27

will you plz help me out in one more thing
.
.
In a table named customers i have made 4 column (id int primary key not null,name varchar(20), surname(20),dates datetime not null default getdate())
create function CustomerNumber (@id int)
returns char(5)
as
begin
return 'C' + right('0000' + convert(varchar(10), @id), 4)
end
alter Customers add CustomerNumber varchar(10)
create trigger Customers_insert on Customers
after insert as
update
Customers
set
Customers.customerNumber = dbo.CustomerNumber(Customers.dbID)
from
Customers
inner join
inserted on Customers.dbID= inserted.dbID

id customernumber name surname date
1 c0001 abc a 2013-12-11 02:02:35.770
2 c0002 efg b 2013-12-11 02:02:35.770
3 c0003 hij xyz 2013-12-11 02:02:35.770
4 c0004 klm pqr 2013-12-11 02:02:35.770

suppose if the date changes from 11 to 12

id customernumber name surname date
5 c0001 jhon a 2013-12-12 02:02:35.770
6 c0002 lee b 2013-12-12 02:02:35.770
7 c0003 sam xyz 2013-12-12 02:02:35.770
8 c0004 jim pqr 2013-12-12 02:02:35.770

the customer number should again start from c0001 onwards

plz do help thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-11 : 05:28:23

you dont need udf for this. just add the logic inline in INSERT ..SELECT statement using rownumber
ie like


'C' + right('0000' + convert(varchar(10), row_number() over (partition by datediff(dd,0,[date]) order by id), 4)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

abuzar
Starting Member

20 Posts

Posted - 2013-12-11 : 06:06:49

thanks for your valuable reply
i will try this out

abuzar
Starting Member

20 Posts

Posted - 2013-12-11 : 06:21:53

create function CustomerNumber (@id int)
returns char(5)
as begin return 'C' + right('0000' + convert(varchar(10), row_number() over (partition by datediff(dd,0,[date]) order by id), 4) )
end

error

Msg 174, Level 15, State 1, Procedure CustomerNumber, Line 3
The right function requires 2 argument(s).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-11 : 12:37:44

quote:
Originally posted by abuzar

create function CustomerNumber (@id int)
returns char(5)
as begin return 'C' + right('0000' + convert(varchar(10), row_number() over (partition by datediff(dd,0,[date]) order by id), 4) )
end

error

Msg 174, Level 15, State 1, Procedure CustomerNumber, Line 3
The right function requires 2 argument(s).

I didnt ask you to put it in udf
I was asking to remove udf altogether and do it in the place where you do the INSERT..SELECT to insert the values to the table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

abuzar
Starting Member

20 Posts

Posted - 2013-12-11 : 14:59:18

ohh thanks once again :)))

abuzar
Starting Member

20 Posts

Posted - 2013-12-11 : 15:06:54

brother can you plz explain me in detail its a humble request if you can

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-11 : 16:19:01

It sounds to me like visakh16 is suggesting to you that you don't create a function, and don't create a trigger, and don't add a customerNumber column. Just generate a CustomerNumber whenever you SELECT out of the table. Like this:


select id
       ,customernumber = 'C' + right('0000' + convert(varchar(10), row_number() over (partition by datediff(dd,0,[date]) order by id), 4) 
       ,name
       ,surname
       ,dates
from   Customers

But if you do that then the CustomerNumber value for a given customer will change from one SELECT statement to another depending on what your WHERE clause is.

Do you need the CustomerNumber to "stick" so it never changes no matter what which rows you select AND you are sure you want to use the logic that you specified? That logic essentially makes the value random (depending on how many customers have been entered that day. In that case I would just use the last 4 digits of the ID as the incremental part. that way you can derive the CustomerNumber without having to query for any other rows that day.

Be One with the Optimizer
TG

abuzar
Starting Member

20 Posts

Posted - 2013-12-11 : 17:04:10

i want to make a column in the table like customernumber to count the how many customer visited per day, like 200 customers visited so c0200 in the textbox.
but if the dates changes it should again start from c0001.
this should happen when ever i use insert to save data in the given table.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-12 : 10:28:30

calculating that value at every insert and then storing it as a counter is really not a good design. So far based on everything you've told us the better solution is what Visakh16 recommended. Get the count per day only when you need to see the count per day.

use the query above to see the details by day and use this to count how many customer visited per day


--count how many customer visited per day

select dateadd(day, datediff(day, 0, dates), 0)
       ,count(*) as customerCountByDay
from   customers
group by dateadd(day, datediff(day, 0, dates), 0)

Be One with the Optimizer
TG

abuzar
Starting Member

20 Posts

Posted - 2013-12-14 : 00:49:09

thankyou you very much tg

abuzar
Starting Member

20 Posts

Posted - 2013-12-14 : 02:37:29

tg i would be very thank full to you if u will help me out.

in my table customer there is a column name passport number which is not null.
as we all know that passport numbers are unique.
i need a query that whenever i insert a passport number from front end it should not except the same passport number until 3 months and just give me a prompt for no already exist but it should save it.
plz do help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-14 : 07:30:11

quote:
Originally posted by abuzar

tg i would be very thank full to you if u will help me out.

in my table customer there is a column name passport number which is not null.
as we all know that passport numbers are unique.
i need a query that whenever i insert a passport number from front end it should not except the same passport number until 3 months and just give me a prompt for no already exist but it should save it.
plz do help.

you need to do the prompt part using front end application language. At the sql end just create a proc like below


CREATE PROC PassportNoSave
other values....,
@PassportNo
AS
IF EXISTS (SELECT 1
FROM Table WHERE PassportNo = @PassportNo
AND <youraduditcolumn> >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-3,0)
)
BEGIN
RAISERROR 'Passed PassportNo Already Exist and is Active'
RETURN 0
END
ELSE
BEGIN
INSERT Table (...columns..,PassportNo)
VALUES(...,@PassportNo)
RETURN 1
END
GO

Call it like

DECLARE @RetVal int
EXEC @RetVal = PassportNoSave ...,<PassportNoValue>
Use this @RetVal at the front end to check and show the prompt if value is 0

make sure you pass the correct audit column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

abuzar
Starting Member

20 Posts

Posted - 2013-12-15 : 14:36:38

i will try this out and will let you know if it runs good
thank you very much visahk
regards
abu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-16 : 03:53:55

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources