Solved

dynamical select table column based on date/period

Posted on 2010-11-09
10
451 Views
Last Modified: 2012-05-10
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
Comment
Question by:dexterhome
  • 5
  • 4
10 Comments
 
LVL 32

Expert Comment

by:Erick37
ID: 34093121
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
 
LVL 7

Expert Comment

by:mmr159
ID: 34093158
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
 
LVL 5

Author Comment

by:dexterhome
ID: 34093271
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 32

Accepted Solution

by:
Erick37 earned 500 total points
ID: 34093373
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
 
LVL 32

Expert Comment

by:Erick37
ID: 34093403
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
 
LVL 5

Author Comment

by:dexterhome
ID: 34093429
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
 
LVL 5

Author Comment

by:dexterhome
ID: 34094327
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
 
LVL 32

Expert Comment

by:Erick37
ID: 34094406
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
 
LVL 5

Author Comment

by:dexterhome
ID: 34094670
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
 
LVL 5

Author Closing Comment

by:dexterhome
ID: 34168911
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question