SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL Query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wibni
Starting Member

28 Posts

Posted - 07/06/2013 :  11:20:57  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/07/2013 :  12:08:14  Show Profile  Reply with Quote
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
Go to Top of Page

wibni
Starting Member

28 Posts

Posted - 07/07/2013 :  21:05:50  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2208 Posts

Posted - 07/08/2013 :  00:47:10  Show Profile  Reply with Quote
--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

Edited by - bandi on 07/08/2013 00:47:41
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/08/2013 :  01:29:27  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wibni
Starting Member

28 Posts

Posted - 07/08/2013 :  05:09:24  Show Profile  Reply with Quote
Thanks a lot. They both seem to do what I need.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/08/2013 :  05:12:51  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2208 Posts

Posted - 07/08/2013 :  06:59:09  Show Profile  Reply with Quote
quote:
Originally posted by wibni

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


welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000