Need to get one row output from multiple rows

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 2008 Forums

Transact-SQL (2008)

Need to get one row output from multiple rows

Author

Topic

prakashpv
Starting Member

2 Posts

Posted - 2013-10-28 : 07:22:38

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

52326 Posts

Posted - 2013-10-28 : 07:52:32

[code]
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
[/code]

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-28 : 08:30:49

[code];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[/code]

prakashpv
Starting Member

2 Posts

Posted - 2013-10-28 : 08:33:56

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources