help with proc

i wrote a procedure which calculates how many minutes a person is late to th
e
office on a particular day
what is the best method for converting it to calculate the minutes late for
the
whole monthHi Raghu
You can sum up all the minutes and show it as a monthly late.
just see if this helps you:
SELECT
NAME, SUM( DATEDIFF(mi, timearrived, officetime ) ),
datepart(month,timearrived)
FROM ATTENDANCE
GROUP BY NAME, datepart(month,timearrived)
please let me know if you have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"raghu veer" wrote:

> i wrote a procedure which calculates how many minutes a person is late to
the
> office on a particular day
> what is the best method for converting it to calculate the minutes late fo
r
> the
> whole month|||Raghu
use datediff(m.date1,date2) in query
date1 or date1 is stored using getdate() default so that the time is also
stored in databse
--
Regards
R.D
--Knowledge gets doubled when shared
"raghu veer" wrote:

> i wrote a procedure which calculates how many minutes a person is late to
the
> office on a particular day
> what is the best method for converting it to calculate the minutes late fo
r
> the
> whole month|||I mean
use datediff('mi'.date1,date2) in query
--
Regards
R.D
--Knowledge gets doubled when shared
"R.D" wrote:
> Raghu
> use datediff(m.date1,date2) in query
> date1 or date1 is stored using getdate() default so that the time is also
> stored in databse
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "raghu veer" wrote:
>

Help with NOT EXISTS query

Newbie here...looking for help with a NOT EXISTS query or suggestions for a
better method.
I have 2 linked tables: CUSTOMERS and ADDRESSES
common fields are CUS_NO and ADR_CD
I need to find records where an address code (ADR_CD) entered into CUSTOMERS
does not have that same ADR_CD existing in the ADRESSES table.

Example:
CUS_NO = 12345
ADR_CD = Ohio01

If the combination of cus_no 12345 and Ohio01 does not exist in the
ADDRESSES table, I need to find them.

Thanks in advance.RDRaider wrote:
> Newbie here...looking for help with a NOT EXISTS query or suggestions for a
> better method.
> I have 2 linked tables: CUSTOMERS and ADDRESSES
> common fields are CUS_NO and ADR_CD
> I need to find records where an address code (ADR_CD) entered into CUSTOMERS
> does not have that same ADR_CD existing in the ADRESSES table.
> Example:
> CUS_NO = 12345
> ADR_CD = Ohio01
> If the combination of cus_no 12345 and Ohio01 does not exist in the
> ADDRESSES table, I need to find them.
> Thanks in advance.

SELECT <select list>
FROM Customers c
WHERE NOT EXISTS (SELECT *
FROM Addresses a
WHERE c.Cus_No = a.Cust_No
AND c.ADR_CD = a.ADR_CD)

Zach|||On Mon, 13 Dec 2004 20:17:45 GMT, RDRaider wrote:

> Newbie here...looking for help with a NOT EXISTS query or suggestions for a
> better method.
> I have 2 linked tables: CUSTOMERS and ADDRESSES
> common fields are CUS_NO and ADR_CD
> I need to find records where an address code (ADR_CD) entered into CUSTOMERS
> does not have that same ADR_CD existing in the ADRESSES table.
> Example:
> CUS_NO = 12345
> ADR_CD = Ohio01
> If the combination of cus_no 12345 and Ohio01 does not exist in the
> ADDRESSES table, I need to find them.
> Thanks in advance.

SELECT Customers.cus_no, Customers.adr_cd
FROM Customers
WHERE NOT EXISTS
(SELECT *
FROM ADDRESSES
WHERE Customers.cus_no = ADDRESSES.cus_no
AND Customers.adr_cd = Addresses.adr_cd )

Alternative method:

SELECT Customers.cus_no, Customers.adr_cd
FROM Customers
LEFT JOIN Addresses
ON Customers.cus_no = Addresses.cus_no
AND Customers.adr_cd = Addresses.adr_cd
WHERE Addresses.cus_no IS NULL|||Thanks for the quick reply. It works! I don't know why I couldn't get the
same results, need to hit the books I guess.

"nib" <individual_news@.nibsworld.com> wrote in message
news:326chiF3i7ns1U1@.individual.net...
> RDRaider wrote:
>> Newbie here...looking for help with a NOT EXISTS query or suggestions for
>> a better method.
>> I have 2 linked tables: CUSTOMERS and ADDRESSES
>> common fields are CUS_NO and ADR_CD
>> I need to find records where an address code (ADR_CD) entered into
>> CUSTOMERS does not have that same ADR_CD existing in the ADRESSES table.
>>
>> Example:
>> CUS_NO = 12345
>> ADR_CD = Ohio01
>>
>> If the combination of cus_no 12345 and Ohio01 does not exist in the
>> ADDRESSES table, I need to find them.
>>
>> Thanks in advance.
> SELECT <select list>
> FROM Customers c
> WHERE NOT EXISTS (SELECT *
> FROM Addresses a
> WHERE c.Cus_No = a.Cust_No
> AND c.ADR_CD = a.ADR_CD)
> Zach

Help with manual Log shipping in SQL Server 2000

I am trying to implement a manual log shipping method in SQL server
2000 (standard edition) but I cannot seem to get the transaction logs
applied. There is a strong chance that I might be doing something
basic wrong. The solution involves two jobs setup in Enterprise
Manager and two stored procedures.
If I do the following:
(a) Database backup as per job (1)
(b) Change the database (via Query Analyser)
(c) Restore logs as per job (2)
I was hoping that the change in the source database would get
refelected in the DR database but to no avail? There are no errors,
just no change in the DR database.
I have two jobs setup in Enterprise Manager:
(1) Database Backup
Checkpoint
backup log Suppliers with truncate_only
backup database Suppliers to disk = '\\drsql\g$\DRSuppliers.BAK' with
init
exec DRSQL.master.dbo.restore_icps_database_backups
(2) Restore logs
backup log suppliers to disk='\\drsql\g$\DRSuppliers_log2.LDF' with
init, no_truncate -- I am not convinced that this step is right?
Note the difference in log file name. If it is the same, then I get
errors.
exec DRSQL.master.dbo.restore_icps_database_logs
The two procs are as follows:
CREATE PROCEDURE dbo.restore_icps_database_backups AS
restore database Suppliers
from disk = 'G:\DRSuppliers.bak'
with
replace
,dbo_only
,standby = 'G:\DRSuppliers_log.ldf'
,move 'Suppliers_data' to 'E:\SQLDATA\MSSQL\Data\Suppliers_Data.mdf'
,move 'Suppliers_Log' to 'F:\SQLDATA\MSSQL\data\Suppiers_Log.ldf'
GO
CREATE PROCEDURE dbo.restore_icps_database_logs AS
restore log Suppliers
from disk = 'G:\DRSuppliers.bak'
with
standby = 'G:\DRSuppliers_log.ldf'
GO
Hi
http://www.sql-server-performance.com/sql_server_log_shipping.asp
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1174892030.962292.33520@.p77g2000hsh.googlegro ups.com...
>I am trying to implement a manual log shipping method in SQL server
> 2000 (standard edition) but I cannot seem to get the transaction logs
> applied. There is a strong chance that I might be doing something
> basic wrong. The solution involves two jobs setup in Enterprise
> Manager and two stored procedures.
> If I do the following:
> (a) Database backup as per job (1)
> (b) Change the database (via Query Analyser)
> (c) Restore logs as per job (2)
> I was hoping that the change in the source database would get
> refelected in the DR database but to no avail? There are no errors,
> just no change in the DR database.
>
> I have two jobs setup in Enterprise Manager:
> (1) Database Backup
> Checkpoint
> backup log Suppliers with truncate_only
> backup database Suppliers to disk = '\\drsql\g$\DRSuppliers.BAK' with
> init
> exec DRSQL.master.dbo.restore_icps_database_backups
> (2) Restore logs
> backup log suppliers to disk='\\drsql\g$\DRSuppliers_log2.LDF' with
> init, no_truncate -- I am not convinced that this step is right?
> Note the difference in log file name. If it is the same, then I get
> errors.
> exec DRSQL.master.dbo.restore_icps_database_logs
> The two procs are as follows:
> CREATE PROCEDURE dbo.restore_icps_database_backups AS
> restore database Suppliers
> from disk = 'G:\DRSuppliers.bak'
> with
> replace
> ,dbo_only
> ,standby = 'G:\DRSuppliers_log.ldf'
> ,move 'Suppliers_data' to 'E:\SQLDATA\MSSQL\Data\Suppliers_Data.mdf'
> ,move 'Suppliers_Log' to 'F:\SQLDATA\MSSQL\data\Suppiers_Log.ldf'
> GO
>
> CREATE PROCEDURE dbo.restore_icps_database_logs AS
> restore log Suppliers
> from disk = 'G:\DRSuppliers.bak'
> with
> standby = 'G:\DRSuppliers_log.ldf'
> GO
>
|||On Mar 26, 5:50 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hihttp://www.sql-server-performance.com/sql_server_log_shipping.asp
> "PromisedOyster" <PromisedOys...@.hotmail.com> wrote in message
> news:1174892030.962292.33520@.p77g2000hsh.googlegro ups.com...
>
>
>
>
>
>
>
>
> - Show quoted text -
Hi
Thats the article I am using, but I think I am having problems with
the backup log stage. I think I may have sussed it out though.
I was not setting log backup device name properly in the proc/job
|||Hi
Have you checked out
http://www.sql-server-performance.com/sql_server_log_shipping.asp which shows
the steps you need to do for this?
"PromisedOyster" wrote:

