retrieve second qty value - 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 2005 Forums

Transact-SQL (2005)

retrieve second qty value

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-06-21 : 05:14:02

Hi

I have this query that give me a qty value.

SELECT     COUNT(dbo.tbl_1.ReportNumber) AS Qty
FROM         dbo.tbl_1 INNER JOIN
                      dbo.tbl_2 ON dbo.tbl_1.ReportID = dbo.tbl_2.ReportID
WHERE     (dbo.tbl_1.CaseActive = 0) AND (dbo.tbl_2.Case_Forwarded = 0) OR
                      (dbo.tbl_1.CaseActive = 0) AND (dbo.tbl_2.Case_Forwarded IS NULL) AND 
                      (dbo.tbl_1.DateReported BETWEEN '2006-01-01' AND '2008-01-01')

But I would also like to retrieve the total qty of "caseactive = 0" and caseforwarded = 1 as "Qty2". Can someone please tell how I can change the query to get this to work?

Regards

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-21 : 05:34:36

Something like this:

SELECT     
	SUM(case When dbo.tbl_1.CaseActive = 0 and Coalesce(dbo.tbl_2.Case_Forwarded,0) = 0 then 1 else 0 end) AS Qty,
	SUM(case when dbo.tbl_1.CaseActive = 0 and Coalesce(dbo.tbl_2.Case_Forwarded,0) = 1 then 1 else 0 end) AS Qty2
FROM	
	dbo.tbl_1 INNER JOIN dbo.tbl_2 ON dbo.tbl_1.ReportID = dbo.tbl_2.ReportID
WHERE
	dbo.tbl_1.DateReported BETWEEN '20060101' AND '20080101'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-06-21 : 05:42:25

Hi Harsh

Wow, that was quick and exatctly what I was looking for. Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-21 : 10:01:09

Read about Cross-tab Reports in sql server help file for more information on Cross tab reports

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources