• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 622
  • Last Modified:

Decimal from datediff and Sum

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
ethanjohnsons
Asked:
ethanjohnsons
3 Solutions
 
CIC AdminCommented:
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

You can read more about COMPUTE here : http://msdn2.microsoft.com/en-us/library/ms181708.aspx

As for adding a tenth decimal to your datediff, change :

   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
 
ethanjohnsonsAuthor Commented:
datediff returns:

343
65
145
.....

If you divide by 30, it shoud return the decimals.  How do you get the decimals?
0
 
CIC AdminCommented:
Sorry, was totally overlooking the division by 30.  

Try this :

convert(numeric(7,1), convert(float, datediff (dd, AssessmentDate, DateDischarged))/30 )
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
LowfatspreadCommented:
convert(decimal(6,1), datediff (dd, AssessmentDate, DateDischarged))/30.0
0
 
imran_fastCommented:
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
 
ethanjohnsonsAuthor Commented:
great!  works fine.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now