[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Decimal from datediff and Sum

Posted on 2006-07-01
Medium Priority
615 Views
I have:

qry1)

select
Disposition,
AssessmentDate,
DateDischarged,
datediff (dd, AssessmentDate, DateDischarged)/30
from Demo
where (DateDischarged is not null and DateDischarged != '')

qry2)

select Disposition, count (Disposition)
from Demo
where (DateDischarged is not null and DateDischarged != '')
group by Disposition

How do you combine these two so that  I dispaly all of the contents from qry 1 and put the count of each 'Disposition' group at the bottom?

for instance, it will looks like:

ALF      2002-02-05       2003-01-14       11
ALF      2004-05-12       2004-07-16       2
ALF      2004-11-05       2005-03-30       4
ALF      2004-11-05       2005-03-30       4

COUNT                           4

Dischared      2004-10-01       2004-10-22       0
Dischared      2004-12-09       2005-03-17       3
Dischared      2004-04-30       2005-09-30       17
Dischared      2005-01-13       2005-04-11       2
Dischared      2005-02-28       2005-09-30       7
Dischared      2005-04-25       2005-08-19       3
Dischared      2005-04-25       2005-08-19       3

COUNT                           8

Also, on datediff (dd, AssessmentDate, DateDischarged)/30, I want to have 10th decimal from the calculation.
0
Question by:ethanjohnsons
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 5

Expert Comment

ID: 17024345
You would get more control of your output to program this in the report generator of your application, e.g. MS Access, Crystal, etc.

If you want to do this solely in Transact SQL, you may want to look at using COMPUTE.

You can try something like :

select
Disposition,
AssessmentDate,
DateDischarged,
datediff (dd, AssessmentDate, DateDischarged)/30
from Demo
where (DateDischarged is not null and DateDischarged != '')
order by Disposition
compute count(disposition) by disposition

datediff (dd, AssessmentDate, DateDischarged)/30

to

convert (decimal(6,1), datediff (dd, AssessmentDate, DateDischarged)/30 )

But keep in mind that it will always be xxx.0 since days will always be in whole numbers.
0

Author Comment

ID: 17024629
datediff returns:

343
65
145
.....

If you divide by 30, it shoud return the decimals.  How do you get the decimals?
0

LVL 5

Accepted Solution

CIC Admin earned 1400 total points
ID: 17024882
Sorry, was totally overlooking the division by 30.

Try this :

convert(numeric(7,1), convert(float, datediff (dd, AssessmentDate, DateDischarged))/30 )
0

LVL 50

Assisted Solution

ID: 17024947
convert(decimal(6,1), datediff (dd, AssessmentDate, DateDischarged))/30.0
0

LVL 28

Assisted Solution

imran_fast earned 400 total points
ID: 17026040
try this

select
Disposition,
AssessmentDate,
DateDischarged,
cast (cast(datediff (dd, AssessmentDate, DateDischarged) as decimal(6,1))/30 as decimal(6,1))
from Demo
where (DateDischarged is not null and DateDischarged != '')
order by Disposition
compute count(disposition) by disposition
0

Author Comment

ID: 17028514
great!  works fine.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
###### Suggested Courses
Course of the Month13 days, 21 hours left to enroll