[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

dynamical select table column based on date/period

Posted on 2010-11-09
10
Medium Priority
?
478 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

650 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