Solved

SQL Query - Repeating a Row

Posted on 2014-09-23
3
116 Views
Last Modified: 2014-10-06
I have a table called "ACCOUNT_TEMP" that has some data in there.  

Here's what my raw table looks like.
Raw Data
From the output example, the column F is what I'm after.  I am able to get all of it with my query, but I need to roll up all the dollar amount in Column E into account "15-200" with the data center.
Output
As you can see, from the above example, the items highlighted, I need to repeat the account "15-200".  This is to roll up all the rows with a value greater than zero in the "Sales Amt" column with the respective "Data Center".  Data Center "K00200" needs to always be first and data center "K00380" always needs to be last.  How would I go about doing this?

Here's what I was able to get with my query, but can't seem to get the highlighted yellow portion and need help.
Query
My sql query.
SELECT	A.ACCOUNT_ID, A.DESCRIPTION, A.TOTAL_BALANCE, A.DATA_CENTER, A.SALES_AMT,

		CASE	WHEN	A.ACCOUNT_ID = '10-200' AND A.DATA_CENTER = 'K00300' 
				THEN	(A.TOTAL_BALANCE - B.SALES_AMT) 
				ELSE	(A.TOTAL_BALANCE + A.SALES_AMT)
		END	NEW_TOTAL
		
FROM	ACCOUNT_TEMP A

		LEFT OUTER JOIN
		(
		SELECT	A.ACCOUNT_ID, SUM(A.SALES_AMT) SALES_AMT
		FROM	ACCOUNT_TEMP A
		GROUP BY A.ACCOUNT_ID
		) B ON B.ACCOUNT_ID = A.ACCOUNT_ID

Open in new window

0
Comment
Question by:holemania
  • 2
3 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40340629
Is this SQL Server 2005? (or, what version of SQL Server is this for?)

why do we have to start at the table ACCOUNT_TEMP and not earlier?
what is the code that produces ACCOUNT_TEMP?

Is there some hierarchy data available to the query?

can you provide some relevant account_temp data in a reusable format please
0
 

Accepted Solution

by:
holemania earned 0 total points
ID: 40355835
There's no hierarchy other than what I posted.  I was able to get what I needed.  Had to create temp table and worked with the temp table.
0
 

Author Closing Comment

by:holemania
ID: 40363271
I was able to resolve the issue myself.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now