Positive/Negative with ROWLOCK

When you want to use a ROWLOCK?
When you not want to user a ROWLOCK?
Thank You,Very rarely!
I've used that hint only twice, temporarily when diagnosing deadlocks. I
eventually removed it in both cases.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:DDD23068-06F5-4103-A85D-98CC2452A065@.microsoft.com...
> When you want to use a ROWLOCK?
> When you not want to user a ROWLOCK?
> Thank You,

Positive/Negative with ROWLOCK

When you want to use a ROWLOCK?
When you not want to user a ROWLOCK?
Thank You,
Very rarely!
I've used that hint only twice, temporarily when diagnosing deadlocks. I
eventually removed it in both cases.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:DDD23068-06F5-4103-A85D-98CC2452A065@.microsoft.com...
> When you want to use a ROWLOCK?
> When you not want to user a ROWLOCK?
> Thank You,

Positive/Negative with ROWLOCK

When you want to use a ROWLOCK?
When you not want to user a ROWLOCK?
Thank You,Very rarely!
I've used that hint only twice, temporarily when diagnosing deadlocks. I
eventually removed it in both cases.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:DDD23068-06F5-4103-A85D-98CC2452A065@.microsoft.com...
> When you want to use a ROWLOCK?
> When you not want to user a ROWLOCK?
> Thank You,

Positive Pay Files in SSRS

I have been tasked with creating a Positive Pay (also known as Safe Pay) file
using SSRS. I was wondering if anyone here has had any experience doing this
or has any idea on the feasibility of this project. I am specifically
wondering whether SSRS is suitable for this type of task.
Thanks
Zack GallingerFirst, I haven't ever heard of Positive Pay so I googled it:"Positive Pay Is
a Way To Successfully Combat Check Fraud
Banks and large businesses are protecting themselves and are promoting a
technique called positive pay as a method of fighting check fraud artists.
Generally speaking, positive pay works like this:
a.. 1. Using accounting and database software, a company regularly sends
its bank a positive pay file that lists all the checks written against that
company's account(s). That file includes a record of each check's issue
date, amount, check number/account and payee name.
b.. 2. When a check reaches the bank for payment, the bank compares the
check against the positive pay file. Any discrepancies in a check's
information trigger a flag that the check in question may have been altered.
c.. 3. The bank notifies its corporate customer that the discrepancy has
been noted and asks the company to verify the authenticity of that check. "
Assuming you can create the query that gets the data then the issue is what
format the bank wants the data. It is very easy to create a CSV file.
You might find a SSIS job would be easier if the data format is complicated.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Zack" <Zack@.discussions.microsoft.com> wrote in mes
sage news:90685B31-1551-4F34-BF3A-A63D0DF181F5@.microsoft.com...
>I have been tasked with creating a Positive Pay (also known as Safe Pay)
>file
> using SSRS. I was wondering if anyone here has had any experience doing
> this
> or has any idea on the feasibility of this project. I am specifically
> wondering whether SSRS is suitable for this type of task.
> Thanks
> Zack Gallinger

positive and negative signs

i have account_entries table which has the following fields:

debit_or_credit

entry_amount

and I want when inserting every field to check if the inserted row was assigned to (D)ebit and (C)redit it should change the entry_amount accoridanly.

maybe something like trigger? plz help...

Are you sure you want to change the value? It could be better to have a calculated field like "case when debit_or_credit = 'D' then entry_amount else -entry_amount end as effective_amount"

Rob|||

I think it's better to keep the credit as - and debit as + in the database otherwise i have to place check routines everwhere, all screen and reports..

|||

A trigger could be written, but I would not personally want to do it that way. I would prefer to either do what Rob suggested or have a calculated column that calculated whether it was a debit or a credit. :

create table stuff
(
value decimal(10,2),
debit_or_credit as (case when value >= 0 then 'Credit' else 'Debit' end)
)
go
insert into stuff (value)
select 1.00
union all
select 2.00
union all
select -2.00
union all
select -100.00
go
select *
from stuff
go
Returns:

value debit_or_credit
- -
1.00 Credit
2.00 Credit
-2.00 Debit
-100.00 Debit

This way NO chance of invalid data...

|||But don't you actually want:

create table stuff
(
entry_amount decimal(10,2),
debit_or_credit char(1),
effective_value as (case when debit_or_credit = 'D' then entry_amount else -entry_amount end)
);
go
insert into stuff values (50,'D');
insert into stuff values (50,'C');
select * from stuff;
/* Results */
entry_amount debit_or_credit effective_value

50.00 D 50.00
50.00 C -50.00

Then you can sum up 'effective_value' (restricting to date-ranges, or whatever) to see the net income/loss. And of course, there are times when you may have a negative entry_amount (for example, if you have an adjustment of depreciation). That should be counted as negative in entry_amount, but could translate to a positive amount in effective_value.

Either way though, a calculated field is the way to go, as I hope Louis and my examples have shown.

Rob|||

i have one more non-IT question plz..

I have assigned the visit fees as expense, visit payment as income

what about the discount? will be positive or negative? income or expense?

|||I'm hoping I've answered your question correctly - I'm not entirely sure of the situation. Here I'm assuming you have revenue which is paid to you, but some people may have a discount, which reduces how much you charge them.

So it depends on whether you count discount as an expense, or a reduction in revenue. I think you should do it as an expense, so that you can clearly see what your discount expense is - but other people might do that differently.

And yes, this is an area where you could potentially have a negative amount.

Rob

positive and negative signs

i have account_entries table which has the following fields:

debit_or_credit

entry_amount

and I want when inserting every field to check if the inserted row was assigned to (D)ebit and (C)redit it should change the entry_amount accoridanly.

maybe something like trigger? plz help...

Are you sure you want to change the value? It could be better to have a calculated field like "case when debit_or_credit = 'D' then entry_amount else -entry_amount end as effective_amount"

Rob|||

I think it's better to keep the credit as - and debit as + in the database otherwise i have to place check routines everwhere, all screen and reports..

|||

A trigger could be written, but I would not personally want to do it that way. I would prefer to either do what Rob suggested or have a calculated column that calculated whether it was a debit or a credit. :

create table stuff
(
value decimal(10,2),
debit_or_credit as (case when value >= 0 then 'Credit' else 'Debit' end)
)
go
insert into stuff (value)
select 1.00
union all
select 2.00
union all
select -2.00
union all
select -100.00
go
select *
from stuff
go
Returns:

value debit_or_credit
- -
1.00 Credit
2.00 Credit
-2.00 Debit
-100.00 Debit

This way NO chance of invalid data...

|||But don't you actually want:

create table stuff
(
entry_amount decimal(10,2),
debit_or_credit char(1),
effective_value as (case when debit_or_credit = 'D' then entry_amount else -entry_amount end)
);
go
insert into stuff values (50,'D');
insert into stuff values (50,'C');
select * from stuff;
/* Results */
entry_amount debit_or_credit effective_value

50.00 D 50.00
50.00 C -50.00

Then you can sum up 'effective_value' (restricting to date-ranges, or whatever) to see the net income/loss. And of course, there are times when you may have a negative entry_amount (for example, if you have an adjustment of depreciation). That should be counted as negative in entry_amount, but could translate to a positive amount in effective_value.

Either way though, a calculated field is the way to go, as I hope Louis and my examples have shown.

Rob|||

i have one more non-IT question plz..

I have assigned the visit fees as expense, visit payment as income

what about the discount? will be positive or negative? income or expense?

|||I'm hoping I've answered your question correctly - I'm not entirely sure of the situation. Here I'm assuming you have revenue which is paid to you, but some people may have a discount, which reduces how much you charge them.

So it depends on whether you count discount as an expense, or a reduction in revenue. I think you should do it as an expense, so that you can clearly see what your discount expense is - but other people might do that differently.

And yes, this is an area where you could potentially have a negative amount.

Rob