Using a condition for cross join on months

Question

---

Table 1 has column date , value Table 2 has monthnumber , monthname (ie 1-12 for number and Jan - Dec) Sample Data

|Table 1|

|ColDate | value|
|1-nov-2016 | 6|

Expected Output

ColDate | value | month | monthnumber
1-nov-2016 | 6 | Nov | 11
1-nov-2016 | 0 | Dec |12

..... 0 for all other months except Nov

I used cross join between table 1 and table 2 but it gives output as

1-nov-2016 | 6 | Nov | 11
1-nov-2016 | 6 | Dec |12

..... 6 for all other months though should be 0 except Nov.

How do i do that ?

Answer 1

Try this,

DECLARE @TB1 TABLE (COLDATE VARCHAR(20),VALUE INT)
INSERT INTO @TB1
SELECT '1-NOV-2016',6

DECLARE @TB2 TABLE(MONTH VARCHAR(10),MONTHNUMBER INT)
INSERT INTO @TB2
SELECT 'NOV',11
UNION ALL
SELECT 'DEC',12

SELECT  COLDATE
        ,CASE WHEN MONTHNUMBER=11 THEN VALUE ELSE 0 END VALUE
        ,MONTH
        ,MONTHNUMBER 
FROM    @TB1
CROSS JOIN @TB2

Result: