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.


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  
day6          50
Who is Participating?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need to have a calendar table:

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

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

Open in new window

keplanAuthor Commented:
Thanks for your answer, it is excellent.
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.