|
I want to add one column in sql table.
That will be in specific format. The format is like that
Year+00001
Year+00002
....and so on. If the year changes to new year, I want to start from 00001. like that.
Year+00001
Year+00002
to be more clear what I need is: like below in applicationID column.
name | date | applicationID
Willam | 2011-12-03 | 201100001
Susan | 2011-12-04 | 201100002
Alex | 2012-01-01 | 201200001
Mata | 2013-01-01 | 201300001
how I have to do. &
please.
|
|
|
|
|
I suspect this is a BAD idea, I also suspect you are using this field as a primary key. If so the DON'T it is a BAD design.
To do this you will need to query the table to find out how many inserts there are for this year, build your string and insert the record. There are a number of alternatives to this process that include triggers and stored procedures.
Here is the reason it is a bad idea. What happens if another user inserts a record during this process, BOOM one busted database.
I suggest you insert the datetime for Created then query filtered on the year and ordered by the created date. If you need an incremental number you can use ROW_NUMBER() assuming you are using sql server
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi, I am not clear your answer...
ok, how about adding auto generated id(identity) column in my table..
I will use id column as primary key.
I will not use applicationID column as primary key.
for applicationID column, I just want to store in this format.
id | name | date | applicationID
1 | Willam | 2011-12-03 | 201100001
2 | Susan | 2011-12-04 | 201100002
3 | Alex | 2012-01-01 | 201200001
4 | Mata | 2013-01-01 | 201300001
pls.
|
|
|
|
|
Better design!
Then you can update the record after it is inserted to calculated the applicationid field.
You will need to pad the ROW_NUMBER()/MAX value with leading zeros and combine that with the year datepart.
This is a better option as the ApplicationID field that is a human consumed piece of info is not used to identify the record.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
I saw same problem like me.
but I didn't see best answer, like sample.
|
|
|
|
|
There is also a security reason for not doing this.
If someone sees that their id is "20110005" it is easy to guess that 20110004 and 20110006 are also valid.
|
|
|
|
|
sankooo wrote: I want to add one column in sql table.
That will be in specific format.
Mycroft is right, but it's half the story. Tables hold data, not information.
sankooo wrote: If the year changes to new year, I want to start from 00001. like that.
That's a composite column, and in the ideal world we try to keep the data atomic. That means that you can loose the date-part, as that's already encoded in the previous column. You also don't need the prefix-zeroes, those are merely there for presentation. Hence, all you need in the ApplicationID-row is a sequence-number; all other information is already present and the complete applicationID as you need can be calculated from the rest of the tuple.
The other half of the story is that we don't want to duplicate information, as that would be redundant (and thus, increases the chances for errors).
I'd suggest you read up on normalization[^] procedures.
Bastard Programmer from Hell 
|
|
|
|
|
I am using this code to view a news headline from mysql table but I want also to add one more thing please... I want to update a field named news_views (int) with +1 everytime someone views this article.. how can I do this please...
<?php
$news_id = $_GET['id'];
$conn = mysql_connect('mysql.jassimrahma.com', 'jassimdb', 'xxxxxxx');
$sql = "SELECT * FROM news WHERE news_id = '$news_id'";
mysql_select_db("jassimrahma", $conn) or die(mysql_error());
$result = mysql_query($sql, $conn) or die(mysql_error());
$count = mysql_num_rows($result);
if($count == 1)
{
$db_field = mysql_fetch_assoc($result);
$news_url = $db_field['news_url'];
$news_title = $db_field['news_title'];
$news_webpage = file_get_contents($news_url);
echo $news_webpage;
}
else
{
header("location:index.php");
}
?>
|
|
|
|
|
I think you will get more response from the PHP forum. 
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
Hi all,
DECLARE
@holidayCount AS int,
@startDate AS datetime,
@endData AS datetime,
@empCode AS VARCHAR(20)
SET @startDate='12/6/2011 00:00:00.000'
SET @endDate='12/8/2011 23:59:59.997'
SET @empCode='EMP001'
SET @holidayCount=(SELECT COUNT(*) FROM tbl_holidays WHERE
holidays BETWEEN @startDate AND @endDate)
--I am assuming that 'holidays' is a datetime field/column
SELECT empCode, name, (Counts + @holidayCount) As holidayCounts
FROM
(
SELECT a.empCode, b.name, (Count(*) as Counts
FROM tbl_leaveentry a,tbl_emp b
WHERE a.empCode=b.empCode AND
a.empCode=@empCode AND
a.entryDate @startDate and @endDate
GROUP BY by a.empCode,b.name
) AS temp
WHERE Counts + @holidayCount>3
From the above query i got the count but now i need to get the start date of the leave that an employee had applied.
eg. 'EMP001' had applied leave from 12/1/2011 to 12/8/2011
but i have only one date option that is the end date. once you select the date option like 12/8/2011 it searches the dates from 2days before like 12/6/2011 and 12/8/2011 and returns the employees who have taken leaves continuously but i need to get the start date of the leave applied by the employee.
eg. EMP001 had applied leave from 12/1/2011 to 12/8/2011
Please suggest that how can i do this task any help will be appreciated
|
|
|
|
|
How about subtracting count of leaves from endDate
like
SELECT @SDate = DATEADD (day, - @holidayCount, @endDate)
|
|
|
|
|
Hi,
select empcode,name,(counts+2) as leaves from (select a.empcode,b.name,count(*) as counts from tbl_leaveentry a,tbl_emp b where a.empcode=b.empcode and a.empcode='EMP001' and
a.entrydate between DATEADD(d,-2,'12/8/2011') and '12/8/2011'
group by a.empcode,b.name) as c where (counts+2) =3
The above code generates the report of the employee who have taken leave more than 3 days from the selected dates but i need to get the start date of the leave applied by the employee
eg: 12/1/2011 to 12/8/2011
Thanks in advance
|
|
|
|
|
What other fields are in tbl_leaveentry besides empcode,entrydate?
Do you have a field that indicates how may leave days were taken? There should be a field that indicates how many days were taken for leave.
Just at glance, it seems to me that your database was poorly designed. I think start date should have been included in the first place.
|
|
|
|
|
Hello .
I have a web service which takes some parameters and inserts them in another database . I have my own database that has a
table (Table_B) .Now
I want to call my web service when a new record inserts to (Table_B)
I know that i should use a trigger . All I want to do is calling a web service inside of my trigger . I don't know how to call it.
Would you please help me
Thanks .
bye
|
|
|
|
|
If I was you I would start here:
www.google.com[^]
Type sql server call web service from trigger and press Enter. That gives over 2 million links, at least one of them must be useful.
P.S. one of those links is to an article here on Code Project. In the article it actually says:
The truth is it's not good to access a web service trought a trigger, due to performance concerns.
I'm sure you can make your own mind up as to whether that is true or not.
|
|
|
|
|
Hi
Thanks for your Guidance. I know all of them but thanks
|
|
|
|
|
If I may ask, what inserts records in table_B; is it a stored procedure?
If so, I would use the same stored procedure to call the web service immediately after it inserts data. I don't know if ordinary stored procedures can call web services so I would suggest an extended stored procedure i.e. a CLR dll.
I haven't done anthing like it; so I am really just guessing.
|
|
|
|
|
I exported my results data into Excel 2007 (784,000 records on one column) so that I can Import it back into SQL Server 2008 as a table. When the data comes back from Excel it comes in as a float which I convert to a nvarchar(255) or varchar(50), but any record that has a letter in it shows up as NULL in SQL Server 2008. I have tried all combinations. Can anyone suggest anything?
Ed
|
|
|
|
|
Try yo convert float Excel column into text and then import in SQL Table.
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi All,
Iam new to database. Here iam using sql server 2000. i want to sum the counts from two different tables. I need this to calculate the employees who have taken continuous leaves in a week/month.
Example: I have three table such as
Table1:tbl_attandence(it contains the employee id and the intimings and outtime)
Fields:empcode,intime,outime
Eg: EMP001,11/24/2011 09:30:45 AM,11/24/2011 04:30:15 PM
Table2:tbl_leaveentry(it contains the leave dates taken by an employee)
Fields:empcode,entrydate
Eg: EMP001,11/25/2011
Table3:tbl_holidays(it contains the list of holidays such as sunday,saturday,chrismas,newyear....)
Fields:holidays,days
Eg: 11/25/2011,sunday
I am attaching my code such that it return the sum but iam unable to return the employee name and id because it is showing the result as null as the holidays does not match
please help me in completion of process
select sum(counts)
from(
select a.empcode as empcode,b.name,count(*) as counts from tbl_leaveentry a,tbl_emp b
where a.empcode=b.empcode
and a.empcode='EMP001'
and a.entrydate between '11/24/2011'; and '11/27/2011'
group by a.empcode,b.name
union
select null,null,count(*) as counts from tbl_holidays
where holidays between '11/24/2011' and '11/27/2011'
) as c having sum(counts)>=3
the above code returns correct sum but if i add name before the sum and execute the result is not been summed because holidays does not match with the leaves
Please suggest that how can i do this task any help will be appreciated
|
|
|
|
|
Try something like:
select name, sum(counts)
from(
select a.empcode as empcode,b.name,count(*) as counts
from tbl_leaveentry a
INNER JOIN tbl_emp b
ON a.empcode=b.empcode
where a.empcode='EMP001'
and a.entrydate between '11/24/2011'; and '11/27/2011'
group by a.empcode, b.name
union
select null, null, count(*) as counts
from tbl_holidays
where holidays between '11/24/2011' and '11/27/2011'
) as c
GROUP BY name
having sum(counts)>=3
Note the GROUP BY and I have changed to an INNER JOIN.
Hope this helps. Sorry, I do not have time to dive deeper into this.
[edit]to spell note correctly[/edit]
modified 14-Dec-11 15:19pm.
|
|
|
|
|
Try this.
DECLARE
@holidayCount AS int,
@startDate AS datetime,
@endData AS datetime,
@empCode AS VARCHAR(20)
SET @startDate='11/24/2011 00:00:00.000'
SET @endDate='11/27/2011 23:59:59.997'
SET @empCode='EMP001'
SET @holidayCount=(SELECT COUNT(*) FROM tbl_holidays WHERE
holidays BETWEEN @startDate AND @endDate)
SELECT empCode, name, (Counts + @holidayCount) As holidayCounts
FROM
(
SELECT a.empCode, b.name, (Count(*) as Counts
FROM tbl_leaveentry a,tbl_emp b
WHERE a.empCode=b.empCode AND
a.empCode=@empCode AND
a.entryDate @startDate and @endDate
GROUP BY by a.empCode,b.name
) AS temp
WHERE Counts + @holidayCount>3
|
|
|
|
|
Hi SilimSayo,
Thank you so much for the query it matches my result perfectly.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
