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 UNC Share

hi all,

i'm trying to write a script just like "copy database wizard" operation, for detaching, copy and ataching a database from a remote sql server to a local server.

Now the problem is when i try to copy the mdf file from the sql remote server to my local server.

Is it possible to create a UNC share in the remote sql server and delete after with t-sql like the Copy Database wizard does?

hope i made my self clear enought.

regards

Things like this should be planned and not ad-hoc. Though it's possible...

Code Snippet

exec xp_cmdshell 'net share sharename=<drive path>'

Create a new date

Hi,

I want to write a query that returns me the first date of the month...

I wrote this query

SELECT DateAdd(day,- Day(GetDate()) + 1,GetDate())

THis works fine for me, is there any function that build a new date, without using the DateAdd function?

here is another way

select dateadd(mm, datediff(mm, 0, getdate())+0, 0)

and another

select convert(datetime,(convert(varchar(6),getdate(),112) + '01'))

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks

I used the second query :

select convert(datetime,(convert(varchar(6),getdate(),112) + '01'))

Counting number of IO Accesses per DB

Hello,

I am trying to write a VB.NET monitoring application for a MS SQL
server.
For that I need to know the following:
1. How do I count the number of read/write accesses per database on
the server since the creation of the DB?
2. How do I get the last access time of the database ?

Any inputs would be really appreciated.
Thanks in advance.

-SourabhThanks Geoff...

---
Re: Counting number of IO Accesses per DB
From: Geoff N. Hiten
Date Posted: 4/13/2004 3:02:00 PM

The basic building block you are looking for is the T-SQL function
fn_virtualfilestats. You can use write a query, call it from ADO.Net, and
do something nifty to display the results using VB.Net.

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

moharss@.auburn.edu (Sourabh) wrote in message news:<cff778d4.0404131042.1d3dbac0@.posting.google.com>...
> Hello,
> I am trying to write a VB.NET monitoring application for a MS SQL
> server.
> For that I need to know the following:
> 1. How do I count the number of read/write accesses per database on
> the server since the creation of the DB?
> 2. How do I get the last access time of the database ?
> Any inputs would be really appreciated.
> Thanks in advance.
> -Sourabh

Counting number of IO Accesses per DB

Hello,
I am trying to write a VB.NET application to do this:
1. Count the number of Read/Write accesses to a DB on the server and,
2. Get the last access time of the DB since its creation.
Tried looking it over at MSDN...tried googling...but have failed. Any
inputs would be more than valuable.
Thanks.
_SourabhThe basic building block you are looking for is the T-SQL function
fn_virtualfilestats. You can use write a query, call it from ADO.Net, and
do something nifty to display the results using VB.Net.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sourabh" <moharss@.auburn.edu> wrote in message
news:cff778d4.0404131052.6cda5743@.posting.google.com...
> Hello,
> I am trying to write a VB.NET application to do this:
> 1. Count the number of Read/Write accesses to a DB on the server and,
> 2. Get the last access time of the DB since its creation.
> Tried looking it over at MSDN...tried googling...but have failed. Any
> inputs would be more than valuable.
> Thanks.
> _Sourabh|||Thanks Geoff for your feedback...I used the T-SQL function to get what
I wanted. However, I have 2 more questions :
1. Will taking backups also affect the number of IO accesses to a DB ?
If this is true, the number of IO accesses would be proportional to the
size of the DB for reasons of virtual memory paging, maybe ?
2. The number of IO's - what time span does that cover? The life of the
database? The capacity of some log file?
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sourabh Moharil" <moharss@.auburn.edu> wrote in message
news:uw$G9rlIEHA.3092@.TK2MSFTNGP10.phx.gbl...
> Thanks Geoff for your feedback...I used the T-SQL function to get what
> I wanted. However, I have 2 more questions :
> 1. Will taking backups also affect the number of IO accesses to a DB ?
> If this is true, the number of IO accesses would be proportional to the
> size of the DB for reasons of virtual memory paging, maybe ?
Yes, but a full backup will be proportional to the number of extents
allocated in a database plus the size of the transaction log segment that is
included in the backup. . The actual database size is irrelevant.
> 2. The number of IO's - what time span does that cover? The life of the
> database? The capacity of some log file?
Since that SQL instance started up.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanks once again.
1. Is there some way to work around those "irrelevant" IO accesses
becuase of backups and get just the IO accesses from the users ?
2. I get funky timestamps out of the ::fn_virtualstats. Is there some
way to make those timestamps look legible ?
3. What I'm going for here is something that we can use to look at
individual databases and determine what percentage of the servers
resources (disk space, cpu utilization, IO accesses, anything other
metric we might think of later) that database consumes. This may later
be used as a basis for charging some categories of users for hosting
their databases. Are there some guidelines in place that could help me
determine what metrics I should use to evaluate cost of hosting a DB for
the enduser ?
Thanks once again.
I drank What ? : Socrates
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sourabh Moharil" <moharss@.auburn.edu> wrote in message
news:%23rMjwrxIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Thanks once again.
> 1. Is there some way to work around those "irrelevant" IO accesses
> becuase of backups and get just the IO accesses from the users ?
>
IO is IO is IO. All SQL knows is that something wanted to read/write to the
disk. Who, what, and why are beyond the scope of the counters.

> 2. I get funky timestamps out of the ::fn_virtualstats. Is there some
> way to make those timestamps look legible ?
>
Hmm. I don't see any docs on that. I will have to do some more digging and
get back to you.

> 3. What I'm going for here is something that we can use to look at
> individual databases and determine what percentage of the servers
> resources (disk space, cpu utilization, IO accesses, anything other
> metric we might think of later) that database consumes. This may later
> be used as a basis for charging some categories of users for hosting
> their databases. Are there some guidelines in place that could help me
> determine what metrics I should use to evaluate cost of hosting a DB for
> the enduser ?
>
Isn't backup part of the overall 'cost' of maintaining a database? Why
should it be separated out? Trying to create a 'funny money' system for
charging end users based on resource utilization may be more complex than
you like. You might write some broad limits for accounts and charge a flat
fee within those limits. The limits should be very general to keep one
account from abusing the system. IMHO, people won't sign up for complex
billing systems based on resource counters that they cannot control. I know
I wouldn't.

> Thanks once again.
> --
> I drank What ? : Socrates
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanks Geoff...you feedback has been really valuable to us.
_Sourabh
--
I drank What ? : Socrates
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Counting NULL columns?

If I had a basic table with 3 VARCHAR fields; let's say A, B, C

How could I write a query that returns the count of the number of NULL columns for every record in a table?

Ideally, it would be something like:

SELECT
CAST (A IS NULL) AS INTEGER
+ CAST (B IS NULL) AS INTEGER
+ CAST (C IS NULL) AS INTEGER
FROM MyTable

That doesn't work at all. I can't seem to do "IS NULL" in the SELECT area. Should I write a T-SQL user-defined function that takes all three columns as parameters? Would that be performance friendly for large data sets?select 'a is null', count(*) from mytable where a is null
union all
select 'b is null', count(*) from mytable where b is null
union all
select 'c is null', count(*) from mytable where c is null
union all
select 'all are null', count(*) where (a is null or b is null or c is null)|||select sum(case when A is null then 1 else 0 end) as Anulls
, sum(case when B is null then 1 else 0 end) as Bnulls
, sum(case when C is null then 1 else 0 end) as Cnulls
from yourtable|||Rudy, I think he wants to count the nulls for each row:

select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable|||blindman, you are too right

the results of your query, however, are totally useless, as there is nothing to tell you which rows have which numbers of nulls

3
2
0
1
0
0
0
2
0
3
0
2
1

at least my query actually produces something useful

:cool:|||Hey, he only said he wanted the count! Ain't my problem if the project specs are no good... :D

And Roger, yes if you have a lot of columns it might be worthwhile to write a function that returns 1 if a value is Null, and 0 if it is not. Call it "NullBit" or something.

... and make Rudy happy by at least including a primary key in your result set!|||many ways to skin the null|||Rudy, I think he wants to count the nulls for each row:

select case when A is null then 1 else 0 end
+ case when B is null then 1 else 0 end
+ case when C is null then 1 else 0 end as NullValues
from yourtable

Yes, you are right; that is exactly what I wanted. It works perfectly!! Thank you so much!! I'm surprised; no one else in my office could come up with this. I was planning on writing a user-defined function with if statements but this is much more elegant.

Thanks Rudy and blindman!

FYI, I'm using this in a WHERE clause for a a large UPDATE command that merges import data into a production table. I only want to overwrite the existing data if the new data has more non-NULL fields.|||I only want to overwrite the existing data if the new data has more non-NULL fields.
surely the number of non-nulls is of secondary concern

suppose i had a Three Stooges table with this row:

'curly','larry',null

you're saying it's okay to overwrite this with

'tom','dick','harry'

but i've done many merges myself, and i'm sure there's more to your example than just three fields...|||Consider using this instead:

Update A
set A.Stooge1 = coalesce(A.Stooge1, B.Stooge1),
A.Stooge2 = coalesce(A.Stooge2, B.Stooge2),
A.Stooge3 = coalesce(A.Stooge3, B.Stooge3),
..etc..
from A inner join B on A.PKey = B.Pkey

This merges the two datasets together, giving priority to data in table A.|||Follow the blindman, follow the blindman!!!

-PatP|||Only when you are in the dark...|||nice

might also want to add a WHERE clause so you don't unnecessarily update every row, just the ones which actually have changed|||Good point. It's a tough call on what proportion of the rows need to be updated to justify the overhead of the where clause. Maybe for a one-time shot a SELECT INTO followed by renaming the resulting table would be fastest?

Counting No Of Words In a Column

Hi,

for some reason, i had to write a function to count the number of words in a particular column in a table. (pl find the attachment). i would like to know whether there is any other mechanism with which we can count the number of words in a particular column.

for example, if the column data is,'This Is A Test', the function, will return 4.
pl suggest any other efficient strategies to accomplish this

thanksIs this too simple?

DECLARE @.Text AS VarChar(100)
SET @.Text = 'This is a sentence'
PRINT 'Number of words: ' + CAST(LEN(@.Text) - LEN(REPLACE(@.Text, ' ', '')) +1 AS VarChar(3))|||BTW - if it isn't then it is vastly more efficient.|||Is this too simple?

DECLARE @.Text AS VarChar(100)
SET @.Text = 'This is a sentence'
PRINT 'Number of words: ' + CAST(LEN(@.Text) - LEN(REPLACE(@.Text, ' ', '')) +1 AS VarChar(3))

one more qn..

wat if the data contains blank space

e.g. : 'This Is A Sentence '|||Bol

Rtrim(ltrim())|||Bol

Rtrim(ltrim())

Thank U all for the comments...

:)|||A bigger issue would be if the text contains double spaces:
"This is a single line of text. This text contains two sentences separated by two space characters."|||A bigger issue would be if the text contains double spaces:
"This is a single line of text. This text contains two sentences separated by two space characters."
LEN(REPLACE(@.Text, ' ', ' ')) - LEN(REPLACE(REPLACE(@.Text, ' ', ' '), ' ', '')) +1 :)|||You're gonna ask about triple spaces now ain't cha?|||Yup. I am.
To make this robust, you need a loop to eliminate double spaces until none remain. And that means this should be a multi-step function rather than a simple formula.|||Does the OP live in Iceland?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56195&whichpage=1|||... you need a loop ...muthaf*$#%$n loops in a muthaf*$#%$n function?

next you're going to suggest using a cursor, aren't you

:)