August 31, 2010 at 10:33 am
Hello,
I need help with following case.
This is cash flow case. I`m giving partner id , periods length (in days) and number of periods to be shown. In rows there are sums of debit and credit grouped in x days groups. The result should be like these:
| 1-7 (days) 8-14 15-21 22-27 ...
------------------------------------------------------
Debit | 569.87 234.23 2324.23 3232.32
Credit | 872.93 324.42 908.32 2344.94
...
Sums are easy to calculate. Problem is how to put them in x columns depending on how many future periods user wants.
Rows number can be different - let`s say it is unknown and each time different.
August 31, 2010 at 10:43 am
Please have a look at the CrossTab article referenced in my signature to get your data pivoted.
Once you know how the concept works move on to the DynamicCrossTab article to learn how to return a pivoted table with flexible number of columns.
August 31, 2010 at 11:32 am
Bezan,
Lutz is right on with how to handle it. However, if you are still having problems, please take a look at the first link in my signature. Then, if you would post the CREATE TABLE and INSERT INTO table statements, we'll be able to help you out.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 31, 2010 at 11:37 am
Thanks for so fast answer. I will check this article and maybe it helps. Otherwise I will post again 😉
September 1, 2010 at 5:53 am
I`ve read both articles but still I`m not sure how to do this.
Variables for SQL (...in final ver):
DECLARE
@startdate datetime,
@periodlength int,
@periodsnumber int,
@ph nvarchar(3),
@acc nvarchar(3)
SET @date = '2009-10-28' -- probably will be fixed as today()
SET @periodlength = 7 -- number of days in each time period - DYNAMIC
SET @periodsnumber = 5 -- number of periods to be shown - DYNAMIC
SET @ph = 'PE1' -- Partner code
SET @acc = '330' -- Account code
In report we are selecting Partner and Account, number of days in time period and number of periods.
In Rows should be:
Partner Debit
Partner Credit
Account Debit
Account Credit
TOTAL SUM
In Columns:
There should be sums for next @periodlength days
We have as many columns as @periodsnumber
There is sample Data - NOW OK:
CREATE TABLE SomeTableX
(
TransId INT,
Date DATETIME,
Partner NVARCHAR(3),
Account NVARCHAR(3),
Debit NUMERIC(16,2),
Credit NUMERIC(16,2)
)
GO
INSERT INTO SomeTableX
(TransId, Date, Partner, Account, Debit, Credit)
SELECT 1, CAST('2009-10-29' AS datetime), 'PE1', '330', 1.1, 2.2 UNION ALL
SELECT 2, CAST('2009-10-29' AS datetime), 'PE1', '330', 1.2, 2.3 UNION ALL
SELECT 3, CAST('2009-10-30' AS datetime), 'AA1', '330', 1.3, 2.4 UNION ALL
SELECT 4, CAST('2009-10-30' AS datetime), 'PE1', '330', 1.4, 2.5 UNION ALL
SELECT 5, CAST('2009-11-04' AS datetime), 'PE1', '330', 1.5, 2.6 UNION ALL
SELECT 6, CAST('2009-11-04' AS datetime), 'PE1', '110', 1.6, 2.7 UNION ALL
SELECT 7, CAST('2009-11-06' AS datetime), 'PE1', '330', 1.7, 2.8 UNION ALL
SELECT 8, CAST('2009-11-12' AS datetime), 'PE1', '330', 1.8, 2.9 UNION ALL
SELECT 9, CAST('2009-11-16' AS datetime), 'PE1', '330', 1.9, 2.2 UNION ALL
SELECT 10, CAST('2009-11-18' AS datetime), 'PE1', '330', 1.1, 2.3 UNION ALL
SELECT 11, CAST('2009-11-25' AS datetime), 'PE1', '330', 1.2, 2.4 UNION ALL
SELECT 12, CAST('2009-11-25' AS datetime), 'PE1', '330', 1.3, 2.5 UNION ALL
SELECT 13, CAST('2009-12-02' AS datetime), 'PE1', '330', 1.4, 2.6 UNION ALL
SELECT 14, CAST('2009-12-06' AS datetime), 'PE1', '330', 1.5, 2.7 UNION ALL
SELECT 15, CAST('2009-12-09' AS datetime), 'AA1', '330', 1.6, 2.8 UNION ALL
SELECT 16, CAST('2009-12-14' AS datetime), 'PE1', '330', 1.7, 2.9 UNION ALL
SELECT 17, CAST('2009-12-14' AS datetime), 'PE1', '110', 1.8, 2.2 UNION ALL
SELECT 18, CAST('2009-12-20' AS datetime), 'PE1', '330', 1.9, 2.3 UNION ALL
SELECT 19, CAST('2009-12-26' AS datetime), 'PE1', '330', 1.1, 2.4 UNION ALL
SELECT 20, CAST('2009-12-26' AS datetime), 'PE1', '330', 1.1, 2.5
GO
Table used for months.
Now I have only changable number of months displayed but don`t know how to change this months into any days periods.
SELECT TOP 11000
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
GRANT SELECT ON dbo.Tally TO PUBLIC
SQL which I was able to adopt to my case....
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @periodsnumber INT
SET @periodsnumber = 10
SET @StartDate = '2009-09-28'
SET @EndDate = DATEADD(month,@periodsnumber,@StartDate)
DECLARE @SwapDate DATETIME
SELECT @SwapDate = @EndDate,
@EndDate = @StartDate,
@StartDate = @SwapDate
WHERE @EndDate < @StartDate
SELECT @StartDate = DATEADD(mm,DATEDIFF(mm,0,@StartDate),0),
@EndDate = DATEADD(mm,DATEDIFF(mm,0,@EndDate)+1,0)
DECLARE @SQL1 NVARCHAR(4000),
@SQL2 NVARCHAR(4000),
@SQL3 NVARCHAR(4000)
SELECT @SQL1 = 'SELECT CASE WHEN GROUPING(Partner) = 1 THEN ''Total'' ELSE Partner END AS Partner,'+CHAR(10)
SELECT @SQL3 =
' SUM(phCredit) AS Total
FROM (
select Partner,DATEADD(mm,DATEDIFF(mm,0,date),0) AS MonthDate, sum(Credit) as phCredit
FROM SomeTableX
WHERE Partner IN (''PE1'')
AND date >= ' + QUOTENAME(@StartDate,'''') + '
AND date < ' + QUOTENAME(@EndDate,'''') + '
GROUP BY DATEADD(mm,DATEDIFF(mm,0,date),0), Partner
) d
GROUP BY Partner WITH ROLLUP
'
SELECT @SQL2 = COALESCE(@SQL2,'')
+ ' SUM(CASE WHEN MonthDate = ' + QUOTENAME(d.MonthName,'''')
+ ' THEN phCredit ELSE 0 END) AS [' + d.MonthName + '],' + CHAR(10)
FROM
(
SELECT N,
STUFF(CONVERT(CHAR(11),DATEADD(mm, N-1, @StartDate),100),4,3,'') AS MonthName
FROM dbo.Tally
WHERE N <= DATEDIFF(mm,@StartDate,@EndDate)
) d
ORDER BY d.N
EXEC (@SQL1 + @SQL2 + @SQL3)
As I mentioned above it is not exactly what I want. Except of months not days periods in ROWS there is sum only for Partner Credit. I don`t know how to add Partner Debit, Account Credit and Account Debit.
I think it is rather clear. Help is rather needed 🙂
September 1, 2010 at 7:22 am
Ooooooo... crud. I'm on my way to work and always miss out on the fun stuff. I'll be watching whoever gets to this one. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
September 1, 2010 at 7:38 am
Hi,
This should get you the data you need, it's then just a case of using the previous advice on dynamic pivots to get the data into the display format you need.
DECLARE
@startdate datetime,
@periodlength int,
@periodsnumber int,
@ph nvarchar(3),
@acc nvarchar(3)
SET @startdate = '2009-10-28' -- probably will be fixed as today()
SET @periodlength = 10 -- number of days in each time period - DYNAMIC
SET @periodsnumber = 10 -- number of periods to be shown - DYNAMIC
SET @ph = 'PE1' -- Partner code
SET @acc = '330' -- Account code
; with cte as (
select [Date]
,[Partner]
,[Account]
,[Debit]
,[Credit]
, (datediff(d,@startdate,[Date]) - (datediff(d,@startdate,[Date]) % @periodlength)) / @periodlength as Period
from #SomeTableX
)
select Period
, sum(Debit) as Debit
, sum(Credit) as Credit
from cte
where [Partner] = @ph
and [Account] = @acc
and Period < @periodsnumber
group by Period
Regards, Iain
September 1, 2010 at 7:42 am
Jeff Moden (9/1/2010)
Ooooooo... crud. I'm on my way to work and always miss out on the fun stuff. I'll be watching whoever gets to this one. 😛
Lol, I'm at work, completing some very important documentation... 😀
September 1, 2010 at 7:57 am
irobertson (9/1/2010)
Hi,This should get you the data you need, it's then just a case of using the previous advice on dynamic pivots to get the data into the display format you need.
It is a little help 🙂 Now there are flexible time periods ... but
now we have i Credit and Debit both sums (Partner + Account). How about having 4(P Debit, P Credit, A Credit, A Debit) sums instead of 2(P+A Debit, P+A Credit)?...
September 1, 2010 at 8:08 am
Hi Bezan,
I'm not sure what you mean - the initial criteria limits the data returned to one combination of Partner & Account only.
To see all combinations, just tweak the criteria:
select Period
, [Partner]
, [Account]
, sum(Debit) as Debit
, sum(Credit) as Credit
from cte
where Period < @periodsnumber
group by Period
, [Partner]
, [Account]
Also, I'm looking into converting to pivot, I couldn't resist the challenge 🙂
Will post something else in a bit...
Edit: hang on, I get it - you want four columns showing the sum two ways...
September 1, 2010 at 9:11 am
Ok, so maybe I'm not quite clear. It seems that you need to group ny Partners with Periods in columns, but also by Accounts with Periods in columns?
If so, this should help. For testing it runs in two stages, but this can be fairly straightforwardly wrapped into a stored procedure.
So, run this bit first to create an interim table holding the data to summarise:
DECLARE
@startdate datetime,
@periodlength int,
@periodsnumber int,
@ph nvarchar(3),
@acc nvarchar(3)
SET @startdate = '2009-10-28' -- probably will be fixed as today()
SET @periodlength = 6 -- number of days in each time period - DYNAMIC
SET @periodsnumber = 10 -- number of periods to be shown - DYNAMIC
SET @ph = 'PE1' -- Partner code
SET @acc = '330' -- Account code
; with cte as (
select [Date]
,[Partner]
,[Account]
,[Debit]
,[Credit]
, (datediff(d,@startdate,[Date]) - (datediff(d,@startdate,[Date]) % @periodlength)) / @periodlength as Period
from #SomeTableX
)
select Period
, [Partner]
, [Account]
, sum(Debit) as Debit
, sum(Credit) as Credit
into #interim
from cte
where Period < @periodsnumber
group by Period
, [Partner]
, [Account]
Then run this code to output the results:
-- declare variables
declare @sql_main varchar(max)
, @sql_select_list varchar(max) = ''
, @sql_pivot_list varchar(max) = ''
, @i int = 0 -- loop counter
-- next 2 variables are redeclared for testing
-- won't be required if you're wrapping this into a single stored procedure
, @periodsnumber int = 10
, @periodslength int = 6
-- set the main sql string
-- note use of placeholders
set @sql_main =
'select [Partner], ''Debit'' as Tran_Type, <<select placeholder>>
from (
select [Partner], Period, Debit from (
select [Partner], Period, sum(Debit) as Debit
from #interim
group by [Partner], Period
) dt
) src
pivot (sum([Debit]) for Period in (<<pivot placeholder>>)) pvt
union all
select [Partner], ''Credit'' as Tran_Type, <<select placeholder>>
from (
select [Partner], Period, Credit from (
select [Partner], Period, sum(Credit) as Credit
from #interim
group by [Partner], Period
) dt
) src
pivot (sum([Credit]) for Period in (<<pivot placeholder>>)) pvt
order by [Partner],Tran_Type'
-- loop n times adding fields to the output list
-- and the pivot list
while @i < @periodsnumber
begin
-- convert the period numbers into meaningful headers
set @sql_select_list = @sql_select_list + quotename(cast(@i as varchar)) + ' as [' + cast(1 + (@i * @periodslength) as varchar) + '_to_' + cast((@i * @periodslength) + @periodslength as varchar) + '],'
-- just add the period numbers to the pivot list
set @sql_pivot_list = @sql_pivot_list + quotename(cast(@i as varchar)) + ','
-- increment counter
set @i = @i + 1
end
-- trim trailing commas
set @sql_select_list = left(@sql_select_list,len(@sql_select_list)-1)
set @sql_pivot_list = left(@sql_pivot_list,len(@sql_pivot_list)-1)
-- debug
print @sql_select_list
print @sql_pivot_list
-- replace placeholders
set @sql_main = replace(@sql_main,'<<select placeholder>>',@sql_select_list)
set @sql_main = replace(@sql_main,'<<pivot placeholder>>',@sql_pivot_list)
-- debug
print @sql_main
-- execute
-- NOTE: exec carries security risks
-- and is best avoided if your procedure
-- is likely to be exposed to external inputs
exec(@sql_main)
Rinse and repeat changing Partner to Account in the dtnamic statement.
Somewhere close to what you're looking for?
September 1, 2010 at 9:13 am
Also, you should think carefully about indexes required on the source table and the temporary interim table - pivot is costly without the correct indexes...
September 1, 2010 at 9:57 am
Would you mind adding your requested result based on your sample data so we have something to compare with?
Irobertson does an excellent job by continuously providing answers to every lpieco of information you provide.
I'm sure there would be more people around helping you as well if we'd just know what you're looking for (at least I would have a closer look at it).
September 1, 2010 at 11:16 am
LutzM (9/1/2010)
@Bezan:Would you mind adding your requested result based on your sample data so we have something to compare with?
Irobertson does an excellent job by continuously providing answers to every lpieco of information you provide.
I'm sure there would be more people around helping you as well if we'd just know what you're looking for (at least I would have a closer look at it).
Let`s say there are periods 7day long and we have 5 periods (10/28-11/05, 11/06-11/13, 11/14-11/21, 11/22-11/29, 11/30-12/07 .... should be ok). We are taking Partner = 'PE1' and Account = 330. Current date = '2009-10-28'
Result should be like:
1-7 8-14 15-21 22-28 29-35 (money flow in next 7, ... days )
-------------------------------------------------------
Partner Debit 6.8 3.5 3.0 2.5 2.9
Partner Credit 12.3 5.7 4.5 4.9 5.3
Account Debit 6.5 3.5 3.0 2.5 2.9
Account Credit 12.0 3.7 4.5 4.9 5.3
SUM 37.6 16.4 15.0 14.8 16.4
September 1, 2010 at 11:37 am
1-7 8-14 15-21 22-28 29-35 (money flow in next 7, ... days )
-------------------------------------------------------
Partner Debit 6.8 3.5 3.0 2.5 2.9
Partner Credit 12.3 5.7 4.5 4.9 5.3
Account Debit 6.5 3.5 3.0 2.5 2.9
Account Credit 12.0 3.7 4.5 4.9 5.3
SUM 37.6 16.4 15.0 14.8 16.4
just a thought....maybe I am missing the point but:
this is a cashflow forecast right?
why are you summing all entries?
surely either a debit or credit should be a negative, dependent upon how you work?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 48 total)
You must be logged in to reply to this topic. Login to reply