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 2008 Forums
 Transact-SQL (2008)
 Need to get one row output from multiple rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

prakashpv
Starting Member

India
2 Posts

Posted - 10/28/2013 :  07:22:38  Show Profile  Reply with Quote
Hi

Need your help urgently for creating a SQL Script to create a view for a report. The Table has the following records

Date(dd/mm/yyyy) Entity Cash on Hand
01/09/2013 S001 100.00
01/09/2013 S002 200.00
02/09/2013 S001 300.00
01/10/2013 S001 400.00
02/10/2013 S002 500.00

Output should be (based on Start and End Date for current period)

Entity Current Date Cash on Hand Month minus 1 Cash on Hand
S001 01/10/2013 400.00 01/09/2013 100.00
S002 02/10/2013 500.00 02/10/2013 0.00

Appreciate all your quick help


Thanks and Best Regards,
Prakash Viswanathan

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/28/2013 :  07:52:32  Show Profile  Reply with Quote

SELECT Entity,
MAX(CASE WHEN [Date] = DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) THEN [Date] END) AS CurrentDate,
MAX(CASE WHEN [Date] = DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) THEN [Cash On Hand] END) AS [Current Cash on Hand],
MAX(CASE WHEN [Date] = DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0) THEN [Date] END) AS [Month - 1],
MAX(CASE WHEN [Date] = DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0) THEN [Cash On Hand] END) AS [(Month-1) Cash On Hand)
FROM Table
GROUP BY ENtity


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

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 10/28/2013 :  08:30:49  Show Profile  Reply with Quote
;WITH cte AS
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY Entity, DATEADD(mm,DATEDIFF(mm,0,Date),0) ORDER BY Date DESC) AS RN
	FROM
		Table
)
SELECT
	a.Entity,
	a.Date AS CurrentDate,
	a.CashOnHand,
	b.CashOnHand
FROM
	cte a
	LEFT JOIN cte b ON b.RN=1 AND DATEADD(mm,DATEDIFF(mm,0,b.Date)+1,0) 
		= DATEADD(mm,DATEDIFF(mm,0,a.Date),0)
		AND a.entity = b.entity
WHERE
	a.RN=1
Go to Top of Page

prakashpv
Starting Member

India
2 Posts

Posted - 10/28/2013 :  08:33:56  Show Profile  Reply with Quote
Thanks a lot for a quick response and it worked. With some small modifications, could get the required output.

Appreciate the help.

Thanks and Best Regards,
Prakash Viswanathan
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.05 seconds. Powered By: Snitz Forums 2000