September 2, 2010 at 6:32 am
Almost working:
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 = 18 -- number of periods to be shown - DYNAMIC
SET @ph = 'PE1' -- Partner code
SET @acc = '330' -- Account code
-- Partner Debit
SELECT @SQLphD = @SQLphD
+ ',[Period ' + [id] +'] = sum(CASE WHEN Period = ' + [id] + ' THEN Debit ELSE 0.0 END)' + CHAR(10)
FROM (SELECT cast(n AS nvarchar) as id
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
+ ',[Period ' + [id] +'] = sum(CASE WHEN Period = ' + [id] + ' THEN Credit ELSE 0.0 END)' + CHAR(10)
FROM (SELECT cast(n AS nvarchar) as id
FROM dbo.Tally
WHERE n <= @periodsnumber
) d
ORDER BY id
SELECT @SQLphC = @SQLphC +
'FROM CTE2
WHERE Partner = ''' + @ph + '''
UNION ALL' + CHAR(10)
-- Account Debit
SELECT @SQLacctD = @SQLacctD
+ ',[Period ' + [id] +'] = sum(CASE WHEN Period = ' + [id] + ' THEN Debit ELSE 0.0 END)' + CHAR(10)
FROM (SELECT cast(n AS nvarchar) as id
FROM dbo.Tally
WHERE n <= @periodsnumber
) d
ORDER BY id
SELECT @SQLacctD = @SQLacctD +
'FROM CTE2
WHERE Account = ''' + @acc + '''
UNION ALL' + CHAR(10)
-- Account Credit
SELECT @SQLacctC = @SQLacctC
+ ',[Period ' + [id] +'] = sum(CASE WHEN Period = ' + [id] + ' THEN Credit ELSE 0.0 END)' + CHAR(10)
FROM (SELECT cast(n AS nvarchar) as id
FROM dbo.Tally
WHERE n <= @periodsnumber
) d
ORDER BY id
SELECT @SQLacctC = @SQLacctC +
'FROM CTE2
WHERE Account = ''' + @acc + '''' + CHAR(10)
-- TOTAL
SELECT @SQLTotal = @SQLTotal
+ ',[Period ' + [id] +'] = sum([Period ' + [id] + '])' + CHAR(10)
FROM (SELECT cast(n AS nvarchar) as id
FROM dbo.Tally
WHERE n <= @periodsnumber
) d
ORDER BY id
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)
Problems/to do:
- with more periods (>10) there is complete mess .... why?
- another test: periodlength = 30 ... also totally different results... why?
- have to figure out better way of getting numbers 1,2,3,4,... than as it is now from table
- Comumns name like 1-7 8-14 not Period 1, Period 2
September 2, 2010 at 6:51 am
Bezan (9/2/2010)
- with more periods (>10) there is complete mess .... why?
Hmm, a real mess indeed. Not sure why, possibly some disconnect between criteria for each select?
Bezan (9/2/2010)
- another test: periodlength = 30 ... also totally different results... why?
Seems to be linked to the above - works fine with <10 periods, not when over
Bezan (9/2/2010)
- have to figure out better way of getting numbers 1,2,3,4,... than as it is now from table
The method relies on @var = @var + column concatenation, so you have to use the tally table. You could replace this with a looping construct as in my example. But I don't see a problem with using the tally table.
Bezan (9/2/2010)
- Comumns name like 1-7 8-14 not Period 1, Period 2
Again, just integrate the code from my example.
September 2, 2010 at 6:54 am
Bezan (9/2/2010)
Problems/to do:- have to figure out better way of getting numbers 1,2,3,4,... than as it is now from table
- Comumns name like 1-7 8-14 not Period 1, Period 2
Column Names:
SELECT N,
PeriodName = CONVERT(varchar(3), (N*@periodlength)-(@periodlength-1)) + ' - ' +
CONVERT(varchar(3), (N*@periodlength))
FROM dbo.TALLY
WHERE N <= @periodsnumber
You could make this into another CTE, and just join to it to get the name.
This does assume you have a tally table. If you don't, please read Tally Table: What it is and how to use it[/url] to learn about one, and how to set one up.
Better way of getting numbers: I guess you're talking about the [Period #] = #? Just use the tally table again.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 7:01 am
periodlength = 30
periodsnumber = 5
irobertson way result:
PartnerDebit15.8010.50NULLNULLNULLNULLNULLNULLNULLNULL
PartnerCredit27.4017.60NULLNULLNULLNULLNULLNULLNULLNULL
AccountDebit15.5010.30NULLNULLNULLNULLNULLNULLNULLNULL
AccountCredit27.1018.20NULLNULLNULLNULLNULLNULLNULLNULL
TotalTotal85.8056.60NULLNULLNULLNULLNULLNULLNULLNULL
WayneS way result (only 5 cols...):
Partner Debit15.8010.500.000.000.00
Partner Credit27.4017.600.000.000.00
Account Debit15.5010.300.000.000.00
Account Credit27.1018.200.000.000.00
Total85.8056.600.000.000.00
my sql result:
Partner Debit15.8010.500.000.000.000.000.000.000.000.00
Partner Credit27.400.0017.600.000.000.000.000.000.000.00
Account Debit15.500.0010.300.000.000.000.000.000.000.00
Account Credit27.100.0018.200.000.000.000.000.000.000.00
Total85.800.0010.5046.100.000.000.000.000.000.00
periodlength = 7
periodsnumber = 15
irobertson way result:
PartnerDebit3.704.803.701.102.502.903.501.902.20NULLNULLNULLNULLNULLNULL
PartnerCredit7.008.105.102.304.905.305.102.304.90NULLNULLNULLNULLNULLNULL
AccountDebit5.003.203.701.102.502.903.301.902.20NULLNULLNULLNULLNULLNULL
AccountCredit9.405.405.102.304.905.305.702.304.90NULLNULLNULLNULLNULLNULL
TotalTotal25.1021.5017.606.8014.8016.4017.608.4014.20NULLNULLNULLNULLNULLNULL
my:
Partner Debit3.704.803.701.102.502.903.501.902.200.000.000.000.000.000.00
Partner Credit7.000.000.000.000.000.000.008.105.102.304.905.305.102.304.90
Account Debit5.000.000.000.000.000.000.003.203.701.102.502.903.301.902.20
Account Credit9.400.000.000.000.000.000.005.405.102.304.905.305.702.304.90
Total25.105.7012.3013.5014.106.5012.004.803.701.102.502.903.5018.6016.10
September 2, 2010 at 7:04 am
Bezan (9/2/2010)
Problems/to do:- with more periods (>10) there is complete mess .... why?
- another test: periodlength = 30 ... also totally different results... why?
- have to figure out better way of getting numbers 1,2,3,4,... than as it is now from table
- Comumns name like 1-7 8-14 not Period 1, Period 2
Partner Debit orders by cast(id as int); All others are ordering by just id. Since this is a char datatype, it orders 1,10,11,2,20,21,3,30... Change all to cast(id as int) (including the total section)
So, does this take care of all of your problems?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 7:09 am
Bumped up the content in the temp table a bit:
select count(*) from SomeTableX = 2080
edit: forgot params
SET @periodslength = 6
SET @periodsnumber = 9
Test
dbcc dropcleanbuffers
dbcc freeproccache
set statistics io on
run CTE version
Table 'Tally'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tally'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tally'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tally'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tally'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(5 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SomeTableX'. Scan count 8, logical reads 120, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
dbcc dropcleanbuffers
dbcc freeproccache
run Pivot version
Table 'SomeTableX'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(6 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
(5 row(s) affected)
Table '#interim'. Scan count 8, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Interestingly, as I increase the amount of data in the table, the number of logical reads on the cte solution increases. Any idea why?
September 2, 2010 at 7:22 am
Maybe final ver:
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)
There is no WayneS improvement: "One thing that can be done to improve on my solution, it to take the first 2 CTEs and pump that data into a temp table."
There was something wrong after changing it... I`ll try once again to do this and to have code in 1 part without need to run this:
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]
September 2, 2010 at 7:25 am
irobertson (9/2/2010)
Interestingly, as I increase the amount of data in the table, the number of logical reads on the cte solution increases. Any idea why?
Yes - you're calling CTE2 5 times, and that calls CTE, which calls the table. Each call to CTE2 does the full select statement all the way down the line to the table. More data = more reads.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 7:34 am
Bezan (9/2/2010)
There is no WayneS improvement: "One thing that can be done to improve on my solution, it to take the first 2 CTEs and pump that data into a temp table."There was something wrong after changing it... I`ll try once again to do this and to have code in 1 part without need to run this:
Here's your "maybe final ver", with my suggested change:
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 #temp
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 #temp
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 #temp
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 #temp
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)
SET @SQL1 = ';WITH CTE3 AS
(
'
-- @SQLphD + @SQLphC + @SQLacctD + @SQLacctC
SELECT @SQL2 =
'
)
SELECT * FROM CTE3
UNION ALL
'
-- @SQLTotal
;WITH CTE AS
(
SELECT Partner, Account, Debit, Credit,
Period = CASE WHEN CEILING(DATEDIFF(day, @startdate, Date)/(@periodlength*1.0)) =
DATEDIFF(day, @startdate, Date)/(@periodlength*1.0)
THEN convert(int, DATEDIFF(day, @startdate, Date)/(@periodlength*1.0) + 1)
ELSE CEILING(DATEDIFF(day, @startdate, Date)/(@periodlength*1.0))
END
FROM SomeTableX
WHERE Date >= @startdate
AND Date < DATEADD(day, (cast(@periodlength AS INT) * cast(@periodsnumber AS INT)), @startdate)
), CTE2 AS
(
SELECT Partner, Account, Period, Debit = SUM(Debit), Credit = SUM(Credit)
FROM CTE
GROUP BY Partner, Account, Period
)
SELECT *
INTO #temp
FROM CTE2
EXEC (@SQL1+@SQLphD+@SQLphC+@SQLacctD+@SQLacctC+@SQL2+@SQLTotal)
/*
print @SQL1
print @SQLphD
print @SQLphC
print @SQLacctD
print @SQLacctC
print @SQL2
print @SQLTotal
*/
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 7:46 am
I just modified it to combine all the sql generation that runs against the tally table to run in just one select statement - reducing the # of reads against the tally table.
if OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
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
SELECT @SQLphD = @SQLphD -- Partner Debit
+ ',[' + [PeriodName] +'] = sum(CASE WHEN Period = ' + [id] + ' THEN Debit ELSE 0.0 END)' + CHAR(10),
@SQLphC = @SQLphC -- Partner Credit
+ ',[' + [PeriodName] +'] = sum(CASE WHEN Period = ' + [id] + ' THEN Credit ELSE 0.0 END)' + CHAR(10),
@SQLacctD = @SQLacctD -- Account Debit
+ ',[' + [PeriodName] +'] = sum(CASE WHEN Period = ' + [id] + ' THEN Debit ELSE 0.0 END)' + CHAR(10),
@SQLacctC = @SQLacctC -- Account Credit
+ ',[' + [PeriodName] +'] = sum(CASE WHEN Period = ' + [id] + ' THEN Credit ELSE 0.0 END)' + CHAR(10),
@SQLTotal = @SQLTotal -- TOTAL
+ ',[' + [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 @SQLphD = @SQLphD +
'FROM #temp
WHERE Partner = ''' + @ph + '''
UNION ALL' + CHAR(10),
@SQLphC = @SQLphC +
'FROM #temp
WHERE Partner = ''' + @ph + '''
UNION ALL' + CHAR(10),
@SQLacctD = @SQLacctD +
'FROM #temp
WHERE Account = ''' + @acc + '''
UNION ALL' + CHAR(10),
@SQLacctC = @SQLacctC +
'FROM #temp
WHERE Account = ''' + @acc + '''' + CHAR(10),
@SQLTotal = @SQLTotal +
'FROM CTE3' + CHAR(10)
SET @SQL1 = ';WITH CTE3 AS
(
'
-- @SQLphD + @SQLphC + @SQLacctD + @SQLacctC
SELECT @SQL2 =
'
)
SELECT * FROM CTE3
UNION ALL
'
-- @SQLTotal
;WITH CTE AS
(
SELECT Partner, Account, Debit, Credit,
Period = CASE WHEN CEILING(DATEDIFF(day, @startdate, Date)/(@periodlength*1.0)) =
DATEDIFF(day, @startdate, Date)/(@periodlength*1.0)
THEN convert(int, DATEDIFF(day, @startdate, Date)/(@periodlength*1.0) + 1)
ELSE CEILING(DATEDIFF(day, @startdate, Date)/(@periodlength*1.0))
END
FROM SomeTableX
WHERE Date >= @startdate
AND Date < DATEADD(day, (cast(@periodlength AS INT) * cast(@periodsnumber AS INT)), @startdate)
), CTE2 AS
(
SELECT Partner, Account, Period, Debit = SUM(Debit), Credit = SUM(Credit)
FROM CTE
GROUP BY Partner, Account, Period
)
SELECT *
INTO #temp
FROM CTE2
EXEC (@SQL1+@SQLphD+@SQLphC+@SQLacctD+@SQLacctC+@SQL2+@SQLTotal)
/*
print @SQL1
print @SQLphD
print @SQLphC
print @SQLacctD
print @SQLacctC
print @SQL2
print @SQLTotal
*/
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 7:47 am
Here's my attempt at a solution
SET @sql = 'SELECT CASE ClassificationNo WHEN 1 THEN ''Partner Debit'' WHEN 2 THEN ''Partner Credit'' WHEN 3 THEN ''Account Debit'' WHEN 4 THEN ''Account Credit'' ELSE ''Total'' END AS [Classification]
'
SELECT @sql = @sql + ',SUM(CASE WHEN [Date] BETWEEN '''+startdate+''' AND '''+enddate+''' THEN Value ELSE 0.00 END) AS ['+colname+']
'
FROM (SELECT N,CONVERT(char(8),DATEADD(day,(N-1)*@periodlength,@startdate),112) AS [startdate],
CONVERT(char(8),DATEADD(day,(N*@periodlength)-1,@startdate),112) AS [enddate],
CAST(((N-1)*@periodlength)+1 as varchar)+'-'+CAST((N*@periodlength) as varchar) AS [colname]
FROM dbo.Tally
WHERE N BETWEEN 1 AND @periodsnumber) a
ORDER BY N ASC
SET @sql = @sql + ' FROM (SELECT 1 AS [ClassificationNo],[Date],Debit AS [Value] FROM dbo.SomeTableX WHERE [Partner] = '''+@ph+'''
UNION ALL
SELECT 2,[Date],Credit AS [Value] FROM dbo.SomeTableX WHERE [Partner] = '''+@ph+'''
UNION ALL
SELECT 3,[Date],Debit AS [Value] FROM dbo.SomeTableX WHERE Account = '''+@acc+'''
UNION ALL
SELECT 4,[Date],Credit AS [Value] FROM dbo.SomeTableX WHERE Account = '''+@acc+'''
) x
GROUP BY ClassificationNo WITH ROLLUP
ORDER BY GROUPING(ClassificationNo) ASC,ClassificationNo ASC'
print @sql
EXECUTE(@sql)
Far away is close at hand in the images of elsewhere.
Anon.
September 2, 2010 at 7:50 am
Bezan,
I've really enjoyed this exercise. It's nice to see someone that isn't posting "do it all for me" - you are working the problem, reading what is being suggested, implementing it. I enjoy helping people that are trying to learn, and I really thank you for being, well, you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 7:58 am
I'll second that Wayne, it's nice to see.
September 2, 2010 at 8:00 am
David Burrows (9/2/2010)
Here's my attempt at a solution
Whoa... VERY NICE!
Edit: I really like using the dates in the case statements... kinda obvious when you see it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 8:11 am
Bezan,
David's solution rocks. Just use that.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply