StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POModifying a cross join query
text
Body
copied!<p>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.</p> <pre><code>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 </code></pre> <p>The table <code>OHEM</code> contains an alphanumeric field called <code>U_Process_month</code> that has characters from January to December. As the query is as above, the <code>SUM(dbo.[@EIM_PROCESS_DATA].U_Amount)</code> gives the totals for all PAYE amounts, <code>ie. U_PD_code = 'SYS033'</code>. </p> <p>I would like to have a query that adds up the last 3 months (PAYE) based on a year and month selected. </p> <p>I would also like to retrieve and extra column, <code>SUM(dbo.[@EIM_PROCESS_DATA].U_Amount) as TAXABLEPAY where (dbo.[@EIM_PROCESS_DATA].U_PD_code = 'SYS034')</code>.</p> <p>How do I achieve this? Any help appreciated.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload