Databases
StackOverflow2013
Postsdbo
POSQL Server 2008 left join where clause based on max date
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server 2008 left join where clause based on max date
    primarykey
    data
    text
    <p>Got some help the other day on this and now really want to make a change for the better</p> <p>Here is code</p> <pre><code>select machines.serialnum, DRPS.assetnumber, DRPS.MOCALCSUM, DRPS.MICALCSUM, DRPS.COCALCSUM, DRPS.CICALCSUM, ISSUED.TotalIssued, REDEEMED.TotalRedeemed,drps.dropdate1,drps.dropdate2 from (select serialnum from machineinfo) as machines LEFT JOIN (select assetnumber, min(dropdate) as [dropdate1], max(dropdate) as [dropdate2], sum(mocalc) AS [MOCALCSUM], sum(micalc) AS [MICALCSUM], sum(cocalc) AS [COCALCSUM],sum(cicalc) AS [CICALCSUM] from drops where dropdate &gt; '09/04/2012' and dropdate &lt; dateadd(hour, -0, getdate()) GROUP BY assetnumber) AS DRPS on machines.serialnum = drps.assetnumber LEFT JOIN (select snissued, cast(sum(amount) as money) / 100 AS [TotalIssued] from tickets where dateissued &gt; '09/04/2012' and dateissued &lt; dateadd(hour, 0, getdate()) group by snissued) AS ISSUED ON machines.serialnum = ISSUED.snissued LEFT JOIN (select snredeemed, cast(sum(amount) as money) / 100 AS [TotalRedeemed] from tickets where dateredeemed &gt; '09/04/2012' and dateredeemed &lt; dateadd(hour, 0, getdate()) group by snredeemed) AS REDEEMED ON machines.serialnum = REDEEMED.snredeemed </code></pre> <p>what I would like to accomplish if possible is for the second and third join is to use the <code>drps.drop1</code> in the where clause like this</p> <pre><code>where dropdate &gt; drps.dropdate1 and dropdate &lt; drps.dropdate2 </code></pre> <p>but it does not work</p> <p>here is working output</p> <pre> serialnum MOCALCSUM MICALCSUM COCALCSUM CICALCSUM TotalIssued TotalRedeemed dropdate1 dropdate2 0-2739-41401 5482 5498 132 148 3258.00 3110.00 2012-09-04 13:36:53.450 2012-09-05 13:55:38.750 0-2459-36182 1110 1054 114 58 1895.00 1657.00 2012-09-04 15:01:19.973 2012-09-05 13:55:38.967 </pre> <p>end result is I need total issued and total redeemed date range to be between the min and max date per serial number</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USRobert Allen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server 2008 left join where clause based on max date
      1. This table or related slice is empty.
      1. VTUpMod
    1. COjust an addition on the first Left join would it be possible that if result is null then it could find the most recent before date as in there is no date between 9/4 and now so use 9/12/2012 since it was the last
      singulars
      1. POSQL Server 2008 left join where clause based on max date
      1. USRobert Allen
    2. COWhen dealing with outer joins one should put all filters concerning outer table in join itself (ON clause) because otherwise null rows will not match the filter (except `is null` one) and be removed from result set. And you might want to add more meaningful description of problem than `but it does not work`. Things don't work in many different ways.
      singulars
      1. POSQL Server 2008 left join where clause based on max date
      1. USNikola Markovinović
    3. COif i change where dateissued > '09/04/2012' and dateissued < dateadd(hour, 0, getdate()) to where dateissued > drps.dropdate1 and dateissued < drps.dropdate2 then i get as a return The multi-part identifier "drps.dropdate2" could not be bound.
      singulars
      1. POSQL Server 2008 left join where clause based on max date
      1. USRobert Allen
 

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