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 2012 Forums
 Transact-SQL (2012)
 please help to write the query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deniskodua
Starting Member

1 Posts

Posted - 12/08/2013 :  07:06:15  Show Profile  Reply with Quote
There is a table with information about
how many partners at what stage is the



need to get another column to the table
"Number of partners in the previous step"
(the previous stage is a stage for the previous row in this table)

I made ??it through the left join, but I need to do without the left join
and without a nested subquery in select

Is this possible?

example


DECLARE @t TABLE
(
	id INT PRIMARY KEY IDENTITY(1,1),
	stage_code NVARCHAR(MAX),
	stage_level INT,
	partner_id INT
)


INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('simple_application', 0, 11)
INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('simple_application',0, 13)
INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('simple_application',0, 14)
INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('simple_application',0, 15)
INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('extended_application',0, 16)
INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('extended_application',0, 18)
INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('extended_application',0, 12)
INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('extended_application',0, 17)
INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('finance_stage',1, 11)
INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('finance_stage',1, 12)
INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('legal_stage',2, 11)
INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('legal_stage',2, 12)
INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('registration_ip',3, 11)
INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('registration_ip',3, 12)
INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('acting_partner',4, 11)
INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('acting_partner',4, 12)
INSERT INTO @t(stage_code, stage_level, partner_id) VALUES('acting_partner', 4, 13)

select * from @t



SELECT res.stage_code,
		ISNULL(res.stage_level, 0) AS current_level,
		 ISNULL(res.partners_count, 0) AS on_current_stage_partners_count,
		  ISNULL(self.stage_level, 0) AS previous_level,
		   ISNULL(self.partners_count, 0) AS on_previous_level_partners_count
 FROM
 (SELECT stage_code,
		  stage_level,
		   COUNT(partner_id) AS partners_count
		 FROM @t
		  GROUP BY stage_code, stage_level) res 
LEFT JOIN
(SELECT stage_level,
		  COUNT(partner_id)	AS partners_count
   FROM @t GROUP BY stage_level) self ON(res.stage_level - 1 = self.stage_level)

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 12/08/2013 :  09:29:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You should consider using a recursive CTE.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
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.09 seconds. Powered By: Snitz Forums 2000