Two Views, merge 2 rows into 1 row

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 2005 Forums

Transact-SQL (2005)

Two Views, merge 2 rows into 1 row

Author

Topic

saw1515
Starting Member

2 Posts

Posted - 2010-10-29 : 13:54:01

I am using two views to pull data from approx 4 to 7 other tables or views. One view pulls result codes and the other view is pulling the amount recorded for that result. For example, user is filling out a form and entering material used and the cost of that material in two separate fields, which creates two unique rows - 1 for the material and 1 for the amount.

I have been struggling to get these two combined, currently having to report with two rows showing duplicate data other than amount.

View 1 (where i get the result code, like material etc):
SELECT DISTINCT TOP 100 PERCENT with TIES
res.wfarepactyspanid,
res.employeeid AS EMPLOYEEID,
res.eventdtm as Event_Date,
res.allocatedhrsqty as Labor_Hours,
act.ACTIVITYNM as Activity,
act.ACTIVITYDSC as Description,
B.mapnm as Result_Code,
MTL_Code=
CASE
WHEN B.mapnm = 'UF1' THEN A.reptxt --Material
WHEN B.mapnm = 'UF2' THEN A.reptxt --Material Costs
WHEN B.mapnm = 'UF3' THEN A.reptxt --Equipment
WHEN B.mapnm = 'UF4' THEN A.reptxt --Equipment Costs
WHEN B.mapnm = 'UF5' THEN A.reptxt --3rd Party Desc
WHEN B.mapnm = 'UF6' THEN A.reptxt --3rd Party Costs
END,
--D.CODEDSC AS MTL_CODE_DESC,
MTL_CODE_DESC=
CASE
WHEN B.mapnm = 'UF1' THEN A.reptxt --Material
WHEN B.mapnm = 'UF2' THEN A.reptxt --Material Costs
WHEN B.mapnm = 'UF3' THEN A.reptxt --Equipment
WHEN B.mapnm = 'UF4' THEN A.reptxt --Equipment Costs
WHEN B.mapnm = 'UF5' THEN A.reptxt --3rd Party Desc
WHEN B.mapnm = 'UF6' THEN A.reptxt --3rd Party Costs
END,
Res_Code_Key=
Case
WHEN SUBSTRING(B.mapnm, 1, 3) = 'UF1' THEN left(b.mapnm, 9) + 'Material ' + right(b.mapnm, 1)
WHEN SUBSTRING(B.mapnm, 1, 3) = 'UF2' THEN left(b.mapnm, 15) + 'Material Costs ' + right(b.mapnm, 1)
WHEN SUBSTRING(B.mapnm, 1, 3) = 'UF3' THEN left(b.mapnm, 10) + 'Equipment ' + right(b.mapnm, 1)
WHEN SUBSTRING(B.mapnm, 1, 3) = 'UF4' THEN left(b.mapnm, 16) + 'Equipment Costs ' + right(b.mapnm, 1)
WHEN SUBSTRING(B.mapnm, 1, 3) = 'UF5' THEN left(b.mapnm, 15) + '3rd Party Desc ' + right(b.mapnm, 1)
WHEN SUBSTRING(B.mapnm, 1, 3) = 'UF6' THEN left(b.mapnm, 16) + '3rd Party Costs ' + right(b.mapnm, 1)
End,
res.wfaactivityid,
C.stepseqnum
FROM
VU_WFAREPACTYSPAN res LEFT OUTER JOIN WFAREPACTYRES A on (res.wfarepactyspanid=a.wfarepactyspanid)
LEFT OUTER JOIN WFAACTIVITY act on (res.wfaactivityid = act.wfaactivityid)
LEFT OUTER JOIN wfamap B ON (A.WFAMAPID=B.WFAMAPID)
LEFT OUTER JOIN WFATRANSTEP C ON (A.WFAMAPID=C.WFAMAPID)
LEFT OUTER JOIN WFACODE D ON (A.REPTXT=D.CODENM)
WHERE
A.deletedsw=0 AND
B.resultcodesw=1 AND
LEN(A.reptxt)>0 AND
(B.mapnm= 'UF1' OR
B.mapnm= 'UF2' OR
B.mapnm= 'UF3' OR
B.mapnm= 'UF4' OR
B.mapnm= 'UF5' OR
B.mapnm= 'UF6')
AND (EVENTDTM>{ts '2010-10-28 00:00:01'}
AND EVENTDTM<={ts '2010-10-31 00:00:00'})
ORDER BY
res.eventdtm,
res.wfaactivityid,
C.stepseqnum

