Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>While I'd prefer seeing your desired output, perhaps something like this will help get you started:</p> <pre><code>SELECT CONVERT(char(6), AdmissionDate, 112) as CalendarMonth , SUM(DAY(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,AdmissionDate)+1,0)))-DAY(AdmissionDate)) as TotalLengthOfStayEachMonth, Location FROM VISIT GROUP BY CONVERT(char(6), AdmissionDate, 112), Location </code></pre> <p>Adding the Location will possibly give you duplicate CalendarMonths, but I assume that's ok.</p> <p>EDIT -- </p> <p>I played around with this a little more as I realized some months would be missing dates if the DischargeDate was greater than 1 month over the AdmissionDate, and came up with this -- it uses the spt_values table:</p> <pre><code>select CONVERT(char(6), AdmissionDate+v.number, 112) as CalendarMonth, COUNT(*) Days, Location from Visit t inner join master..spt_values v on v.type='P' and v.number &lt;= DATEDIFF(d, AdmissionDate, DischargeDate) group by Location, CONVERT(char(6), AdmissionDate+v.number, 112) order by CONVERT(char(6), AdmissionDate+v.number, 112), Location </code></pre> <p>Here is the <a href="http://sqlfiddle.com/#!3/4cd3f/1" rel="nofollow">SQL Fiddle</a>.</p> <p>-- Another Edit</p> <p>While reviewing another answer, and not really understanding your situation (need that desired output), if your desired output is 2 days in April for your first record, then you just need to add and v.number &lt;> 0 to the join above -- easy enough, just wasn't sure what you were requesting. This should be the simplest solution:</p> <pre><code>select CONVERT(char(6), AdmissionDate+v.number, 112) as CalendarMonth, COUNT(*) Days, Location from Visit t inner join master..spt_values v on v.type='P' and v.number &lt;= DATEDIFF(d, AdmissionDate, DischargeDate) and v.number &lt;&gt; 0 group by Location, CONVERT(char(6), AdmissionDate+v.number, 112) order by CONVERT(char(6), AdmissionDate+v.number, 112), Location </code></pre> <p>More <a href="http://sqlfiddle.com/#!3/4cd3f/2" rel="nofollow">fiddle</a>.</p> <p>Hope some of this helps.</p> <p>Good luck.</p>
    singulars
    1. This table or related slice is empty.
    1. POCalculate Length of Stay grouped by calendar months
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USsgeddes
    1. USsgeddes
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCalculate Length of Stay grouped by calendar months
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COPlease read the question. This will only give the total number of days grouped by admission date. He wants to see the number of days in each month between admission date and discharge date
      singulars
      1. PO
      1. UScha
    2. COSee edits -- This will produce a better performance than using a CTE. Good luck.
      singulars
      1. PO
      1. USsgeddes
    3. COBoth solutions (Cha's and this one) work but this one produce much better performance. That's why I flagged this post as the accepted answer. Thanks to you both.
      singulars
      1. PO
      1. USThracian
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload