?
Solved

How to convert a "C" query to SQL so it can be used in MS Query?

Posted on 2009-04-29
2
Medium Priority
?
238 Views
Last Modified: 2012-05-06
How can I convert the solution of question
http://www.experts-exchange.com/Programming/Languages/C/Q_22619649.html
to a SQL query?  I do not have a source to create a view for me (unless it can be done in the SQL query).  
Ultimately I will be asked to put this query in Access 2003...

My SQL query that is returned to an Excel pivot table is below.

I am a beginner and have no experience with C and very little with SQL (I made a query in MS Query, viewed in the SQL tab and had someone tell me how to use the "Case When" statement.

Thanks a million!

SELECT ACCOUNTS.account_num, ACCOUNTS.type, ACCOUNTS.terminated, ACCOUNTS.category, ACCOUNTS.balance, FULL_ACCOUNT.account_num, FULL_ACCOUNT.full_account, CATEGORY_INFO.category, CATEGORY_INFO.description, TRANS_MAIN.account_num, TRANS_MAIN.posting_date, TRANS_MAIN.event_date, TRANS_MAIN.accounting_date, TRANS_MAIN.posting_code,        
  Case When TRANS_MAIN.posting_code < 199 
     Then TRANS_MAIN.net_amount
  When TRANS_MAIN.posting_code >= 200 And TRANS_MAIN.posting_code <= 255
     Then (TRANS_MAIN.net_amount * -1)
  End As NetAmount, 
TRANS_MAIN.division, TRANS_MAIN.type, TRANS_MAIN.category 
FROM AR1.dbo.ACCOUNTS ACCOUNTS, AR1.dbo.CATEGORY_INFO CATEGORY_INFO, AR1.dbo.FULL_ACCOUNT FULL_ACCOUNT, AR1.dbo.TRANS_MAIN TRANS_MAIN
WHERE ACCOUNTS.account_num = FULL_ACCOUNT.account_num AND ACCOUNTS.account_num = TRANS_MAIN.account_num AND ACCOUNTS.category = CATEGORY_INFO.category AND ((ACCOUNTS.type<>3 And ACCOUNTS.type<>6 And ACCOUNTS.type<>7) AND (ACCOUNTS.category=14) AND (ACCOUNTS.terminated<>'Y') AND (ACCOUNTS.balance<>$0) AND (TRANS_MAIN.posting_code<=255) AND (TRANS_MAIN.accounting_date<={ts '2009-04-10 00:00:00'}))

Open in new window

0
Comment
Question by:kgittinger
2 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 24259823
Use a template query like the one below and create a new pivot table in MS-Excel using the derived field 'Ageing' as a pivot row -
SELECT  Tmp.*, 
	CASE	WHEN DaysElapsed <= 30 THEN	'   <= 30 days'
		WHEN DaysElapsed <= 60 THEN	' 31 - 60 days'
		WHEN DaysElapsed <= 90 THEN	' 61 - 90 days'
		WHEN DaysElapsed <= 120 THEN	' 91 -120 days'
		ELSE				'   > 120 days'
	END as Ageing
FROM
	(
	SELECT	AC.account_num, AC.type, AC.terminated, AC.category, AC.balance, FA.account_num, 
		FA.full_account, CI.category, CI.description, TM.account_num, TM.posting_date,
		TM.event_date, TM.accounting_date, TM.posting_code,        
		Case	When TM.posting_code < 199 
			Then TM.net_amount
			When TM.posting_code >= 200 And TM.posting_code <= 255
			Then (TM.net_amount * -1)
		End As NetAmount, 
		TM.division, TM.type, TM.category,
		DATEDIFF(day, TM.accounting_date, {ts '2009-04-10 00:00:00'}) As DaysElapsed
	FROM
		AR1.dbo.ACCOUNTS AS AC,
		AR1.dbo.CATEGORY_INFO AS CI,
		AR1.dbo.FULL_ACCOUNT AS FA, 
		AR1.dbo.TRANS_MAIN AS TM
	WHERE
		AC.account_num = FA.account_num
	AND AC.account_num = TM.account_num
	AND AC.category = CI.category
	AND AC.category=14
	AND TM.posting_code<=255
	AND TM.accounting_date<={ts '2009-04-10 00:00:00'}
	AND NOT EXISTS (SELECT NULL FROM AR1.dbo.ACCOUNTS AS AC1 
			WHERE AC1.account_num = AC.account_num
			AND AC1.type in (3,6,7)
			AND AC1.balance = 0
			AND AC1.terminated = 'Y')
	) AS Tmp

Open in new window

0
 

Author Closing Comment

by:kgittinger
ID: 31575861
This certainly is a beautiful thing.

Thanks for your help.  

If I need to tweek further, I will post a related question.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

862 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