SUM value from 3 rows and store in other row? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2012)

SUM value from 3 rows and store in other row?

Author

Topic

tudorH
Starting Member

13 Posts

Posted - 2013-12-16 : 18:23:16

I have a table with data which looks like this:
sopnumbe incidents custname startdate enddate desc
123 6 custA 11/11/2013 11/11/2014 prod1 mod1
123 2 custA 11/11/2013 11/11/2014 prod1 mod2
123 2 custA 11/11/2013 11/11/2014 prod1 mod3
123 2 custA 11/11/2013 11/11/2014 prod1 mod4
123 6 custA 11/11/2013 11/11/2014 prod2 mod1
123 2 custA 11/11/2013 11/11/2014 prod2 mod2
123 2 custA 11/11/2013 11/11/2014 prod2 mod3

*Note: the description is a text field so it won't always contain the same amount of words, nor will they be in the same order, however, it is safe to assume that the product name (prod1 etc.) will be the first word.

What I need to do is sum up under 'prod1 mod1' the other modules such as 'prod1 mod2' etc. and then set their incidents value to 0. So for the above data I need the result to look like this:

sopnumbe incidents custname startdate enddate desc
123 12 custA 11/11/2013 11/11/2014 prod1 mod1
123 0 custA 11/11/2013 11/11/2014 prod1 mod2
123 0 custA 11/11/2013 11/11/2014 prod1 mod3
123 0 custA 11/11/2013 11/11/2014 prod1 mod4
123 10 custA 11/11/2013 11/11/2014 prod2 mod1
123 0 custA 11/11/2013 11/11/2014 prod2 mod2
123 0 custA 11/11/2013 11/11/2014 prod2 mod3

Is this possible? What I currently have is this:


SELECT 
CASE 
	WHEN desc LIKE '%Core%' THEN 6 + 
		((SELECT count(*) FROM myView test1 WHERE test1.SOPNUMBE = test2.SOPNUMBE AND test1.desc NOT LIKE '%core%' AND test1.CURTRXAM = 0 AND test1.StartDate IS NOT NULL AND test1.EndDate IS NOT NULL)*2)
		ELSE 0
	END AS Incidents,
	 * from myView test2
WHERE [CURTRXAM] = 0 AND StartDate IS NOT NULL AND EndDate IS NOT NULL 
ORDER BY EndDate DESC

Which does what I want except that if both prod1 and prod2 have the same sopnumbe then they will have the exact same incident count...I don't have any other unique column between the two lines so I'm not sure where to take it from here. Any ideas?

With my above query the result is as follows:
sopnumbe incidents custname startdate enddate desc
123 12 custA 11/11/2013 11/11/2014 prod1 mod1
123 0 custA 11/11/2013 11/11/2014 prod1 mod2
123 0 custA 11/11/2013 11/11/2014 prod1 mod3
123 0 custA 11/11/2013 11/11/2014 prod1 mod4
123 12 custA 11/11/2013 11/11/2014 prod2 mod1
123 0 custA 11/11/2013 11/11/2014 prod2 mod2
123 0 custA 11/11/2013 11/11/2014 prod2 mod3

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-17 : 01:14:02

[code]
; with
cte as
(
select *,
prod = left([desc], charindex(' ', [desc]) - 1),
rn = row_number() over (partition by sopnumbe, left([desc], charindex(' ', [desc]) - 1) order by [desc])
from myView
)
select sopnumbe,
incidents = case when rn = 1
then sum(incidents) over (partition by sopnumbe, prod)
else 0
end,
custname , startdate , enddate , [desc]
from cte[/code]

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

tudorH
Starting Member

13 Posts

Posted - 2013-12-17 : 11:16:13

Thank you khtan! Had to modify it a bit to get exactly what I need and expand on it a bit :), but definitely pointed me in the right direction. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources