SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Split Data


Split Data

Author
Message
ringovski
ringovski
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 445
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.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39129 Visits: 38518
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.

Cool
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)
mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1570 Visits: 3317
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


ringovski
ringovski
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 445
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search