x
Solved

# Decimal from datediff and Sum

Posted on 2006-07-01
Medium Priority
624 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

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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.