Newbie Syntax Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Newbie Syntax Help

Author

Topic

Job
Yak Posting Veteran

69 Posts

Posted - 2005-06-28 : 11:54:49

Here are a list of the fields from a view;
Yr|LocNme|CPT|CPT_26_TC|WRVU|NF_PE_RVU|F_PE_RVU|MP_RVU|Conv_Fac|Wrk_GPCI|PE_GPCI|
MP_GPCI|FAC_TRVU|NFAC_TRVU|F_Pmnt|NF_Pymnt

The view above has all of these CPT_26_TC and associated fields that span a
few years. Based upon this view, I would create
a view that look identical, however it would list all of the CPT_26_TC codes
in the first column from all of the years (Unique).
Then from the original view I would want to populate the new view with the
information associated by CPT_26_TC from 2005.
There will be many codes that are blank because the code is no longer used
in 05, so I would want to populate from the 04 data
and so on.

Basically I have a list of unique codes encompassing all years in the
database. Then I need logic built in that says starting
with the Max year, fill in the values, if that year doesn't have the value,
then use the next year and so on. I also would need to include another field that tells me what year the data came from. I.e if the CPT_26_TC was populated using 2003, the field would simply say 2003.

In MS Access I would create a table that had the unique CPT_26_TC from all the years and proceed to update the table linked on CPT_26_TC from the 05 data, then update where blank from the 04 and then update where blank from the 03 data and so on. I don't know how to do this A- In SQL and B-using views so it is dynamic.

Hopefully this makes a little more sense.

Cheers,

Job

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-28 : 21:37:56

I'm not sure if I understood what you wanted, but I think this might be it:

SELECT A.CPC_26_TC, B.MaxYr, A.SomeCol1, ...
FROM YourTable AS A
JOIN
(SELECT CPC_26_TC, MAX(Yr) AS MaxYr
FROM YourTable
WHERE YourCol1 IS NOT NULL
AND YourCol2 IS NOT NULL ...
GROUP BY CPC_26_TC) AS B
ON A.CPC_26_TC = B.CPC_26_TC AND A.Yr = B.MaxYr

I didn't know what columns would have to be NULL to make that record a "blank" one, so I just wrote "WHERE YourCol1 IS NOT NULL ..." for you to fill in.

Job
Yak Posting Veteran

69 Posts

Posted - 2005-06-29 : 13:32:34

Right or wrong, this redundant set of statements get me to the correct table. Maybe when you see this you can understand what I'm doing and suggest better/more efficient ways.

--Set the variables
DECLARE @LocNme nvarchar(255), @SetYR as Float
SET @LocNme = 'REST OF NEW JERSEY'
SET @SetYR = 2005
--Step 1
Insert into Tbl_Master_Blnd_RVU (CPT_26_TC)
Select CPT_26_TC
From VW_Master_CPT
Group By CPT_26_TC

--Step 2
UPDATE Tbl_Master_Blnd_RVU
SET Tbl_Master_Blnd_RVU.YR = VW_Master_CPT.[Year],
Tbl_Master_Blnd_RVU.LocNme = VW_Master_CPT.LocNme,
Tbl_Master_Blnd_RVU.CPT = VW_Master_CPT.[HCPCS],
Tbl_Master_Blnd_RVU.CPT_26_TC = VW_Master_CPT.CPT_26_TC,
Tbl_Master_Blnd_RVU.WRVU = VW_Master_CPT.WRVU,
Tbl_Master_Blnd_RVU.NF_PE_RVU = VW_Master_CPT.Non_Fac_PE_RVU,
Tbl_Master_Blnd_RVU.F_PE_RVU = VW_Master_CPT.Fac_PE_RVU,
Tbl_Master_Blnd_RVU.Conv_Fac = VW_Master_CPT.Conv_Factor,
Tbl_Master_Blnd_RVU.MP_RVU = VW_Master_CPT.MP_RVU,
Tbl_Master_Blnd_RVU.Wrk_GPCI = VW_Master_CPT.Wrk_GPCI,
Tbl_Master_Blnd_RVU.PE_GPCI = VW_Master_CPT.PE_GPCI,
Tbl_Master_Blnd_RVU.MP_GPCI = VW_Master_CPT.MP_GPCI,
Tbl_Master_Blnd_RVU.FAC_TRVU = VW_Master_CPT.[Fac TRVU],
Tbl_Master_Blnd_RVU.NFAC_TRVU = VW_Master_CPT.[Non Fac TRVU],
Tbl_Master_Blnd_RVU.F_Pmnt = VW_Master_CPT.[Fac Payment],
Tbl_Master_Blnd_RVU.NF_Pymnt = VW_Master_CPT.[Non Fac Payment]

FROM VW_Master_CPT RIGHT OUTER JOIN
Tbl_Master_Blnd_RVU ON VW_Master_CPT.CPT_26_TC = Tbl_Master_Blnd_RVU.CPT_26_TC
WHERE (VW_Master_CPT.[Year] = @SetYR) AND (VW_Master_CPT.LocNme = @LocNme)

--Step 3
UPDATE Tbl_Master_Blnd_RVU
SET Tbl_Master_Blnd_RVU.YR = VW_Master_CPT.[Year],
Tbl_Master_Blnd_RVU.LocNme = VW_Master_CPT.LocNme,
Tbl_Master_Blnd_RVU.CPT = VW_Master_CPT.[HCPCS],
Tbl_Master_Blnd_RVU.CPT_26_TC = VW_Master_CPT.CPT_26_TC,
Tbl_Master_Blnd_RVU.WRVU = VW_Master_CPT.WRVU,
Tbl_Master_Blnd_RVU.NF_PE_RVU = VW_Master_CPT.Non_Fac_PE_RVU,
Tbl_Master_Blnd_RVU.F_PE_RVU = VW_Master_CPT.Fac_PE_RVU,
Tbl_Master_Blnd_RVU.Conv_Fac = VW_Master_CPT.Conv_Factor,
Tbl_Master_Blnd_RVU.Wrk_GPCI = VW_Master_CPT.Wrk_GPCI,
Tbl_Master_Blnd_RVU.MP_RVU = VW_Master_CPT.MP_RVU,
Tbl_Master_Blnd_RVU.PE_GPCI = VW_Master_CPT.PE_GPCI,
Tbl_Master_Blnd_RVU.MP_GPCI = VW_Master_CPT.MP_GPCI,
Tbl_Master_Blnd_RVU.FAC_TRVU = VW_Master_CPT.[Fac TRVU],
Tbl_Master_Blnd_RVU.NFAC_TRVU = VW_Master_CPT.[Non Fac TRVU],
Tbl_Master_Blnd_RVU.F_Pmnt = VW_Master_CPT.[Fac Payment],
Tbl_Master_Blnd_RVU.NF_Pymnt = VW_Master_CPT.[Non Fac Payment]

FROM VW_Master_CPT RIGHT OUTER JOIN
Tbl_Master_Blnd_RVU ON VW_Master_CPT.CPT_26_TC = Tbl_Master_Blnd_RVU.CPT_26_TC
WHERE (VW_Master_CPT.[Year] = @SetYR -1) AND (VW_Master_CPT.LocNme = @LocNme) AND (YR IS NULL)

--Step 4
UPDATE Tbl_Master_Blnd_RVU
SET Tbl_Master_Blnd_RVU.YR = VW_Master_CPT.[Year],
Tbl_Master_Blnd_RVU.LocNme = VW_Master_CPT.LocNme,
Tbl_Master_Blnd_RVU.CPT = VW_Master_CPT.[HCPCS],
Tbl_Master_Blnd_RVU.CPT_26_TC = VW_Master_CPT.CPT_26_TC,
Tbl_Master_Blnd_RVU.WRVU = VW_Master_CPT.WRVU,
Tbl_Master_Blnd_RVU.NF_PE_RVU = VW_Master_CPT.Non_Fac_PE_RVU,
Tbl_Master_Blnd_RVU.F_PE_RVU = VW_Master_CPT.Fac_PE_RVU,
Tbl_Master_Blnd_RVU.Conv_Fac = VW_Master_CPT.Conv_Factor,
Tbl_Master_Blnd_RVU.Wrk_GPCI = VW_Master_CPT.Wrk_GPCI,
Tbl_Master_Blnd_RVU.MP_RVU = VW_Master_CPT.MP_RVU,
Tbl_Master_Blnd_RVU.PE_GPCI = VW_Master_CPT.PE_GPCI,
Tbl_Master_Blnd_RVU.MP_GPCI = VW_Master_CPT.MP_GPCI,
Tbl_Master_Blnd_RVU.FAC_TRVU = VW_Master_CPT.[Fac TRVU],
Tbl_Master_Blnd_RVU.NFAC_TRVU = VW_Master_CPT.[Non Fac TRVU],
Tbl_Master_Blnd_RVU.F_Pmnt = VW_Master_CPT.[Fac Payment],
Tbl_Master_Blnd_RVU.NF_Pymnt = VW_Master_CPT.[Non Fac Payment]

FROM VW_Master_CPT RIGHT OUTER JOIN
Tbl_Master_Blnd_RVU ON VW_Master_CPT.CPT_26_TC = Tbl_Master_Blnd_RVU.CPT_26_TC
WHERE (VW_Master_CPT.[Year] = @SetYR -2) AND (VW_Master_CPT.LocNme = @LocNme) AND (YR IS NULL)

--Step 5
UPDATE Tbl_Master_Blnd_RVU
SET Tbl_Master_Blnd_RVU.YR = VW_Master_CPT.[Year],
Tbl_Master_Blnd_RVU.LocNme = VW_Master_CPT.LocNme,
Tbl_Master_Blnd_RVU.CPT = VW_Master_CPT.[HCPCS],
Tbl_Master_Blnd_RVU.CPT_26_TC = VW_Master_CPT.CPT_26_TC,
Tbl_Master_Blnd_RVU.WRVU = VW_Master_CPT.WRVU,
Tbl_Master_Blnd_RVU.NF_PE_RVU = VW_Master_CPT.Non_Fac_PE_RVU,
Tbl_Master_Blnd_RVU.F_PE_RVU = VW_Master_CPT.Fac_PE_RVU,
Tbl_Master_Blnd_RVU.Conv_Fac = VW_Master_CPT.Conv_Factor,
Tbl_Master_Blnd_RVU.Wrk_GPCI = VW_Master_CPT.Wrk_GPCI,
Tbl_Master_Blnd_RVU.MP_RVU = VW_Master_CPT.MP_RVU,
Tbl_Master_Blnd_RVU.PE_GPCI = VW_Master_CPT.PE_GPCI,
Tbl_Master_Blnd_RVU.MP_GPCI = VW_Master_CPT.MP_GPCI,
Tbl_Master_Blnd_RVU.FAC_TRVU = VW_Master_CPT.[Fac TRVU],
Tbl_Master_Blnd_RVU.NFAC_TRVU = VW_Master_CPT.[Non Fac TRVU],
Tbl_Master_Blnd_RVU.F_Pmnt = VW_Master_CPT.[Fac Payment],
Tbl_Master_Blnd_RVU.NF_Pymnt = VW_Master_CPT.[Non Fac Payment]

FROM VW_Master_CPT RIGHT OUTER JOIN
Tbl_Master_Blnd_RVU ON VW_Master_CPT.CPT_26_TC = Tbl_Master_Blnd_RVU.CPT_26_TC
WHERE (VW_Master_CPT.[Year] = @SetYR -3) AND (VW_Master_CPT.LocNme = @LocNme) AND (YR IS NULL)

--Step 6
UPDATE Tbl_Master_Blnd_RVU
SET Tbl_Master_Blnd_RVU.YR = VW_Master_CPT.[Year],
Tbl_Master_Blnd_RVU.LocNme = VW_Master_CPT.LocNme,
Tbl_Master_Blnd_RVU.CPT = VW_Master_CPT.[HCPCS],
Tbl_Master_Blnd_RVU.CPT_26_TC = VW_Master_CPT.CPT_26_TC,
Tbl_Master_Blnd_RVU.WRVU = VW_Master_CPT.WRVU,
Tbl_Master_Blnd_RVU.NF_PE_RVU = VW_Master_CPT.Non_Fac_PE_RVU,
Tbl_Master_Blnd_RVU.F_PE_RVU = VW_Master_CPT.Fac_PE_RVU,
Tbl_Master_Blnd_RVU.Conv_Fac = VW_Master_CPT.Conv_Factor,
Tbl_Master_Blnd_RVU.MP_RVU = VW_Master_CPT.MP_RVU,
Tbl_Master_Blnd_RVU.Wrk_GPCI = VW_Master_CPT.Wrk_GPCI,
Tbl_Master_Blnd_RVU.PE_GPCI = VW_Master_CPT.PE_GPCI,
Tbl_Master_Blnd_RVU.MP_GPCI = VW_Master_CPT.MP_GPCI,
Tbl_Master_Blnd_RVU.FAC_TRVU = VW_Master_CPT.[Fac TRVU],
Tbl_Master_Blnd_RVU.NFAC_TRVU = VW_Master_CPT.[Non Fac TRVU],
Tbl_Master_Blnd_RVU.F_Pmnt = VW_Master_CPT.[Fac Payment],
Tbl_Master_Blnd_RVU.NF_Pymnt = VW_Master_CPT.[Non Fac Payment]

FROM VW_Master_CPT RIGHT OUTER JOIN
Tbl_Master_Blnd_RVU ON VW_Master_CPT.CPT_26_TC = Tbl_Master_Blnd_RVU.CPT_26_TC
WHERE (VW_Master_CPT.[Year] = @SetYR -4) AND (VW_Master_CPT.LocNme = @LocNme) AND (YR IS NULL)

--Step 7
UPDATE Tbl_Master_Blnd_RVU
SET Tbl_Master_Blnd_RVU.YR = VW_Master_CPT.[Year],
Tbl_Master_Blnd_RVU.LocNme = VW_Master_CPT.LocNme,
Tbl_Master_Blnd_RVU.CPT = VW_Master_CPT.[HCPCS],
Tbl_Master_Blnd_RVU.CPT_26_TC = VW_Master_CPT.CPT_26_TC,
Tbl_Master_Blnd_RVU.WRVU = VW_Master_CPT.WRVU,
Tbl_Master_Blnd_RVU.NF_PE_RVU = VW_Master_CPT.Non_Fac_PE_RVU,
Tbl_Master_Blnd_RVU.F_PE_RVU = VW_Master_CPT.Fac_PE_RVU,
Tbl_Master_Blnd_RVU.Conv_Fac = VW_Master_CPT.Conv_Factor,
Tbl_Master_Blnd_RVU.MP_RVU = VW_Master_CPT.MP_RVU,
Tbl_Master_Blnd_RVU.Wrk_GPCI = VW_Master_CPT.Wrk_GPCI,
Tbl_Master_Blnd_RVU.PE_GPCI = VW_Master_CPT.PE_GPCI,
Tbl_Master_Blnd_RVU.MP_GPCI = VW_Master_CPT.MP_GPCI,
Tbl_Master_Blnd_RVU.FAC_TRVU = VW_Master_CPT.[Fac TRVU],
Tbl_Master_Blnd_RVU.NFAC_TRVU = VW_Master_CPT.[Non Fac TRVU],
Tbl_Master_Blnd_RVU.F_Pmnt = VW_Master_CPT.[Fac Payment],
Tbl_Master_Blnd_RVU.NF_Pymnt = VW_Master_CPT.[Non Fac Payment]

FROM VW_Master_CPT RIGHT OUTER JOIN
Tbl_Master_Blnd_RVU ON VW_Master_CPT.CPT_26_TC = Tbl_Master_Blnd_RVU.CPT_26_TC
WHERE (VW_Master_CPT.[Year] = @SetYR -5) AND (VW_Master_CPT.LocNme = @LocNme) AND (YR IS NULL)

--Step 8
UPDATE Tbl_Master_Blnd_RVU
SET Tbl_Master_Blnd_RVU.YR = VW_Master_CPT.[Year],
Tbl_Master_Blnd_RVU.LocNme = VW_Master_CPT.LocNme,
Tbl_Master_Blnd_RVU.CPT = VW_Master_CPT.[HCPCS],
Tbl_Master_Blnd_RVU.CPT_26_TC = VW_Master_CPT.CPT_26_TC,
Tbl_Master_Blnd_RVU.WRVU = VW_Master_CPT.WRVU,
Tbl_Master_Blnd_RVU.NF_PE_RVU = VW_Master_CPT.Non_Fac_PE_RVU,
Tbl_Master_Blnd_RVU.F_PE_RVU = VW_Master_CPT.Fac_PE_RVU,
Tbl_Master_Blnd_RVU.Conv_Fac = VW_Master_CPT.Conv_Factor,
Tbl_Master_Blnd_RVU.Wrk_GPCI = VW_Master_CPT.Wrk_GPCI,
Tbl_Master_Blnd_RVU.MP_RVU = VW_Master_CPT.MP_RVU,
Tbl_Master_Blnd_RVU.PE_GPCI = VW_Master_CPT.PE_GPCI,
Tbl_Master_Blnd_RVU.MP_GPCI = VW_Master_CPT.MP_GPCI,
Tbl_Master_Blnd_RVU.FAC_TRVU = VW_Master_CPT.[Fac TRVU],
Tbl_Master_Blnd_RVU.NFAC_TRVU = VW_Master_CPT.[Non Fac TRVU],
Tbl_Master_Blnd_RVU.F_Pmnt = VW_Master_CPT.[Fac Payment],
Tbl_Master_Blnd_RVU.NF_Pymnt = VW_Master_CPT.[Non Fac Payment]

FROM VW_Master_CPT RIGHT OUTER JOIN
Tbl_Master_Blnd_RVU ON VW_Master_CPT.CPT_26_TC = Tbl_Master_Blnd_RVU.CPT_26_TC
WHERE (VW_Master_CPT.[Year] = @SetYR -6) AND (VW_Master_CPT.LocNme = @LocNme) AND (YR IS NULL)

Cheers,

Job

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources