September 2, 2010 at 9:26 am
WayneS (9/2/2010)
Bezan,David's solution rocks. Just use that.
Thanks Wayne :blush:
Not sure that it rocks, might need a bit of fine tuning 🙂
Far away is close at hand in the images of elsewhere.
Anon.
September 2, 2010 at 9:48 am
David Burrows (9/2/2010)
WayneS (9/2/2010)
Bezan,David's solution rocks. Just use that.
Thanks Wayne :blush:
Not sure that it rocks, might need a bit of fine tuning 🙂
The only area of improvement that I can see is the 4 unioned select statements - everything else is just plain rock solid. It would be nice if it could be taken down to one pass of the table, instead of 4. Edit: But I just don't see a way of doing this, what with a transaction being able to be for both the account and partner at the same time.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 4:46 pm
I would like to thank everybody for helping me with this problem.
It is good to learn something new especially when you can ask if there are any problems.
September 6, 2010 at 3:15 am
There is one more issue on this 😉
Having code:
DECLARE
@startdate nvarchar(10), --cast datetime
@periodlength nvarchar(20), --cast int
@periodsnumber nvarchar(20), --cast int
@ph nvarchar(3),
@acc nvarchar(3),
@SQL1 nvarchar(max),
@SQLphD nvarchar(max),
@SQLphC nvarchar(max),
@SQLacctD nvarchar(max),
@SQLacctC nvarchar(max),
@SQL2 nvarchar(max),
@SQLTotal nvarchar(max)
SET @SQLphD = 'SELECT [Classification] = ''Partner Debit''' + CHAR(10)
SET @SQLphC = 'SELECT [Classification] = ''Partner Credit''' + CHAR(10)
SET @SQLacctD = 'SELECT [Classification] = ''Account Debit''' + CHAR(10)
SET @SQLacctC = 'SELECT [Classification] = ''Account Credit''' + CHAR(10)
SET @SQLTotal = 'SELECT [Classification] = ''Total''' + CHAR(10)
SET @startdate = '2009-10-28' -- probably will be fixed as today()
SET @periodlength = 7 -- number of days in each time period - DYNAMIC
SET @periodsnumber = 15 -- number of periods to be shown - DYNAMIC
SET @ph = 'PE1' -- Partner code
SET @acc = '330' -- Account code
-- Partner Debit
SELECT @SQLphD = @SQLphD
+ ',[' + [PeriodName] +'] = sum(CASE WHEN Period = ' + [id] + ' THEN Debit ELSE 0.0 END)' + CHAR(10)
FROM (SELECT cast(n AS nvarchar) as id,
PeriodName = CONVERT(varchar(3), (N*@periodlength)-(@periodlength-1)) + ' - ' + CONVERT(varchar(3), (N*@periodlength))
FROM dbo.Tally
WHERE n <= @periodsnumber
) d
ORDER BY cast(id as int)
SELECT @SQLphD = @SQLphD +
'FROM CTE2
WHERE Partner = ''' + @ph + '''
UNION ALL' + CHAR(10)
-- Partner Credit
SELECT @SQLphC = @SQLphC
+ ',[' + [PeriodName] +'] = sum(CASE WHEN Period = ' + [id] + ' THEN Credit ELSE 0.0 END)' + CHAR(10)
FROM (SELECT cast(n AS nvarchar) as id,
PeriodName = CONVERT(varchar(3), (N*@periodlength)-(@periodlength-1)) + ' - ' + CONVERT(varchar(3), (N*@periodlength))
FROM dbo.Tally
WHERE n <= @periodsnumber
) d
ORDER BY cast(id as int)
SELECT @SQLphC = @SQLphC +
'FROM CTE2
WHERE Partner = ''' + @ph + '''
UNION ALL' + CHAR(10)
-- Account Debit
SELECT @SQLacctD = @SQLacctD
+ ',[' + [PeriodName] +'] = sum(CASE WHEN Period = ' + [id] + ' THEN Debit ELSE 0.0 END)' + CHAR(10)
FROM (SELECT cast(n AS nvarchar) as id,
PeriodName = CONVERT(varchar(3), (N*@periodlength)-(@periodlength-1)) + ' - ' + CONVERT(varchar(3), (N*@periodlength))
FROM dbo.Tally
WHERE n <= @periodsnumber
) d
ORDER BY cast(id as int)
SELECT @SQLacctD = @SQLacctD +
'FROM CTE2
WHERE Account = ''' + @acc + '''
UNION ALL' + CHAR(10)
-- Account Credit
SELECT @SQLacctC = @SQLacctC
+ ',[' + [PeriodName] +'] = sum(CASE WHEN Period = ' + [id] + ' THEN Credit ELSE 0.0 END)' + CHAR(10)
FROM (SELECT cast(n AS nvarchar) as id,
PeriodName = CONVERT(varchar(3), (N*@periodlength)-(@periodlength-1)) + ' - ' + CONVERT(varchar(3), (N*@periodlength))
FROM dbo.Tally
WHERE n <= @periodsnumber
) d
ORDER BY cast(id as int)
SELECT @SQLacctC = @SQLacctC +
'FROM CTE2
WHERE Account = ''' + @acc + '''' + CHAR(10)
-- TOTAL
SELECT @SQLTotal = @SQLTotal
+ ',[' + [PeriodName] +'] = sum([' + [PeriodName] + '])' + CHAR(10)
FROM (SELECT cast(n AS nvarchar) as id,
PeriodName = CONVERT(varchar(3), (N*@periodlength)-(@periodlength-1)) + ' - ' + CONVERT(varchar(3), (N*@periodlength))
FROM dbo.Tally
WHERE n <= @periodsnumber
) d
ORDER BY cast(id as int)
SELECT @SQLTotal = @SQLTotal +
'FROM CTE3' + CHAR(10)
-- SQL1
SELECT @SQL1 =
';WITH CTE AS
(
SELECT Partner, Account, Debit, Credit,
Period = CASE WHEN CEILING(DATEDIFF(day, CONVERT(datetime, ''' + @startdate + '''), Date)/(' + @periodlength + '*1.0)) =
DATEDIFF(day, CONVERT(datetime, ''' + @startdate + '''), Date)/(' + @periodlength + '*1.0)
THEN convert(int, DATEDIFF(day, CONVERT(datetime, ''' + @startdate + '''), Date)/(' + @periodlength + '*1.0) + 1)
ELSE CEILING(DATEDIFF(day, CONVERT(datetime, ''' + @startdate + '''), Date)/(' + @periodlength + '*1.0))
END
FROM SomeTableX
WHERE Date >= CONVERT(datetime, ''' + @startdate + ''')
AND Date < DATEADD(day, (' + @periodlength + ' * ' + @periodsnumber + '), CONVERT(datetime, ''' + @startdate + '''))
), CTE2 AS
(
SELECT Partner, Account, Period, Debit = SUM(Debit), Credit = SUM(Credit)
FROM CTE
GROUP BY Partner, Account, Period
)
, CTE3 AS
(
'
-- @SQLphD + @SQLphC + @SQLacctD + @SQLacctC
SELECT @SQL2 =
'
)
SELECT * FROM CTE3
UNION ALL
'
-- @SQLTotal
EXEC (@SQL1+@SQLphD+@SQLphC+@SQLacctD+@SQLacctC+@SQL2+@SQLTotal)
Is it possible to add 1 more row (as first one) whch will be something as opening balance?
For first col it will be sum from database col (let`s say sum from column Credit = '130'). In each next col this Opening Balance should be 'Total' from previous col...
Viewing 4 posts - 46 through 48 (of 48 total)
You must be logged in to reply to this topic. Login to reply