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
ReportDateSiteReport_SlicerPfolioC_TypeCallsHSecAcceptRejectASecRSec
2020-03-01BA2020-03 Brd6032.001803005.00180132939405.8750507.74
2020-03-01CE2020-03 Brd958.00276525.006338815113.9816216.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
ReportDateSiteReport_SlicerPfolioC_TypeCallsHSecAcceptRejectASecRSecLC
2020-03-01BA2020-03Brands6032.001803005.00180132939405.8750507.74LC2
2020-03-01BA2020-03Brands6032.001803005.00180132939405.8750507.74LC5
2020-03-01BA2020-03Brands6032.001803005.00180132939405.8750507.74Tenured
2020-03-01CE2020-03Brands958.00276525.006338815113.9816216.97LC5
2020-03-01CE2020-03Brands958.00276525.006338815113.9816216.97Tenured
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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