I have this query that I found that I want to modify in order to get an extra column and also sum the last 3 months of the amounts found. I want to make a Crystal Report for this. Query below.
SELECT
dbo.[@EIM_PROCESS_DATA].U_Tax_year,
dbo.[@EIM_PROCESS_DATA].U_Employee_ID,
SUM(dbo.[@EIM_PROCESS_DATA].U_Amount) AS PAYE,
dbo.OADM.CompnyName,
dbo.OADM.CompnyAddr,
dbo.OADM.TaxIdNum,
dbo.OHEM.lastName + ', ' + ISNULL(dbo.OHEM.middleName, '') + ' ' +
ISNULL(dbo.OHEM.firstName, '') AS EmployeeName, dbo.OHEM.govID
FROM dbo.[@EIM_PROCESS_DATA]
INNER JOIN dbo.OHEM ON dbo.[@EIM_PROCESS_DATA].U_Employee_ID
= dbo.OHEM.empID CROSS JOIN dbo.OADM
WHERE (dbo.[@EIM_PROCESS_DATA].U_PD_code = 'SYS033')
GROUP BY
dbo.[@EIM_PROCESS_DATA].U_Tax_year,
dbo.[@EIM_PROCESS_DATA].U_Employee_ID,
dbo.OADM.CompnyName,
dbo.OADM.CompnyAddr,
dbo.OADM.TaxIdNum,
dbo.OHEM.lastName,
dbo.OHEM.firstName,
dbo.OHEM.middleName,
dbo.OHEM.govID
The table OHEM
contains an alphanumeric field called U_Process_month
that has characters from January to December. As the query is as above, the SUM(dbo.[@EIM_PROCESS_DATA].U_Amount)
gives the totals for all PAYE amounts, ie. U_PD_code = 'SYS033'
.
I would like to have a query that adds up the last 3 months (PAYE) based on a year and month selected.
I would also like to retrieve and extra column, SUM(dbo.[@EIM_PROCESS_DATA].U_Amount) as TAXABLEPAY where (dbo.[@EIM_PROCESS_DATA].U_PD_code = 'SYS034')
.
How do I achieve this? Any help appreciated.
If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN. An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause.
A cross join is a type of join that returns the Cartesian product of rows from the tables in the join. In other words, it combines each row from the first table with each row from the second table.
Ways to avoid performance issues associated with CROSS JOINs: Use another JOIN (INNER/LEFT/RIGHT) with 2 ON conditions. Use the GROUP BY clause to pre-aggregate data.
I wasn't sure what datatype U_Tax_year was so I left it as INT. However, this query should return the 3 months previous to the month you set.
DECLARE @start_month DATETIME;
DECLARE @start_year INT;
SET @start_month = '2013-04-01';
SET @start_year = 2013;
SELECT dbo.[@EIM_PROCESS_DATA].U_Tax_year
, dbo.[@EIM_PROCESS_DATA].U_Employee_ID
, SUM(CASE WHEN dbo.[@EIM_PROCESS_DATA].U_PD_code = 'SYS033' THEN dbo.[@EIM_PROCESS_DATA].U_Amount ELSE 0 END) AS PAYE
, SUM(CASE WHEN dbo.[@EIM_PROCESS_DATA].U_PD_code = 'SYS034' THEN dbo.[@EIM_PROCESS_DATA].U_Amount ELSE 0 END) AS TAXABLEPAY
, dbo.OADM.CompnyName
, dbo.OADM.CompnyAddr
, dbo.OADM.TaxIdNum
, dbo.OHEM.lastName + ', ' + ISNULL(dbo.OHEM.middleName, '') + ' ' + ISNULL(dbo.OHEM.firstName, '') AS EmployeeName
, dbo.OHEM.govID
FROM dbo.[@EIM_PROCESS_DATA]INNER JOIN dbo.OHEM ON dbo.[@EIM_PROCESS_DATA].U_Employee_ID = dbo.OHEM.empID CROSS JOIN dbo.OADM
WHERE dbo.[@EIM_PROCESS_DATA].U_PD_code IN ('SYS033', 'SYS034')
AND dbo.OHEM.U_Process_month IN (DATENAME(MONTH, DATEADD(MONTH,-3, @start_month)), DATENAME(MONTH, DATEADD(MONTH,-2, @start_month)), DATENAME(MONTH, DATEADD(MONTH,-1, @start_month)))
AND dbo.[@EIM_PROCESS_DATA].U_Tax_year = @start_year
GROUP BY dbo.[@EIM_PROCESS_DATA].U_Tax_year
, dbo.[@EIM_PROCESS_DATA].U_Employee_ID
, dbo.OADM.CompnyName
, dbo.OADM.CompnyAddr
, dbo.OADM.TaxIdNum
, dbo.OHEM.lastName
, dbo.OHEM.firstName
, dbo.OHEM.middleName
, dbo.OHEM.govID;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With