Dear Group:
I am missing something and not sure what.
Below, is the output and query when I do a simple select statement and these numbers are correct.
SELECT
A.ReportDate,
A.Site,
FORMAT(A.ReportDate, 'yyy') + '-' + FORMAT(A.ReportDate, 'MM') AS Report_Slicer,
A.Pfolio, A.C_Type,
SUM(A.Calls) AS Calls,
SUM(A.HSec) AS HSec,
SUM(A.Accept) AS Accept,
SUM(A.Reject) AS Reject,
SUM(A.ASec) AS ASec,
SUM(A.RSec) AS RSec
FROM
Output_1 A
GROUP BY
A.ReportDate,
A.Pfolio,
A.Site,
A.C_Type
ReportDate Site Report_Slicer Pfolio C_Type Calls HSec Accept Reject ASec RSec
2020-03-01 BA 2020-03 Brd 6032.00 1803005.00 180 1329 39405.87 50507.74
2020-03-01 CE 2020-03 Brd 958.00 276525.00 63 388 15113.98 16216.97
I am trying to join another table to the above, but when I do, the numbers are all identical and I am not sure what I am missing here.
SELECT
A.ReportDate,
A.Site,
FORMAT(A.ReportDate, 'yyy') + '-' + FORMAT(A.ReportDate, 'MM') AS Report_Slicer,
A.Pfolio, A.C_Type,
SUM(A.Calls) AS Calls,
SUM(A.HSec) AS HSec,
SUM(A.Accept) AS Accept,
SUM(A.Reject) AS Reject,
SUM(A.ASec) AS ASec,
SUM(A.RSec) AS RSec,
B.LC
FROM
Output_1 A
JOIN LC B ON A.ReportDate = B.Report_Date AND A.Site = B.Site
GROUP BY
A.ReportDate,
A.Pfolio,
A.Site,
B.LC,
A.C_Type
ReportDate Site Report_Slicer Pfolio C_Type Calls HSec Accept Reject ASec RSec LC
2020-03-01 BA 2020-03 Brands 6032.00 1803005.00 180 1329 39405.87 50507.74 LC2
2020-03-01 BA 2020-03 Brands 6032.00 1803005.00 180 1329 39405.87 50507.74 LC5
2020-03-01 BA 2020-03 Brands 6032.00 1803005.00 180 1329 39405.87 50507.74 Tenured
2020-03-01 CE 2020-03 Brands 958.00 276525.00 63 388 15113.98 16216.97 LC5
2020-03-01 CE 2020-03 Brands 958.00 276525.00 63 388 15113.98 16216.97 Tenured
I am trying to add in the LC, so I get a breakdown of the numbers based on the LC. As you can see, my data for C_Type, Calls, HSec, Accept are all identical and incorrect. I am expecting to see a breakdown of all the various data points by LC (the last column) but I am missing something and I am not sure what.
Was hoping my explanation is information is enough that someone can show me what I am missing here. For instance, I fully expect 3 records for Site 'BA', as it has three LC values (LC2, LC5, Tenure).
June 29, 2020 at 8:28 pm
Does column LC exist in table A?
If not, it would seem that this breakdown is not possible.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 29, 2020 at 8:55 pm
deleted answer
June 30, 2020 at 11:57 am
Unfortunately it does not exist in Table A 🙁
Unfortunately it does not exist in Table A 🙁
As the numbers you are summing exist in a form where they are not broken down by LC, you cannot retrospectively add that breakdown, unless there are other columns in Table A from which you can somehow derive LC.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 30, 2020 at 12:19 pm
I understand what you are saying. I appreciate the help and information you gave 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply