Skip to Content
0
Former Member
Aug 06, 2013 at 10:23 AM

Joining two queries

18 Views

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