2 lines combine into 1 line - 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)

2 lines combine into 1 line

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-05-06 : 09:31:41

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 - 2013-05-06 : 09:49:42

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]

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-06 : 09:53:34

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-06 : 09:55:04

--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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources