Create A/R distribution using IF...THEN or CASE

I am trying to write a query in Transact-SQL to create a user view in my SQL
database. I am trying to populate columns for each "aging" category (30, 60,
90, etc), so the correct age receives the amount due, but other columns are
zero. However, I can't find the correct CASE or IF...THEN syntax. It needs t
o
do something equal to the following:
If ServiceDateAge BETWEEN 0 AND 29 Then CurrentDue = PatientDue Else
CurrentDue = 0.
If ServiceDateAge BETWEEN 30 and 59 Then 30DayDue = PatientDue Else 30DayDue
= 0...
etc
I would be grateful for help...Thank you.Try using CASE expressions like the snippet below. See the Books Online for
more info.
SELECT
CASE WHEN ServiceDateAge BETWEEN 0 AND 29 THEN PatientDue ELSE 0 END AS
"CurrentDue",
CASE WHEN ServiceDateAge BETWEEN 30 AND 59 Then PatientDue ELSE 0 END AS
"30DayDue"
etc...
Hope this helps.
Dan Guzman
SQL Server MVP
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:30EBD6C4-AB7F-4F70-93EB-8C68AB5646C0@.microsoft.com...
>I am trying to write a query in Transact-SQL to create a user view in my
>SQL
> database. I am trying to populate columns for each "aging" category (30,
> 60,
> 90, etc), so the correct age receives the amount due, but other columns
> are
> zero. However, I can't find the correct CASE or IF...THEN syntax. It needs
> to
> do something equal to the following:
> If ServiceDateAge BETWEEN 0 AND 29 Then CurrentDue = PatientDue Else
> CurrentDue = 0.
> If ServiceDateAge BETWEEN 30 and 59 Then 30DayDue = PatientDue Else
> 30DayDue
> = 0...
> etc
> I would be grateful for help...Thank you.
>|||Thank you. That should do it. I did not see an example in the on-line books
of using CASE to populate a quantity in a column (thought it may have been
there).
"Dan Guzman" wrote:

> Try using CASE expressions like the snippet below. See the Books Online f
or
> more info.
> SELECT
> CASE WHEN ServiceDateAge BETWEEN 0 AND 29 THEN PatientDue ELSE 0 END A
S
> "CurrentDue",
> CASE WHEN ServiceDateAge BETWEEN 30 AND 59 Then PatientDue ELSE 0 END
AS
> "30DayDue"
> etc...
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:30EBD6C4-AB7F-4F70-93EB-8C68AB5646C0@.microsoft.com...
>
>|||Dan one more question: Let's say that when ServiceDateAge BETWEEN 0 AND 29 I
want to update CurrentPatientDue to PatientDue but at the same time also
update CurrentInsuranceDue to InsuranceDue. Can I achieve this without
writing another full CASE statement?
"Dan Guzman" wrote:

> Try using CASE expressions like the snippet below. See the Books Online f
or
> more info.
> SELECT
> CASE WHEN ServiceDateAge BETWEEN 0 AND 29 THEN PatientDue ELSE 0 END A
S
> "CurrentDue",
> CASE WHEN ServiceDateAge BETWEEN 30 AND 59 Then PatientDue ELSE 0 END
AS
> "30DayDue"
> etc...
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:30EBD6C4-AB7F-4F70-93EB-8C68AB5646C0@.microsoft.com...
>
>|||The important concept here is that SQL CASE is an expression and not a
statement. It simply returns a single value conditionally so you need to
replicate the CASE expression. If you are using SQL 2000, you can
encapsulate CASE in a user-defined scalar function like the example below.
CREATE FUNCTION dbo.AgeAmount
(
LowAge int,
HighAge int,
Age int,
Value int
)
RETURNS int
AS
BEGIN
RETURN
CASE
WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
ELSE 0
END
END
GO
SELECT
dbo.AgeAmount(0, 29, ServiceDateAge, PatientDue) AS "CurrentDue",
dbo.AgeAmount(30, 59, ServiceDateAge, PatientDue) AS "30DayDue",
dbo.AgeAmount(0, 29, ServiceDateAge, CurrentInsuranceDue) AS
"CurrentInsuranceDue"
etc..
Hope this helps.
Dan Guzman
SQL Server MVP
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:79FB0AFA-77E7-46CA-93CA-1B815E514B8F@.microsoft.com...
> Dan one more question: Let's say that when ServiceDateAge BETWEEN 0 AND 29
> I
> want to update CurrentPatientDue to PatientDue but at the same time also
> update CurrentInsuranceDue to InsuranceDue. Can I achieve this without
> writing another full CASE statement?
> "Dan Guzman" wrote:
>|||This opens up a whole new area for me to learn and use. However, my first
attempt is producing an error I don't understand. Instead of declaring the
function every time, I thought I would attempt to create a "User Defined
Function". In the properties dialog I believe I am using your text as follow
s:
CREATE FUNCTION dbo.AgeAmount (LowAge int, HighAge int, Age int, Value int)
RETURNS int AS
BEGIN
(RETURN
CASE
WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
ELSE 0
END)
END
GO
When I try to save this or check the syntax, the error is: "Must declare the
variable @.age". Can you help me with this as well, please?
"Dan Guzman" wrote:

> The important concept here is that SQL CASE is an expression and not a
> statement. It simply returns a single value conditionally so you need to
> replicate the CASE expression. If you are using SQL 2000, you can
> encapsulate CASE in a user-defined scalar function like the example below.
>
> CREATE FUNCTION dbo.AgeAmount
> (
> LowAge int,
> HighAge int,
> Age int,
> Value int
> )
> RETURNS int
> AS
> BEGIN
> RETURN
> CASE
> WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
> ELSE 0
> END
> END
> GO
> SELECT
> dbo.AgeAmount(0, 29, ServiceDateAge, PatientDue) AS "CurrentDue",
> dbo.AgeAmount(30, 59, ServiceDateAge, PatientDue) AS "30DayDue",
> dbo.AgeAmount(0, 29, ServiceDateAge, CurrentInsuranceDue) AS
> "CurrentInsuranceDue"
> etc..
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:79FB0AFA-77E7-46CA-93CA-1B815E514B8F@.microsoft.com...
>
>|||Try running the following using Query Analyzer:
CREATE FUNCTION dbo.AgeAmount (@.LowAge int, @.HighAge int, @.Age int, @.Value
int)
RETURNS int AS
BEGIN
RETURN
CASE
WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
ELSE 0
END
END
Hope this helps.
Dan Guzman
SQL Server MVP
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:6FB45184-BC27-4FC4-8D7F-81208CA173BF@.microsoft.com...
> This opens up a whole new area for me to learn and use. However, my first
> attempt is producing an error I don't understand. Instead of declaring the
> function every time, I thought I would attempt to create a "User Defined
> Function". In the properties dialog I believe I am using your text as
> follows:
> CREATE FUNCTION dbo.AgeAmount (LowAge int, HighAge int, Age int, Value
> int)
> RETURNS int AS
> BEGIN
> (RETURN
> CASE
> WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
> ELSE 0
> END)
> END
> GO
> When I try to save this or check the syntax, the error is: "Must declare
> the
> variable @.age". Can you help me with this as well, please?
> "Dan Guzman" wrote:
>|||Hello Dan,
OK I'm in business. However, I ran the script in Query Analyzer. It ran
successfully, but I could not find my function, even though I'm sure I was
accessing the correct database. So, I opened a new user defined function,
deleted the standard text and inserted your text. This saved with no errors
and I am using the function now. Thank you for introducing me to this
interesting area of SQL 2000.
"Dan Guzman" wrote:

> Try running the following using Query Analyzer:
> CREATE FUNCTION dbo.AgeAmount (@.LowAge int, @.HighAge int, @.Age int, @.Value
> int)
> RETURNS int AS
> BEGIN
> RETURN
> CASE
> WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
> ELSE 0
> END
> END
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:6FB45184-BC27-4FC4-8D7F-81208CA173BF@.microsoft.com...
>
>

Create a counter

I have to create a counter (it is possible to increase or decrease).
It is not record ID, but a part of an automatic generated name.
In case of intensive concurrent use I have a deadlock.
The steps:
1. Launch transaction with isolation level REPEATABLE READ (or SERIALIZABLE
in respect of finish are equal)
2. open the recordset
3. get old number
4. set new number
5. update
6. close record set
7. commit
If I run my program on three computer on the same database a deadlock occur.
How can I isolate the sessions without exceptions?
How can I qued up my clients for its new counter?
I am glad of any idea.
Regards,
Imre Ament"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:F59F319C-190C-4B80-BEAF-FAEA4CFD7E91@.microsoft.com...
> I have to create a counter (it is possible to increase or decrease).
> It is not record ID, but a part of an automatic generated name.
> In case of intensive concurrent use I have a deadlock.
> The steps:
> 1. Launch transaction with isolation level REPEATABLE READ (or
SERIALIZABLE
> in respect of finish are equal)
> 2. open the recordset
> 3. get old number
> 4. set new number
> 5. update
> 6. close record set
> 7. commit
> If I run my program on three computer on the same database a deadlock
occur.
> How can I isolate the sessions without exceptions?
> How can I qued up my clients for its new counter?
>
Yep that's a recipe for a deadlock. Each session gets and holds a read lock
on the table, then each tries to escalate it to an update lock. You need to
get the update lock when you first open the recordset. Then your clients
will serialize properly.
To get an exclusive or update lock with a select statement look at the
UPDLOCK and XLOCK hints.
David|||Never do something like this from the client, always use a stored procedure
and minimize the locking. How about doing something like this instead:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
Andrew J. Kelly SQL MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:F59F319C-190C-4B80-BEAF-FAEA4CFD7E91@.microsoft.com...
> I have to create a counter (it is possible to increase or decrease).
> It is not record ID, but a part of an automatic generated name.
> In case of intensive concurrent use I have a deadlock.
> The steps:
> 1. Launch transaction with isolation level REPEATABLE READ (or
SERIALIZABLE
> in respect of finish are equal)
> 2. open the recordset
> 3. get old number
> 4. set new number
> 5. update
> 6. close record set
> 7. commit
> If I run my program on three computer on the same database a deadlock
occur.
> How can I isolate the sessions without exceptions?
> How can I qued up my clients for its new counter?
> I am glad of any idea.
> Regards,
> Imre Ament
>sql

Create a counter

I have to create a counter (it is possible to increase or decrease).
It is not record ID, but a part of an automatic generated name.
In case of intensive concurrent use I have a deadlock.
The steps:
1. Launch transaction with isolation level REPEATABLE READ (or SERIALIZABLE
in respect of finish are equal)
2. open the recordset
3. get old number
4. set new number
5. update
6. close record set
7. commit
If I run my program on three computer on the same database a deadlock occur.
How can I isolate the sessions without exceptions?
How can I qued up my clients for its new counter?
I am glad of any idea.
Regards,
Imre Ament
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:F59F319C-190C-4B80-BEAF-FAEA4CFD7E91@.microsoft.com...
> I have to create a counter (it is possible to increase or decrease).
> It is not record ID, but a part of an automatic generated name.
> In case of intensive concurrent use I have a deadlock.
> The steps:
> 1. Launch transaction with isolation level REPEATABLE READ (or
SERIALIZABLE
> in respect of finish are equal)
> 2. open the recordset
> 3. get old number
> 4. set new number
> 5. update
> 6. close record set
> 7. commit
> If I run my program on three computer on the same database a deadlock
occur.
> How can I isolate the sessions without exceptions?
> How can I qued up my clients for its new counter?
>
Yep that's a recipe for a deadlock. Each session gets and holds a read lock
on the table, then each tries to escalate it to an update lock. You need to
get the update lock when you first open the recordset. Then your clients
will serialize properly.
To get an exclusive or update lock with a select statement look at the
UPDLOCK and XLOCK hints.
David
|||Never do something like this from the client, always use a stored procedure
and minimize the locking. How about doing something like this instead:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
Andrew J. Kelly SQL MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:F59F319C-190C-4B80-BEAF-FAEA4CFD7E91@.microsoft.com...
> I have to create a counter (it is possible to increase or decrease).
> It is not record ID, but a part of an automatic generated name.
> In case of intensive concurrent use I have a deadlock.
> The steps:
> 1. Launch transaction with isolation level REPEATABLE READ (or
SERIALIZABLE
> in respect of finish are equal)
> 2. open the recordset
> 3. get old number
> 4. set new number
> 5. update
> 6. close record set
> 7. commit
> If I run my program on three computer on the same database a deadlock
occur.
> How can I isolate the sessions without exceptions?
> How can I qued up my clients for its new counter?
> I am glad of any idea.
> Regards,
> Imre Ament
>

CR & LF Problems

I'm trying to concatenate three address fields into one for a SELECT
statement. Below is that part:
CASE WHEN ADDR1 IS NULL
THEN ''
ELSE ADDR1 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR2 IS NULL
THEN ''
ELSE ADDR2 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR3 IS NULL
THEN ''
ELSE ADDR3
END AS Address,
But the results do not have the CR and LF in it. What am I doing
wrong?I don't see a problem here:
create table #tmp (addr1 varchar(20), addr2 varchar(20), addr3 varchar(20))
insert #tmp values ('line 1 field 1', 'line 1 field 2', 'line 1 field 3')
insert #tmp values ('line 2 field 1', 'line 2 field 2', NULL)
insert #tmp values (NULL, 'line 3 field 2', NULL)
SELECT
CASE WHEN ADDR1 IS NULL
THEN ''
ELSE ADDR1 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR2 IS NULL
THEN ''
ELSE ADDR2 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR3 IS NULL
THEN ''
ELSE ADDR3
END AS Address
FROM #tmp
output:
Address
---
line 1 field 1
line 1 field 2
line 1 field 3
line 2 field 1
line 2 field 2
line 3 field 2
P.S. It sure woulda been nice if you had taken the time to construct the
demo code I did! :-))
--
TheSQLGuru
President
Indicium Resources, Inc.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1178224810.009904.225630@.h2g2000hsg.googlegroups.com...
> I'm trying to concatenate three address fields into one for a SELECT
> statement. Below is that part:
> CASE WHEN ADDR1 IS NULL
> THEN ''
> ELSE ADDR1 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR2 IS NULL
> THEN ''
> ELSE ADDR2 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR3 IS NULL
> THEN ''
> ELSE ADDR3
> END AS Address,
> But the results do not have the CR and LF in it. What am I doing
> wrong?
>|||Paul,
If you run this query to return results in a grid in Query Analyzer or the
Management Studio Query editor (depending on version of SQL) the results
appear in a single cell. But if your results return as text you will see
the line breaks.
RLF
"Paul" <pwh777@.hotmail.com> wrote in message
news:1178224810.009904.225630@.h2g2000hsg.googlegroups.com...
> I'm trying to concatenate three address fields into one for a SELECT
> statement. Below is that part:
> CASE WHEN ADDR1 IS NULL
> THEN ''
> ELSE ADDR1 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR2 IS NULL
> THEN ''
> ELSE ADDR2 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR3 IS NULL
> THEN ''
> ELSE ADDR3
> END AS Address,
> But the results do not have the CR and LF in it. What am I doing
> wrong?
>

Coverting mixed case data to UPPERCASE

I am trying to convert all my client first and last names in my table to uppercase. They are currently listed as mixed case. Also I wanted to know what is the best way to force the data to UPPERCASE hwen a end user tries to insert or update the clients name. I am thinking about trying a trigger, but I am unsure how to set it up. Thanks for all the help.upper (COLUMNNAME) ?|||I tried your suggestion and it fixed my first issue, but now I have to figure out how to force the data to be uppercase whenever the user inserts/updates a client name. I would like to do this in a BEFORE trigger. Can anyone suggest a way for me to do that. Thanks.|||ahhhhh...smell the Oracle background...

nope INSTEAD of AND AFTER Triggers...

Have you thought about a constraint?|||User updating the column through the application or through QA ??
if application then
update table_name set column_name = upper(variable_here) will do

if you want a trigger
see instead of triggers in BOL ...

counting problem...

Hi. I have a small problem that i just can't seem to figure out. I'm trying to generate a report for a case management system. The problem I am having is trying to exclude some of these results. Here is my query:

SELECT COUNT(DefendantCase.ProsAtty) AS CountOfProsAtty
FROM DefendantCase LEFT JOIN DefendantEventPros ON DefendantCase.VBKey=DefendantEventPros.VBKey
WHERE DefendantCase.StatusID=17 AND DefendantCase.ProsAtty=55
AND DefendantEventPros.EventDate BETWEEN DATEADD(DAY,-60,GETDATE()) AND GETDATE() AND DefendantEventPros.EventID=9

This query is trying to find the total amount of cases where the statusid=17, the prosatty=55, the date is between today and 60 days ago, and there is an eventid=9.

now, i'm not getting errors in the query itself; it's just that it's inflating the total number. If a case has more than one eventid=9, it will include that extra in the results. I do not want to include those in the results. Does anyone have any suggestions? Thanks!select count(ProsAtty) AS CountOfProsAtty
from DefendantCase
where StatusID=17
and ProsAtty=55
and EventID=9
and exists
( select 937
from DefendantEventPros
where VBKey = DefendantCase.VBKey
and EventDate
between dateadd(day,-60,getdate())
and getdate() )|||I hope I don't show up in that result set :)|||select count(ProsAtty) AS CountOfProsAtty
from DefendantCase
where StatusID=17
and ProsAtty=55
and EventID=9
and exists
( select 937
from DefendantEventPros
where VBKey = DefendantCase.VBKey
and EventDate
between dateadd(day,-60,getdate())
and getdate() )
thank you! that works perfectly!

Counting items

Hi,

I'm trying to include the COUNT(*) value of a sub-query in the results of a parent query. My SQL code is:

SELECT appt.ref, (Case When noteCount > 0 Then 1 Else 0 End) AS notes FROM touchAppointments appt, (SELECT COUNT(*) as noteCount FROM touchNotes WHERE appointment=touchAppointments.ref) note WHERE appt.practitioner=1

This comes up with an error basically saying that 'touchAppointments' isn't valid in the subquery. How can I get this statement to work and return the number of notes that relate to the relevant appointment?

Cheers.Hi!

Would this one help out?

SELECT appt.ref
, (Case When noteCount > 0 Then 1 Else 0 End) AS notes
FROM touchAppointments appt
, (SELECT appointment
, COUNT(*) as noteCount
FROM touchNotes) note
WHERE appt.practitioner=1
AND appt.ref = note.appointment

Greetings,
Carsten|||I would have writen like this
<code>
SELECT appt.ref , count(*)/count(*) AS notes
FROM touchAppointments as appt inner join touchNotes as note
on appt.ref = note.appointment
WHERE appt.practitioner=1 group by appt.ref
</code>|||You would?

count(*)/count(*) is at best going to return only 1s or Nulls, and at worst would return DivZero errors.

There are serveral ways to do this. CarstenK had one, though it is preferable to use a JOIN rather than linking tables in the WHERE clause.

Here are two more methods:

SELECT touchAppointments.ref, cast(count(touchNotes.appointment) as bit) notes
FROM touchAppointments
left outer join touchNotes on touchNotes.appointment = touchAppointments.ref
WHERE touchApointment.practictioner = 1
GROUP BY touchAppointments.ref

SELECT touchAppointments.ref, isnull(notesSubquery.hasnotes, 0) as notes
FROM touchAppointments
left outer join (select distinct touchNotes.appointment, 1 as hasnotes from touchNotes) notesSubquery
on notesSubquery.appointment = touchAppointments.ref
WHERE touchApointment.practictioner = 1|||you are right
count(*)/count(*) is at best going to return only 1s
but how come nulls and div by zero error(even at worst case) with inner join on touchAppointments.ref.|||How do you get the "0" paulbrooks wants to get with his CASE (....)?

Carsten|||Blindman,

Your second solution worked the trick. It returns a 1 for true and 0 for false, which is exactly what I needed.

Thanks a lot, guys.

Paul