Click here to Skip to main content
15,917,862 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello everyone
I have a problem. I'm doing a turnover report by year. But we have changed our datastructure per 2016. How do i get our TOTALSALES per SalesYear when i want to differ the SQL Query if the salesyear is 2016 or later.

//My Query that gets sales before 2016:
SQL
SELECT        SUM([Cust. Ledger Entry ].[Sales (LCY)]) AS TotalSales, YEAR([Cust. Ledger Entry ].[Posting Date]) AS SalesYear
FROM[Cust. Ledger Entry ] INNER JOIN
Customer ON [Cust. Ledger Entry ].[Customer No.] = Customer.[No.]
WHERE        ([Cust. Ledger Entry ].[Customer No.] <> '80501608') AND (Customer.[Gen. Bus. Posting Group] IN ('OFFSH EU', 'OFFSH Ø', 'OFFSH DK', 'FISK ØV', 'FISK EU', 'FISK DK', 'ØVRIG ØV', 'ØVRIG EU', 'ØVRIG DK', 'SLAM EU', 'SLAM DK', 'SLAM ØV', 'FØDE DK', 'FØDE EU', 'FØDE ØV')) AND 
([Cust. Ledger Entry ].[Document Type] IN ('2', '3')) AND (YEAR([Cust. Ledger Entry ].[Posting Date]) < '2016')
GROUP BY YEAR([Cust. Ledger Entry ].[Posting Date])
ORDER BY SalesYear


//My Query that gets sales after 2016:
SQL
SELECT        SUM([Credit Amount] - [Debit Amount]) AS TotalSales, YEAR([Posting Date]) AS SalesYear
FROM            [G/L Entry]
WHERE        ([G/L Account No.] IN ('100210', '100220', '100230', '100260', '100270', '100280', '100310', '100320', '100330', '100360', 
                         '100370', '100380', '100410', '100420', '100430'))
GROUP BY YEAR([Posting Date])
ORDER BY SalesYear


How on earth do i get the data returned together?

What I have tried:

I have tried to use UNION but i can't get this working.
Posted
Updated 7-Mar-16 0:26am
v2
Comments
Suvendu Shekhar Giri 7-Mar-16 6:19am    
It would be great if you can replicate the structure and data in SQLFiddle and give us the link.
Kristian_dk 7-Mar-16 7:04am    
I don't know SQL fiddle, but i will learn this for the NeXT time i post an question in here. Thanks for submitting to my question.
Maciej Los 7-Mar-16 6:24am    
UNIONA ALL
Mehdi Gholam 7-Mar-16 6:26am    
UNION ALL :)
Maciej Los 7-Mar-16 6:28am    
You're right, Mehdi. Simple typo ;)

1 solution

Seems you want to combine two queries into single result. So, check this: UNION (Transact-SQL)[^]
SQL
[QUERY 1]
UNION ALL
[QUERY 2]


Note: the data type for each column must be the same!
 
Share this answer
 
Comments
Kristian_dk 7-Mar-16 7:02am    
Thank you so much for helping out. This Works!! yehaa
Maciej Los 7-Mar-16 7:10am    
You're very welome.
Cheers,
Maciej

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900