Help with CURRENT DATE Query

Hello, I have a table that lists my bank transactions. I have a uniqueid in
the first field and several other fields...one of which is DATE... now, I
want to run a query where my view is based upon the DATE. however I want to
only show transactions that are the same date as the system date (this is to
see how much work my employees have done). Please see my example below, this
code works...
SELECT transid, [date], description, amt, taxamt
FROM dbo.taxtransactions
WHERE ([date] = '9/9/2007')
However, where it has 9/9/2007, I want it to be TODAYS Date
Thanks for your help in advance!WHERE [date] = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);
"SQL Brad" <SQLBrad@.discussions.microsoft.com> wrote in message
news:58DEF545-F6C1-4086-B247-9080CEFBC125@.microsoft.com...
> Hello, I have a table that lists my bank transactions. I have a uniqueid
> in
> the first field and several other fields...one of which is DATE... now,
> I
> want to run a query where my view is based upon the DATE. however I want
> to
> only show transactions that are the same date as the system date (this is
> to
> see how much work my employees have done). Please see my example below,
> this
> code works...
> SELECT transid, [date], description, amt, taxamt
> FROM dbo.taxtransactions
> WHERE ([date] = '9/9/2007')
> However, where it has 9/9/2007, I want it to be TODAYS Date
> Thanks for your help in advance!
>|||Try:
SELECT transid, [date], description, amt, taxamt
FROM dbo.taxtransactions
WHERE
[date] >= convert(char(8), getdate(), 112) and
and [date] < dateadd(day, 1, convert(char(8), getdate(), 112))
go
AMB
"SQL Brad" wrote:
> Hello, I have a table that lists my bank transactions. I have a uniqueid in
> the first field and several other fields...one of which is DATE... now, I
> want to run a query where my view is based upon the DATE. however I want to
> only show transactions that are the same date as the system date (this is to
> see how much work my employees have done). Please see my example below, this
> code works...
> SELECT transid, [date], description, amt, taxamt
> FROM dbo.taxtransactions
> WHERE ([date] = '9/9/2007')
> However, where it has 9/9/2007, I want it to be TODAYS Date
> Thanks for your help in advance!
>|||Aaron...thanks for your help, it worked perfectly!! I also changed the 0 to
a 1 and it went to yesterday....very much appreciated!
"Aaron Bertrand [SQL Server MVP]" wrote:
> WHERE [date] = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);
>
> "SQL Brad" <SQLBrad@.discussions.microsoft.com> wrote in message
> news:58DEF545-F6C1-4086-B247-9080CEFBC125@.microsoft.com...
> > Hello, I have a table that lists my bank transactions. I have a uniqueid
> > in
> > the first field and several other fields...one of which is DATE... now,
> > I
> > want to run a query where my view is based upon the DATE. however I want
> > to
> > only show transactions that are the same date as the system date (this is
> > to
> > see how much work my employees have done). Please see my example below,
> > this
> > code works...
> >
> > SELECT transid, [date], description, amt, taxamt
> > FROM dbo.taxtransactions
> > WHERE ([date] = '9/9/2007')
> >
> > However, where it has 9/9/2007, I want it to be TODAYS Date
> >
> > Thanks for your help in advance!
> >
>
>sql

Help w/ unique identity

Not sure if I have the right term in my Subject, but I have written an
application using VB6, which stores employee transactions in a SQL database.
I had originally designed the app for independent use in a standalone PC in
our remote offices but was then asked to expand its use to our corporate
office and import the transactions from all the locations into one database.
I have an Identity column in my table labeled "TransactionID" with the
Identity Seed and Indentity Increment set to the default of 1 and
automatically increments. The TransactionID number ensures that each
transaction is unique because other pieces of data collected in the table
could be the same such as StoreNumber, EmployeeNumber, etc.
All this is working fine, but now I want to import the transactions from the
remote location(s) into the SQL database at the corporate office. Now, there
is the very real possibility that a TransactionID from any of the remote
locations will match. Plus, when I import the records into the corporate
database, won't the master database want to reassign it a TransactionID as
well?
Since the TransactionID is unique at each store/office level, I was thinking
of formatting the TransactionID to something like "1234.01" where "1234" is
the next row in the table and "01" is the store number. This would be
separated by a decimal point. The store number would never exceed 2 digits
and the corporate office could be "00" or "99".
Is this possible or is there a better way to do this? I need to preserve the
TransactionID from the store/office because I use it to create an audit
trail if a transaction is ever modified.
Thanks,
BarryYou can add StoreID to the table and keep current Identity column as it (int
).
Perayu
"BCS" wrote:

> Not sure if I have the right term in my Subject, but I have written an
> application using VB6, which stores employee transactions in a SQL databas
e.
> I had originally designed the app for independent use in a standalone PC i
n
> our remote offices but was then asked to expand its use to our corporate
> office and import the transactions from all the locations into one databas
e.
> I have an Identity column in my table labeled "TransactionID" with the
> Identity Seed and Indentity Increment set to the default of 1 and
> automatically increments. The TransactionID number ensures that each
> transaction is unique because other pieces of data collected in the table
> could be the same such as StoreNumber, EmployeeNumber, etc.
> All this is working fine, but now I want to import the transactions from t
he
> remote location(s) into the SQL database at the corporate office. Now, the
re
> is the very real possibility that a TransactionID from any of the remote
> locations will match. Plus, when I import the records into the corporate
> database, won't the master database want to reassign it a TransactionID as
> well?
> Since the TransactionID is unique at each store/office level, I was thinki
ng
> of formatting the TransactionID to something like "1234.01" where "1234" i
s
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
> Is this possible or is there a better way to do this? I need to preserve t
he
> TransactionID from the store/office because I use it to create an audit
> trail if a transaction is ever modified.
> Thanks,
> Barry
>
>|||I suggest you add another field in the table called store id
modify the unique constraint or the Pk to a composite (PK) or constraint
a composite pk is composed of at least two or more fields. In that way
you still preserve uniquesness of the data without violation atomicity of
the column
or better known as the domain integrity
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"BCS" wrote:

> Not sure if I have the right term in my Subject, but I have written an
> application using VB6, which stores employee transactions in a SQL databas
e.
> I had originally designed the app for independent use in a standalone PC i
n
> our remote offices but was then asked to expand its use to our corporate
> office and import the transactions from all the locations into one databas
e.
> I have an Identity column in my table labeled "TransactionID" with the
> Identity Seed and Indentity Increment set to the default of 1 and
> automatically increments. The TransactionID number ensures that each
> transaction is unique because other pieces of data collected in the table
> could be the same such as StoreNumber, EmployeeNumber, etc.
> All this is working fine, but now I want to import the transactions from t
he
> remote location(s) into the SQL database at the corporate office. Now, the
re
> is the very real possibility that a TransactionID from any of the remote
> locations will match. Plus, when I import the records into the corporate
> database, won't the master database want to reassign it a TransactionID as
> well?
> Since the TransactionID is unique at each store/office level, I was thinki
ng
> of formatting the TransactionID to something like "1234.01" where "1234" i
s
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
> Is this possible or is there a better way to do this? I need to preserve t
he
> TransactionID from the store/office because I use it to create an audit
> trail if a transaction is ever modified.
> Thanks,
> Barry
>
>|||> Since the TransactionID is unique at each store/office level, I was thinkingd">
> of formatting the TransactionID to something like "1234.01" where "1234" i
s
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
I can not understand well because you are mixing names here.
My recommendation is to add a new column to the transaction table, to store
the [store] from where the transaccion was made. Do not use the formatting
approach (1234.01), then you will have to parse it into a select statement
and this will avoid sql server from considering it as a search argument.
Also, you can not create indexes based on a formula unless you create a
computed column and base the index on this column.
avoid this:
select c1,..., cn
from dbo.[transactions]
where right(transactionid, 2) = '01'
AMB
"BCS" wrote:

> Not sure if I have the right term in my Subject, but I have written an
> application using VB6, which stores employee transactions in a SQL databas
e.
> I had originally designed the app for independent use in a standalone PC i
n
> our remote offices but was then asked to expand its use to our corporate
> office and import the transactions from all the locations into one databas
e.
> I have an Identity column in my table labeled "TransactionID" with the
> Identity Seed and Indentity Increment set to the default of 1 and
> automatically increments. The TransactionID number ensures that each
> transaction is unique because other pieces of data collected in the table
> could be the same such as StoreNumber, EmployeeNumber, etc.
> All this is working fine, but now I want to import the transactions from t
he
> remote location(s) into the SQL database at the corporate office. Now, the
re
> is the very real possibility that a TransactionID from any of the remote
> locations will match. Plus, when I import the records into the corporate
> database, won't the master database want to reassign it a TransactionID as
> well?
> Since the TransactionID is unique at each store/office level, I was thinki
ng
> of formatting the TransactionID to something like "1234.01" where "1234" i
s
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
> Is this possible or is there a better way to do this? I need to preserve t
he
> TransactionID from the store/office because I use it to create an audit
> trail if a transaction is ever modified.
> Thanks,
> Barry
>
>|||I already have a StoreNumber column, so I guess I just need to find some
info on constructing a Composite Constraint. Any good links?
Thanks,
Barry
<Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
news:EF3BF399-EDA4-4652-BC12-4321B73497D2@.microsoft.com...
> I suggest you add another field in the table called store id
> modify the unique constraint or the Pk to a composite (PK) or constraint
> a composite pk is composed of at least two or more fields. In that way
> you still preserve uniquesness of the data without violation atomicity of
> the column
> or better known as the domain integrity
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "BCS" wrote:
>
database.
in
database.
table
the
there
as
thinking
is
digits
the|||
> locations will match. Plus, when I import the records into the corporate
> database, won't the master database want to reassign it a TransactionID as
> well?
>
You didn't mention, but I'm assuming that records in this table are only
inserted at the store level and are consolidated at the corporate level for
accounting and reporting purposes. The corporate data model can have the
same basic column layout as the store level model, but do not use the
identity property for TransactionID, becuase there is no need for it and
would create problem when importing.

> Since the TransactionID is unique at each store/office level, I was
> thinking
> of formatting the TransactionID to something like "1234.01" where "1234"
> is
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
>
Identity columns (in this case TransactionID) must be integers, and you
cannot alter their format.

> Is this possible or is there a better way to do this? I need to preserve
> the
> TransactionID from the store/office because I use it to create an audit
> trail if a transaction is ever modified.
>
The combination of TransactionID and StoreID would make each transaction
unique at the corporate level, and this would be a good candidate for the
primary key at both the store and corporate level. When your primary key is
an identoty key, you should attempt to identify a natural key (ex: StoreID,
RegisterID, TransactionDateTime) and place a unique constraint on it.

> Thanks,
> Barry
>|||He probably means that the primary key should be composed of more than 1
column. For example:
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
(
[StoreNumber],
[TransactionID]
)
"BCS" <bswedeen@.tayloroil.com> wrote in message
news:rT_Oe.36020$es2.553871@.twister.southeast.rr.com...
>I already have a StoreNumber column, so I guess I just need to find some
> info on constructing a Composite Constraint. Any good links?
> Thanks,
> Barry
> <Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
> news:EF3BF399-EDA4-4652-BC12-4321B73497D2@.microsoft.com...
> database.
> in
> database.
> table
> the
> there
> as
> thinking
> is
> digits
> the
>

Help Trigger

i want to audit transactions done to table TOrig.
I created table TAudit same as TOrig in addition to ActionID (1 for
insert, 2 for update, 3 for delete), System Date and System User.
I created triggers on TOrigto insert into TAudit in case of insert,
update & delete.

TOrig contains text column. So i created instead of Trigger like:

Create TRIGGER TrigDelete
ON dbo.TOrig
Instead of Delete
AS
declare @.id int
select @.id=Liq_ID from Deleted

If Not Exists (Select * from TAudit where Liq_ID=@.id and ActionID=3 )
BEGIN
Insert into TAudit
select * , 3, GetDate(), System_USer
from Deleted

Delete from TOrig where Liq_ID=@.id
END
GO

PROBLEM: is that Torig is a detail table to a master table and has a
cascade delete relationship with the master table.
So Instead Trigger does it work. What do I DO??On 4 Oct 2004 06:21:50 -0700, Marie-Christine wrote:

>PROBLEM: is that Torig is a detail table to a master table and has a
>cascade delete relationship with the master table.
>So Instead Trigger does it work. What do I DO??

Hi Marie-Christine,

1. Create the trigger as an AFTER trigger, not as an INSTEAD OF trigger.

2. Rewrite the trigger, so that it will handle multi-rows inserts, updates
and deletes as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||
I would've liked to use For Delete trigger but i can't since there are
text columns in the table. Neither can i use After Trigger. And i can't
retrieve rows from Deleted table when there are text columns. That's why
i used Instead of Delte trigger. But now i'm hung cause Instead of
Delete trigger doesn't work with Cascade. That's why i'm confused now.
There are 2 problems: text columns in the table, and cascade delete. How
do i solve the 2 problems?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
ANyone!!! no solution???

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||MC <anonymous@.discussions.microsoft.com> wrote in message news:<4163a5d8$0$26140$c397aba@.news.newsgroups.ws>...
> ANyone!!! no solution???
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

If you can't use a trigger, you could put the DELETE and the audit
logic into a procedure, then make users and applications use the
procedure for all deletions.

Simon