How to get equal group data - 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 2008 Forums

Transact-SQL (2008)

How to get equal group data

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2014-07-02 : 03:40:11

Hi All,

I have a table stored

Dates
-----
01/Apr/2014
01/May/2014
01/Jun/2014
01/Jul/2014

I have data Data table
-----------------------
ID Description Dates Amount
2 A 01/Apr/2014 500
3 B 01/Apr/2014 300
3 B 01/May/2014 200
4 T 01/Apr/2014 600
4 T 01/May/2014 700
4 T 01/Jun/2014 800
4 T 01/Jul/2014 500

I want output to be:-
----------------------------

ID Description Dates Amount
2 A 01/Apr/2014 500
2 A 01/May/2014 0 <<New added
2 A 01/Jun/2014 0
2 A 01/Jul/2014 0 <<new added
3 B 01/Apr/2014 300
3 B 01/May/2014 200
3 B 01/Jun/2014 0 <<new added
3 B 01/Jul/2014 0 <<new added
4 T 01/Apr/2014 600
4 T 01/May/2014 700
4 T 01/Jun/2014 800
4 T 01/Jul/2014 500

Please advise.

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-02 : 04:13:12

[code]SELECT x.ID,
x.[Description],
d.Dates,
ISNULL(w.Amount, 0) AS Amount
FROM dbo.Dates AS d
CROSS JOIN (
SELECT DISTINCT ID,
[Description]
FROM dbo.Data
) AS x
LEFT JOIN dbo.Data AS w ON w.ID = x.ID
AND w.[Description] = x.[Description]
AND w.Dates = d.Dates;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources