sql query issues - 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 2005 Forums

Transact-SQL (2005)

sql query issues

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2008-11-21 : 05:36:18

[code]
Declare @Inc_Count table
(
incID int null
,typeID int null
,deptDESC VARCHAR (20) null
)
insert into @Inc_Count (incID,typeID,deptDESC)
select 5238, 3, 'DEPT A' union all
select 5245, 3, 'DEPT A' union all
select 5258, 3, 'DEPT B' union all
select 5266, 4, 'DEPT C' union all
select 5281, 1, 'DEPT A' union all
select 5301, 3, 'DEPT B' union all
select 5306, 3, 'DEPT A' union all
select 5306, 2, 'DEPT A' union all
select 5306, 4, 'DEPT A' union all
select 5306, 2, 'DEPT A' union all
select 5309, 1, 'DEPT C'

select * from @Inc_Count ORDER BY deptDesc, typeid
[/code]

i need to generate data on the basis of departments with a particular formula -

[code]
typeID Mult. factor
1 50
2 31
3 18
4 1

Final output required:
deptDesc Score
'DEPT A' (50*1 + 31*2 + 18*3 + 1*1)
'DEPT B' (50*0 + 31*0 + 18*2 + 1*0)
'DEPT C' (50*1 + 31*0 + 18*0 + 1*1)

[/code]
i start with getting the count of all typeID for each department into a temporary table & then it sum it up
to get the score; creating 3 tables for this purpose

can anybody please suggest me a better approach to do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 06:08:55

is this what you want

Declare @Inc_Count table
(
incID int null
,typeID int null
,deptDESC VARCHAR (20) null
)
DEclare @Mul_Factor table
(
typeID int null,
Factor int null
)

insert into @Inc_Count (incID,typeID,deptDESC)
select 5238,  3, 'DEPT A' union all
select 5245,  3, 'DEPT A' union all
select 5258,  3, 'DEPT B' union all
select 5266,  4, 'DEPT C' union all
select 5281,  1, 'DEPT A' union all
select 5301,  3, 'DEPT B' union all
select 5306,  3, 'DEPT A' union all
select 5306,  2, 'DEPT A' union all
select 5306,  4, 'DEPT A' union all
select 5306,  2, 'DEPT A' union all
select 5309,  1, 'DEPT C'

insert into @Mul_Factor
select 1,50 union all
select 2,31 union all
select 3,18 union all
select 4,1 

;With CTE (incID,typeID,deptDESC,typeCnt) AS
(
select i.incID,i.typeID,i.deptDESC,count(*) over (partition by i.deptDESC,mf.typeID) * mf.Factor AS typeCnt
from @Inc_Count  i
INNER JOIN @Mul_Factor mf
ON mf.typeID=i.typeID
)

SELECT *
FROM CTE c1
INNER JOIN (SELECT deptDESC,SUM(DISTINCT typeCnt) AS deptCount
            FROM CTE
            GROUP BY deptDESC)c2
ON c1.deptDESC=c2.deptDESC



output
-------------------------------------
incID	typeID	deptDESC	typeCnt	deptDESC	deptCount
5281	1	DEPT A	50	DEPT A	167
5306	2	DEPT A	62	DEPT A	167
5306	2	DEPT A	62	DEPT A	167
5306	3	DEPT A	54	DEPT A	167
5238	3	DEPT A	54	DEPT A	167
5245	3	DEPT A	54	DEPT A	167
5306	4	DEPT A	1	DEPT A	167
5301	3	DEPT B	36	DEPT B	36
5258	3	DEPT B	36	DEPT B	36
5309	1	DEPT C	50	DEPT C	51
5266	4	DEPT C	1	DEPT C	51

replace * with only columns you want

zion99
Posting Yak Master

141 Posts

Posted - 2008-11-21 : 08:49:38

thanks visakh16, it works great

had a small doubt. i tried using WITH CTE2 ... in place of INNER JOIN, but it didnt work. any ideas?

quote:

SELECT *
FROM CTE c1
INNER JOIN (SELECT deptDESC,SUM(DISTINCT typeCnt) AS deptCount
FROM CTE
GROUP BY deptDESC)c2
ON c1.deptDESC=c2.deptDESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 08:56:00

quote:
Originally posted by zion99

thanks visakh16, it works great
had a small doubt. i tried using WITH CTE2 ... in place of INNER JOIN, but it didnt work. any ideas?
quote:

SELECT *
FROM CTE c1
INNER JOIN (SELECT deptDESC,SUM(DISTINCT typeCnt) AS deptCount
FROM CTE
GROUP BY deptDESC)c2
ON c1.deptDESC=c2.deptDESC

you need join with CTE here. With CTE is used for defining the CTE and here you need it to be joined. its like a temporary table which is available for one time use after creation.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 08:56:34

Refer about CTEs in link below to understand how they work

http://msdn.microsoft.com/en-us/library/ms190766(SQL.90).aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 09:13:50

Why overcomplicate things?

-- Prepare sample data
DECLARE	@Master TABLE
	(
		incID INT,
		typeID TINYINT,
		deptDesc VARCHAR(20)
	)

INSERT	@Master
	(
		incID,
		typeID,
		deptDesc
	)
SELECT	5238,  3, 'Dept A' UNION ALL
SELECT	5245,  3, 'Dept A' UNION ALL
SELECT	5258,  3, 'Dept B' UNION ALL
SELECT	5266,  4, 'Dept C' UNION ALL
SELECT	5281,  1, 'Dept A' UNION ALL
SELECT	5301,  3, 'Dept B' UNION ALL
SELECT	5306,  3, 'Dept A' UNION ALL
SELECT	5306,  2, 'Dept A' UNION ALL
SELECT	5306,  4, 'Dept A' UNION ALL
SELECT	5306,  2, 'Dept A' UNION ALL
SELECT	5309,  1, 'Dept C'

DECLARE	@Factor TABLE
	(
		typeID TINYINT,
		factValue MONEY
	)

INSERT	@Factor
	(
		typeID,
		factValue
	)
SELECT	1, 50 UNION ALL
SELECT	2, 31 UNION ALL
SELECT	3, 18 UNION ALL
SELECT	4,  1

-- Here is the final required output
SELECT		m.deptDesc,
		SUM(f.factValue) AS Score
FROM		@Master AS m
INNER JOIN	@Factor AS f ON f.typeID = m.typeID
GROUP BY	m.deptDesc
ORDER BY	m.deptDesc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources