Help with Querys - 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)

Help with Querys

Author

Topic

tyekhan786
Starting Member

9 Posts

Posted - 2014-07-17 : 08:24:36

need to the below information to have the hours down the row & have MULTI_SUB_FLAG across the top with only displaying the con figures under it.

SELECT DATEPART(hour, History.CallDateTime) AS hours, OverallTable.MULTI_SUB_FLAG,
SUM(CASE LastCR2 WHEN 'EE002' THEN SuccessValue * 1 WHEN 'EE003' THEN SuccessValue * 1 WHEN 'EE004' THEN SuccessValue * 1 ELSE SuccessValue
END) AS TotalSales
INTO totalsalest
FROM OverallTable INNER JOIN
History ON OverallTable.DID = History.DID INNER JOIN
EECR2SuccessValues ON History.CR2 = EECR2SuccessValues.CR2
WHERE (OverallTable.paygMigration = N'June 14')
GROUP BY OverallTable.MULTI_SUB_FLAG, DATEPART(hour, History.CallDateTime)
go
SELECT DATEPART(hour, History.CallDateTime) AS hours, OverallTable.MULTI_SUB_FLAG, COUNT(CR2.Contact) AS TotalDmcs
INTO totaldmcst
FROM OverallTable INNER JOIN
History ON OverallTable.DID = History.DID INNER JOIN
CR2 ON History.CR2 = CR2.CR2
WHERE (OverallTable.paygMigration = N'June 14') AND (CR2.Contact = 1)
GROUP BY OverallTable.MULTI_SUB_FLAG, DATEPART(hour, History.CallDateTime)
go
SELECT totaldmcst.hours, totaldmcst.MULTI_SUB_FLAG, totalsalest.TotalSales, totaldmcst.TotalDmcs, CONVERT(varchar(5), CONVERT(numeric(4, 1),
(SUM(totalsalest.TotalSales) / 1.0) / (SUM(totaldmcst.TotalDmcs) / 1.0) * 100)) AS Con
FROM totaldmcst INNER JOIN
totalsalest ON totaldmcst.hours = totalsalest.hours AND totaldmcst.MULTI_SUB_FLAG = totalsalest.MULTI_SUB_FLAG
GROUP BY totaldmcst.hours, totaldmcst.MULTI_SUB_FLAG, totalsalest.TotalSales, totaldmcst.TotalDmcs

--------------------

this is the output now

9 N 41.00 1070 3.8
9 Y 2.00 98 2.0
10 N 352.00 5611 6.3
10 Y 32.00 413 7.7
11 N 554.00 9220 6.0
11 Y 65.00 672 9.7
12 N 563.00 10069 5.6
12 Y 68.00 915 7.4
13 N 450.00 7838 5.7
13 Y 60.00 772 7.8
14 N 530.00 10385 5.1
14 Y 68.00 807 8.4
15 N 281.00 5402 5.2
15 Y 26.00 460 5.7
16 N 439.00 7835 5.6
16 Y 31.00 702 4.4
17 N 294.00 5476 5.4
17 Y 26.00 543 4.8
18 N 423.00 8011 5.3
18 Y 38.00 486 7.8
19 N 380.00 6647 5.7
19 Y 26.00 387 6.7
20 N 5.00 10 50.0

--------------------

But want this output,

N Y
9 3.8 2
10 6.3 7.7
11 6 9.7
12 5.6 7.4
13 5.7 7.8
14 5.1 8.4
15 5.2 5.7
16 5.6 4.4
17 5.4 4.8
18 5.3 7.8
19 5.7 6.7
20 50

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-17 : 08:50:55

[code]
;with aCTE
AS
(SELECT 9 AS [hours],'N' AS MULTI_SUB_FLAG,41.00 AS TotalSales,1070 AS TotalDmcs,3.8 AS Con UNION ALL
SELECT 9,'Y',2.00,98,2.0 UNION ALL
SELECT 10,'N',352.00,5611,6.3 UNION ALL
SELECT 10,'Y',32.00,413,7.7 UNION ALL
SELECT 19,'N',380.00,6647,5.7 UNION ALL
SELECT 19,'Y',26.00,387,6.7 UNION ALL
SELECT 20,'N',5.00,10,50.0)

SELECT
[hours]
,SUM(CASE WHEN MULTI_SUB_FLAG ='N' THEN Con ELSE 0 END) as [N]
,SUM(CASE WHEN MULTI_SUB_FLAG ='Y' THEN Con ELSE 0 END) as [Y]
FROM
aCTE
GROUP BY
[hours]
[/code]

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources