Datename() in SQL column header? Dynamic SQL?

[Ach005ki]

Hi all

I'm sorry if this question has been posted previously - I couldn't find evidence of such in six pages of searched posts; the nearest I came to was Results from Query as Headers


[blue]Background[/blue]

I have monthly sales information in a table which is stored as SalesQty1 through to SalesQty12 with 1 being the most recent previous month's data and so on.

Every month, as part of the month-end routine, the data is archived along a month - so SalesQty1's previous value is passed to SalesQty2, 2 to 3 and so on (SalesQty12's previous value is lost as this would then fall outside of the previous 12 months).

I know nothing off dynamic SQL, and can only currently use MSQuery (query engine associated with Microsoft Excel) to interrogate the database (SQL 2000). The month-names are not being extracted from any table data



[blue]Current output[/blue]

In March, I currently see
[tt][blue]StockCode Description CurrentUsage [highlight]SalesQty1[/highlight] [highlight]SalesQty2[/highlight] [highlight]SalesQty3[/highlight][/blue]
Item00001 A big shake 47 21 104 1[/tt]


[blue]Desired output[/blue]

I want my report to output the month name rather than SalesQty1, SalesQty2 etc - this will obviously change dependent upon the month in which the report is being refreshed:

In March, I'd like to see
[tt][blue]StockCode Description CurrentUsage [highlight]Feb09[/highlight] [highlight]Jan09[/highlight] [highlight]Dec08[/highlight][/blue]
Item00001 A big shake 47 21 104 1[/tt]

In April, I'd like to see
[tt][blue]StockCode Description CurrentUsage [highlight]Mar09[/highlight] [highlight]Feb09[/highlight] [highlight]Jan09[/highlight][/blue]
Item00001 A big shake 232 47 21 104[/tt]



[blue]Attempt[/blue]

I gather that in order to return the month name (e.g. Jan, Feb, Mar, etc) , I'd need something along the lines of the following in order to generate the abbreviated names of the previous month, the month before and the month before that:

DATENAME(m, DateAdd("m", -1, GETDATE()))
DATENAME(m, DateAdd("m", -2, GETDATE()))
DATENAME(m, DateAdd("m", -3, GETDATE()))

I can return this in a normal [tt]SELECT [/tt] statement.
However, when I try to implement this to return the values as column labels, I get an error message
[red][tt]Didnt expect '(' after the SELECT column list[/tt][/red]

SELECT MyTable.SalesQty1 AS DATENAME(m, DateAdd("m", -1, GETDATE()))
FROM MyDatabase.dbo.MyTable
WHERE MyTable.Warehouse = 'BL'

Mark, somewhere near Blackburn Lancs!

 

[bborissov]

WHY?
That is the job for your Frontend not for SQL Server. How to name columns in report is your Frontend issue.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Ach005ki]

Hi bb (and any / all other thread viewers)

Why is a good question; the users of this report are relying on the report to provide the data pre-formatted.

Ordinarily and usually I would agree but unfortunately I have to work within the parameters I'm set!

The report is generated via an Excel spreadsheet's database query engine (MSQuery).

I can't build an app or use a vbs macro to get the system date (or prompt the user to enter this) before refreshing the report as the users are usually running tightly locked-down versions of Excel (often on systems belonging to our clients).


I have previously resorted to a formulaic approach within Excel whereby a formula within the target-cell(s) of the column(s) check the system date / rely on the user to type in the date and then calculate the preceding month names retrospectively on a rolling 12-month basis.

This falls down when the users forgets to update the date, (resulting in the spreadsheet showing the wrong month name for a period's usage figures, or #REF errors when they delete the date entirely).

Alternatively, if the date is automatically retrieved and the user opens a non-current report and inadvertently saves this without refreshing the data then their data will appear to be corrupt (as their column headings will have changed but the data will not relate to the month names displayed).

The Excel formula method also means I have to make date calculations in days and I also have to take into account the day on which the report may be refreshed (which comes into play in February) as well as providing a wraparound contingency for when the current month is January (in which case we'd obviously loop backwards to December - from month 1 to month 12 as a preceding month).


Excel examples:

The formulas below determine the month name for the preceding month.

The first formula helps to overcome the problem with Feb only having 28 / 29 days per year.

The second formula helps to wrap the year when the current month is January. Wherever else you place the 2nd formula, it should still return the previous month; and if you copy the formula to the right by one column then this will return the preceding month's name and so on.

[blue]Cell A2:
reference today's date and ensure that if we subtract 29 days then the result is a day in the preceding month[/blue]

=( IF( DAY(TODAY()) >28, TODAY()-8, TODAY()) -29)

[blue]Column header to return the month name directly preceding the current month. [/blue]

=IF(MONTH($A$2)-(COLUMN(A2)-1)<1,TEXT(ABS((12+(MONTH($A$2)-(COLUMN(A2)-1))))*29,"mmmmmmmm"),TEXT(ABS((MONTH($A$2)-(COLUMN(A2)-1)))*29,"mmmmmmmm"))

The next column header would be as follows

=IF(MONTH($A$2)-(COLUMN[highlight](B2)[/highlight]-1)<1,TEXT(ABS((12+(MONTH($A$2)-(COLUMN[highlight](B2)[/highlight]-1))))*29,"mmmmmmmm"),TEXT(ABS((MONTH($A$2)-(COLUMN[highlight](B2)[/highlight]-1)))*29,"mmmmmmmm"))

... and so on, incrementing the highlighted formula part by one column ident as we work backwards through the month names.



SO.....

... I thought that the above, although working OK, is unsightly and relies too much on user intervention, whereas if I could code the headers into the SQL then I'd remove most of the problem (except ensuring that the user does refresh the report when they want new data and that they don't refresh when they want to preserve the existing data - but we're never going to do away with that little problem, eh?).

If anyone could therefore possibly illuminate the way for this weary apprentice of these dark arts then I'd be very grateful (not to mention more than a little relieved).

Many thanks to all with the patience to have read (or even the guile to have skipped) to the end of this arduous post!

I also gladly welcome constructive criticism of my Excel formulas if they can be simplified whilst producing the same result.


Mark, somewhere near Blackburn Lancs!

 

[RyanEK]

You could create a Dates table with a description field that links to the relative date.

 

[RiverGuy]

If you need to use dynamic SQL, something like the following would work

DECLARE @SQL NVARCHAR(2000)
DECLARE @D DATETIME
SELECT @D = GETDATE()

SELECT @SQL = ''

SELECT @SQL = @SQL + 'SELECT StockCode, Description, CurrentUsage, 
SalesQty1 AS ' + LEFT(CONVERT(VARCHAR(14), @D, 107), 3) + RIGHT(CONVERT(VARCHAR(14), @D, 107), 2) + ', ' +
'SalesQty2 AS ' + LEFT(CONVERT(VARCHAR(14), DATEADD(month, -1, @D), 107), 3) + RIGHT(CONVERT(VARCHAR(14), DATEADD(month, -1, @D), 107), 2) + ', ' +
'SalesQty3 AS ' + LEFT(CONVERT(VARCHAR(14), DATEADD(month, -2, @D), 107), 3) + RIGHT(CONVERT(VARCHAR(14), DATEADD(month, -2, @D), 107), 2) + ', ' +
'SalesQty4 AS ' + LEFT(CONVERT(VARCHAR(14), DATEADD(month, -3, @D), 107), 3) + RIGHT(CONVERT(VARCHAR(14), DATEADD(month, -3, @D), 107), 2) + ', ' +
'SalesQty5 AS ' + LEFT(CONVERT(VARCHAR(14), DATEADD(month, -4, @D), 107), 3) + RIGHT(CONVERT(VARCHAR(14), DATEADD(month, -4, @D), 107), 2) + ', ' +
'SalesQty6 AS ' + LEFT(CONVERT(VARCHAR(14), DATEADD(month, -5, @D), 107), 3) + RIGHT(CONVERT(VARCHAR(14), DATEADD(month, -5, @D), 107), 2) + ', ' +
'SalesQty7 AS ' + LEFT(CONVERT(VARCHAR(14), DATEADD(month, -6, @D), 107), 3) + RIGHT(CONVERT(VARCHAR(14), DATEADD(month, -6, @D), 107), 2) + ', ' +
'SalesQty8 AS ' + LEFT(CONVERT(VARCHAR(14), DATEADD(month, -7, @D), 107), 3) + RIGHT(CONVERT(VARCHAR(14), DATEADD(month, -7, @D), 107), 2) + ', ' +
'SalesQty9 AS ' + LEFT(CONVERT(VARCHAR(14), DATEADD(month, -8, @D), 107), 3) + RIGHT(CONVERT(VARCHAR(14), DATEADD(month, -8, @D), 107), 2) + ', ' +
'SalesQty10 AS ' + LEFT(CONVERT(VARCHAR(14), DATEADD(month, -9, @D), 107), 3) + RIGHT(CONVERT(VARCHAR(14), DATEADD(month, -9, @D), 107), 2) + ', ' +
'SalesQty11 AS ' + LEFT(CONVERT(VARCHAR(14), DATEADD(month, -10, @D), 107), 3) + RIGHT(CONVERT(VARCHAR(14), DATEADD(month, -10, @D), 107), 2) + ', ' +
'SalesQty12 AS ' + LEFT(CONVERT(VARCHAR(14), DATEADD(month, -11, @D), 107), 3) + RIGHT(CONVERT(VARCHAR(14), DATEADD(month, -11, @D), 107), 2) + 
' FROM SalesTable' 

PRINT @SQL

EXEC sp_executesql @SQL

 

[Ach005ki]

Hi RyanEK & RiverGuy

I'll review your comments but part of my problem (esp with regard to the dynamic Sql) is that I'm almost certain I can't build or execute stored procs in MSQuery, and so I don't know where that leaves me with regard to variable declarations.

Ryan, the dates are not contained within my data.

However, I'm now wondering if I add in getdate() col for each line then I may be able to create a more simplified excel formula within the column headers to return previous month names.

Thanks for your input anyway. Its 2am and I'm due some sleep before our youngest decides it is morning!

Mark, somewhere near Blackburn Lancs!

 

[jbenson001]

This really isn't a question for the SQL Server Fourum then. You will need to either post in one of the Excel forums or find examples online for MSQuery