?
Solved

dynamical select table column based on date/period

Posted on 2010-11-09
10
Medium Priority
?
474 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Accepted Solution

by:
Erick37 earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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.
Suggested Courses

752 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