[ebolisa]

Hi all,
I have a peculiar MSSQL DB which is used in accounting and each year is generating a new name based on the current year as 2008LH for 2008, 2009LH for 2009 and so on.
Obviously when the queries are designed within the same year, there is no problem but when we want to cross information is when the problems arise (sort of).
The following query lists the annual earnings and as you can see, it needs to be updated every year to add the latest DB.
I’m thinking to design a function which would automatically pick up all DBs regardless the year (20xxLH). Can someone pls give me some hints?
TIA

SELECT SUM(A) AS [2008], SUM(B)/> AS [2009], SUM(C) AS [2010]
FROM
(SELECT sum(amount) AS A, '0' AS B, '0' AS C 
FROM [2008LH]..d_albven
UNION ALL
SELECT '0' , sum(amount) , '0' 
FROM [2009LH]..d_albven
UNION ALL
SELECT '0' , '0' , sum(amount)  
FROM [2010LH]..d_albven) AS a

[Ionut]

Hi TIA,

As looking for some pieces of information, I got an idea to automatically generate the query you posted depending on the database name pattern. Probably it's not what you are looking for, but you may find something you can use.

--Script date : 08/22/2010
--Author : Ionut Dragu
--Purpose : example

CREATE PROCEDURE usp_Annual_Earnings
AS 
BEGIN
--declaration
DECLARE @table_name VARCHAR(20) -- it is used as a constant
DECLARE @full_table_name varchar(50) -- full name used in select clause (db..table_name)
DECLARE @database_name varchar(20) -- database name
DECLARE @iDatabaseCount INT --number of databases
DECLARE @iStartASCIIChar TINYINT
DECLARE @sql_statement NVARCHAR(500) -- used for unions 
DECLARE @tmp_sql_statement NVARCHAR(100) --used for select clauses 
DECLARE @outer_sql_statement NVARCHAR(100) -- here it will built select SUM(A) as 2008 etc
DECLARE @final_sql_statement NVARCHAR(500) -- final query that will be executed
DECLARE @iCursor INT --cursor for looping through databases
DECLARE @iInnerCursor INT 
--initialization
SET @iCursor = 0 
SET @outer_sql_statement = ''
SET @sql_statement = ''
SET @table_name = 'd_albven' --table from which we pull data
SET @iStartASCIIChar = 65 --char A 
--get the number of databases
SELECT @iDatabaseCount = (SELECT COUNT(sd.name) FROM sys.databases sd
						JOIN sysusers su ON su.sid = sd.owner_sid
						WHERE UPPER(RTRIM(LTRIM(su.name))) LIKE 'DBO' 
						AND UPPER(RTRIM(LTRIM(sd.name))) LIKE '20%LH')
--declare a cursor to loop through databases
DECLARE crs CURSOR FOR 
SELECT sd.name FROM sys.databases sd
JOIN sysusers su ON su.sid = sd.owner_sid
WHERE UPPER(RTRIM(LTRIM(su.name))) LIKE 'DBO' AND UPPER(RTRIM(LTRIM(sd.name))) LIKE '20%LH'
OPEN  crs
FETCH NEXT FROM crs INTO @database_name

--loop through databases
WHILE @@FETCH_STATUS = 0 
BEGIN

	SET @outer_sql_statement = @outer_sql_statement + 'SUM(' + CHAR(@iStartASCIIChar + @iCursor) + ') AS [' + LEFT(@database_name, 4) + ']'

	SET @full_table_name = '[' + @database_name + ']' + '..' + @table_name	
	--zeros before the current year/database
	SET @tmp_sql_statement = ''
	SET @iInnerCursor = 0 
	WHILE @iInnerCursor < @iCursor
	BEGIN
		SET @tmp_sql_statement = @tmp_sql_statement + '0 AS [' + CHAR(@iStartASCIIChar + @iInnerCursor) + '], '
		SET @iInnerCursor = @iInnerCursor + 1
	END
	--current year
	SET @tmp_sql_statement = @tmp_sql_statement + 'SUM(amount) AS [' + CHAR(@iStartASCIIChar + @iInnerCursor) + '] '
	IF @iCursor < @iDatabaseCount - 1
		 SET @tmp_sql_statement = @tmp_sql_statement + ', '
		 
	--zeros after the current year / database
	SET @iInnerCursor = @iCursor + 1 
	WHILE @iInnerCursor < @iDatabaseCount
	BEGIN
		SET @tmp_sql_statement = @tmp_sql_statement + '0 AS [' + CHAR(@iStartASCIIChar + @iInnerCursor) + ']'
		IF @iInnerCursor < @iDatabaseCount - 1  
			SET @tmp_sql_statement = @tmp_sql_statement + ', '
		SET @iInnerCursor = @iInnerCursor + 1
	END
	
	SET @sql_statement = @sql_statement + 'SELECT ' + @tmp_sql_statement + ' FROM ' + @full_table_name
	IF @iCursor < @iDatabaseCount - 1
	BEGIN
		SET @sql_statement = @sql_statement + ' UNION ALL '
		SET @outer_sql_statement = @outer_sql_statement + ', '
	END 
	
	SET @iCursor = @iCursor + 1
	FETCH NEXT FROM crs INTO @database_name
END --end loop

SET @outer_sql_statement = 'SELECT ' + @outer_sql_statement + ' FROM ('

SET @final_sql_statement = @outer_sql_statement + @sql_statement + ' ) X' 
--execute query
EXECUTE sys.sp_executesql @final_sql_statement

--clear resources
CLOSE crs
DEALLOCATE crs

END

[ebolisa]

Thank you for your help Ionut,
will play with it.
Best regards,
Emilio

BTW, TIA => Thanks In Advance

[Ionut]

Yeah, silly me. Abbreviations are killing me..

Good luck,
Ionut