Re: Database sample - Database Discussion Boards

Re: Remove duplicates from result set

sreejith ss nair2-Dec-05 6:24

sreejith ss nair

2-Dec-05 6:24

Did that solution served your purpose ? Big Grin | :-D

Re: Remove duplicates from result set

jszpila2-Dec-05 6:27

jszpila

2-Dec-05 6:27

yes it did. thanks again!

-------------------
abort, retry, fail?

Help Me plz abt this dateTime UDF_Function?

majidbhutta1-Dec-05 15:32

majidbhutta

1-Dec-05 15:32

below is the function that compares only time .there are 3 datetime variables.
@StartTime datetime
@EndTime datetime
@DayTime datetime

@StartTime is always less than @EndTime regardless of its
dates.and @DayTime may be any time. We have to calculate
whether @DayTime lies between @StartTime and @EndTime or
not. And on its behalf we return a value.
if @DayTime lies between @StartTime and @EndTime return 1
else return 0.But tested with these values result is not ok.
set @StartTime='12/1/2005 12:30:49 PM'
set @EndTime='12/2/2005 4:30:49 AM'
set @DayTime='2005-12-02 00:56:02.730'
select dbo.udf_IsShiftValid(@StartTime,@EndTime,@DayTime )

it returns 0 where i think it should return 1. plz correct
me where m i wrong.

Actually i have to use this function on CheckInShfit event of the Employee. @StartTime and @EndTime are the time of the shifts that have already been defined in the database very ago. Now whenever Employee ChecksIn we have to confirm wheather he is entering Between his shift timing that have been Defined already in the database, if his CheckInTime (only time not date) @DayTime lies between his @StartTime and @EndTime then he is allowed to enter (i.e 1) else Not(0).
Now keeing in view the baove Scenario, plz guide me
plz modify this function so as it returns 1 if the @DayTime

Lies Between @StartTime And @EndTime else one( BUt keep in

Mind i m required to compare only time not the dates and

@startTime value is always less than @EndTime value)

CREATE FUNCTION dbo.udf_IsShiftValid(@StartTime datetime,

@EndTime datetime,@DayTime datetime)
RETURNS int AS
BEGIN
DECLARE @RtValue AS int

IF
convert(varchar,@DayTime,114)>=
convertvarchar,@StartTime,114) AND

convert(varchar,@DayTime,114)<
(convert(varchar,@EndTime,114)
BEGIN
SET @RtValue=1
END
ELSE
BEGIN
SET @RtValue=0
END

RETURN @RtValue
END

-- modified at 21:40 Thursday 1st December, 2005

Re: Help Me plz abt this dateTime UDF_Function?

sreejith ss nair2-Dec-05 6:47

sreejith ss nair

2-Dec-05 6:47

Eg :

CREATE FUNCTION dbo.udf_IsShiftValid(@StartTime datetime, @EndTime datetime,@DayTime datetime) <br />
<br />
RETURNS INT AS <br />
BEGIN <br />
DECLARE @RET AS INT<br />
<br />
IF<br />
            (convert(varchar,@EndTime,114) < convert(varchar,@DayTime,114)  ) AND  (convert(varchar,@DayTime,114) > convert(varchar,@StartTime,114) )<br />
 SET @RET=1<br />
<br />
ELSE<br />
<br />
SET @RET=0<br />
<br />
<br />
RETURN @RET<br />
END

Please have a look at this URL also.
http://www.databasejournal.com/features/mssql/article.php/3348181[^]

-- modified at 12:47 Friday 2nd December, 2005

OLEDB data source issue

Joshua Lunsford1-Dec-05 13:06

Joshua Lunsford

1-Dec-05 13:06

the following connection string throws an error... how can I have the data source be a network share like so?

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\server\share$;Extended Properties='text;HDR=no;FMT=Delimited(;)';

Database sample

sasan561-Dec-05 9:44

sasan56

1-Dec-05 9:44

Hi, I want to see a complete sample code for a database with C#.NET & SQL server at Windows Base.where i can find it? can anyone send it to me?

-- modified at 17:08 Thursday 1st December, 2005

Re: Database sample

Raj Lal1-Dec-05 10:56

Raj Lal

1-Dec-05 10:56

check this[^]

"Not everything that counts can be counted..."
-Albert Einstein

Re: Database sample

sasan561-Dec-05 10:59

sasan56

1-Dec-05 10:59

Thanks.you gave me a great help!!!!!!!!!!!

Re: Database sample

Raj Lal1-Dec-05 11:16

Raj Lal

1-Dec-05 11:16

don't forget to vote if you like it Smile | :)

"Not everything that counts can be counted..."
-Albert Einstein

crystal report problem

sasan561-Dec-05 9:41

sasan56

1-Dec-05 9:41

I used Crystal Report 10 for created my report and then i added the report to my project.
I opened the report in V.S.2003 and change it, then i saved it in V.S.2003 too.
V.S.2003 told me that it's Crystal Report version is older than my Crystal Report version, but i saved the changed report in it.
Now when i install the project in a client machine , my report don't work.
My language is V.C#.NET & my project is a windows base.
what can i do ,to solve this problem?

datagrid updation

smdbt1-Dec-05 6:46

smdbt

1-Dec-05 6:46

i have created a datagrid on a windows form using vb.net. It is showing the data after retrival from a table. but now i have added a button --update changes and now i want that when the user changes anything in the datagrid it should get updated in the database. how can we do this?

Transfer local sql database to web-server

Rama Sharma1-Dec-05 5:46

Rama Sharma

1-Dec-05 5:46

Once i have created a database in the local sql server, how can i upload it to my website? can I simply copy the two database files to my relevant folder on the web-server (in a manner similar to access database)? Or is there any special method? Is there any detailed article on this?

Secondly, after uploading this database to web-server, how can i update it - locally or online only? can i continue to have a copy in my local sql server, which i can update and again upload the updated version?

thanks in advance

Re: Transfer local sql database to web-server

Farhan Noor Qureshi1-Dec-05 7:15

Farhan Noor Qureshi

1-Dec-05 7:15

1. Easy method would be to take a backup, upload it and restore.

OR

2. Use database management tools like Red Gate tools www.red-gate.com[^]

OR

3. Generate DDL, DML scripts, upload and run it on destination server.

Farhan Noor Qureshi

^{if (this == this) thow this;}

Re: Transfer local sql database to web-server

Rama Sharma1-Dec-05 14:39

Rama Sharma

1-Dec-05 14:39

Thanks for your reply.

But is it possible to restore from a backup when I do not have administrative rights on the web-server? I just have the user-account with dsn information.

Secondly, if I use the DDL / DML scripts, how I can them from my virtual folder on the website?

Thanks again.

Re: Transfer local sql database to web-server

Farhan Noor Qureshi2-Dec-05 6:24

Farhan Noor Qureshi

2-Dec-05 6:24

Rama Sharma wrote:
But is it possible to restore from a backup when I do not have administrative rights on the web-server?

You need necessary permission in database. First check if your hosting company lets you connect vi EM. Then FTP your backup file to your folder and use EM to restore it.

Rama Sharma wrote:
Secondly, if I use the DDL / DML scripts, how I can them from my virtual folder on the website?

Does your host have any web based data management tools? I doubt they'd let you run isql/osql.

Farhan Noor Qureshi

^{if (this == this) thow this;}

get last 7 days inserted Record

imshally811-Dec-05 1:21

imshally81

1-Dec-05 1:21

Hi All,
i have a database, and every time when any row enter , it capture the date and time of insertation,
then does any function or query available, which help to investigate me, today inserted record,yesterday inserted record,last 7 days inserted record,last 30 days inserted record and last year inserted record..
i am using asp.net and MS Access...
Any idea
thanx in advance...
sajjad

-- modified at 7:22 Thursday 1st December, 2005

Re: get last 7 days inserted Record

sreejith ss nair2-Dec-05 6:51

sreejith ss nair

2-Dec-05 6:51

Try to insert a CreatedDate record When ever you try to fire a queary in Datbase.

Re: get last 7 days inserted Record

S Douglas7-Dec-05 0:33

S Douglas

7-Dec-05 0:33

imshally81 wrote:
today inserted record,yesterday inserted record,last 7 days inserted record,last 30 days inserted record and last year inserted record..

You want the last number of days right? How about this?

SELECT tbl.DateColumn
FROM tbl
WHERE tbl.DateColumn Between Date() And DateAdd("d",-7,Date());

ZeePain! wrote:
This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.

thedailywtf.com[^]

How to write a SQL Function to Find Internal Return Rate(IRR) Value ?

pubududilena30-Nov-05 23:17

pubududilena

30-Nov-05 23:17

hi all,
I need to find IRR Value to my Web Application.So Now I need a SQL Function which return IRR Value.

Can any body please help me how to find it using a SQL Function ?

Thanks in advance...

Re: How to write a SQL Function to Find Internal Return Rate(IRR) Value ?

Colin Angus Mackay1-Dec-05 0:24

Colin Angus Mackay

1-Dec-05 0:24

pubududilena wrote:
Internal Return Rate

I don't know what an IRR value is. If you have a calculation for this then it would help.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

VS2005 with SQL Server 2000

vtr_ajr30-Nov-05 21:51

vtr_ajr

30-Nov-05 21:51

Hi
Is there any reason I could not use VS2005 with SQL Server 2000. I have just started at a new organisation and they already have SQL Server 2000 (with no plans to upgrade). They don't, however, have any development tools, so I am looking at using VS2005.

Any input would be greatfully accepted.

Alec

SQL

Robert197430-Nov-05 12:16

Robert1974

30-Nov-05 12:16

I'm in need of some sql help

SELECT P1.name, P2.name , J.text FROM person p1, person p2, job J WHERE P1.persID = J.creatorID AND P2.persID = J.responsibleID AND J.id = 1

the above statement does not return any rows if the job J has null in the responsible field. But I want it to return an empty string for P2.name in that case. Is that possible. Would be thankful for any help!

Re: SQL

Farhan Noor Qureshi30-Nov-05 13:19

Farhan Noor Qureshi

30-Nov-05 13:19

You need an OUTER join. Start with this query.

SELECT     p1.name as p1name, isNULL(p2.name, '') as p2name , J.text<br />
FROM         person p1 INNER JOIN<br />
                      job J ON p1.persID = J.creatorID LEFT OUTER JOIN<br />
                      person p2 ON J.responsibleID = p2.persID<br />
WHERE     (J.id = 1)

Farhan Noor Qureshi

^{if (this == this) thow this;}

Distinct syntax's matter

ventomito30-Nov-05 7:41

ventomito

30-Nov-05 7:41

Hello!
I have a problem to write a query.
this is the query:

<br />
SELECT Description, COUNT(DISTINCT id_stud)<br />
   FROM Student,School<br />
   ecc...<br />
   ecc...<br />

When i run this code an error appear. It says Syntax Error near Distinct...

Any suggestion?
How can i solve?

Thank You
Enrico

VentoEngine corp.
Program your life ^

Re: Distinct syntax's matter

Farhan Noor Qureshi30-Nov-05 9:10

Farhan Noor Qureshi

30-Nov-05 9:10

Post the complete query. You need to have a GROUP BY along with JOINs in your query b/c of an aggregate function.

Farhan Noor Qureshi

^{if (this == this) thow this;}

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.