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

Transact-SQL (2005)

SQL Query help

Author

Topic

wibni
Starting Member

31 Posts

Posted - 2013-07-06 : 11:20:57

Hi,
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):

Working eqipment:
Country | Date | Eqipment
10 |20130430| 8
20 |20130430| 4
30 |20130430| 10
10 |20130531| 12
20 |20130531| 6
30 |20130531| 4

Purchase Orders:

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-07 : 12:08:14

You've not shown any data n PurchaseOrder also for June.
can you post some data from PO table for June and then show us how you want the output to come for them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wibni
Starting Member

31 Posts

Posted - 2013-07-07 : 21:05:50

Including June, my tables look like this (simplified):

Working eqipment:
Country | Date | Eqipment
10 |20130430| 8
20 |20130430| 4
30 |20130430| 10
10 |20130531| 12
20 |20130531| 6
30 |20130531| 3

Purchase Orders:

Value | Date | Country
100 |20130430| 10
500 |20130430| 20
800 |20130430| 30
500 |20130531| 10
750 |20130531| 20
800 |20130531| 30
400 |20130630| 10
300 |20130630| 20
450 |20130630| 30

The result I'd like to see is:
YearMonth|SumOfEquipment|POValue
201304 | 22 |1400
201305 | 21 |2050
201306 | 21 |1150

The red number is the one I struggle to generate.
It is always the sum of equipment of the most recent month.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-07-08 : 00:47:10

--May be this?

;WITH cte 
AS(SELECT p.Date, SUM(Eqipment) SumOfEquipment,  SUM(Value) POValue, ROW_NUMBER() OVER(ORDER BY p.date DESC) RN
		FROM PurchaseOrders p 
		LEFT JOIN Workingeqipment w on p.Country = w.Country AND p.Date = w.Date
		GROUP BY p.Date
	 )
SELECT c1.Date ,CASE WHEN c1.RN=1 THEN c2.SumOfEquipment ELSE c1.SumOfEquipment END SumOfEquipment, c1.POValue
FROM cte c1 
LEFT JOIN cte c2 ON c1.RN = c2.RN-1

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 01:29:27

[code]
SELECT *
FROM (SELECT [Date], SUM(Value) AS POValue
FROM [Purchase Orders]
GROUP BY [Date])po
CROSS APPLY (SELECT TOP 1 SUM(Eqipment) AS SumOfEquipment
FROM [Working eqipment]
WHERE [Date]<=po.[Date]
GROUP BY [date]
ORDER BY [date] DESC
)eq
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wibni
Starting Member

31 Posts

Posted - 2013-07-08 : 05:09:24

Thanks a lot. They both seem to do what I need.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 05:12:51

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-07-08 : 06:59:09

quote:
Originally posted by wibni

Thanks a lot. They both seem to do what I need.

welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources