Adding Dataset 1 to Dataset 2 to make Dataset 3

Question

Post reply

Adding Dataset 1 to Dataset 2 to make Dataset 3

paula_sql

SSCrazy

Points: 2665
More actions
August 26, 2009 at 8:38 pm

#133263

This is View 1:
SELECT
d.ITEMNMBR,
d.ITEMDESC,
d.ABCCODE,
d.UOMSCHDL,
Month6 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 6)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
else 0
END),
Month5 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 5)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
else 0
END),
Month4 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 4)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
else 0
END),
Month3 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 3)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
else 0
END),
Month2 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 2)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
else 0
END),
Month1 = SUM(CASE
WHEN b.DOCTYPE IN (1, 6)
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()) - 1)
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE()))
THEN (b.TRXQTY) * - 1
else 0
END),
CurrentMonth = SUM(CASE
WHEN ((b.ITEMNMBR = d.ITEMNMBR)
AND (b.DOCTYPE IN (1, 6))
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJRTS')
AND (LEFT(b.TRXSORCE, 5) + LEFT(b.DOCNUMBR, 3) <> 'IVADJADJ')
AND (b.TRXQTY < 0)
AND (DATEPART(MM, b.DOCDATE) = DATEPART(mm, GETDATE()))
AND (DATEPART(year, b.DOCDATE) = DATEPART(year, GETDATE())))
THEN (b.TRXQTY) * - 1
else 0
END)
FROM IV00101 AS d
LEFT JOIN IV30300 AS b ON d.ITEMNMBR = b.ITEMNMBR
WHERE (d.ITEMTYPE <> 2)
GROUP BY
d.ITEMNMBR,
d.ITEMDESC,
d.ABCCODE,
d.UOMSCHDL
Here is View 2:
SELECT
d.ITEMNMBR,
d.ITEMDESC,
d.ABCCODE,
d.UOMSCHDL,
JobMonth6 = SUM(CASE
WHEN left(h.trxsorce,5) = 'IVADJ'
AND (DATEPART(MM, h.DOCDATE) = DATEPART(mm, GETDATE()) - 6)
AND (DATEPART(year, h.DOCDATE) = DATEPART(year, GETDATE()))
THEN h.TRXQTY
else 0
END),
JobMonth5 = SUM(CASE
WHEN left(h.trxsorce,5) = 'IVADJ'
AND (DATEPART(MM, h.DOCDATE) = DATEPART(mm, GETDATE()) - 5)
AND (DATEPART(year, h.DOCDATE) = DATEPART(year, GETDATE()))
THEN h.TRXQTY
else 0
END),
JobMonth4 = SUM(CASE
WHEN left(h.trxsorce,5) = 'IVADJ'
AND (DATEPART(MM, h.DOCDATE) = DATEPART(mm, GETDATE()) - 4)
AND (DATEPART(year, h.DOCDATE) = DATEPART(year, GETDATE()))
THEN h.TRXQTY
else 0
END),
JobMonth3 = SUM(CASE
WHEN left(h.trxsorce,5) = 'IVADJ'
AND (DATEPART(MM, h.DOCDATE) = DATEPART(mm, GETDATE()) - 3)
AND (DATEPART(year, h.DOCDATE) = DATEPART(year, GETDATE()))
THEN h.TRXQTY
else 0
END),
JobMonth2 = SUM(CASE
WHEN left(h.trxsorce,5) = 'IVADJ'
AND (DATEPART(MM, h.DOCDATE) = DATEPART(mm, GETDATE()) - 2)
AND (DATEPART(year, h.DOCDATE) = DATEPART(year, GETDATE()))
THEN h.TRXQTY
else 0
END),
JobMonth1 = SUM(CASE
WHEN left(h.trxsorce,5) = 'IVADJ'
AND (DATEPART(MM, h.DOCDATE) = DATEPART(mm, GETDATE()) - 1)
AND (DATEPART(year, h.DOCDATE) = DATEPART(year, GETDATE()))
THEN h.TRXQTY
else 0
END),
JobCurrentMonth = SUM(CASE
WHEN left(h.trxsorce,5) = 'IVADJ'
AND (DATEPART(MM, h.DOCDATE) = DATEPART(mm, GETDATE()))
AND (DATEPART(year, h.DOCDATE) = DATEPART(year, GETDATE()))
AND left(h.Job_TRX_Number,3) = 'WOA'
THEN h.TRXQTY
else 0
END)
FROM IV00101 AS d
left JOIN JC20001 AS h on d.ITEMNMBR = h.ITEMNMBR
WHERE (d.ITEMTYPE <> 2)
GROUP BY
d.ITEMNMBR,
d.ITEMDESC,
d.ABCCODE,
d.UOMSCHDL
How can I add these datasets together? For example add Month6 to JobMonth6,
add Month 5 to JobMonth5 etc.
Thanks for all help!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

something like this is the way i interpreted it:

SELECT

VIEW1.ITEMNMBR,

VIEW1.ITEMDESC,

VIEW1.ABCCODE,

VIEW1.UOMSCHDL,

Month6 + JobMonth6 AS TotMonth6,

Month5 + JobMonth5 AS TotMonth5,

Month4 + JobMonth4 AS TotMonth4,

Month3 + JobMonth3 AS TotMonth3,

Month2 + JobMonth2 AS TotMonth2,

Month1 + JobMonth1 AS TotMonth1,

CurrentMonth + CurrentJobMonth As TotCurrentMonth

FROM (--view1 removed for brevity paste it back in

) VIEW1

INNER JOIN (--view2 removed for brevity paste it back in

) VIEW2

ON VIEW1.ITEMNMBR = VIEW2.ITEMNMBR

AND VIEW1.ITEMDESC = VIEW2.ITEMDESC

AND VIEW1.ABCCODE = VIEW2.ABCCODE,

AND VIEW1.UOMSCHDL = VIEW2.UOMSCHDL

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

paula_sql SSCrazy Points: 2665 More actions · Answer 2

paula_sql

SSCrazy

Points: 2665

August 27, 2009 at 5:05 pm

#1045978

Thanks so much for that. I'll give it a go.

paula_sql SSCrazy Points: 2665 More actions · Answer 3

Hi There,

Here is the code and it works!! Thanks very much!!

SELECT

R1.ITEMNMBR,

R1.ITEMDESC,

R1.ABCCODE,

R1.UOMSCHDL,

R1.Month6 + R2.JobMonth6 AS TotMonth6,

R1.Month5 + R2.JobMonth5 AS TotMonth5,

R1.Month4 + R2.JobMonth4 AS TotMonth4,

R1.Month3 + R2.JobMonth3 AS TotMonth3,

R1.Month2 + R2.JobMonth2 AS TotMonth2,

R1.Month1 + R2.JobMonth1 AS TotMonth1,

R1.CurrentMonth + R2.JobCurrentMonth As TotCurrentMonth

FROM dbo.ReplinishmentReport AS R1

inner join dbo.ReplinishmentReportWennsoft AS R2 on

R1.ITEMNMBR = R2.ITEMNMBR

AND R1.ITEMDESC = R2.ITEMDESC

AND R1.ABCCODE = R2.ABCCODE

AND R1.UOMSCHDL = R2.UOMSCHDL

Lowell SSC Guru Points: 323518 More actions · Answer 4

Glad my example worked out for you;

sometimes it's the forest and the trees...hard to see the solution until you step back a bit.

i saw you are using functions that return a table; just as an FYI, table-value-functions don't benefit from statistics; if they are return more than say, 100 rows of data, you should switch your two tables to be either in line or a view; it will perform better.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

paula_sql SSCrazy Points: 2665 More actions · Answer 5

Thnaks for all your help. It's really really appreciated!!