I have 2 tables. One with Purchase order info, the other with the number of working equipment we have.
I want to show the average PO value for working eqipment of every month.
The tricky part is that the numbers for the working equipment are only available middle of each month, but the report I'm doing runs at the start of each month.
What I'd like to do is use the working equipment numbers of the last available months for the current month.
My tables look like this (simplified):
Country | Date | Eqipment
10 |20130430| 8
20 |20130430| 4
30 |20130430| 10
10 |20130531| 12
20 |20130531| 6
30 |20130531| 4
Value | Date | Country
100 |20130430| 10
500 |20130430| 20
800 |20130430| 30
500 |20130531| 10
750 |20130531| 20
800 |20130531| 30
The SQL I use is this
SELECT SUM(POValue) AS LineTotal, LEFT(PH.DATE, 6) AS YearMonth, LEFT(PH.VDCODE, 2) AS Country, AM.Eqipment
FROM POPORH1 AS PH INNER JOIN
POPORL AS PL ON PH.PORHSEQ = PL.PORHSEQ LEFT OUTER JOIN
AMWorkingRigs AS AM ON LEFT(PH.VDCODE, 2) = AM.Country AND LEFT(PH.DATE, 6) = LEFT(AM.Date, 6)
WHERE (LEFT(PH.DATE, 4) >= LEFT(CAST(CONVERT(varchar(8), DATEADD(YEAR, - 1, GETDATE()), 112) AS INT), 4))
GROUP BY LEFT(PH.DATE, 6), LEFT(PH.VDCODE, 2), AM.Equipment
With no data for June in my equipment table June is not shown in the results.
I'd like to show June PO values (which exist) with the most recent equipment numbers (May in this case).
Once June equipment numbers are available the table is updated and the actual June numbers are being used.
Is that possible with SQL?