CODEWITHSUNDEEP
sql-server
Revathi Vijay
Revathi Vijay

Reputation: 1308

Temptable in SQL Server

Query #1: 

SELECT a.*  
INTO #TempTable1 
FROM
    (SELECT 
         Vendor,
         CASE WHEN CONVERT(varchar(50), DateModified, 101)='01/01/1900' THEN '' ELSE CONVERT(varchar(50), DateModified, 101) END AS 'Date of Last Check', 
         CASE WHEN CONVERT(varchar(50), PaycommissionDate, 101)='01/01/1900' THEN '' ELSE CONVERT(varchar(50), PaycommissionDate, 101) END AS 'Date of check for month',
         SUM([Original $ Total]) 'Amount'
     FROM
         Tbl_Commission_Reconcilation  
     WHERE
         Vendor IS NOT NULL
         AND MONTH([Order Date]) = MONTH (GETDATE())
     GROUP BY
         Tbl_Commission_Reconcilation.Vendor,
         CONVERT(varchar(50), DateModified, 101),
         [Sales Rep], PaycommissionDate) a

Output:

Vendor              Date of Last Check   Date of check for month       Amount
-----------------------------------------------------------------------------
Boston Warehouse    12/12/2017            12/12/2017                   919.00
Woodlink            12/12/2017            12/12/2017                   979.86

Query #2:

SELECT b.*  
INTO #TempTable2  
FROM
    (SELECT 
         [Sales Rep],
         SUM([Commission $ paid]) 'Commission $ paid'
     FROM 
         Tbl_Commission_Reconcilation  
     WHERE 
         Vendor IS NOT NULL
         AND MONTH([Order Date]) = MONTH (GETDATE())
     GROUP BY 
         [Sales Rep]) b

Output 2:

Sales Rep       Commission $ paid
---------------------------------
Tammy Hanson    379.77

Final query:

select * 
from #TempTable1, #TempTable2

Final output:

Vendor      DateofLastCheck Dateofcheckformonth Amount  Sales Rep      Commpaid
Boston 
Warehouse   12/12/2017     12/12/2017          919.00   Tammy Hanson    379.77
Woodlink    12/12/2017     12/12/2017          979.86   Tammy Hanson    379.77

Output 2 shows only one commission paid 379.77 for Tammy Hanson.But in final output shows 2 times commission paid 379.77.How to rectify this problem? How to show only one commission paid in the final output?Please, any one helps me.

Upvotes: 2

Views: 59

Answers (3)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You should also add row_number to your queries

For Table1

SELECT a.*  
INTO #TempTable1 
FROM
    (SELECT 
         Vendor,
         CASE WHEN CONVERT(varchar(50), DateModified, 101)='01/01/1900' THEN '' ELSE CONVERT(varchar(50), DateModified, 101) END AS 'Date of Last Check', 
         CASE WHEN CONVERT(varchar(50), PaycommissionDate, 101)='01/01/1900' THEN '' ELSE CONVERT(varchar(50), PaycommissionDate, 101) END AS 'Date of check for month',
         SUM([Original $ Total]) 'Amount',
         ROW_NUMBER() OVER(ORDER BY Vendor, DateModified, [Sales Rep]) RN
     FROM
         Tbl_Commission_Reconcilation  
     WHERE
         Vendor IS NOT NULL
         AND MONTH([Order Date]) = MONTH (GETDATE())
     GROUP BY
         Tbl_Commission_Reconcilation.Vendor,
         CONVERT(varchar(50), DateModified, 101),
         [Sales Rep], PaycommissionDate) a

For Table 2

SELECT b.*  INTO #TempTable2  
    from
    (SELECT [Sales Rep],SUM([Commission $ paid]) 'Commission $ paid',
        ROW_NUMBER() OVER(ORDER BY [Sales Rep]) RN
    from Tbl_Commission_Reconcilation  where Vendor is not null and Month([Order Date])= MONTH (Getdate())
    group by [Sales Rep])b

and also left join table1 and table2 with this RN column

select 
  *
from #TempTable1 t1
    LEFT JOIN #TempTable2 t2
ON t1.RN = t2.RN

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35583

It appears you can just do one query. The larger query already has [sales rep] in the group by clause, so why not just sum the commission?

SELECT
      a.* INTO #TempTable1
FROM (
      SELECT
            Vendor
          , CASE WHEN CONVERT(varchar(50), DateModified, 101) = '01/01/1900' THEN '' ELSE CONVERT(varchar(50), DateModified, 101) END
            AS 'Date of Last Check'
          , CASE WHEN CONVERT(varchar(50), PaycommissionDate, 101) = '01/01/1900' THEN '' ELSE CONVERT(varchar(50), PaycommissionDate, 101) END
            AS 'Date of check for month'
          , SUM([Original $ Total]) 'Amount'
          , [Sales Rep]
          , SUM([Commission $ paid]) 'Commission $ paid'
      FROM Tbl_Commission_Reconcilation
      WHERE Vendor IS NOT NULL
      AND MONTH([Order Date]) = MONTH(GETDATE())
      GROUP BY
            Tbl_Commission_Reconcilation.Vendor
          , CONVERT(varchar(50), DateModified, 101)
          , [Sales Rep]
          , PaycommissionDate
) a

Upvotes: 2

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

You are getting the duplicate values because you are using a Cross Join ( Cartesian Product) here. Please specify at least 1 condition to match the records from both tables so that the values will be displayed only against the desired records.

As per your current query, it will return all the possible combinations of rows in both tables. change it like this

select * from #TempTable1 T1,#TempTable2  T2 WHERE T1.ColumnName = T2.ColumnName

or you can also say

select * from #TempTable1 T1
inner join #TempTable2  T2 
WHERE T1.ColumnName = T2.ColumnName

-- if you want records that have matches on both tables

or

select * from #TempTable1 T1
    left join #TempTable2  T2 
    WHERE T1.ColumnName = T2.ColumnName

-- if you want records from #TempTable1 and display null for TempTable2 if no matching records

Upvotes: 2

Related Questions