UNION not giving expected result
UNION not giving expected result
(OP)
I need to create a .csv file grouped by Account, Ccentre with 14 columns, OBAL, JAN, FEB, etc to CBAL.
I have existing views to get the data into the individual parts, the following is for JAN:
SELECT SUBSTRING(GL06001, 1, 4) AS Account, SUBSTRING(GL06001, 5, 4) AS Ccentre, GL06004 AS JAN
FROM dbo.GL060102
WHERE (GL06014 >= CONVERT(DATETIME, '2002-01-01 00:00:00', 102)) AND (GL06014 <= CONVERT(DATETIME, '2002-01-31 00:00:00', 102))
this will give a table of entries for JAN only.
I have tried the following to ADD the views together to give a result with OBAL, JAN as columns but only get OBAL
CREATE VIEW dbo.[24HR_GL06_2002_groupby]
AS
SELECT Account, Ccentre, OBAL
FROM dbo.[24HR_GL06_2002_OB]
union
SELECT Account, Ccentre, JAN
FROM dbo.[24HR_GL06_2002_JAN]
How can I get all 14 periods?
I have existing views to get the data into the individual parts, the following is for JAN:
SELECT SUBSTRING(GL06001, 1, 4) AS Account, SUBSTRING(GL06001, 5, 4) AS Ccentre, GL06004 AS JAN
FROM dbo.GL060102
WHERE (GL06014 >= CONVERT(DATETIME, '2002-01-01 00:00:00', 102)) AND (GL06014 <= CONVERT(DATETIME, '2002-01-31 00:00:00', 102))
this will give a table of entries for JAN only.
I have tried the following to ADD the views together to give a result with OBAL, JAN as columns but only get OBAL
CREATE VIEW dbo.[24HR_GL06_2002_groupby]
AS
SELECT Account, Ccentre, OBAL
FROM dbo.[24HR_GL06_2002_OB]
union
SELECT Account, Ccentre, JAN
FROM dbo.[24HR_GL06_2002_JAN]
How can I get all 14 periods?





RE: UNION not giving expected result
Assuming that each unique combination of Account, Ccentre has only one record in each table, then you could do something like this:
SELECT OB.Account, OB.Ccentre, OB.OBAL, JAN, FEB, ..., DEC
FROM dbo.OB OB, dbo.JAN, dbo.FEB, ..., dbo.DEC
WHERE dbo.OB.Account = dbo.JAN.Account
AND dbo.OB.Account = dbo.FEB.Account
.
.
.
AND dbo.OB.Ccentre = dbo.DEC.Ccentre
ORDER BY OB.Account, OB.Ccentre
But this will probably be a very big, time-consuming query since it has to go across 13 tables and make 26 cross-table comparisons.
Hope this helps!