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...
>
>