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 2008 Forums
 Transact-SQL (2008)
 2 lines combine into 1 line
 New Topic  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
2223 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  
 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.08 seconds. Powered By: Snitz Forums 2000