Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Split Data Expand / Collapse
Author
Message
Posted Monday, September 2, 2013 12:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:03 AM
Points: 81, Visits: 315
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
ABCD SGD FCR -28901.4 71343.22 113375.79 155817.61 ABCD SGD FCP 0 50507.58 -2068.07 48439.51 ABCD SGD DFCP 0 0 0 0
ACME HKD FCR 161.74 182.24 19963.11 20307.09 ACME HKD FCP 1875.94 2347.15 -4324.07 -100.98 ACME HKD DFCP 0 0 150.01 150.01
ACME MYR FCR 0 0 150.01 150.01 ACME MYR FCP 195.85 -34239.96 -2754.33 -36798.44 ACME MYR DFCP 0 0 0 0




;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.4 71343.22 113375.79 155817.61
ABCD SGD FCP 0 50507.58 -2068.07 48439.51
ABCD SGD DFCP 0 0 0 0
ACME HKD FCR 161.74 182.24 19963.11 20307.09
ACME HKD FCP 1875.94 2347.15 -4324.07 -100.98
ACME HKD DFCP 0 0 150.01 150.01
ACME MYR FCR 0 0 150.01 150.01
ACME MYR FCP 195.85 -34239.96 -2754.33 -36798.44
ACME MYR DFCP 0 0 0 0


Thanks for any help.
Post #1490521
Posted Monday, September 2, 2013 12:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 20,857, Visits: 32,877
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
ABCD SGD FCR -28901.4 71343.22 113375.79 155817.61 ABCD SGD FCP 0 50507.58 -2068.07 48439.51 ABCD SGD DFCP 0 0 0 0
ACME HKD FCR 161.74 182.24 19963.11 20307.09 ACME HKD FCP 1875.94 2347.15 -4324.07 -100.98 ACME HKD DFCP 0 0 150.01 150.01
ACME MYR FCR 0 0 150.01 150.01 ACME MYR FCP 195.85 -34239.96 -2754.33 -36798.44 ACME MYR DFCP 0 0 0 0




;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.4 71343.22 113375.79 155817.61
ABCD SGD FCP 0 50507.58 -2068.07 48439.51
ABCD SGD DFCP 0 0 0 0
ACME HKD FCR 161.74 182.24 19963.11 20307.09
ACME HKD FCP 1875.94 2347.15 -4324.07 -100.98
ACME HKD DFCP 0 0 150.01 150.01
ACME MYR FCR 0 0 150.01 150.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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1490524
Posted Monday, September 2, 2013 1:09 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:17 PM
Points: 1,095, Visits: 3,182
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

Post #1490681
Posted Monday, September 2, 2013 11:32 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:03 AM
Points: 81, Visits: 315
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.
Post #1490749
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse