In relational theory, tables are unordered sets so the Party column has no order unless you use an ORDER BY clause.

(ie SQL does not understand...

This is a nested join. I would enforce the use of brackets and formatting:

FROM scheme.cocontypm

INNER JOIN scheme.coconcdsm

ON scheme.cocontypm.contract_type = scheme.coconcdsm.contract_type

LEFT JOIN scheme.cfanalm

ON scheme.coconcdsm.analysis1 = scheme.cfanalm.analysis_code

RIGHT JOIN scheme.ophdcontm

ON...

-- *** Test Data ***

DECLARE @t TABLE

(

YCol varchar(10) NOT NULL

)

INSERT INTO @t

SELECT 'A01'

UNION ALL SELECT 'B02'

UNION ALL SELECT 'C03'

UNION ALL SELECT '2'

-- *** End Test Data ***

SELECT

CASE

WHEN YCol LIKE...

You can do it all with dynamic SQL:

DECLARE @sql varchar(8000)

,@CRLF char(2)

,@tab char(1)

,@TABLENAME sysname

SELECT @CRLF = CHAR(13) + CHAR(10)

,@tab = CHAR(9)

,@TABLENAME = 'Commissioner'

,@SQL =

'SELECT ' + @TABLENAME + ' AS TableName'...

Or maybe:

;WITH cte

AS

(

SELECT *

,ROW_NUMBER() OVER (PARTITION BY custID, module ORDER BY visit_date DESC) AS RowNum

FROM @t

)

SELECT T.*

FROM @t T

JOIN

(

SELECT C.*

FROM cte C

JOIN

(

SELECT custID, module

,MIN(found_count) AS found_count

,MIN(quality) AS quality

,MIN(pressure) AS pressure

FROM...

Try something like:

;WITH cte

AS

(

SELECT *

,ROW_NUMBER() OVER (PARTITION BY custID, module ORDER BY visit_date DESC) AS RowNum

FROM @t

)

SELECT C.*

FROM cte C

JOIN

(

SELECT custID, module

,MIN(found_count) AS found_count

,MIN(quality) AS quality

,MIN(pressure) AS pressure

FROM cte

WHERE RowNum...

To drive by withdrawals with the same idea, try the following:

;WITH Reenrollments

AS

(

SELECT *

...

In my query they do.

The idea is that RowNum % 2 = 1 for enrollments and RowNum % 2 = 0 for withdrawals.

Assuming an enrollment is never followed by an enrollment, I would be inclined to ignore the EndDate.

Try something like:

;WITH Reenrollments

AS

(

SELECT *

,ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY StartDate) As RowNum

FROM...

The main clauses in SQL are logically executed in the following order:

FROM (including JOINS)

WHERE

GROUP BY

HAVING

SELECT

DISTINCT

ORDER BY

TOP

-- *** Test Data ***

DECLARE @t TABLE

(

minutes varchar(3) NOT NULL

)

INSERT INTO @t

SELECT '10'

UNION ALL SELECT '20'

UNION ALL SELECT '60'

-- *** End Test Data ***

SELECT REPLACE(STR(minutes/60, 2), ' ', '0')

+ ':'

+...

INSERT INTO DATABASE..STAG_TEMP_TABLE

SELECT ABC,SUM(ZXC)ZXC,SUM(XYZ)XYZ

FROM

(

-- Filter data here as the result will be passed up.

-- These two selects may also run in parallel.

SELECT *

FROM DATABASENAME..WWW

WHERE A1 BETWEEN '20091009' AND '20100408'

AND...

Try putting the 'AND c.AcctName is NULL' into the ON clause of the join to table3.

Also:

a.FllwUpDate < DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)

will be more efficient than:

a.FllwUpDate < Convert(DateTime,convert(varchar(12),getdate(),101))

The problem with either renaming the tables or using ALTER VIEW is likely to be obtaining the schema lock.

To avoid this, you should update the main table from the staging...

As this query is quite straightforward to translate, prove that these two statements produce different results.

select Count(*)

from dbo.it_1_Revenue__Costs rev,dbo.it_2_elist e,dbo.it_2_LOB l,

dbo.it_2_RegionCurrency rg,dbo.it_4_Months_QTRS mnt,dbo.et_Summary_Income_Statement inc

where rev.itemiid*=inc.dimension_1_revenu

and l.itemiid*=inc.dimension_2_lob

and e.itemiid*=inc.elist

and rg.itemiid*=inc.dimension_2_region

and mnt.itemiid*=inc.dimension_4_months

SELECT COUNT(*)

FROM...