Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Modifying a cross join query

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.

like image 227
Kinyanjui Kamau Avatar asked Apr 12 '13 11:04

Kinyanjui Kamau


People also ask

What is the alternative to cross join in SQL?

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.

What is the result of a cross join query?

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.

How avoid cross join in SQL Server?

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.


1 Answers

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;
like image 86
Andrew Therin Avatar answered Sep 19 '22 02:09

Andrew Therin