asked on

dynamical select table column based on date/period

Hi Guys,

I have created a view which contains columns for the customer turnover for each month last year.
columns TL1 - TL12 (this can be changed as required to make it all work)

I wish to select a few other columns from this table (customer name, account code) but I wish to select ONLY one of the TL columns based on the current month.

I have been looking at the getdate command to get the month but I do not know how to link this into a select statement.

So please can someone advise how to select a specific column from a view based on the current month?

Note: MS SQL 2005

Erick37

select case DATEPART(month,getdate())
      when 1 then TL1
      when 2 then TL2
      when 3 then TL3
      when 4 then TL4
      --and so on through 12
      end as result

from yourtable

mmr159

SELECT
CASE
      WHEN date_column = 'date_month_desired' THEN column_desired
      WHEN date_column = 'other_date_month_desired' THEN other_column_desired
      ELSE else_column
END
FROM ...

Keep in mind the columns you select must be of same data type, so you will have to cast/convert if necessary.

dexterhome

ASKER

mmr159 - forgive me I am wrong but it appears that I would need to manually change your query month by month which is not what I want.

Erick37 - I have tried what you advise but cannot get it working, which may just be me. Please can you advise where your command fits into the query - I have attached a sample of code.

Select custcode,custname,RSM,PTD,TLcolumn
/*select case DATEPART(month,getdate())
		when 1 then TL1
		when 2 then TL2
		when 3 then TL3
		when 4 then TL4
		when 5 then TL5
		when 6 then TL6
		when 7 then TL7
		when 8 then TL8
		when 9 then TL9
		when 10	then TL10
		when 11 then TL11
		WHEN 12 THEN TL12*/
from PYR_VIEW_RSM_DATA

Open in new window

ASKER CERTIFIED SOLUTION

Erick37

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Erick37

I named the case statement column TLColumn, but noticed you already had a column by that name so you will need to rename it:

...
WHEN 12 THEN TL12
END as nameThisColumn --<<--
from PYR_VIEW_RSM_DATA

dexterhome

ASKER

Erick37 - I noticed you had and sorted it.

I have tested in my SQL and it appears to work correctly.

I actually need the query in a Orbis alert so will go away and configure that to confirm working as required before awarding points.

dexterhome

ASKER

I have now configured the Orbis alert and appears to work as required.

I have an extra bit of a question if not too much trouble.

I could do with a sum to compare current month (PTD) to the result from your query (PLY).
Ideally a divide by function.

Can this be done easily.
I had a quick look and it requires a group by command, but then it looks as if it cannot reference the created column.

Am i missing something or is this hard anyway.

snippet of the working code you provided attached

Select 
      custcode,
      custname,
      PTD,
            case DATEPART(month,getdate())
            when 1 then TL1
            when 2 then TL2
            when 3 then TL3
            when 4 then TL4
            when 5 then TL5
            when 6 then TL6
            when 7 then TL7
            when 8 then TL8
            when 9 then TL9
            when 10      then TL10
            when 11 then TL11
            WHEN 12 THEN TL12 
            END as PLY
from PYR_VIEW_RSM_DATA
where rsm = 'AG-STD'

Open in new window

Erick37

I don't quite understand the question.
Do you need a new column? How do you need to compare PTD and PLY?
Give an example of what output you need.

dexterhome

ASKER

Does not matter.
I quickly did the sum in Excel to show you but because the comparison is current month data against last year it creates a random looking figure which will only fall in line near the end of the month.

Thanks for all your help.

dexterhome

ASKER

Thanks for this.
I thought I had accepted the solution and only just noticed I had not.
Sorry about that and thanks again.