Hi,
I have a composite key in VFP like
Padl(empno,4,'0') + DTOS(kdate) + Str(shiftno,1)
I use this index to do a seek to find out if a record exists for a particular employee on a particular date in a particular shift.
How can I do that in sql. I am going to use cursor adaptors.
TIA
Regards,
Ajoy Khaund
Neamati Road
Near Bhogdoi Bridge
Jorhat 785001-21
Assam, India
Cell: +91-9435092287
Fixed: +91-376-2351288
Mail: akhaund@hotmail.com
Blog: http://teaanalyst.blogspot.com/
Downloads: http://cid-40c8c3127198e7ff.skydrive.live.com/browse.aspx/Public
"Walking on water and developing software from a specification are easy if both are frozen."
- Edward V. Berard, "Life-Cycle Approaches"
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/F23BDBF052A642E4B8035BA0BE0F5E37@ajoykcompaq
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On Wed, Sep 21, 2011 at 7:37 AM, Ajoy Khaund <akhaund@gmail.com> wrote:
> Hi,
>
> I have a composite key in VFP like
>
> Padl(empno,4,'0') + DTOS(kdate) + Str(shiftno,1)
>
> I use this index to do a seek to find out if a record exists for a particular employee on a particular date in a particular shift.
>
> How can I do that in sql. I am going to use cursor adaptors.
>
-------------
Do you know how to create indexs in SQL Server tables that span
multiple columns?
CREATE UNIQUE NONCLUSTERED INDEX newKey ON YourTable
(
empNumber,
kDate,
shiftNo
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
In your where clause :
Where empNumber = YourmakeshiftValue
and kDatebetween StartOfDay and EndOfDay
and shftno = 1
As a heads up. This index may never get used if the table is joined
so all of this is mute.
--
Stephen Russell
Unified Health Services
60 Germantown Court
Suite 220
Cordova, TN 38018
Telephone: 888.510.2667
901.246-0159 cell
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMY+LR4iahdRUZBHv4n25ZBgQaQeR4Y_ruPiPec8T-9QGNw@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
I would create three indexes, one for each column, and use a WHERE
clause in SELECT, similar to this one:
WHERE empno = ?m.empno AND kdate = ?m.kdate AND shiftno = ?m.shiftno
Note: using functions in expressions used in where is a performance
killer because those functions get evaluated for each and every
record. Index the columns and use WHERE properly. Character columns
should be defined as VARCHAR(n) and make sure no extra spaces go to
server with the actual values. In VFP this could be easily
accomplished by using SET VARCHARMAPPING. There's one more issue which
you may need to take care of: the ThisForm.txtName.Value expression
contains extra spaces at the end, which need to be trimmed out. This
could be done programatically, before sending the values to server, or
directly from the interface, by setting the Format property to "F".
Hope this helps.
On Wed, Sep 21, 2011 at 3:54 PM, Stephen Russell <srussell705@gmail.com> wrote:
> On Wed, Sep 21, 2011 at 7:37 AM, Ajoy Khaund <akhaund@gmail.com> wrote:
>> Hi,
>>
>> I have a composite key in VFP like
>>
>> Padl(empno,4,'0') + DTOS(kdate) + Str(shiftno,1)
>>
>> I use this index to do a seek to find out if a record exists for a particular employee on a particular date in a particular shift.
>>
>> How can I do that in sql. I am going to use cursor adaptors.
>>
> -------------
>
> Do you know how to create indexs in SQL Server tables that span
> multiple columns?
>
> CREATE UNIQUE NONCLUSTERED INDEX newKey ON YourTable
> (
> empNumber,
> kDate,
> shiftNo
> ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> GO
>
>
> In your where clause :
> Where empNumber = YourmakeshiftValue
> and kDatebetween StartOfDay and EndOfDay
> and shftno = 1
>
> As a heads up. This index may never get used if the table is joined
> so all of this is mute.
>
>
> --
> Stephen Russell
>
> Unified Health Services
> 60 Germantown Court
> Suite 220
> Cordova, TN 38018
>
> Telephone: 888.510.2667
>
> 901.246-0159 cell
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAH=CQDJTOyC5iK14CLDB1DKOFhVs0GUJGVy8XvGT65jaYgcktQ@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On Wed, Sep 21, 2011 at 8:22 AM, Grigore Dolghin <gdolghin@gmail.com> wrote:
> I would create three indexes, one for each column, and use a WHERE
> clause in SELECT, similar to this one:
>
> WHERE empno = ?m.empno AND kdate = ?m.kdate AND shiftno = ?m.shiftno
Those 3 indexes would never be considered for use in SQL Server. It
will do a tablescan all the time.
If the user sees empno as 12334 but the data has it as 0001324 you
will have to append the "0" but I am sure you know that.
Dates in SQL Server are to the second, so [SQLServer] getdate() <>
date() [VFP] All of your dates coming from VFP may be set to midnight?
That would be great for now but you will get burned sooner or later
on it.
Always use a between in date searches
--
Stephen Russell
Unified Health Services
60 Germantown Court
Suite 220
Cordova, TN 38018
Telephone: 888.510.2667
901.246-0159 cell
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMYJbTSu_f9LeCC7A_L=M8ED9D9nVvNo1PUjV0Hh-2pjwbA@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Thanks.
I have a transactions table where before doing an entry I check if that
record exists by empno + date + shiftno. If the seek fails I add the records
else I overwrite existing records. Maybe in SQL server we have to do it
slightly differently.
--------------------------------------------------
From: "Stephen Russell" <srussell705@gmail.com>
Sent: Wednesday, September 21, 2011 6:24 PM
To: <profoxtech@leafe.com>
Subject: Re: Composite Key in Sql Server 2008
> On Wed, Sep 21, 2011 at 7:37 AM, Ajoy Khaund <akhaund@gmail.com> wrote:
>> Hi,
>>
>> I have a composite key in VFP like
>>
>> Padl(empno,4,'0') + DTOS(kdate) + Str(shiftno,1)
>>
>> I use this index to do a seek to find out if a record exists for a
>> particular employee on a particular date in a particular shift.
>>
>> How can I do that in sql. I am going to use cursor adaptors.
>>
> -------------
>
> Do you know how to create indexs in SQL Server tables that span
> multiple columns?
>
> CREATE UNIQUE NONCLUSTERED INDEX newKey ON YourTable
> (
> empNumber,
> kDate,
> shiftNo
> ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> GO
>
>
> In your where clause :
> Where empNumber = YourmakeshiftValue
> and kDatebetween StartOfDay and EndOfDay
> and shftno = 1
>
> As a heads up. This index may never get used if the table is joined
> so all of this is mute.
>
>
> --
> Stephen Russell
>
> Unified Health Services
> 60 Germantown Court
> Suite 220
> Cordova, TN 38018
>
> Telephone: 888.510.2667
>
> 901.246-0159 cell
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/7572B5A7801D405DA3B483F240409EC9@ajoykcompaq
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
@Stephen: SQL Server 2008 has a date field. Would that also keep the time
portion internally.
If I have to run a sql command to find if a record exists that's also fine
by me becoz. I suppose that query will be very fast compared to VFP.
--------------------------------------------------
From: "Grigore Dolghin" <gdolghin@gmail.com>
Sent: Wednesday, September 21, 2011 6:52 PM
To: <profoxtech@leafe.com>
Subject: Re: Composite Key in Sql Server 2008
> I would create three indexes, one for each column, and use a WHERE
> clause in SELECT, similar to this one:
>
> WHERE empno = ?m.empno AND kdate = ?m.kdate AND shiftno = ?m.shiftno
>
> Note: using functions in expressions used in where is a performance
> killer because those functions get evaluated for each and every
> record. Index the columns and use WHERE properly. Character columns
> should be defined as VARCHAR(n) and make sure no extra spaces go to
> server with the actual values. In VFP this could be easily
> accomplished by using SET VARCHARMAPPING. There's one more issue which
> you may need to take care of: the ThisForm.txtName.Value expression
> contains extra spaces at the end, which need to be trimmed out. This
> could be done programatically, before sending the values to server, or
> directly from the interface, by setting the Format property to "F".
>
> Hope this helps.
>
> On Wed, Sep 21, 2011 at 3:54 PM, Stephen Russell <srussell705@gmail.com>
> wrote:
>> On Wed, Sep 21, 2011 at 7:37 AM, Ajoy Khaund <akhaund@gmail.com> wrote:
>>> Hi,
>>>
>>> I have a composite key in VFP like
>>>
>>> Padl(empno,4,'0') + DTOS(kdate) + Str(shiftno,1)
>>>
>>> I use this index to do a seek to find out if a record exists for a
>>> particular employee on a particular date in a particular shift.
>>>
>>> How can I do that in sql. I am going to use cursor adaptors.
>>>
>> -------------
>>
>> Do you know how to create indexs in SQL Server tables that span
>> multiple columns?
>>
>> CREATE UNIQUE NONCLUSTERED INDEX newKey ON YourTable
>> (
>> empNumber,
>> kDate,
>> shiftNo
>> ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
>> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>> GO
>>
>>
>> In your where clause :
>> Where empNumber = YourmakeshiftValue
>> and kDatebetween StartOfDay and EndOfDay
>> and shftno = 1
>>
>> As a heads up. This index may never get used if the table is joined
>> so all of this is mute.
>>
>>
>> --
>> Stephen Russell
>>
>> Unified Health Services
>> 60 Germantown Court
>> Suite 220
>> Cordova, TN 38018
>>
>> Telephone: 888.510.2667
>>
>> 901.246-0159 cell
>>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/3FFBD6F30FC0483687179E1DD9D79D39@ajoykcompaq
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On Wed, Sep 21, 2011 at 10:20 AM, Ajoy Khaund <akhaund@gmail.com> wrote:
> Thanks.
> I have a transactions table where before doing an entry I check if that
> record exists by empno + date + shiftno. If the seek fails I add the records
> else I overwrite existing records. Maybe in SQL server we have to do it
> slightly differently.
--------------------------------
If you add the records in the check I would do all of that inside of a
SPROC I made one below to start you off.
create proc spCheckEmpWorking
@empno varchar(20)
,@wDate datetime
,@shiftno int
as
declare @wSdate datetime , @wEdate datetime
-- figure your date math here to set the scope of time to the shift
they passed it
select id from myTable
where empno = @empno
and wDate between @wSdate and @wEdate
and shiftno = @shifno
if @@rowcount>0
begin
return 1
end
else
begin
-- Do you wnat to insert now? If so do it here
insert into myTable (ColA, ColB, ColC..) values (@empno, ......)
return 1
end
--
Stephen Russell
Unified Health Services
60 Germantown Court
Suite 220
Cordova, TN 38018
Telephone: 888.510.2667
901.246-0159 cell
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMYKoY3gtkPOmAZk4BHoKM0=gVJY=4vhQEAc3Lp6sVittxw@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Something like this:
select * from EmpTable into cursor CX where EMPNO = nEmp ;
and KDATE = dShift and SHIFTNO = nShift
if reccount(CX) = 0
*-- then he's not there
In other words, forget about the VFP concatenations, just ask the
question field by field.
Dan Covill
San Diego
On 09/21/11 05:37, Ajoy Khaund wrote:
> Hi,
>
> I have a composite key in VFP like
>
> Padl(empno,4,'0') + DTOS(kdate) + Str(shiftno,1)
>
> I use this index to do a seek to find out if a record exists for a particular employee on a particular date in a particular shift.
>
> How can I do that in sql. I am going to use cursor adaptors.
>
> TIA
>
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4E7A3720.9070007@san.rr.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Hey, Stephen
Are you sure the indexes won't be used in the WHERE clause I posted? Why it
would do a tablescan?
-----Original Message-----
From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com] On
Behalf Of Stephen Russell
Sent: Wednesday, September 21, 2011 5:24 PM
To: profoxtech@leafe.com
Subject: Re: Composite Key in Sql Server 2008
On Wed, Sep 21, 2011 at 8:22 AM, Grigore Dolghin <gdolghin@gmail.com> wrote:
> I would create three indexes, one for each column, and use a WHERE
> clause in SELECT, similar to this one:
>
> WHERE empno = ?m.empno AND kdate = ?m.kdate AND shiftno = ?m.shiftno
Those 3 indexes would never be considered for use in SQL Server. It will do
a tablescan all the time.
If the user sees empno as 12334 but the data has it as 0001324 you will have
to append the "0" but I am sure you know that.
Dates in SQL Server are to the second, so [SQLServer] getdate() <>
date() [VFP] All of your dates coming from VFP may be set to midnight?
That would be great for now but you will get burned sooner or later on it.
Always use a between in date searches
--
Stephen Russell
Unified Health Services
60 Germantown Court
Suite 220
Cordova, TN 38018
Telephone: 888.510.2667
901.246-0159 cell
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/001b01cc78a7$cdbcdd40$693697c0$@gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.