Joining two queries

[Former Member]

Hi

  I am trying to join two queries so I get the following result:

Date          Invoice Type                     Amount

2011/08     Customer Payment               3433

2011/08     Interest                                      65

2011/08     Moving Balance                   23455

this will be the same for each month (the moving balance should always display)

Query 1 (SQL COMMAND)

SELECT CONVERT(VARCHAR(7),case_createddate, 111) AS Date,

       case_invoicetype,

       Sum(case_totalexvat)

       FROM   cases AS ca

       WHERE  case_primaryCompanyid = 2174 and

                      datediff(m,case_createddate,getDate()) < 13

       GROUP  BY CONVERT(VARCHAR(7),case_createddate, 111),

                     case_invoicetype

        order by CONVERT(VARCHAR(7),case_createddate, 111)

this produces the following output

Date          Invoice Type                     Amount

2011/08     Customer Payment               3433

2011/08     Interest                                      65

Query 2

select
case_primaryCompanyId,
'Moving Balance' as InvoiceType,

CONVERT(VARCHAR(7),ca.case_createddate, 111) AS Date,
sum(mb.Amount) as amount
from
     cases as ca
    left join (
               select
                    case_primaryCompanyId as ID,
                    case_createdDate,
                    case_TotalExVat as Amount
               from
                    cases
                    ) mb
   on ca. case_primaryCompanyId = mb.ID 
   and ca.case_createdDate >= mb.case_CreatedDate
   where
         ca.case_primaryCompanyId = 2174 and
         ca.case_createdDate > DATEADD(m, -12, current_timestamp)
   group by
         case_primaryCompanyId,
   CONVERT(VARCHAR(7),ca.case_createddate, 111)

         order by  ca.case_primaryCompanyid, CONVERT(VARCHAR(7),ca.case_createddate, 111)

displays the following information ( an moving balance for each month)

Date          Invoice Type                     Amount

2011/08     Moving Balance                   23455

2011/09     Moving Balance                   32145

2011/10     Moving Balance                   32000

Now I just need to combine them but am unsure as what to do

I need the Invoice Type to be one column, as I will be making a chart out of the results

Thanks

[Former Member · Accepted solution]

Have tried using Union, but i keep getting the error "Invalid Syntax at ")"". maybe i am writing it wrong, will keep trying

[DellSC · Accepted solution]

Your problem is that both queries in a union have to have the same field type in the same places.  In your first query it looks like the date field is the first field, in the second query it is the last field.

Also, both queries in a union have to have the same number of fields.  Your first query has 3 and your second query has 4.

-Dell

[Former Member · Accepted solution]

Thanks Dell , It is all sorted now, exactly as you said.

[Former Member · Accepted solution]

Hi again

   Thought I had this issue resolved but when looking at the results i discovered some issues

If there is only one record per month then it works fine but if there is more than one record per month, then the sum is calculated for each record in that month.

So

Date                    Amount

01/2012               100

02/2012                200

02/2012                300

02/2012                100

03/2012                400

So Results should be

Date                Invoice Type              Should be                  Showing

01/2012           Moving balance         100                               100

02/2012           Moving Balance         700                             1900

03/2012           Moving Balance        1100                            1100

Here is my code

Have tried the with statement, distinct , but not sure what to do

select

          case_PrimaryCompanyId as companyid,

          CONVERT(VARCHAR(7),ca.case_createddate, 111) AS Date,

          'Moving Balance' as InvoiceType,

          sum( mb.Amount) as Amount

from

         cases as ca

left join (

select

    case_PrimaryCompanyId as companyid,

    case_createdDate,

    case_TotalExVat as Amount

from

    cases

) mb

on ca. case_primaryCompanyId = mb.companyid 

     and ca.case_createdDate >= mb.case_CreatedDate

where

    ca.case_primaryCompanyId = companyid

group by

     case_primaryCompanyId,

     CONVERT(VARCHAR(7),ca.case_createddate, 111)

[JWiseman · Accepted solution]

hi Rachael,

instead of joining the table results from mb to the rest of your main query, have you tried to bring back the amount as a subselect in the main query (to avoid the left outer join) and also change the main query so that it utilizes a group by on the date?

[Former Member · Accepted solution]

ok will try that thanks

[Former Member · Accepted solution]

That worked a treat, thank you so much , was starting to go slightly insane