Database

Ashfield16-Feb-09 1:33

16-Feb-09 1:33

Change your select to make 121 a char

SELECT     id_student, CASE WHEN (CODE) = 1 THEN 'Abs' ELSE '121' END AS m1

or

SELECT     id_student, CASE WHEN (CODE) = 1 THEN 'Abs' ELSE convert(varchar,121) END AS m1

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

foryou16-Feb-09 3:43

foryou

16-Feb-09 3:43

Thank you very much Ashfield.
but the problem is in 'abs' text in float champ Frown | :(

Ashfield16-Feb-09 5:21

16-Feb-09 5:21

I know, you want the literal value 'abs' or 121, so if you force your value 121 to become a char type, either by convert (or cast) or just by enclosing it in quotes then sql server will treat BOTH values as char types and will be quite happy (as far as I know).

Try it and see, if it still doesn't work post your new code and the error message again.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Help is needed in Triggers and indexes in sql server 2000

foryou16-Feb-09 23:50

foryou

16-Feb-09 23:50

Thanks Ashfield!merci beaucoup
i have anouther problem with this code ,I posted a new message.
Thanks Smile | :)

Rameez Raja15-Feb-09 20:00

Rameez Raja

15-Feb-09 20:00

Hi all,

I have to work on Indexes and triggers in sql server 2000 can any body suggest me any article or resourse where i can learn these things.

Thanks in advance

Best regards
Rameez

Re: Help is needed in Triggers and indexes in sql server 2000

Wendelius15-Feb-09 20:16

Wendelius

15-Feb-09 20:16

Lots of results in Google: http://www.google.com/search?hl=en&q=sql+server+2000+indexing&meta=&aq=f&oq=[^]

For triggers you could have a look at these:
- http://msdn.microsoft.com/en-us/magazine/cc164047.aspx[^]
- http://msdn.microsoft.com/en-us/magazine/cc164032.aspx[^]

and for indexing for example this:
- http://www.sqlservercentral.com/articles/Indexing/sqlserver2000indexing/1479/[^]

The need to optimize rises from a bad design.My articles[^]

How will we stop the duplicating entries while we entering in Excel database?

hitesh.kalra15-Feb-09 18:54

hitesh.kalra

15-Feb-09 18:54

I insert and update in an excel database but what is the coding to prevent the duplicating entries in the database. Plz tell in c# language. thanx in advance.

how to create a query by using store procedure in SQL Server 2000 ?

Golden Jing15-Feb-09 15:10

Re: how to create a query by using store procedure in SQL Server 2000 ?

15-Feb-09 15:10

I have a table of employee attendent like that:

EmpID-----Date----------------Time------------------Status (Field Name)
1-------01/01/2009------01/01/2009 8:00 am--------I
1-------01/01/2009------01/01/2009 12:00 am-------O
1-------01/01/2009------01/01/2009 1:00 pm--------I
1-------01/01/2009------01/01/2009 5:00 pm--------O
2-------01/01/2009------01/01/2009 7:50 am--------I
2-------01/01/2009------01/01/2009 12:00 am-------O

But i want to make a query that take data from table above to be like that:

EmpID-----Date------------In------------Out----------In-------------Out (Fiel Name)
1-------01/01/2009------8:00 am-----12:00 am----1:00 pm------5:00 pm
2-------01/01/2009------7:50 am-----12:00 am----Null----------Null

Can we do that ?
Thank for your kindly to help me...
Best regard,
Sovann

VB.Net

modified on Sunday, February 15, 2009 9:18 PM

_Maxxx_15-Feb-09 16:34

Re: how to create a query by using store procedure in SQL Server 2000 ?

15-Feb-09 16:34

Something like this should get you close

