Creating report from one table
-
Hi looking for a solution for next problem. Got one table :
Customer, AccountCode, Entrydate, Debit, Credit
Data exemple :
AUBM, 600000, 01/12/2004, 10, 0
AUBM, 700000, 01/12/2004, 0, 25
AUBM, 700000, 05/12/2004, 0, 55
AUBM, 600000, 07/10/2004, 77, 0
The results I need (in pivot table excel) is :
Customer Month
Octobre December
AUBM Turnover 0 80
Costs 77 10
JV -
Probably something like this?
set dateformat dmy
create table #t
(
customer varchar(4)
, accountcode int
, dt datetime
, debit float
, credit float
)
insert into #t
select 'AUBM', 600000, '01/12/2004', 10, 0
union all
select 'AUBM', 700000, '01/12/2004', 0, 25
union all
select 'AUBM', 700000, '05/12/2004', 0, 55
union all
select 'AUBM', 600000, '07/10/2004', 77, 0
select
customer, sum(cost) cost, sum(turnover) turnover, dt
from
(select max(customer) customer
, case when min(accountcode)=600000 then sum(debit) else 0 end cost
, case when min(accountcode)=700000 then sum(credit) else 0 end turnover
, datename(month,min(dt)) dt
from #t
group by dateadd(dd,-day(dt)+1,dt) ,accountcode)x
group by x.dt, x.customer
drop table #t
customer cost turnover dt
-------- ----- --------- ------------------------------
AUBM 10.0 80.0 December
AUBM 77.0 0.0 October
(2 row(s) affected)
And do the rest in Excel.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
or like this (will only work for one year!)
SELECT Customer, [Type],
SUM(CASE WHEN [Month] = 1 THEN [Value] ELSE 0 END) as [Jan],
SUM(CASE WHEN [Month] = 2 THEN [Value] ELSE 0 END) as [Feb],
SUM(CASE WHEN [Month] = 3 THEN [Value] ELSE 0 END) as [Mar],
SUM(CASE WHEN [Month] = 4 THEN [Value] ELSE 0 END) as [Apr],
SUM(CASE WHEN [Month] = 5 THEN [Value] ELSE 0 END) as [May],
SUM(CASE WHEN [Month] = 6 THEN [Value] ELSE 0 END) as [Jun],
SUM(CASE WHEN [Month] = 7 THEN [Value] ELSE 0 END) as [Jul],
SUM(CASE WHEN [Month] = 8 THEN [Value] ELSE 0 END) as [Aug],
SUM(CASE WHEN [Month] = 9 THEN [Value] ELSE 0 END) as [Sep],
SUM(CASE WHEN [Month] = 10 THEN [Value] ELSE 0 END) as [Oct],
SUM(CASE WHEN [Month] = 11 THEN [Value] ELSE 0 END) as [Nov],
SUM(CASE WHEN [Month] = 12 THEN [Value] ELSE 0 END) as [Dec]
FROM (
SELECT Customer as [Cust],Customer as [Customer],
'Turnover' as [Type],
MONTH(Entrydate) as [Month],
Credit AS [Value]
FROM #t
UNION ALL
SELECT Customer as [Cust],
'' as [Customer],
'Costs' as [Type],
MONTH(Entrydate) as [Month],
Debit AS [Value]
FROM #t
) a
GROUP BY Cust, Customer, Type
ORDER BY Cust, Type DESC
Far away is close at hand in the images of elsewhere.
Anon. -
Guys
In advance thx for yr answers. Question though. It's not always AUBM in that columd. There are other customers too. So how do I go on from there ?
Thx
J
JV -
My query will return two rows per customer!!
We need more info if the query does not produce what you want
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply