I am new, trying to create a complex view.

SQL Apprentice Question
Here is the current code. It is actually currently working:
SELECT TOP 100 PERCENT dbo.Placements.JobId, dbo.Placements.Branch,
dbo.Placements.PeopleName, dbo.Placements.Status,
CASE WHEN dbo.Place_FallOff.date IS NOT NULL THEN
dbo.Place_FallOff.date ELSE dbo.Placements.PlacementDate END AS 'Placement
Date',
CASE WHEN dbo.Place_FallOff.date IS NOT NULL THEN
YEAR(dbo.Place_FallOff.date) ELSE YEAR(dbo.Placements.PlacementDate) END AS
[Year],
CASE WHEN dbo.Place_FallOff.date IS NOT NULL THEN
MONTH(dbo.Place_FallOff.date) ELSE MONTH(dbo.Placements.PlacementDate)
END AS [Month], dbo.Placements.Reqtype,
dbo.Placements.FeePercentage, dbo.Placements.CandidatePC,
dbo.Placements.SCommission2,
dbo.Placements.jobreqpc, dbo.Placements.SCommission1,
dbo.Placements.JobFillPC, dbo.Placements.Commission1, dbo.Placements.Other,
dbo.Placements.Commission2, dbo.Invoice.Amount,
dbo.Placements.Division, dbo.Placements.CompanyName, CASE WHEN feeinfo =
'flat' AND
dbo.Place_FallOff.date IS NULL THEN feepercentage WHEN
feeinfo = 'percentage' AND dbo.Place_FallOff.date IS NULL
THEN feepercentage / 100 * Salary WHEN feeinfo =
'flat' AND dbo.Place_FallOff.date IS NOT NULL
THEN - 1 * feepercentage WHEN feeinfo = 'percentage'
AND dbo.Place_FallOff.date IS NOT NULL
THEN - 1 * feepercentage / 100 * Salary END AS Fee
FROM dbo.Placements INNER JOIN
dbo.Invoice ON dbo.Placements.PlacementCode =
dbo.Invoice.placementcode FULL OUTER JOIN
dbo.Place_FallOff ON dbo.Placements.PlacementCode =
dbo.Place_FallOff.Placementcode
WHERE (MONTH(dbo.Placements.PlacementDate) = MONTH(GETDATE()) OR
MONTH(dbo.Place_FallOff.[date]) = MONTH(GETDATE()))
AND (YEAR(dbo.Placements.PlacementDate) = YEAR(GETDATE()) OR
YEAR(dbo.Place_FallOff.[date]) = YEAR(GETDATE())) OR
(MONTH(dbo.Placements.PlacementDate) =
MONTH(GETDATE()) - 1) OR
(MONTH(dbo.Placements.PlacementDate) =
MONTH(GETDATE()) - 2)
ORDER BY dbo.Placements.Branch

We have a fall off table that captures the date of the fall off as well as
the placement code and a note as to why the candidate fell off.
I refer to the fall off in the statement above, but get slightly skewed data
(curently reflect the fall off date as the date and the positive fee in one
column and a negative fee in another column). I am looking for a way to
show the original placement (with orignal fee and orginal date) in one row
and then a second row that shows the fall off date and a negative fee
(fee*-1). Any ideas as how to insert this infomation into the view?


Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Just a few guesses at some problems


DATE is a reserved word, as well as useless name that begs the question
"of what?"


SQL can handle unary plus and minus, so that "-1 *" is not needed.


Look up the COALESCE() function to replace your CASE expressions, such
as:
COALESCE (F.falloff_date, P.placement_date) placement_date,


Get rid of the SELECT 100 PERCENT .. think about it for two seconds.
Likewise, putting an ORDER BY on a VIEW also useless. The VIEW is a
table and tables have no ordering by definition.


Learn to use temporal data types and function instead of splitting up
dates into parts, COBOL style.


Why are you doing a FULL OUTER JOIN between placements and fall-offs?
How can someone fall off without being a placement? Also isn't falling
off a status change (i.e attribute) and not a different kind of entity
which requires a new table?