<br />
select c.empid, c.dt as in1,  <br />
<br />
(select c1.dt from clock as c1 where c1.empid = c.empid and status = 'O'<br />
and not exists (select 1 from clock as c2 where c2.empid = c1.empid and c2.status = 'O' and c2.dt < c1.dt)) as out1,<br />
<br />
(select c1.dt from clock as c1 where c1.empid = c.empid and status = 'I'<br />
and c1.dt > c.dt) as in2,<br />
<br />
<br />
<br />
(select c1.dt from clock as c1 where c1.empid = c.empid and status = 'O'<br />
and c1.dt > (select c1.dt from clock as c1 where c1.empid = c.empid and status = 'I'<br />
and c1.dt > c.dt)) as out2<br />
<br />
from clock as c<br />
where c.status = 'I'<br />
and not exists (select 1 from clock as c1 where c1.empid = c.empid and c1.status = 'I' and c1.dt < c.dt)

___________________________________________
.\\axxx
(That's an 'M')

Golden Jing15-Feb-09 17:21

Re: how to create a query by using store procedure in SQL Server 2000 ?

15-Feb-09 17:21

Thanks for your kindly to help me.
but what you wrote is in query right ?
It did not process.
but if there are more employee, Does it work ?

My idea if we select and use for loop, what can we do ?

VB.Net

_Maxxx_15-Feb-09 18:31

Re: how to create a query by using store procedure in SQL Server 2000 ?

15-Feb-09 18:31

Sovann wrote:
Thanks for your kindly to help me.

No worries

Sovann wrote:
but what you wrote is in query right ?

Yes - just plain SQL - so you can put it in a stored proc or whatever

Sovann wrote:
It did not process

? in what way. I created a dummy table and entered data similar to your example, ran the query against it and got your desired results.
The column and table names were different to yours, but the query certainly worked fine...

Sovann wrote:
but if there are more employee, Does it work ?

I think it should work as long as there is always an In and Out (not sure what it would do if there was only an In time and no Out - or if there were more than two of each. It doesn't matter how many employees there are.

Sovann wrote:
My idea if we select and use for loop, what can we do ?

If you WANT to write loops etc. then sure, you could do that. You could create a temporary table, populate it using a number of queries in a loop, or whatever you want to do. Depends whether your aim is to solve the problem or to write some sql using a for loop?

___________________________________________
.\\axxx
(That's an 'M')

Golden Jing15-Feb-09 20:45

Re: how to create a query by using store procedure in SQL Server 2000 ?

15-Feb-09 20:45

I want to write some sql using for loop. Because i do not know that about number of employee. It will increase when we add new employee info.

VB.Net

_Maxxx_16-Feb-09 12:50

Re: how to create a query by using store procedure in SQL Server 2000 ?

16-Feb-09 12:50

Sovann wrote:
I want to write some sql using for loop.

If that is what you want to do (i.e. you want to write loop rather than you want to achive the results most efficiently) then take a look here[^] or an example -but read the highlighted bit abut this being inefficient!

Sovann wrote:
Because i do not know that about number of employee. It will increase when we add new employee info.

The SQL I wrote will handle any number of employees..

select c.empid, c.dt as in1, 

(select c1.dt from clock as c1 where c1.empid = c.empid and status = 'O'
and not exists (select 1 from clock as c2 where c2.empid = c1.empid and c2.status = 'O' and c2.dt < c1.dt)) as out1,

(select c1.dt from clock as c1 where c1.empid = c.empid and status = 'I'
and c1.dt > c.dt) as in2,

(select c1.dt from clock as c1 where c1.empid = c.empid and status = 'O'
and c1.dt > (select c1.dt from clock as c1 where c1.empid = c.empid and status = 'I'
and c1.dt > c.dt)) as out2

from clock as c
where c.status = 'I'
and not exists (select 1 from clock as c1 where c1.empid = c.empid and c1.status = 'I' and c1.dt < c.dt)

the bit not crossed out selects all employees records with an 'In' status where there is not an earlier I status record - i.e. it selects the first In record for the employee. Obviously, if you need to look at multiple days, you will need to change this slightly so that it tests the date and time separately (I wasn't sure from your question whether these were stored in separate columns or not)

So - the SQL will work as it stands with any number of employees, for a single date. Simply expand it a bit to allow multiple dates and it will give you what you want.

OK - I couldn't resist, so I expanded it.

SELECT     EmpId, Dt AS in1,
                          (SELECT     Dt
                            FROM          clock AS c1
                            WHERE      (EmpId = c.EmpId) AND (status = 'O') AND (NOT EXISTS
                                                       (SELECT     1 AS Expr1
                                                         FROM          clock AS c2
                                                         WHERE      (EmpId = c1.EmpId) AND (status = 'O') AND (Dt < c1.Dt) AND (FLOOR(CAST(Dt AS Float)) = FLOOR(CAST(c1.Dt AS Float))))) AND 
                                                   (FLOOR(CAST(Dt AS Float)) = FLOOR(CAST(c.Dt AS Float)))) AS out1,
                          (SELECT     Dt
                            FROM          clock AS c1
                            WHERE      (EmpId = c.EmpId) AND (status = 'I') AND (Dt > c.Dt) AND (FLOOR(CAST(Dt AS Float)) = FLOOR(CAST(c.Dt AS Float)))) AS in2,
                          (SELECT     Dt
                            FROM          clock AS c1
                            WHERE      (EmpId = c.EmpId) AND (status = 'O') AND (Dt >
                                                       (SELECT     Dt
                                                         FROM          clock AS c1
                                                         WHERE      (EmpId = c.EmpId) AND (status = 'I') AND (Dt > c.Dt) AND (FLOOR(CAST(Dt AS Float)) = FLOOR(CAST(c.Dt AS Float))))) AND 
                                                   (FLOOR(CAST(Dt AS Float)) = FLOOR(CAST(c.Dt AS Float)))) AS out2
FROM         clock AS c
WHERE     (status = 'I') AND (NOT EXISTS
                          (SELECT     1 AS Expr1
                            FROM          clock AS c1
                            WHERE      (EmpId = c.EmpId) AND (status = 'I') AND (Dt < c.Dt) AND (FLOOR(CAST(Dt AS Float)) = FLOOR(CAST(c.Dt AS Float)))))

That should work, I think, for all dates for any number of employees, where there is at least in 'I' record for any one employee on a date (i.e. if the employee doesn't turn up at all on one day there will be no output for that employee.

___________________________________________
.\\axxx
(That's an 'M')

Golden Jing17-Feb-09 20:58

Re: how to create a query by using store procedure in SQL Server 2000 ?

17-Feb-09 20:58

I wrote similar like what you wrote but it did not work.
Sorry i do not understand SQL you wrote. i think Dt is Date field right ? what about Time field ? I do not know which one is Date and one is Time...

Sorry, If it should work and you show me the result to sure that what you write is what i want...

I am very glad and to say thanks you so much for your help me...
It had help me some knowledge about SQL...
I will try to find it more and try to change other way to get the result what i want.
If you have any some about it, please share to me.
Thanks you again.
Best regard,
Sovann

VB.Net

_Maxxx_17-Feb-09 21:53

Re: how to create a query by using store procedure in SQL Server 2000 ?

17-Feb-09 21:53

I wasn't sure whether there was a date and a time field - or a single datetime field in your DB - I had assumed a single datetime field which I called dt.

FLOOR(CAST(Dt AS Float)) gives you just the Date portion of your DateTime field. if you have separate Date and Time fields you can replace
FLOOR(CAST(Dt AS Float))
by
dt

if your date field is called dt

The idea of the query is that it selects all records for all employees where there is an 'In' record ... so this

WHERE     (status = 'I') AND (NOT EXISTS
                          (SELECT     1 AS Expr1
                            FROM          clock AS c1
                            WHERE      (EmpId = c.EmpId) AND (status = 'I') AND (Dt < c.Dt) AND (FLOOR(CAST(Dt AS Float)) = FLOOR(CAST(c.Dt AS Float)))))

selects where is is an In record (status = 'I') and

There are no records (not exists) for the same employee (empId = c.EmpId) that is an 'In' record (status = 'I') where the datetime is earlier (dt < c/DT) and the Date is the same.

You could rewrite this if you have a date column called dt and a time column called tm as

(EmpId = c.EmpId) AND (status = 'I') AND (tm < c.tm) AND (dt = c.Dt )

which makes it easier to read!
You can see then that this logic is just checking that tehre are no In records, for this employee, for the same date, that are earlier in the day - or, in other words, only include records that are the first In record for the day.

Having got this record, the first Out column, second In and Out columns are all based upon this record

I'm not on the computer where I created the database to test this right now - when I am, if I have time, I will change it to look like your DB and chaneg teh SQL for you - but if you follow my advice above, you should get there!@

good luck

___________________________________________
.\\axxx
(That's an 'M')

Golden Jing17-Feb-09 22:27

Re: how to create a query by using store procedure in SQL Server 2000 ?

17-Feb-09 22:27

Thank you so much.
Yes i will follow your advice.
I hope i can get the result like what i want.
If still not, try other way more...

Thank you again.
Good luck too

VB.Net

Wendelius15-Feb-09 18:09

Wendelius

15-Feb-09 18:09

In your example result you have 2 In columns and 2 Out columns. But what happens if a person goes in and out 3 or 4 times a day?

The need to optimize rises from a bad design.My articles[^]

Re: how to create a query by using store procedure in SQL Server 2000 ?

Golden Jing15-Feb-09 20:35

Re: how to create a query by using store procedure in SQL Server 2000 ?

15-Feb-09 20:35

if a person goes in and out 3 or 4 time a day, one field will get null...

VB.Net

Wendelius15-Feb-09 21:23

Wendelius

15-Feb-09 21:23

Sovann wrote:
if a person goes in and out 3 or 4 time a day, one field will get null...

Don't quite understand. What field?

What I mean is that can a person go in and out undefined number of times during the same day? If he can, do you plan to use dynamic amount of columns (person, in1, out1, in2, out2, in3, out3...). Since this is very hard to achieve using plain sql

The need to optimize rises from a bad design.My articles[^]

Re: how to create a query by using store procedure in SQL Server 2000 ?

Golden Jing15-Feb-09 22:41

Re: Ab Inito conundrums....

15-Feb-09 22:41

Oh sorry.
No over that columns bro.
that columns is (EmpID,Date,In1,Out1,In2,Out2) so each employee will have 4 attendants but some day they will absent in afternoon so their attendants will be just only 2 so
in columns In2, Out2 data will null. More is number of employee.

at the first i posted, i want to change data in Employee table like table under it. (hm... maybe is called crosstab.)

VB.Net

Ab Inito conundrums....

PoisonCreed14-Feb-09 15:04

PoisonCreed

14-Feb-09 15:04

Hi

i am stuck in some issues professionally regarding Ab Inito, is there any one who can guide me as to how to go about this Ab Initio things, mates it is really compliv\cated and there is very limited resource available on net regarding Ab Inito......

This is an urgent requirement.......... Sigh | :sigh:

Ashfield15-Feb-09 8:16

Re: Ab Inito conundrums....

15-Feb-09 8:16

PoisonCreed wrote:
This is an urgent requirement.

but not urgent enough to contact their support team, which is listed on their website.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

PoisonCreed15-Feb-09 12:48

PoisonCreed

15-Feb-09 12:48

I have already contacted their support team 15 days back , even asking them as to how to go about if i have to purchase the licensed copy of the product. but their answer is inconclusive and way of the track ......

Well what i have gathered so far is that company is very discreet and miser in disclosing any of their trade secrets regarding the product and hardly there is any more information regrading it available on web itself, i hardly found anything substantial so far..... Confused | :confused:

Re: Ab Inito conundrums....

Ashfield15-Feb-09 20:57