Using a Stored Procedure as A Data Source

Question

Post reply

Using a Stored Procedure as A Data Source

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

As a workaround could I remove a generic static heading and insert the dynamic column headings (Previous Year, Previous Month, Previous Weeks) based on the current Date using a Script task that uses the Excel Object Model?

If I stored the static headings in Variables and replaced the to their original values prior to the transformation would that work or would it mess up the metadata?

Just trying to come up with a workaround.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

kramaswamy SSCoach Points: 18175 More actions · Answer 2

kramaswamy

SSCoach

Points: 18175

November 3, 2011 at 8:00 am

#1402656

As far as I know, if you try to make column headings dynamic, you're going to get inconsistent metadata. The only way to effectively pull it off is to just not use the column headers as the first row, and refer to the columns by their column numbers instead.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

kramaswamy (11/3/2011)

As far as I know, if you try to make column headings dynamic, you're going to get inconsistent metadata. The only way to effectively pull it off is to just not use the column headers as the first row, and refer to the columns by their column numbers instead.

I should have been clear.

I have resigned the thought of using the first row as column headers.

I do not intend to check the forst row contains column headings.

What I need to two SQL Queries, the First populates the First row with the dynamic column headings. For example one column is the previous year, currently 2010, anohter is 2011 YTD.

Those values will change next year. The current Month To Date and the Previous Month as well as the previous weeks changed.

I need to heading to reflect the Actual Values, 2010, 2011 YTD, etc.

After this is completed I was hoping that I could populate the sheet with the actual numbers.

I'm trying to duplicate the existing logic from a complicated Excel macro.:w00t:

Does this make sense?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

kramaswamy SSCoach Points: 18175 More actions · Answer 4

Sure - you can do it in two queries, that will work just fine, you don't even need to use variables. Or you can do it in one query, like I suggested 😛 Your query will be something like:



SELECT

        SUM(CASE WHEN DATEDIFF(yy, [Year], GETDATE()) = 1 THEN Value ELSE NULL END) AS 'PrevYear',

        SUM(CASE WHEN DATEDIFF(yy, [Year], GETDATE()) = 0 THEN Value ELSE NULL END) AS 'CurrYear',

        SUM(CASE WHEN DATEDIFF(yy, [Year], GETDATE()) = -1 THEN Value ELSE NULL END) AS 'NextYear',

        2 AS RowOrder

FROM [Table]

UNION

SELECT

	CAST(YEAR(DATEADD(yy, -1, GETDATE())) AS VARCHAR(4)) AS 'PrevYear',

	CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS 'CurrYear',

	CAST(YEAR(DATEADD(yy, 1, GETDATE())) AS VARCHAR(4)) AS 'NextYear',

	1 AS RowOrder

ORDER BY RowOrder

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Welsh Corgi

SSC Guru

Points: 116520

November 3, 2011 at 12:29 pm

#1402865

Thanks Kramaswamy! 😎

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

jd 60382 SSC Enthusiast Points: 119 More actions · Answer 6

@kramaswamy (11/3/2011)

SELECT CAST(YEAR(DATEADD(yy, -1, GETDATE())) AS VARCHAR(4)) AS 'PrevYear', CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS 'CurrYear', CAST(YEAR(DATEADD(yy, 1, GETDATE())) AS VARCHAR(4)) AS 'NextYear', 1 AS RowOrder ORDER BY RowOrder

I've been wrestling with an issue that this will solve for the past three weeks. Totally forgot about the ability to define and sort on row order.

Thanks for posting.

kramaswamy SSCoach Points: 18175 More actions · Answer 7

kramaswamy

SSCoach

Points: 18175

November 4, 2011 at 7:32 am

#1403209

No prob, glad it could help 🙂

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

I have several sheets that have Report Headings and the Column Headings are on Row 7.

I currently have mappings to Worksheets that do not have the Report Heading but have the Column Heading on Row 1.

I tried changing this but I remember how to get SSIS to ignore this.

If you use the SSIS Wizard or DTS a Screen is displayed where you can check/uncheck a box (First row contains column headings).

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/