Derive information from a transaction table and unpivote the information on SQL 2008

I've a table which got the data as below example: I need to organize the data according to the
sample below. I really appreciate if you can help me to write a SQL query to achieve this.


Table:

process_date              transaction_from_date                         transaction_to_date           rate            

DAY4                                         DAY1                           DAY3                100.00
DAY6                                              ---                        --                                       50.00  

Require data format:

DATE         rate
day1         100    
day2         100  
day3         100  
day4
day5
day6          50
-
-
-
-
-
-
day31
keplanAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need to have a calendar table:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

from there, you can left join to a (inline) view like this:

select c.date
  , sum(t.rate)
 from calendar c
 left join yourtable t
    on t.transaction_from_date <= c.date
  and t.transaciton_to_date >= c.date
  group by c.date

Open in new window

0
 
keplanAuthor Commented:
Thanks for your answer, it is excellent.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.