Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You have to work backwards. In order to get rows for dates that don't exist, you need to outer join them to rows that do have those dates. In order to outer join, you need to have a sequence to join to. Since you don't have a sequence, you need to create one.</p> <p>To create that sequence, you have two options:</p> <ul> <li>Create a static date sequence and store it in a permanent table (Larry's answer); or</li> <li>Use an existing numeric sequence (such as spt_values) to create one on the fly.</li> </ul> <p>Let's assume you want the flexibility of the second approach. Here's a common snippet I use for things like that:</p> <pre><code>SELECT DATEADD(DAY, v.number, @fromdate) FROM master.dbo.spt_values v WHERE v.type = 'P' AND v.number &lt;= DATEDIFF(DAY, @fromdate, @todate) </code></pre> <p>Now just toss that in a CTE and join it:</p> <pre><code>WITH Dates_CTE (dt) AS ( -- // Paste the snippet above in here ) SELECT d.dt AS paymentdate, ISNULL(SUM(p.paymentamount), 0) AS subtotal FROM Dates_CTE d LEFT JOIN tblpayment p ON p.paymentdate = d.dt GROUP BY d.dt ORDER BY d.dt </code></pre> <p>(Update: I left out the WHERE clause in the main query because it's technically handled by the the join, but in some instances you might get better performance by leaving it in)</p> <p>As for the currency conversion, look up the syntax for PIVOT.</p> <hr> <p>Update on PIVOT: You should be able to just enclose that entire query in parentheses, then go:</p> <pre><code>SELECT paymentdate, [Euro] AS euroamount, [Pound] as poundamount FROM ( -- // Insert the full query from above in here ) p PIVOT ( SUM(subtotal) FOR paymentcurrency IN ([Euro], [Pound]) ) AS pvt </code></pre> <p>Hard to verify without knowing exactly what kind of data is in there, but try that as a starting point.</p>
    singulars
    1. This table or related slice is empty.
    1. POsql query to get daily payments for a month even if no payments on a given day
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAaronaught
    1. USAaronaught
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POsql query to get daily payments for a month even if no payments on a given day
      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. VTAcceptedByOriginator
    1. COgreat, that works!! thanks so much. I was not aware of the spt_values , what else can be done with it ? And any ideas on separating the currencies out, hopefully thats easier than this.
      singulars
      1. PO
      1. USAnuj Gakhar
    2. COI am trying the PIVOT syntax but never tried it before. I cant get the two to work together. Any ideas?
      singulars
      1. PO
      1. USAnuj Gakhar
    3. COWell, spt_values has all kinds of fun stuff, for example, a list of languages/LCIDs, but I mostly use it for sequences. The performance cost is usually minuscule, but others here are correct in that you should probably have a persistent date table if you do a lot of these kinds of queries. But spt_values is great for when you need to rattle off something fast or if you don't have control over the database schema.
      singulars
      1. PO
      1. USAaronaught
 

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