Modifying a cross join query

crystal-reports-8.5, sql, sql-server-2008

Solution

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;

Problem

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.

Original source