How to get the occurence (Count)

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

General SQL Server Forums

New to SQL Server Programming

How to get the occurence (Count)

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-02-03 : 01:00:28

Hi Forumers,

My objective is to get the occurence of OrigReturntype and count how often this Origreturntype changes into New returntype based on
correctiondate and get the average TAT month to date.

*****************
SAMPLE DATA
*****************

IF OBJECT_ID('tempdb..#Table1', 'U') IS NOT NULL DROP TABLE #Table1
GO
Create Table #Table1
(
OrigreturnType Int,
NewReturnType Int,
CorrectionDate DateTime,
TAT numeric(28,12)
)

SET NOCOUNT ON
Insert Into #Table1 (OrigreturnType, NewReturnType, CorrectionDate, TAT) Values (0,4,'2011-10-26 00:00:00.000',105)
Insert Into #Table1 (OrigreturnType, NewReturnType, CorrectionDate, TAT) Values (0,4,'2011-10-26 00:00:00.000',10)
Insert Into #Table1 (OrigreturnType, NewReturnType, CorrectionDate, TAT) Values (0,6,'2011-11-30 00:00:00.000',63)
Insert Into #Table1 (OrigreturnType, NewReturnType, CorrectionDate, TAT) Values (0,6,'2011-12-01 00:00:00.000',9)
Insert Into #Table1 (OrigreturnType, NewReturnType, CorrectionDate, TAT) Values (0,2,'2011-12-23 00:00:00.000',174)
Insert Into #Table1 (OrigreturnType, NewReturnType, CorrectionDate, TAT) Values (2,0,'2011-10-22 00:00:00.000',43)
Insert Into #Table1 (OrigreturnType, NewReturnType, CorrectionDate, TAT) Values (2,0,'2011-12-23 00:00:00.000',66)
Insert Into #Table1 (OrigreturnType, NewReturnType, CorrectionDate, TAT) Values (3,8,'2011-11-07 00:00:00.000',9)
Insert Into #Table1 (OrigreturnType, NewReturnType, CorrectionDate, TAT) Values (3,8,'2011-12-07 00:00:00.000',8)
Insert Into #Table1 (OrigreturnType, NewReturnType, CorrectionDate, TAT) Values (8,7,'2011-12-09 00:00:00.000',39)
Insert Into #Table1 (OrigreturnType, NewReturnType, CorrectionDate, TAT) Values (8,7,'2011-12-09 00:00:00.000',41)

***************
SAMPLE RESULT
***************

OrigReturnType|NewReturnTpe|CorrectionMonth|TOTAL|AVERAGETAT
-----------------------------------------------------------
0 | 4 | 2011/10 | 2 | 58
0 | 6 | 2011/11 | 1 | 63
0 | 6 | 2011/12 | 1 | 9
0 | 2 | 2011/12 | 1 | 174
2 | 0 | 2011/10 | 1 | 43
2 | 0 | 2011/12 | 1 | 66
3 | 8 | 2011/11 | 1 | 9
3 | 8 | 2011/12 | 1 | 8
8 | 7 | 2011/12 | 2 | 40

[Code]
[\Code]
Thank you.

JOV

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-03 : 03:01:54

[code]
select OrigreturnType,
NewReturnType,
CorrectionMth = dateadd(month, datediff(month, 0, CorrectionDate), 0),
TOTAL = count(*),
AVERAGETAT = avg(TAT)
from #Table1
group by OrigreturnType, NewReturnType, dateadd(month, datediff(month, 0, CorrectionDate), 0)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-03 : 03:09:15

[code]SELECT OrigReturnType,
NewReturnType,
CONVERT(CHAR(7), CorrectionDate, 111) AS CorrectionMonth,
COUNT(*) AS TOTAL,
CAST(.5 + AVG(TAT) AS INT) AS AVERAGETAT
FROM #Table1
GROUP BY OrigReturnType,
NewReturnType,
CONVERT(CHAR(7), CorrectionDate, 111)[/code]

N 56°04'39.26"
E 12°55'05.63"

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-02-03 : 04:15:19

Thank you very much khtan & SwePeso...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources