Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  SQL Query help Reply to Topic  Printer Friendly
Author  Topic

wibni
Starting Member

31 Posts

 Posted - 07/06/2013 :  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 | Eqipment10 |20130430| 820 |20130430| 430 |20130430| 1010 |20130531| 1220 |20130531| 630 |20130531| 4Purchase Orders:Value | Date | Country 100 |20130430| 10500 |20130430| 20800 |20130430| 30500 |20130531| 10750 |20130531| 20800 |20130531| 30The 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
52326 Posts

 Posted - 07/07/2013 :  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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

wibni
Starting Member

31 Posts

 Posted - 07/07/2013 :  21:05:50 Including June, my tables look like this (simplified):Working eqipment:Country | Date | Eqipment10 |20130430| 820 |20130430| 430 |20130430| 1010 |20130531| 1220 |20130531| 630 |20130531| 3Purchase Orders:Value | Date | Country100 |20130430| 10500 |20130430| 20800 |20130430| 30500 |20130531| 10750 |20130531| 20800 |20130531| 30400 |20130630| 10300 |20130630| 20450 |20130630| 30The result I'd like to see is:YearMonth|SumOfEquipment|POValue201304 | 22 |1400201305 | 21 |2050201306 | 21 |1150The red number is the one I struggle to generate.It is always the sum of equipment of the most recent month.

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts

 Posted - 07/08/2013 :  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 Edited by - bandi on 07/08/2013 00:47:41

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

 Posted - 07/08/2013 :  01:29:27 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

wibni
Starting Member

31 Posts

 Posted - 07/08/2013 :  05:09:24 Thanks a lot. They both seem to do what I need.

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

 Posted - 07/08/2013 :  05:12:51 welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts

 Posted - 07/08/2013 :  06:59:09 quote:Originally posted by wibniThanks a lot. They both seem to do what I need.welcome--Chandu
Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC