• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

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
0
dexterhome
Asked:
dexterhome
  • 5
  • 4
1 Solution
 
Erick37Commented:
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
0
 
mmr159Commented:
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.
0
 
dexterhomeAuthor Commented:
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

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Erick37Commented:
Select
      custcode,
      custname,
      RSM,
      PTD,
      TLcolumn,
      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 TLcolumn
from PYR_VIEW_RSM_DATA
0
 
Erick37Commented:
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
0
 
dexterhomeAuthor Commented:
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.
0
 
dexterhomeAuthor Commented:
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

0
 
Erick37Commented:
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.
0
 
dexterhomeAuthor Commented:
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.
0
 
dexterhomeAuthor Commented:
Thanks for this.  
I thought I had accepted the solution and only just noticed I had not.
Sorry about that and thanks again.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now