View 2 - where i get the amount:
SELECT DISTINCT TOP 100 PERCENT with TIES
res.wfarepactyspanid,
res.employeeid AS EMPLOYEEID,
res.eventdtm as Event_Date,
act.ACTIVITYNM as Activity,
act.ACTIVITYDSC as Description,
B.mapnm as SW_AMT2, --USED FOR MASTER SQL QUERY
Result_Code=
Case
WHEN SUBSTRING(B.mapnm, 1, 3) = 'UF1' THEN left(b.mapnm, 9) + 'Material ' + right(b.mapnm, 1)
WHEN SUBSTRING(B.mapnm, 1, 3) = 'UF2' THEN left(b.mapnm, 15) + 'Material Costs ' + right(b.mapnm, 1)
WHEN SUBSTRING(B.mapnm, 1, 3) = 'UF3' THEN left(b.mapnm, 10) + 'Equipment ' + right(b.mapnm, 1)
WHEN SUBSTRING(B.mapnm, 1, 3) = 'UF4' THEN left(b.mapnm, 16) + 'Equipment Costs ' + right(b.mapnm, 1)
WHEN SUBSTRING(B.mapnm, 1, 3) = 'UF5' THEN left(b.mapnm, 15) + '3rd Party Desc ' + right(b.mapnm, 1)
WHEN SUBSTRING(B.mapnm, 1, 3) = 'UF6' THEN left(b.mapnm, 16) + '3rd Party Costs ' + right(b.mapnm, 1)
End,
--D.CODEDSC AS MTL_CODE_DESC,
MTL_CODE_DESC =
CASE
WHEN B.mapnm = 'UF1' THEN A.reptxt --Material
WHEN B.mapnm = 'UF2' THEN A.reptxt --Material Costs
WHEN B.mapnm = 'UF3' THEN A.reptxt --Equipment
WHEN B.mapnm = 'UF4' THEN A.reptxt --Equipment Costs
WHEN B.mapnm = 'UF5' THEN A.reptxt --3rd Party Desc
WHEN B.mapnm = 'UF6' THEN A.reptxt --3rd Party Costs
ELSE '-'
END,
B.mapnm as Amount_Code,
Amount=
CASE
WHEN SUBSTRING(B.mapnm,1,3) = 'UF1' THEN '0.00' --Material
WHEN SUBSTRING(B.mapnm,1,3) = 'UF2' THEN A.reptxt --Material Costs
WHEN SUBSTRING(B.mapnm,1,3) = 'UF3' THEN '0.00' --Equipment
WHEN SUBSTRING(B.mapnm,1,3) = 'UF4' THEN A.reptxt --Equipment Costs
WHEN SUBSTRING(B.mapnm,1,3) = 'UF5' THEN '0.00' --3rd Party Desc
WHEN SUBSTRING(B.mapnm,1,3) = 'UF6' THEN A.reptxt --3rd Party Costs
ELSE
'0.00'
END,
res.wfaactivityid,
C.stepseqnum
FROM
VU_WFAREPACTYSPAN res LEFT OUTER JOIN WFAREPACTYRES A on (res.wfarepactyspanid=a.wfarepactyspanid)
LEFT OUTER JOIN WFAACTIVITY act on (res.wfaactivityid = act.wfaactivityid)
LEFT OUTER JOIN wfamap B ON (A.WFAMAPID=B.WFAMAPID)
LEFT OUTER JOIN WFATRANSTEP C ON (A.WFAMAPID=C.WFAMAPID)
LEFT OUTER JOIN WFACODE D ON (A.REPTXT=D.CODENM)
WHERE
A.deletedsw=0 AND
B.resultcodesw=1 AND
LEN(A.reptxt)>0 AND
(B.mapnm= 'UF1' OR
B.mapnm= 'UF2' OR
B.mapnm= 'UF3' OR
B.mapnm= 'UF4' OR
B.mapnm= 'UF5' OR
B.mapnm= 'UF6')
AND (EVENTDTM>{ts '2010-10-28 00:00:01'}
AND EVENTDTM<={ts '2010-10-31 00:00:00'})
ORDER BY
res.eventdtm,
res.wfaactivityid,
C.stepseqnum

Master Query trying to pull it all together into 1 row (amount with material etc, currently getting two rows for each entry/form submitted- looking for 1 row):
SELECT
VIEW2.Event_Date,
VIEW2.Amount,
VIEW1.MTL_Code,
VP_HOMELABORACCT.ENDDATE,
VP_HOMELABORACCT.LABORLEVELNAME3,
VIEW1.Activity,
VIEW1.Event_Date,
VIEW1.Description,
VIEW1.MTL_CODE_DESC,
VIEW1.Result_Code
FROM DB.dbo.VP_HOMELABORACCT VP_HOMELABORACCT
INNER JOIN (DB.dbo.VIEW2 VIEW2
INNER JOIN DB.dbo.VIEW1 VIEW1
ON ((((VIEW2.EMPLOYEEID=VIEW1.EMPLOYEEID)
AND (VIEW2.Event_Date=VIEW1.Event_Date))
AND (VIEW2.Activity=VIEW1.Activity))
AND (VIEW2.wfaactivityid=VIEW1.wfaactivityid))
AND (VIEW2.Result_Code=VIEW1.Res_Code_Key))
ON VP_HOMELABORACCT.EMPLOYEEID=VIEW1.EMPLOYEEID
WHERE
VIEW1.Result_Code LIKE N'U%'
AND VIEW1.MTL_Code<>N'0'
AND (VP_HOMELABORACCT.ENDDATE>={ts '3000-01-01 00:00:00'} AND VP_HOMELABORACCT.ENDDATE<{ts '3000-01-01 00:00:01'})
AND (VIEW1.Event_Date>{ts '2010-10-17 00:00:01'}
AND VIEW1.Event_Date<={ts '2010-10-29 00:00:00'})
ORDER BY VIEW1.MTL_Code

Any help is appreciated, thanks in advance.

saw1515
Starting Member

2 Posts

Posted - 2010-10-29 : 19:31:16

Is the data I provided ok to potentially get assistance? If not, please let me know what may help the issue.

Here is the query that I run from report with sample data below (2 rows that i am trying to get into 1). In this instance, the first row of data captures material and the second row captures cost of material, because they are captured in same form two rows are created. In summary I would like to get the amt (515.00) from second row into first row (18g alum). In my first post i actually have the two views but guess that may not be imp.

SELECT
VIEW2.Event_Date,
VIEW2.Amount,
VIEW1.MTL_Code,
VP_HOMELABORACCT.ENDDATE,
VP_HOMELABORACCT.LABORLEVELNAME3,
VIEW1.Activity,
VIEW1.Event_Date,
VIEW1.Description,
VIEW1.MTL_CODE_DESC,
VIEW1.Result_Code
FROM DB.dbo.VP_HOMELABORACCT VP_HOMELABORACCT
INNER JOIN (DB.dbo.VIEW2 VIEW2
INNER JOIN DB.dbo.VIEW1 VIEW1
ON ((((VIEW2.EMPLOYEEID=VIEW1.EMPLOYEEID)
AND (VIEW2.Event_Date=VIEW1.Event_Date))
AND (VIEW2.Activity=VIEW1.Activity))
AND (VIEW2.wfaactivityid=VIEW1.wfaactivityid))
AND (VIEW2.Result_Code=VIEW1.Res_Code_Key))
ON VP_HOMELABORACCT.EMPLOYEEID=VIEW1.EMPLOYEEID
WHERE
VIEW1.Result_Code LIKE N'U%'
AND VIEW1.MTL_Code<>N'0'
AND (VP_HOMELABORACCT.ENDDATE>={ts '3000-01-01 00:00:00'} AND VP_HOMELABORACCT.ENDDATE<{ts '3000-01-01 00:00:01'})
AND (VIEW1.Event_Date>{ts '2010-10-17 00:00:01'}
AND VIEW1.Event_Date<={ts '2010-10-29 00:00:00'})
ORDER BY VIEW1.MTL_Code

2010-10-29 00:00:00.000 0.00 18g alum 3000-01-01 00:00:00.000 326 101 2010-10-29 00:00:00.000 Setup Castings 18g alum UF1
2010-10-29 00:00:00.000 515.00 NULL 3000-01-01 00:00:00.000 326 101 2010-10-29 00:00:00.000 Setup Castings 515.00 UF2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources