|
I agree with the others, except when the application would fit the following description:
- a lot of fields are time values, so storing them in a compact format will be significant to DB size;
- the time resolution and range required are such that a small integer could do, say 1-minute resolution and a 2-day range (to cope with midnight shifts), would fit in a 16-bit int;
- your DB queries are known in advance and don't require complex datetime calculations.
If there is start time and end time, I would consider storing begin time and duration instead.
If the above matches pretty well, then you're set for a smaller DB, and no installation, as the JET engine is always present AFAIK.
OTOH if the above feel like restrictions, if there is too much uncertainty about how the requirements will evolve, if you want the comfort of full T-SQL, ... then by all means use a real DB and pay the price.
|
|
|
|
|
Luc Pattyn wrote: then by all means use a real DB and pay the price.
I would love to use SQL Server Express but the software is being deployed with CD-ROM using InstallShield 2010 express edition. This edition cannot install SQL Server silently. (Which would be a must)
At this point I can't justify the $900 upgrade cost on InstallShield so Access seems to be my best bet.
|
|
|
|
|
I guess the problem here is not the field type in Access - in almost all cases it must be DateTime - but the user interface.
Make sure you can convert a user input like "7:30 PM" correctly into a DateTime value. And that you format values returned from the database in a way the user wants to see them.
For the communication with the database, you must use a parameterized query when you want to send DateTime values from your application to the database - be certain to add the DateTime values as DateTime values, and do not convert them to strings when adding them!
|
|
|
|
|
We have saved the 24 hr time in a text field as HH:MM
That being said you need to look at what overhead is needed to convert it to a true time.
By the way Microsoft Access and serious do not go together.
|
|
|
|
|
I have created sql stored procedure to calculate work hours of employees in a week.EmployeeID,FromDate,ToDate,Department,Designation are the parameters passed.My output is:
Day EmpID EmpName FirstTimeIn LastTimeOut WorkHours Break
-------------------------------------------------------------------------
Wednesday 2855747 AlexanderMathew 9:01:09 AM 6:15:14PM 8:59:05
Thursday 2855747 AlexanderMathew 9:45:09 AM 6:45:09 PM 8:00:00 Friday 2855747 AlexanderMathew 9:08:09 AM 7:20:09 PM 9:00:00
Saturday 2855747 AlexanderMathew 9:30:09 AM 6:30:09 PM 8:00:00
Sunday 2855747 AlexanderMathew 9:32:09 AM 6:28:09 PM 8:00:00
Monday 2855747 AlexanderMathew 9:20:09 AM 6:25:09 PM 8:05:00
Tuesday 2855747 AlexanderMathew 9:02:09 AM 6:02:09 PM 8:30:00
But the problem is that,in my output 'day' is shown as row.But is there any way to display day as columns.ie:
eg:
EmpName Friday Saturday Sunday Monday Tuesday Wednesday WorkHours
-------------------------------------------------------------------------
Alexander 07:11:18 Saturday Sunday 06:20:41 08:06:28 03:23:27 25:01:54
This stored procedure is for reporting.Hope you will help me to overcome this problem..
Thanks in advance
|
|
|
|
|
Just have a look through and see whether it solves your need,
Approach - 1 (using subquery)
sample table,
create table #tm (days varchar(10), empid varchar(10), empname varchar(100), intime varchar(20), outtime varchar(20), workhours varchar(20))
sample records,
insert into #tm
select 'Wednesday', '2855747', 'AlexanderMathew', '9:01:09 AM', '6:15:14PM', '8:59:05'
union
select 'Thursday','2855747','AlexanderMathew','9:45:09 AM','6:45:09 PM','8:00:00'
union
select 'Friday','2855747','AlexanderMathew','9:08:09 AM','7:20:09 PM','9:00:00'
union
select 'Saturday','2855747','AlexanderMathew','9:30:09 AM','6:30:09 PM','8:00:00'
union
select 'Sunday','2855747','AlexanderMathew','9:32:09 AM','6:28:09 PM','8:00:00'
union
select 'Monday','2855747','AlexanderMathew','9:20:09 AM','6:25:09 PM','8:05:00'
union
select 'Tuesday','2855747','AlexanderMathew','9:02:09 AM','6:02:09 PM','8:30:00'
select distinct empid, empname,
(select workhours from #tm b where a.empid = b.empid and days = 'Sunday') sunday,
(select workhours from #tm b where a.empid = b.empid and days = 'Monday') Monday,
(select workhours from #tm b where a.empid = b.empid and days = 'Tuesday') Tuesday,
(select workhours from #tm b where a.empid = b.empid and days = 'Wednesday') Wednesday,
(select workhours from #tm b where a.empid = b.empid and days = 'Thursday') Thursday,
(select workhours from #tm b where a.empid = b.empid and days = 'Friday') Friday,
(select workhours from #tm b where a.empid = b.empid and days = 'Saturday') Saturday
from #tm a
disadvantage of approach-1:
---------------------------
1) if the table has huge records then the above will take time for execution
Approach - 2
-------------
create table #tm1 (empid varchar(10), empname varchar(100),
sunday varchar(20), monday varchar(20), tuesday varchar(20), wednesday varchar(20), thursday varchar(20),
friday varchar(20), saturday varchar(10))
insert into #tm1 (empid, empname)
select distinct empid, empname from #tm
update a set a.sunday = b.workhours
from #tm1 a
join #tm b on a.empid = b.empid and b.days = 'Sunday'
update a set a.monday = b.workhours
from #tm1 a
join #tm b on a.empid = b.empid and b.days = 'Monday'
update a set a.tuesday = b.workhours
from #tm1 a
join #tm b on a.empid = b.empid and b.days = 'Tuesday'
update a set a.wednesday = b.workhours
from #tm1 a
join #tm b on a.empid = b.empid and b.days = 'Wednesday'
update a set a.thursday = b.workhours
from #tm1 a
join #tm b on a.empid = b.empid and b.days = 'Thursday'
update a set a.friday = b.workhours
from #tm1 a
join #tm b on a.empid = b.empid and b.days = 'Friday'
update a set a.saturday = b.workhours
from #tm1 a
join #tm b on a.empid = b.empid and b.days = 'Saturday'
select * from #tm1
i didn't do calculating total work hour calculation.
|
|
|
|
|
You need to use the pivot function[^], assuming you are using sql server of course.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi all
Does PRINT work in sql server function???
One person's data is another person's program.
--J.Walia
|
|
|
|
|
Yes
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
but i am getting the below error:
Invalid use of side-effecting or time dependent operator in 'PRINT' within function
One person's data is another person's program.
--J.Walia
|
|
|
|
|
Then you are trying to print something and it produces an error, find out what the produces the error and fix it. Master of logic - thats me!
What are you trying to print
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
J walia wrote: Does PRINT work in sql server function???
No.
You cannot use PRINT inside a function in SQL Server.
|
|
|
|
|
David Skelly wrote: in sql server function
My mistake I didn't even see that - thanks for fixing that.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thanks for reply.
then what is solution for this?
I have one more question. can we use
Execute sp_executesql in functions
One person's data is another person's program.
--J.Walia
|
|
|
|
|
You do realise that it is probably quicker to actually create a function and try and execute a stored proc. Then take the error message to google/BOL and read up on the problem. It will give you a greater depth of knowledge than a forum post. AND you will not risk some one giving you the wrong answer.
You also can't use dynamic SQL in a function, just to save your next question!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thanks sir.
One person's data is another person's program.
--J.Walia
|
|
|
|
|
J walia wrote: then what is solution for this?
The solution to what? Your question was "Can I use print in a function?" The answer is "No, you can't".
J walia wrote: I have one more question. can we use
Execute sp_executesql in functions
No.
|
|
|
|
|
J walia wrote: Does PRINT work in sql server function???
You can convert your prints to match something like below;
DECLARE @printz AS TABLE(
Stamp DATETIME DEFAULT GETDATE()
,Msg NVARCHAR(MAX)
)
INSERT INTO @printz(Msg)
SELECT 'We are at the start of the proc'
INSERT INTO @printz(Msg)
SELECT 'Something went terribly wrong here, eracing all evidence'
INSERT INTO @printz(Msg)
SELECT 'We are at the end of the proc'
SELECT Stamp, Msg FROM @printz
Another option that I sometimes resort to, is the RAISERROR [^] statement. To test that it'll print both statements from a sproc;
try
{
using (System.Data.SqlClient.SqlConnection con =
new System.Data.SqlClient.SqlConnection(
"Server=.;Database=[YOURDBNAME];Trusted_Connection=True;"))
using(var cmd = con.CreateCommand())
{
con.Open();
cmd.CommandText = "testerror";
cmd.ExecuteNonQuery();
}
}
catch(System.Data.SqlClient.SqlException ex)
{
System.Diagnostics.Debug.Print(ex.ToString());
}
CREATE PROCEDURE TESTERROR AS
BEGIN
RAISERROR (N'This is message %s %d.',
18,
1,
N'number',
5);
RAISERROR (N'This is message %s %d.',
18,
1,
N'number',
6);
END
Good luck
I are Troll
|
|
|
|
|
I have Employees and Reporting hirarchy.For Ex:
1
2 3 4
5 6 7 8
Employees 2,3,4 report to 1 and 5,6 report to 2 and 7,8 report t0 4.
If I pass 1 to query it results
2,3,4,5,6,7,8
2 then 5,6
3 then 7,8
How can i get this in sqlserver 2005
|
|
|
|
|
What did you so far for your query?
Use CASE switch to archive your query.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
I have thousands of records how can I use CASE ?
|
|
|
|
|
|
I have only two columns like below.
id id_project id_parent
-------------------------
1 root root
2 abc123 root
3 xyz098 root
4 cmd003 xyz098
5 asd874 abc123
6 f8jk12 cmd003
So, I will need the output to look similar to this when pass id as '1'
- abc123
-- asd874
- xyz098
-- cmd003
--- f8jk12
So, I will need the output to look similar to this when pass id as '3'
- cmd003
-- f8jk12
So, I will need the output to look similar to this when pass id as '2'
- asd874
How can I do this in sqlserver2005
|
|
|
|
|
Did you read the article i linked you to?
|
|
|
|
|
To get result, you need to write recursive function
A function to get Parent
CREATE FUNCTION dbo.FindRoot(@id int)
RETURNS int
AS
BEGIN
DECLARE @Id_parent int
SELECT @Id_parent= Id_parent
FROM TableName
WHERE id = @id
WHILE @Id_parent <> NULL
BEGIN
SELECT @id = @Id_parent
SELECT @Id_parent = Id_parent
FROM PrimeInfo
WHERE id = @id
END
RETURN @id
END
and then store procedure to extract your data by providing id value
CREATE PROCEDURE BuildTree(@id int)
AS
SET NOCOUNT ON
CREATE TABLE #results(level int, id int, id_parent int)
DECLARE @id_parent int
DECLARE @level int
SELECT @level = 1
DECLARE @root int
SELECT @root = dbo.FindRoots(@id)
CREATE TABLE #stack (id int, level smallint)
INSERT INTO #stack VALUES (@root, @level)
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @id = s.id, @id_parent= IsNull(t.id_parent, 0)
FROM #stack s INNER JOIN TableName t
ON t.id = s.id
WHERE level = @level
INSERT INTO #results VALUES (@level, @id, @id_parent)
DELETE FROM #stack
WHERE level = @level
AND id = @id
INSERT #stack
SELECT id, @level + 1
FROM TableName
WHERE id_parent = @id
IF @@ROWCOUNT > 0
BEGIN
SELECT @level = @level + 1
END
END
ELSE
BEGIN
SELECT @level = @level - 1
END
END
SELECT id, id_parent, level FROM #results
|
|
|
|
|