Missing records from one table in SQL Server 2008R2

Question

Table 1:

Date           PlacementID     CampaignID    Impressions
04/01/2014     100             10            1000
04/01/2014     101             10            1500
04/01/2014     100             11            500

Table 2:

Date           PlacementID      CampaignID    Cost
04/01/2014     100             10            5000
04/01/2014     101             10            6000
04/01/2014     100             11            7000
04/01/2014     103             10            8000

When I have joined this table using Full Join and Left Join statement, I am not able to get uncommon record which is last row in table2 that display PlacementID 103 and campaignID 10 and Cost 8000. However I have searched all raw data and file but this missing records are not common between two sources. However, I want to include this records in final table. How can I do that? This two table are two different source and I have got results only common records.

Moreover, when I found out that missing value is exact value that are required in final figure so want to include every thing. I am including my SQL script below:

SELECT A.palcementid, 
       A.campaignid, 
       A.date, 
       Sum(A.impressions) AS Impressions, 
       Sum(CASE 
             WHEN C.placement_count > 1 THEN ( B.cost / C.placement_count ) 
             ELSE B.cost 
           END)           AS Cost 
FROM   table1 A 
       FULL JOIN table2 B 
              ON A.placementid = B.placementid 
                 AND A.campaignid = B.campaignid 
                 AND A.date = B.date 
       LEFT JOIN (SELECT Count(A.placementid) AS Placement_Count, 
                         placementid. campaignid, 
                         date 
                  FROM   table1 
                  GROUP  BY placementid, 
                            campaignid, 
                            date) c 
              ON A.placementid = C.placementid 
                 AND A.campaignid = C.campaignid 
                 AND A.date = C.date 
GROUP  BY A.placementid, 
          A.campaignid, 
          A.date

I am dividing Cost by placement because in source the cost was allocated for one placement only and one time so I have to divide those because in actual table the same Placementid repeat more than 1 times on same date.

Hi there. If you want to have a conversation as to why [your recent question](https://stackoverflow.com/questions/44422870/convert-transfer-multiple-value-into-columns/44424157) was closed as too broad (and then unexpectedly deleted), do please ping me, and I will try to give reasons. My broad advice, which I have added to some of your other questions, is that it would be good to see you accepting some of your answers. Let me know if you need help with that. — halfer, Jun 09 '17 at 11:29

jpw · Answer 1 · 2014-04-01T14:38:37.927

As you didn't provide any expected output I guessing here but if the result you want is this:

PlacementID CampaignID  Date                    Impressions Cost
----------- ----------- ----------------------- ----------- -----------
100         10          2014-04-01 02:00:00.000 1000        5000
100         11          2014-04-01 02:00:00.000 500         7000
101         10          2014-04-01 02:00:00.000 1500        6000
103         10          2014-04-01 02:00:00.000 NULL        8000

Then the following query should do it:

SELECT COALESCE(A.PlacementID,b.placementid) AS PlacementID,
       COALESCE(A.campaignid, b.campaignid) AS CampaignID, 
       COALESCE(A.date, b.date) AS [Date],
       SUM(A.impressions) AS Impressions,
       SUM(CASE
             WHEN C.placement_count > 1 THEN ( B.cost / C.placement_count )
             ELSE B.cost
           END ) AS Cost
FROM   table1 A
       FULL JOIN table2 B
              ON A.[PlacementID] = B.placementid
                 AND A.campaignid = B.campaignid
                 AND A.date = B.date
       LEFT JOIN (SELECT COUNT(PlacementID) AS Placement_Count,
                         placementid, campaignid,
                         date
                  FROM   table1
                  GROUP  BY placementid,
                            campaignid,
                            date) c
              ON A.[PlacementID] = C.placementid
                 AND A.campaignid = C.campaignid
                 AND A.date = C.date
GROUP  BY COALESCE(A.PlacementID, B.PlacementID),
          COALESCE(A.campaignid, b.campaignid), 
          COALESCE(A.date, b.date)

Sample SQL Fiddle

jpw: yes I want the same result as you have shown in your table but when i tried your solution using COALESCE, the required result didn't appear. It was a same result that I got before. — ABD, Apr 01 '14 at 14:36
@user3299024 I did change some other things beside the COALESCE; Take a look at this SQL Fiddle: http://www.sqlfiddle.com/#!3/cc480e/12 — jpw, Apr 01 '14 at 14:39
@user3299024: I am sorry sir but I didn't see any changes except COALESCE as jpw has done before. but, Thanks both of you. can you please help me out to solve this issue as it's urgent. — ABD, Apr 01 '14 at 14:46
@user3299024 Did you look at the SQL Fiddle and try the query as entered there? — jpw, Apr 01 '14 at 15:07
Yes I did try in SQL fiddle it was working there whatever you have wrote in Script but when I applied that concept in SQL management studio it is not working. — ABD, Apr 01 '14 at 15:14

Missing records from one table in SQL Server 2008R2

1 Answers1