Employee Year Calculation - Database Discussion Boards

AMRITESH ASTHANA11-Sep-14 1:43

11-Sep-14 1:43

I am developing a project ASSET MANAGEMENT SYSTEM in which i am giving the asset Id to every asset . I have my office at different places in INDIA. Every now and then new asset is purchased so I want a perfect record in a database.

My question is that i want a asset number consists of company name then 3 words of city and then number .
for eg: let company name is XYZ it is purchased in city MUMBAI so i want asset id like----> XYZ/MUM/001. on every new entry of a record.

AMRITESH ASTHANA

Re: alphanumeric number generation

Eddy Vluggen11-Sep-14 3:02

Eddy Vluggen

11-Sep-14 3:02

You are talking about a real-life identifier. That's not the same as a primary key in a database. A primary key does NEVER, EVER have a constant text in there.

You create a field for you numeric; don't use the PK. Next, add in a FK to the place where it is bought. Then create a calculated field to get the name of the place, and format it as your real-life identifier.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: alphanumeric number generation

George Jonsson13-Sep-14 8:19

George Jonsson

13-Sep-14 8:19

You could have three columns in your table called
company_name of type varchar(5) (or whatever length you desire)
purchase_city of type varchar(5) (or whatever length you desire)
asset_sn of type int

Make these columns unique so you can't add the same number twice. Index name could be ix_asset_number

When you add a new inventory item you first get the last registered row for the company and city.
SELECT LAST(asset_sn) AS AssetNumber FROM assets WHERE company_name = 'XYZ' AND purchase_city = 'MUM';

Then increment the AssetNumber with 1 and insert the new record.

Re: alphanumeric number generation

AMRITESH ASTHANA14-Sep-14 0:06

AMRITESH ASTHANA

14-Sep-14 0:06

Thanks George it works Smile | :)

Re: alphanumeric number generation

George Jonsson14-Sep-14 0:12

George Jonsson

14-Sep-14 0:12

You are welcome.

create a folder in a file server using xp_create_subdir

Member 128472110-Sep-14 16:37

Member 1284721

10-Sep-14 16:37

In VS I called master.sys.xp_create_subdir @dir to create a folder on a file server, it is successful, but I called my own sp that includes this built-in sp, it is error out and the message is ';Error executing extended stored procedure: Invalid Parameter';, can anyone tell me what the problem is, how should I fix it? Here is the code

create procedure usp_MyProcedure
as
declare @dir varchar(50)
set @dir = '\\servername\folder\subfolder\'
exec master.sys.xp_create_subdir @dir
go

Thank you for your help in advance

Re: create a folder in a file server using xp_create_subdir

Mycroft Holmes14-Sep-14 5:45

Mycroft Holmes

14-Sep-14 5:45

This will be a permissions issue. Your executing sp doors not have permission to use master system procedures

Never underestimate the power of human stupidity
RAH

Employee Year Calculation

Jassim Rahma9-Sep-14 2:26

Jassim Rahma

9-Sep-14 2:26

Hi,

I have employees table with date_of_join field

and I have employee_leaves table with the following fields:

employee_id
leave_from
leave_to
total_days

the employee joined on 15 Feb 2011

I want to have a query showing the cound of leaves for every employee years based on his date_of_join

for example, if the employee joined on 15 Feb 2011 then the result will be like this:

Feb 2011 to feb 2012 ---- totals days: 21
Feb 2012 to feb 2013 ---- totals days: 26
Feb 2013 to feb 2014 ---- totals days: 8

where Feb to feb is the employee year so it's from 15 Feb to 14 Feb every year

can anyone help please?

Technology News @ www.JassimRahma.com

Re: Employee Year Calculation

Eddy Vluggen9-Sep-14 2:59

Eddy Vluggen

9-Sep-14 2:59

What have you tried? Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Employee Year Calculation

Jassim Rahma9-Sep-14 3:02

Jassim Rahma

9-Sep-14 3:02

I am not able to get it...

I tried:

SQL

SELECT employee_leaves.leave_from, employee_leaves.leave_to FROM employee_leaves
JOIN leave_category ON leave_category.leave_category_id = employee_leaves.leave_category
GROUP BY YEAR(employee_leaves.leave_from);

but this will just group by cal;ander year not employee year

Technology News @ www.JassimRahma.com

Re: Employee Year Calculation

Eddy Vluggen9-Sep-14 8:43

Eddy Vluggen

9-Sep-14 8:43

You can only group on fields that are present within the query (or calculations based on one of those fields). You can get get the start of the contract by looking for a MIN-entry. Add 365 days to that fact, that's your range.

That does not account for leap-years of course.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Employee Year Calculation

Kornfeld Eliyahu Peter9-Sep-14 10:38

Kornfeld Eliyahu Peter

9-Sep-14 10:38

Better add 1 year and not 365 day...

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

Re: Employee Year Calculation

Eddy Vluggen10-Sep-14 10:07

Eddy Vluggen

10-Sep-14 10:07

Does it account for me joining the company on the 29th februari?

Edge case from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

modified 10-Sep-14 16:14pm.

Re: Employee Year Calculation

Kornfeld Eliyahu Peter10-Sep-14 10:20

Kornfeld Eliyahu Peter

10-Sep-14 10:20

That does not matter to SQL - it has the requested knowledge to handle the 29th of February...
In fact if you are working with 365 days you have to know when to add 365 or 366 to land on the right date - otherwise you will always get 28 of February.
If you work with year you will move between 28 and 29 as it proper to the year...

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

MySQL CREATE EVENT Question

Jassim Rahma8-Sep-14 23:08

Jassim Rahma

8-Sep-14 23:08

Hi,

I want to ask how the CREATE EVENT in MySQL works? does it use the operating system event schedule (Wndows Task Scheduler, CRON, etc) or MySQL has its own event scheduler?

and can you rely on MySQL CREATE EVENT or it's better to have a process in Windows task Scheduler?

Technology News @ www.JassimRahma.com

Re: MySQL CREATE EVENT Question

Eddy Vluggen9-Sep-14 8:51

Eddy Vluggen

9-Sep-14 8:51

Jassim Rahma wrote:
and can you rely on MySQL

..well, if they say they implement a timer, you can rely on it that it works as described in the manual Smile | :)

Jassim Rahma wrote:
or it's better to have a process in Windows task Scheduler

That depends on your needs. If you are modifying data at a specified interval, then it might best be done in the database. If it doesn't need the database, then it would be better of in the task scheduler. Another big difference (does not make one better than the other) is how they are managed, and the person who has those permissions. For the task-scheduler that's the local Windows-admin, for MySQL it is probably the MySQL SA.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Arabic langauge not display when i make search by arabic langauge why

ahmed_sa5-Sep-14 20:44

ahmed_sa

5-Sep-14 20:44

Hi guys

I have database hr has table Employee this table has field EmployeeName

EmployeeName nvarchar(50)

when i write in query analyzer :

select ^ from Employee where EmployeeName='احمد'

not give me any result

but when i write

select * from Employee where EmployeeName=N'احمد'

it give me result

meaning it support arabic

but i have stored procedure not accept arabic and i dont know how to handel it to accept search by EmployeeName

CREATE Procedure sp_EmployeeSelect
@EmployeeName nvarchar(50)
AS
Declare @SQLQuery as nvarchar(2000)
SET @SQLQuery ='SELECT * from Employee Where (1=1)'
If @EmployeeName <>''
Set @SQLQuery = @SQLQuery + ' AND (EmployeeName LIKE ''%'+@EmployeeName +'%'') '
Exec (@SQLQuery)

what is the proplem in this stored procedure and how to solve it

please help me

Re: Arabic langauge not display when i make search by arabic langauge why

Bernhard Hiller7-Sep-14 22:25

Bernhard Hiller

7-Sep-14 22:25

Why don't you apply the solution you found in query analyzer

select * from Employee where EmployeeName=N'احمد'

in the stored procedure? I.e.

Set @SQLQuery = @SQLQuery + ' AND (EmployeeName LIKE N''%'+@EmployeeName +'%'') '

Re: Arabic langauge not display when i make search by arabic langauge why

Richard Deeming8-Sep-14 2:02

Richard Deeming

8-Sep-14 2:02

I'm repeating myself here:
Don't use string concatenation to build a dynamic SQL query. Your code will be susceptible to SQL Injection[^].

If you really need to use a dynamic query, use sp_executesql[^] to execute it:

SQL

CREATE Procedure sp_EmployeeSelect
    @EmployeeName nvarchar(50)
AS
Declare @SQLQuery as nvarchar(2000)

SET @SQLQuery = N'SELECT * from Employee Where (1=1)'
If @EmployeeName <> ''
    Set @SQLQuery = @SQLQuery + N' AND (EmployeeName LIKE N''%'' + @EmployeeName + N''%'')'

Exec sp_executesql @SQLQuery,
    N'@EmployeeName nvarchar(50)',
    @EmployeeName

However, in this case, as with all of your QA questions, you don't need a dynamic query:

SQL

CREATE Procedure sp_EmployeeSelect
    @EmployeeName nvarchar(50)
AS
    SELECT
        *
    FROM
        Employee
    WHERE
        @EmployeeName = N''
    Or
        EmployeeName Like N'%' + @EmployeeName + N'%'

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Select records between day, time 05:00:00 and day+1 until time 05:00:00

Ambertje4-Sep-14 0:07

Ambertje

4-Sep-14 0:07

Hello everyone,

Can someone help me select rows between 2 dates en between 2 times.
I need to select all records from 02/09/2014 starting from time 05:00:00 until the next day time until 05:00

If I do it like this then it wont work, no records shows:

SQL

SELECT TOP 10000 *
FROM Staging.[dbo].AD
WHERE DATE_CREATED BETWEEN '02/09/2014' and '03/09/2014'
AND Convert(Time,TIME_CREATED) between '05:00:00' AND '05:00:00'

It should be something like this:
Between DATE_CREATED 02/09/2014, TIME_CREATED 05:00:00 and DATE_CREATED 03/09/2014, TIME_CREATED 05:00:00

Kind regards,
Ambertje

Re: Select records between day, time 05:00:00 and day+1 until time 05:00:00

Chris Quinn4-Sep-14 0:13

Chris Quinn

4-Sep-14 0:13

SQL

SELECT TOP 10000 *
FROM Staging.[dbo].AD
WHERE DATE_CREATED BETWEEN '02/09/2014 05:00:00' and '03/09/2014 05:00:00'

=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================

Re: Select records between day, time 05:00:00 and day+1 until time 05:00:00

Ambertje4-Sep-14 1:40

Ambertje

4-Sep-14 1:40

Thank you for the reply but this is not working, it's showing me all the records for both days, not the records between 05:00:00 and 05:00:00

Re: Select records between day, time 05:00:00 and day+1 until time 05:00:00

Eddy Vluggen4-Sep-14 0:31

Eddy Vluggen

4-Sep-14 0:31