Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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)
 2 lines combine into 1 line
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

120 Posts

Posted - 05/06/2013 :  09:31:41  Show Profile  Reply with Quote
I have the code that shows 2 lines and I would like to get it to show all on one line instead. Also if you know a better Case when Else please help with that. This I am sure is very simple and stupid. Sorry for wasting your time on this.

SELECT [disp-no]
, CASE WHEN (Billed <> 'True') THEN (SUM([hours-bill])) END AS Unbilledhr
, CASE WHEN (Billed = 'True') THEN (SUM([hours-bill])) END AS billedhr
, SUM([hours-bill]) AS totalhr
, CASE WHEN (Billed <> 'TRUE') THEN (SUM([cost-amt])) END AS unbilledamt
, CASE WHEN (Billed = 'TRUE') THEN (SUM([cost-amt])) END AS billedamt
, SUM([cost-amt])AS totalamt
, SUM([hours-bill]) AS totalhr

FROM dbo.[jc-time-sh]
WHERE ([disp-no] = '046210')
GROUP BY [disp-no], Billed

disp-no, Unbilledhr, billedhr, totalhr, unbilledamt, billedamt, Totalamt
046210, 17.00, NULL, 441.58, NULL, 441.58, 17.00
046210, NULL, 412.00, NULL, 11417.65, 11417.65, 412.00

hbadministrator
Posting Yak Master

120 Posts

Posted - 05/06/2013 :  09:49:42  Show Profile  Reply with Quote
I created the above as a view and then created another view that did a sum and gave me the lines I was looking for but I was wondering if this can all be done in 1 view?
SELECT [disp-no], SUM(Unbilledhr) AS Unbilledhr, SUM(billedhr) AS Billedhr, SUM(totalhr) AS Totalhr, SUM(unbilledamt) AS Unbilledamt, SUM(billedamt) AS Billedamt,
SUM(totalamt) AS Totalamt
FROM dbo.JL_comtimesh
GROUP BY [disp-no]
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 05/06/2013 :  09:53:34  Show Profile  Reply with Quote
without knowing the details of the data, we (at least I) can only guess... use Max for each "case" e.g.

SELECT [disp-no]
, Max(CASE WHEN (Billed <> 'True') THEN (SUM([hours-bill]))) END AS Unbilledhr
, Max(CASE WHEN (Billed = 'True') THEN (SUM([hours-bill]))) END AS billedhr
....
...
..
FROM dbo.[jc-time-sh]
WHERE ([disp-no] = '046210')
GROUP BY [disp-no]

Cheers
MIK

Edited by - MIK_2008 on 05/06/2013 09:54:56
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 05/06/2013 :  09:55:04  Show Profile  Reply with Quote
--Try this??
SELECT [disp-no]
,SUM( CASE WHEN (Billed <> 'True') THEN [hours-bill] END) AS Unbilledhr
,SUM( CASE WHEN (Billed = 'True') THEN [hours-bill])) END) AS billedhr
, SUM([hours-bill]) AS totalhr
,SUM( CASE WHEN (Billed <> 'TRUE') THEN [cost-amt] END) AS unbilledamt
,SUM( CASE WHEN (Billed = 'TRUE') THEN [cost-amt] END) AS billedamt
, SUM([cost-amt])AS totalamt
, SUM([hours-bill]) AS totalhr
FROM dbo.[jc-time-sh]
WHERE ([disp-no] = '046210')
GROUP BY [disp-no]

--
Chandu
Go to Top of Page
  Previous Topic Topic Next 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