0

I have these data in my database

+--------------+------------+
| TRX_DATE     |  AMOUNT    |
+--------------+------------+
| 2012-12-01   |  100.00    |
| 2012-12-07   |  125.00    |
+--------------+------------+

I need these data Left Join with "Generate full Month Row" like this

+--------------+------------+-------------+
|   DATE       |  TRX_DATE  |   AMOUNT    |
+--------------+------------+-------------+
| 2012-12-01   | 2012-12-01 |   100.00    |
| 2012-12-02   |    NULL    |    NULL     |
....
| 2012-12-07   | 2012-12-07 |   125.00    |
....
+--------------+------------+-------------+

How to do that thank you.

Edit : More Clarify Question

Bear0x3f
  • 142
  • 1
  • 16

2 Answers2

1

I think all you need is dates between two dates of month and then you can do left join with it. 

DECLARE @startDate DATETIME   
DECLARE @endDate DATETIME

SET @startDate = '2012-12-01'
SET @endDate = '2011-12-31';

WITH dates(Date) AS 
(
    SELECT @startdate as Date
    UNION ALL
    SELECT DATEADD(d,1,[Date])
    FROM dates 
    WHERE DATE < @enddate
)

SELECT Date
FROM dates
LEFT JOIN yourtablename ON yourtablename.trx_date=dates.date

see the SOURCE :- the link will show you some good techniques to achieve this.

Community
  • 1
  • 1
Pranav
  • 8,563
  • 4
  • 26
  • 42
0

SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.trx_date = table_name2.trx_date

You need to join on unique data from both tables.

8bitcat
  • 2,206
  • 5
  • 30
  • 59
  • COLUMN "DATE" is not real table I try to generate full month row and left join with my data Sorry for unclear question. – Bear0x3f Dec 15 '12 at 17:43