> I am trying to implement a manual log shipping method in SQL server
> 2000 (standard edition) but I cannot seem to get the transaction logs
> applied. There is a strong chance that I might be doing something
> basic wrong. The solution involves two jobs setup in Enterprise
> Manager and two stored procedures.
> If I do the following:
> (a) Database backup as per job (1)
> (b) Change the database (via Query Analyser)
> (c) Restore logs as per job (2)
If you have not backed up the log after b abd restored it, then it will not
be reflected on your restored database.

> I was hoping that the change in the source database would get
> refelected in the DR database but to no avail? There are no errors,
> just no change in the DR database.
>
John

Help with manual Log shipping in SQL Server 2000

I am trying to implement a manual log shipping method in SQL server
2000 (standard edition) but I cannot seem to get the transaction logs
applied. There is a strong chance that I might be doing something
basic wrong. The solution involves two jobs setup in Enterprise
Manager and two stored procedures.
If I do the following:
(a) Database backup as per job (1)
(b) Change the database (via Query Analyser)
(c) Restore logs as per job (2)
I was hoping that the change in the source database would get
refelected in the DR database but to no avail? There are no errors,
just no change in the DR database.
I have two jobs setup in Enterprise Manager:
(1) Database Backup
Checkpoint
backup log Suppliers with truncate_only
backup database Suppliers to disk = '\\drsql\g$\DRSuppliers.BAK' with
init
exec DRSQL.master.dbo.restore_icps_database_backups
(2) Restore logs
backup log suppliers to disk='\\drsql\g$\DRSuppliers_log2.LDF' with
init, no_truncate -- I am not convinced that this step is right'
Note the difference in log file name. If it is the same, then I get
errors.
exec DRSQL.master.dbo.restore_icps_database_logs
The two procs are as follows:
CREATE PROCEDURE dbo.restore_icps_database_backups AS
restore database Suppliers
from disk = 'G:\DRSuppliers.bak'
with
replace
,dbo_only
,standby = 'G:\DRSuppliers_log.ldf'
,move 'Suppliers_data' to 'E:\SQLDATA\MSSQL\Data\Suppliers_Data.mdf'
,move 'Suppliers_Log' to 'F:\SQLDATA\MSSQL\data\Suppiers_Log.ldf'
GO
CREATE PROCEDURE dbo.restore_icps_database_logs AS
restore log Suppliers
from disk = 'G:\DRSuppliers.bak'
with
standby = 'G:\DRSuppliers_log.ldf'
GOHi
http://www.sql-server-performance.c...og_shipping.asp
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1174892030.962292.33520@.p77g2000hsh.googlegroups.com...
>I am trying to implement a manual log shipping method in SQL server
> 2000 (standard edition) but I cannot seem to get the transaction logs
> applied. There is a strong chance that I might be doing something
> basic wrong. The solution involves two jobs setup in Enterprise
> Manager and two stored procedures.
> If I do the following:
> (a) Database backup as per job (1)
> (b) Change the database (via Query Analyser)
> (c) Restore logs as per job (2)
> I was hoping that the change in the source database would get
> refelected in the DR database but to no avail? There are no errors,
> just no change in the DR database.
>
> I have two jobs setup in Enterprise Manager:
> (1) Database Backup
> Checkpoint
> backup log Suppliers with truncate_only
> backup database Suppliers to disk = '\\drsql\g$\DRSuppliers.BAK' with
> init
> exec DRSQL.master.dbo.restore_icps_database_backups
> (2) Restore logs
> backup log suppliers to disk='\\drsql\g$\DRSuppliers_log2.LDF' with
> init, no_truncate -- I am not convinced that this step is right'
> Note the difference in log file name. If it is the same, then I get
> errors.
> exec DRSQL.master.dbo.restore_icps_database_logs
> The two procs are as follows:
> CREATE PROCEDURE dbo.restore_icps_database_backups AS
> restore database Suppliers
> from disk = 'G:\DRSuppliers.bak'
> with
> replace
> ,dbo_only
> ,standby = 'G:\DRSuppliers_log.ldf'
> ,move 'Suppliers_data' to 'E:\SQLDATA\MSSQL\Data\Suppliers_Data.mdf'
> ,move 'Suppliers_Log' to 'F:\SQLDATA\MSSQL\data\Suppiers_Log.ldf'
> GO
>
> CREATE PROCEDURE dbo.restore_icps_database_logs AS
> restore log Suppliers
> from disk = 'G:\DRSuppliers.bak'
> with
> standby = 'G:\DRSuppliers_log.ldf'
> GO
>|||On Mar 26, 5:50 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hihttp://www.sql-server-performance.com/sql_server_log_shipping.asp
> "PromisedOyster" <PromisedOys...@.hotmail.com> wrote in message
> news:1174892030.962292.33520@.p77g2000hsh.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Hi
Thats the article I am using, but I think I am having problems with
the backup log stage. I think I may have sussed it out though.
I was not setting log backup device name properly in the proc/job|||Hi
Have you checked out
http://www.sql-server-performance.c...og_shipping.asp which show
s
the steps you need to do for this?
"PromisedOyster" wrote:

