September 2, 2013 at 12:16 am
Hi All,
I have written 3 queries and put them in 3 cte statements each is a different report. 1. FCR 2. FCP 3. DFPC. The code is putting each report after the other in one row, when I need each type under each other where the division and currency are the same.
Current Data:
Division,Code,FCR,jan,feb,mar,total,division,code,FCP,jan,feb,mar,total,division,code,DFPC,Jan,Feb,Mar,total
ABCDSGDFCR-28901.471343.22113375.79155817.61ABCDSGDFCP050507.58-2068.0748439.51ABCDSGDDFCP0000
ACMEHKDFCR161.74182.2419963.1120307.09ACMEHKDFCP1875.942347.15-4324.07-100.98ACME HKDDFCP00150.01150.01
ACMEMYRFCR00150.01150.01ACMEMYRFCP195.85-34239.96-2754.33-36798.44ACMEMYRDFCP0000
;with
cteFCR
(DIVName1,CCYCODE,title,FCRJan,FCRFeb,FCRMar,total)
AS (
select
d.DIVName1
,c.CCYCode
,'Foreign Currency Receivables' as [Title]
,SUM(CASE WHEN r.ACRFSMID IN (128) THEN r.ACRBalanceBase ELSE 0 END) AS [January 13]
,SUM(CASE WHEN r.ACRFSMID IN (129) THEN r.ACRBalanceBase ELSE 0 END) AS [Feburary 13]
,SUM(CASE WHEN r.ACRFSMID IN (130) THEN r.ACRBalanceBase ELSE 0 END) AS [March 13]
,SUM(CASE WHEN r.ACRFSMID IN (128, 129, 130) THEN r.ACRBalanceBase ELSE 0 END) AS [Total]
from vwAccountsReceivable r
inner join vwDivision d
on r.ACRDIVID = d.DIVID
inner join vwCurrency c
on r.ACRTransactionCCYID = c.CCYID
group by
d.DIVName1
,c.CCYCODE
),
cteFCP
(DIVName1,CCYCODE,title,FCPJan,FCPFeb,FCPMar,total)
AS (
select
d.DIVName1
,c.CCYCODE
,'Foreign Currency Payables' as [Title]
,SUM(CASE WHEN p.ACPFSMID IN (128) THEN p.ACPBalanceBase ELSE 0 END) AS [January 13]
,SUM(CASE WHEN p.ACPFSMID IN (129) THEN p.ACPBalanceBase ELSE 0 END) AS [Feburary 13]
,SUM(CASE WHEN p.ACPFSMID IN (130) THEN p.ACPBalanceBase ELSE 0 END) AS [March 13]
,SUM(CASE WHEN p.ACPFSMID IN (128, 129, 130) THEN '$' + (CONVERT(money,p.ACPBalanceBase,1))ELSE 0 END) AS [Total]
from vwAccountsPayable p
inner join vwDivision d
on p.ACPDIVID = d.DIVID
inner join vwCurrency c
on p.ACPTransactionCCYID = c.CCYID
group by
d.DIVName1
,c.CCYCODE
),
cteDFCP
(DIVName1,CCYCODE,title,DFCPJan, DFCPFeb, DFCPMar,total)
AS(
Select
ISNULL(pvt.[Division Buyer],0) as[DIVName1]
,pvt.currency as [CCYCode]
,'Draft Foreign Currency Payables' as [Title]
,ISNULL(pvt.[January],0) as [January]
,ISNULL(pvt.[Feburary],0) as [Feburary]
,ISNULL(pvt.[March],0) as [March]
,SUM(ISNULL(pvt.[January],0)+ ISNULL(pvt.[Feburary],0) + ISNULL(pvt.[March],0)) as [Total]
from
(
SELECT
h.optional_1 as [Division Buyer]
,currency
,DATENAME(Month,h.issuedate) as [Month]
,SUM(h.invoicetotal) as [Invoice Total]
FROM Companies c
JOIN Invoice_head h ON h.company_id = c.company_id
JOIN Invoice_lines l ON l.invoice_id = h.invoice_id
WHERE l.approvedate is null
and h.company_id in (36,37,38,39,40,41,42,43,44,45,46,47,99,103,114)
GROUP BY
h.optional_1
,currency
,h.issuedate
)as scre
PIVOT
(SUM([Invoice Total])
for [Month] IN(
[January]
,[Feburary]
,[March]
))as pvt
GROUP BY
pvt.[January]
,pvt.[Feburary]
,pvt.[March]
,pvt.[Division Buyer]
,pvt.currency
)
select
r.DIVName1 as [Division]
,r.CCYCODE as [Code]
,r.title as [Foreign Currency Receivables]
,r.FCRJan as [Jan]
,r.FCRFeb as [Feb]
,r.FCRMar as [Mar]
,r.total as [Total]
,p.DIVName1 as [Division]
,p.CCYCODE as [Code]
,p.title as [Foreign Currency Payables]
,p.FCPJan as [Jan]
,p.FCPFeb as[Feb]
,p.FCPMar as [Mar]
,p.total as [Total]
,d.DIVNAME1 as [Division]
,d.CCYCODE as [Code]
,d.title as [Draft Foreign Currency Payables]
,d.DFCPJan as [Jan]
,d.DFCPFeb as [Feb]
,d.DFCPMar as [Mar]
,d.total as [Total]
from cteFCR r
inner join cteFCP p
on r.DIVName1 = p.DIVName1 and r.CCYCODE = p.CCYCODE
inner join cteDFCP d
on r.DIVName1 = d.DIVName1 and r.CCYCODE = d.CCYCODE
GROUP BY
r.DIVName1
,r.CCYCODE
,r.title
,r.FCRJan
,r.FCRFeb
,r.FCRMar
,r.total
,p.DIVName1
,p.CCYCODE
,p.title
,p.FCPJan
,p.FCPFeb
,p.FCPMar
,p.total
,d.DIVNAME1
,d.CCYCODE
,d.title
,d.DFCPJan
,d.DFCPFeb
,d.DFCPMar
,d.total
Expected:
Division, Currency, Report, Jan, Feb, Mar, Total
ABCD SGD FCR -28901.471343.22113375.79155817.61
ABCD SGD FCP 050507.58-2068.0748439.51
ABCD SGD DFCP 0 0 0 0
ACME HKD FCR 161.74182.24 19963.11 20307.09
ACME HKD FCP 1875.942347.15-4324.07-100.98
ACME HKD DFCP 0 0 150.01 150.01
ACME MYR FCR 0 0 150.01150.01
ACME MYR FCP 195.85-34239.96-2754.33-36798.44
ACME MYR DFCP 0 0 0 0
Thanks for any help.
September 2, 2013 at 12:41 am
ringovski (9/2/2013)
Hi All,I have written 3 queries and put them in 3 cte statements each is a different report. 1. FCR 2. FCP 3. DFPC. The code is putting each report after the other in one row, when I need each type under each other where the division and currency are the same.
Current Data:
Division,Code,FCR,jan,feb,mar,total,division,code,FCP,jan,feb,mar,total,division,code,DFPC,Jan,Feb,Mar,total
ABCDSGDFCR-28901.471343.22113375.79155817.61ABCDSGDFCP050507.58-2068.0748439.51ABCDSGDDFCP0000
ACMEHKDFCR161.74182.2419963.1120307.09ACMEHKDFCP1875.942347.15-4324.07-100.98ACME HKDDFCP00150.01150.01
ACMEMYRFCR00150.01150.01ACMEMYRFCP195.85-34239.96-2754.33-36798.44ACMEMYRDFCP0000
;with
cteFCR
(DIVName1,CCYCODE,title,FCRJan,FCRFeb,FCRMar,total)
AS (
select
d.DIVName1
,c.CCYCode
,'Foreign Currency Receivables' as [Title]
,SUM(CASE WHEN r.ACRFSMID IN (128) THEN r.ACRBalanceBase ELSE 0 END) AS [January 13]
,SUM(CASE WHEN r.ACRFSMID IN (129) THEN r.ACRBalanceBase ELSE 0 END) AS [Feburary 13]
,SUM(CASE WHEN r.ACRFSMID IN (130) THEN r.ACRBalanceBase ELSE 0 END) AS [March 13]
,SUM(CASE WHEN r.ACRFSMID IN (128, 129, 130) THEN r.ACRBalanceBase ELSE 0 END) AS [Total]
from vwAccountsReceivable r
inner join vwDivision d
on r.ACRDIVID = d.DIVID
inner join vwCurrency c
on r.ACRTransactionCCYID = c.CCYID
group by
d.DIVName1
,c.CCYCODE
),
cteFCP
(DIVName1,CCYCODE,title,FCPJan,FCPFeb,FCPMar,total)
AS (
select
d.DIVName1
,c.CCYCODE
,'Foreign Currency Payables' as [Title]
,SUM(CASE WHEN p.ACPFSMID IN (128) THEN p.ACPBalanceBase ELSE 0 END) AS [January 13]
,SUM(CASE WHEN p.ACPFSMID IN (129) THEN p.ACPBalanceBase ELSE 0 END) AS [Feburary 13]
,SUM(CASE WHEN p.ACPFSMID IN (130) THEN p.ACPBalanceBase ELSE 0 END) AS [March 13]
,SUM(CASE WHEN p.ACPFSMID IN (128, 129, 130) THEN '$' + (CONVERT(money,p.ACPBalanceBase,1))ELSE 0 END) AS [Total]
from vwAccountsPayable p
inner join vwDivision d
on p.ACPDIVID = d.DIVID
inner join vwCurrency c
on p.ACPTransactionCCYID = c.CCYID
group by
d.DIVName1
,c.CCYCODE
),
cteDFCP
(DIVName1,CCYCODE,title,DFCPJan, DFCPFeb, DFCPMar,total)
AS(
Select
ISNULL(pvt.[Division Buyer],0) as[DIVName1]
,pvt.currency as [CCYCode]
,'Draft Foreign Currency Payables' as [Title]
,ISNULL(pvt.[January],0) as [January]
,ISNULL(pvt.[Feburary],0) as [Feburary]
,ISNULL(pvt.[March],0) as [March]
,SUM(ISNULL(pvt.[January],0)+ ISNULL(pvt.[Feburary],0) + ISNULL(pvt.[March],0)) as [Total]
from
(
SELECT
h.optional_1 as [Division Buyer]
,currency
,DATENAME(Month,h.issuedate) as [Month]
,SUM(h.invoicetotal) as [Invoice Total]
FROM Companies c
JOIN Invoice_head h ON h.company_id = c.company_id
JOIN Invoice_lines l ON l.invoice_id = h.invoice_id
WHERE l.approvedate is null
and h.company_id in (36,37,38,39,40,41,42,43,44,45,46,47,99,103,114)
GROUP BY
h.optional_1
,currency
,h.issuedate
)as scre
PIVOT
(SUM([Invoice Total])
for [Month] IN(
[January]
,[Feburary]
,[March]
))as pvt
GROUP BY
pvt.[January]
,pvt.[Feburary]
,pvt.[March]
,pvt.[Division Buyer]
,pvt.currency
)
select
r.DIVName1 as [Division]
,r.CCYCODE as [Code]
,r.title as [Foreign Currency Receivables]
,r.FCRJan as [Jan]
,r.FCRFeb as [Feb]
,r.FCRMar as [Mar]
,r.total as [Total]
,p.DIVName1 as [Division]
,p.CCYCODE as [Code]
,p.title as [Foreign Currency Payables]
,p.FCPJan as [Jan]
,p.FCPFeb as[Feb]
,p.FCPMar as [Mar]
,p.total as [Total]
,d.DIVNAME1 as [Division]
,d.CCYCODE as [Code]
,d.title as [Draft Foreign Currency Payables]
,d.DFCPJan as [Jan]
,d.DFCPFeb as [Feb]
,d.DFCPMar as [Mar]
,d.total as [Total]
from cteFCR r
inner join cteFCP p
on r.DIVName1 = p.DIVName1 and r.CCYCODE = p.CCYCODE
inner join cteDFCP d
on r.DIVName1 = d.DIVName1 and r.CCYCODE = d.CCYCODE
GROUP BY
r.DIVName1
,r.CCYCODE
,r.title
,r.FCRJan
,r.FCRFeb
,r.FCRMar
,r.total
,p.DIVName1
,p.CCYCODE
,p.title
,p.FCPJan
,p.FCPFeb
,p.FCPMar
,p.total
,d.DIVNAME1
,d.CCYCODE
,d.title
,d.DFCPJan
,d.DFCPFeb
,d.DFCPMar
,d.total
Expected:
Division, Currency, Report, Jan, Feb, Mar, Total
ABCD SGD FCR -28901.471343.22113375.79155817.61
ABCD SGD FCP 050507.58-2068.0748439.51
ABCD SGD DFCP 0 0 0 0
ACME HKD FCR 161.74182.24 19963.11 20307.09
ACME HKD FCP 1875.942347.15-4324.07-100.98
ACME HKD DFCP 0 0 150.01 150.01
ACME MYR FCR 0 0 150.01150.01
ACME MYR FCP 195.85-34239.96-2754.33-36798.44
ACME MYR DFCP 0 0 0 0
Thanks for any help.
You've provided us with your code, now if you would provide us with the DDL (CREATE TABLE) statement(s) for the table(s) and put your sample data into INSERT INTO statements to populate the appropriate table(s) I am sure we could provide you with the assistance you are asking for and in return provide you with tested code.
September 2, 2013 at 1:09 pm
I may be misunderstanding you requirement, but you could simply change your outer select to do a union all (or union depending on your data)
select
r.DIVName1 as [Division]
,r.CCYCODE as [Code]
,r.title as [Foreign Currency Receivables]
,r.FCRJan as [Jan]
,r.FCRFeb as [Feb]
,r.FCRMar as [Mar]
,r.total as [Total]
from cteFCR r
union all
select
p.DIVName1 as [Division]
,p.CCYCODE as [Code]
,p.title as [Foreign Currency Payables]
,p.FCPJan as [Jan]
,p.FCPFeb as[Feb]
,p.FCPMar as [Mar]
,p.total as [Total]
from cteFCP p
union all
select
d.DIVNAME1 as [Division]
,d.CCYCODE as [Code]
,d.title as [Draft Foreign Currency Payables]
,d.DFCPJan as [Jan]
,d.DFCPFeb as [Feb]
,d.DFCPMar as [Mar]
,d.total as [Total]
from cteDFCP d
September 2, 2013 at 11:32 pm
Thanks for the replies, I got it working with a union for each statement. I was originally using two unions (for same DB) and then added a another statement (different db) and didn't think of using a union again, cheers.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply