0

This is the first temp table

    CREATE TABLE #tempvesvoy
(
    scn VARCHAR(10),
    vsl_name VARCHAR(150),
    act_arr_dt_tm DATETIME,
    act_dept_dt_tm DATETIME,
    del_remarks VARCHAR(150)
)

INSERT INTO #tempvesvoy


SELECT t1.scn, t1.vsl_name, t1.act_arr_dt_tm, t1.act_dept_dt_tm, t1.del_remarks
FROM vesvoy t1
WHERE ((@move_type= 'ATA' AND MONTH(t1.act_arr_dt_tm) = @month AND YEAR(t1.act_arr_dt_tm)= @year) OR
(@move_type= 'ATD' AND MONTH(t1.act_dept_dt_tm) = @month AND YEAR(t1.act_dept_dt_tm)= @year))

Second Temp Table

    CREATE TABLE #TempVesselInvoice
(

    inv_num_vsl VARCHAR(10),
    sum_vsl_inv DECIMAL(12,2)
)

INSERT INTO #TempVesselInvoice

SELECT SUM(CAST(t1.inv_amt as decimal(12,2)))as sum_vsl_inv, t1.inv_num as inv_num_vsl
FROM pbosinvoice t1
LEFT JOIN pbosinvoiceitem t2 ON t1.id = t2.master_id
WHERE t1.inv_type = 'I01'
GROUP BY t1.inv_num, t2.scn

The result i get is this 

SCN         vsl_name    act_arr_dt_tm  act_dept_dt_tm   del_remarks   sum_vsl_inv   inv_num_vsl
16120043    Asd         2016-12-21     00:00:00.000     NULL          NULL            NULL  
16120040    UNI AMPLE   2016-12-17     00:06:00.000     NULL          NULL            NULL

The problem is some of the data ,have 1 scn and can have many inv_num_vsl from temp table 2 how to show all the inv_num_vsl based on the scn. I use left join but it result shows null.

hunt
  • 321
  • 2
  • 17
  • The output you showed us, having six columns, does not match the query, which generates only two columns. – Tim Biegeleisen Mar 02 '17 at 01:37
  • What is the relationship between the two temp tables and what is the output that you expect ? – Ian Kenney Mar 02 '17 at 01:42
  • @IanKenney the relationship between the 2 table is the scn, the output that i want is eg scn for 16120043 can have 2 inv_num_vsl. but now the result is showing null. – hunt Mar 02 '17 at 01:56
  • but you do not have scn in the second table – Ian Kenney Mar 02 '17 at 01:58
  • I agree to @Tim Biegeleisen, not just 6 but 7 columns. The result is different from the query itself. The things is, the result is concatenated from the first query above. – Vijunav Vastivch Mar 02 '17 at 02:00
  • try reading this http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field I suspect that is what you are trying to do with the inv_num_vsl column ? – Ian Kenney Mar 02 '17 at 02:04

0 Answers0