> I am trying to implement a manual log shipping method in SQL server
> 2000 (standard edition) but I cannot seem to get the transaction logs
> applied. There is a strong chance that I might be doing something
> basic wrong. The solution involves two jobs setup in Enterprise
> Manager and two stored procedures.
> If I do the following:
> (a) Database backup as per job (1)
> (b) Change the database (via Query Analyser)
> (c) Restore logs as per job (2)
If you have not backed up the log after b abd restored it, then it will not
be reflected on your restored database.

> I was hoping that the change in the source database would get
> refelected in the DR database but to no avail? There are no errors,
> just no change in the DR database.
>
John

Help with manual Log shipping in SQL Server 2000

I am trying to implement a manual log shipping method in SQL server
2000 (standard edition) but I cannot seem to get the transaction logs
applied. There is a strong chance that I might be doing something
basic wrong. The solution involves two jobs setup in Enterprise
Manager and two stored procedures.
If I do the following:
(a) Database backup as per job (1)
(b) Change the database (via Query Analyser)
(c) Restore logs as per job (2)
I was hoping that the change in the source database would get
refelected in the DR database but to no avail? There are no errors,
just no change in the DR database.
I have two jobs setup in Enterprise Manager:
(1) Database Backup
Checkpoint
backup log Suppliers with truncate_only
backup database Suppliers to disk = '\\drsql\g$\DRSuppliers.BAK' with
init
exec DRSQL.master.dbo.restore_icps_database_backups
(2) Restore logs
backup log suppliers to disk='\\drsql\g$\DRSuppliers_log2.LDF' with
init, no_truncate -- I am not convinced that this step is right'
Note the difference in log file name. If it is the same, then I get
errors.
exec DRSQL.master.dbo.restore_icps_database_logs
The two procs are as follows:
CREATE PROCEDURE dbo.restore_icps_database_backups AS
restore database Suppliers
from disk = 'G:\DRSuppliers.bak'
with
replace
,dbo_only
,standby = 'G:\DRSuppliers_log.ldf'
,move 'Suppliers_data' to 'E:\SQLDATA\MSSQL\Data\Suppliers_Data.mdf'
,move 'Suppliers_Log' to 'F:\SQLDATA\MSSQL\data\Suppiers_Log.ldf'
GO
CREATE PROCEDURE dbo.restore_icps_database_logs AS
restore log Suppliers
from disk = 'G:\DRSuppliers.bak'
with
standby = 'G:\DRSuppliers_log.ldf'
GOHi
http://www.sql-server-performance.com/sql_server_log_shipping.asp
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1174892030.962292.33520@.p77g2000hsh.googlegroups.com...
>I am trying to implement a manual log shipping method in SQL server
> 2000 (standard edition) but I cannot seem to get the transaction logs
> applied. There is a strong chance that I might be doing something
> basic wrong. The solution involves two jobs setup in Enterprise
> Manager and two stored procedures.
> If I do the following:
> (a) Database backup as per job (1)
> (b) Change the database (via Query Analyser)
> (c) Restore logs as per job (2)
> I was hoping that the change in the source database would get
> refelected in the DR database but to no avail? There are no errors,
> just no change in the DR database.
>
> I have two jobs setup in Enterprise Manager:
> (1) Database Backup
> Checkpoint
> backup log Suppliers with truncate_only
> backup database Suppliers to disk = '\\drsql\g$\DRSuppliers.BAK' with
> init
> exec DRSQL.master.dbo.restore_icps_database_backups
> (2) Restore logs
> backup log suppliers to disk='\\drsql\g$\DRSuppliers_log2.LDF' with
> init, no_truncate -- I am not convinced that this step is right'
> Note the difference in log file name. If it is the same, then I get
> errors.
> exec DRSQL.master.dbo.restore_icps_database_logs
> The two procs are as follows:
> CREATE PROCEDURE dbo.restore_icps_database_backups AS
> restore database Suppliers
> from disk = 'G:\DRSuppliers.bak'
> with
> replace
> ,dbo_only
> ,standby = 'G:\DRSuppliers_log.ldf'
> ,move 'Suppliers_data' to 'E:\SQLDATA\MSSQL\Data\Suppliers_Data.mdf'
> ,move 'Suppliers_Log' to 'F:\SQLDATA\MSSQL\data\Suppiers_Log.ldf'
> GO
>
> CREATE PROCEDURE dbo.restore_icps_database_logs AS
> restore log Suppliers
> from disk = 'G:\DRSuppliers.bak'
> with
> standby = 'G:\DRSuppliers_log.ldf'
> GO
>|||On Mar 26, 5:50 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hihttp://www.sql-server-performance.com/sql_server_log_shipping.asp
> "PromisedOyster" <PromisedOys...@.hotmail.com> wrote in message
> news:1174892030.962292.33520@.p77g2000hsh.googlegroups.com...
>
> >I am trying to implement a manual log shipping method in SQL server
> > 2000 (standard edition) but I cannot seem to get the transaction logs
> > applied. There is a strong chance that I might be doing something
> > basic wrong. The solution involves two jobs setup in Enterprise
> > Manager and two stored procedures.
> > If I do the following:
> > (a) Database backup as per job (1)
> > (b) Change the database (via Query Analyser)
> > (c) Restore logs as per job (2)
> > I was hoping that the change in the source database would get
> > refelected in the DR database but to no avail? There are no errors,
> > just no change in the DR database.
> > I have two jobs setup in Enterprise Manager:
> > (1) Database Backup
> > Checkpoint
> > backup log Suppliers with truncate_only
> > backup database Suppliers to disk = '\\drsql\g$\DRSuppliers.BAK' with
> > init
> > exec DRSQL.master.dbo.restore_icps_database_backups
> > (2) Restore logs
> > backup log suppliers to disk='\\drsql\g$\DRSuppliers_log2.LDF' with
> > init, no_truncate -- I am not convinced that this step is right'
> > Note the difference in log file name. If it is the same, then I get
> > errors.
> > exec DRSQL.master.dbo.restore_icps_database_logs
> > The two procs are as follows:
> > CREATE PROCEDURE dbo.restore_icps_database_backups AS
> > restore database Suppliers
> > from disk = 'G:\DRSuppliers.bak'
> > with
> > replace
> > ,dbo_only
> > ,standby = 'G:\DRSuppliers_log.ldf'
> > ,move 'Suppliers_data' to 'E:\SQLDATA\MSSQL\Data\Suppliers_Data.mdf'
> > ,move 'Suppliers_Log' to 'F:\SQLDATA\MSSQL\data\Suppiers_Log.ldf'
> > GO
> > CREATE PROCEDURE dbo.restore_icps_database_logs AS
> > restore log Suppliers
> > from disk = 'G:\DRSuppliers.bak'
> > with
> > standby = 'G:\DRSuppliers_log.ldf'
> > GO- Hide quoted text -
> - Show quoted text -
Hi
Thats the article I am using, but I think I am having problems with
the backup log stage. I think I may have sussed it out though.
I was not setting log backup device name properly in the proc/job|||Hi
Have you checked out
http://www.sql-server-performance.com/sql_server_log_shipping.asp which shows
the steps you need to do for this?
"PromisedOyster" wrote:
> I am trying to implement a manual log shipping method in SQL server
> 2000 (standard edition) but I cannot seem to get the transaction logs
> applied. There is a strong chance that I might be doing something
> basic wrong. The solution involves two jobs setup in Enterprise
> Manager and two stored procedures.
> If I do the following:
> (a) Database backup as per job (1)
> (b) Change the database (via Query Analyser)
> (c) Restore logs as per job (2)
If you have not backed up the log after b abd restored it, then it will not
be reflected on your restored database.
> I was hoping that the change in the source database would get
> refelected in the DR database but to no avail? There are no errors,
> just no change in the DR database.
>
John