February 11, 2014 at 12:15 pm
I am wanting to break a branch office out of the corporate office to determine the revenue for this branch, other branches have already been separated from the corporate office but now Corporate wants the branch that does installs removed from Corporate to determine what is the true expenses for this Branch, the DB is a 2012 SQL Server DB. There is Sales, Marketing, Contracts, Installs, Warehouse and Admin within each Branch. Currently everything for the branch at Corporate is being charged to Corporate, and there is a unique value (00XXXX-XX-XX) Here is a real one, 1st 2 numbers is org 2nd 4 number is item 3rd set consists of org and branch
(19) (2700)-(40-AA) And what I am needing is a clean way to add a new branch example (60-AA), and 60 is Corporate and needs to be split off to a new Branch 66 for example, but need to be able to keep historical data. I hope people are able to understand what I am trying to do.
Thank You...
February 11, 2014 at 12:54 pm
It sounds like you are looking for SQL query help for SQL Server 2012. This forum is for Administrating SQL Server 2005.
Try your question over here at T-SQL
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
February 11, 2014 at 12:57 pm
Please provide sample data and what the desired results should look like.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 11, 2014 at 12:59 pm
You have given us your business requirements, but we cannot see a thing about your system. We don't know how it's organized, we don't know what the tables look like, and we have no examples of how its used.
Any advice given here would be flawed, at best, without a lot more detail into the problem.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 11, 2014 at 1:56 pm
Where GL_CODE has last 2 AA that is Corporate, I need to have for example AB for Atlanta branch but AA stays as Corporate, I also need to reserve the history of older records, but change them to the new if the charge was to corporate.
BRANCH_IDINV_TYPE INVOICE_NUM CASH_APPLIED GL_CODE POST_DEBIT_GL_CODE
AACA 231760-713.40 045500-00-AA 050000-00-AA
AACA 231870-199.86 045500-00-AA 050000-00-AA
AACA 224546-399.90 045500-00-AA 050000-00-AA
AACA 239779-497.16 047500-00-AA 050000-00-AA
AACA 237353-89.40 045500-00-AA 050000-00-AA
AACA 237499-74.91 047500-00-AA 050000-00-AA
AACA 218522-413.88 047500-00-AA 050000-00-AA
AACA 227799-96.54 047500-00-AA 050000-00-AA
AACA 211681-94.65 045500-00-AA 050000-00-AA
RICA 218698-55.70 045500-00-AA 050000-00-RI
AACA 227935-128.40 047500-00-AA 050000-00-AA
AACA 220940-129.72 047500-00-AA 050000-00-AA
AACJ 29057-2125.00 045500-00-AA 050000-00-AA
AACA 220999-87.81 047500-00-AA 050000-00-AA
AACA 228122-91.58 047500-00-AA 050000-00-AA
AACA 214144-94.44 047500-00-AA 050000-00-AA
AACA 223237-94.50 045500-00-AA 050000-00-AA
AACA 235951-135.72 047500-00-AA 050000-00-AA
AACA 226303-375.09 047500-00-AA 050000-00-AA
AACA 219223-28.12 047500-00-AA 050000-00-AA
WC J 3553-650.00 045500-00-AA 050000-00-AA
WC A 18513-162.54 047500-00-AA 050000-00-AA
AACA 223652-914.13 047500-00-AA 050000-00-AA
AACA 230829-955.29 047500-00-AA 050000-00-AA
RICP !)6 -286.08 045500-00-AA 103400-00-AA PREPAYMENT
RICT !#( -375.87 045500-00-AA 050000-00-AA DEPOSIT
AACA 218666-94.65 045500-00-AA 050000-00-AA
AACS 65516-84.85 045500-00-AA 050000-00-AA
AACJ 29058-200.00 045500-00-AA 050000-00-AA
AACC 2786-15.00 045500-00-AA 050000-00-AA
AACA 219473-240.00 045500-00-AA 050000-00-AA
AACF 18528-2.54 951500-95-AA 050000-00-AA WRITEOFF
RICI 5284-30.00 047500-00-AA 050000-00-RI
RICA 233298-92.28 047500-00-AA 050000-00-RI
RICA 220972-84.36 047500-00-AA 050000-00-RI
RICA 223237-94.50 045500-00-AA 050000-00-RI
AACA 238049-96.00 045500-00-AA 050000-00-AA
AACA 230605-773.88 047500-00-AA 050000-00-AA
AACA 219223-28.12 047500-00-AA 050000-00-AA
WC A 19116-2.43 047500-00-AA 050000-00-WC
AACJ 21330-37.00 047500-00-AA 050000-00-AA
AACI 5008-200.00 047500-00-AA 050000-00-AA
AACF 17632-7.56 045500-00-AA 050000-00-AA
RICA 204832-80.85 045500-00-AA 050000-00-RI
RICI 4761-15.00 045500-00-AA 050000-00-RI
AACA 212049-80.85 045500-00-AA 050000-00-AA
WCA 217158-80.85 045500-00-AA 050000-00-WC
WCS 64761-230.00 045500-00-AA 050000-00-WC
WCF 18491-2.60 045500-00-AA 050000-00-WC
AACF 18491-1.38 951500-95-AA 050000-00-WCWRITEOFF
February 11, 2014 at 4:05 pm
cbrammer1219 (2/11/2014)
Where GL_CODE has last 2 AA that is Corporate, I need to have for example AB for Atlanta branch but AA stays as Corporate, I also need to reserve the history of older records, but change them to the new if the charge was to corporate.BRANCH_IDINV_TYPE INVOICE_NUM CASH_APPLIED GL_CODE POST_DEBIT_GL_CODE
AACA 231760-713.40 045500-00-AA 050000-00-AA
AACA 231870-199.86 045500-00-AA 050000-00-AA
AACA 224546-399.90 045500-00-AA 050000-00-AA
AACA 239779-497.16 047500-00-AA 050000-00-AA
AACA 237353-89.40 045500-00-AA 050000-00-AA
AACA 237499-74.91 047500-00-AA 050000-00-AA
AACA 218522-413.88 047500-00-AA 050000-00-AA
AACA 227799-96.54 047500-00-AA 050000-00-AA
AACA 211681-94.65 045500-00-AA 050000-00-AA
RICA 218698-55.70 045500-00-AA 050000-00-RI
AACA 227935-128.40 047500-00-AA 050000-00-AA
AACA 220940-129.72 047500-00-AA 050000-00-AA
AACJ 29057-2125.00 045500-00-AA 050000-00-AA
AACA 220999-87.81 047500-00-AA 050000-00-AA
AACA 228122-91.58 047500-00-AA 050000-00-AA
AACA 214144-94.44 047500-00-AA 050000-00-AA
AACA 223237-94.50 045500-00-AA 050000-00-AA
AACA 235951-135.72 047500-00-AA 050000-00-AA
AACA 226303-375.09 047500-00-AA 050000-00-AA
AACA 219223-28.12 047500-00-AA 050000-00-AA
WC J 3553-650.00 045500-00-AA 050000-00-AA
WC A 18513-162.54 047500-00-AA 050000-00-AA
AACA 223652-914.13 047500-00-AA 050000-00-AA
AACA 230829-955.29 047500-00-AA 050000-00-AA
RICP !)6 -286.08 045500-00-AA 103400-00-AA PREPAYMENT
RICT !#( -375.87 045500-00-AA 050000-00-AA DEPOSIT
AACA 218666-94.65 045500-00-AA 050000-00-AA
AACS 65516-84.85 045500-00-AA 050000-00-AA
AACJ 29058-200.00 045500-00-AA 050000-00-AA
AACC 2786-15.00 045500-00-AA 050000-00-AA
AACA 219473-240.00 045500-00-AA 050000-00-AA
AACF 18528-2.54 951500-95-AA 050000-00-AA WRITEOFF
RICI 5284-30.00 047500-00-AA 050000-00-RI
RICA 233298-92.28 047500-00-AA 050000-00-RI
RICA 220972-84.36 047500-00-AA 050000-00-RI
RICA 223237-94.50 045500-00-AA 050000-00-RI
AACA 238049-96.00 045500-00-AA 050000-00-AA
AACA 230605-773.88 047500-00-AA 050000-00-AA
AACA 219223-28.12 047500-00-AA 050000-00-AA
WC A 19116-2.43 047500-00-AA 050000-00-WC
AACJ 21330-37.00 047500-00-AA 050000-00-AA
AACI 5008-200.00 047500-00-AA 050000-00-AA
AACF 17632-7.56 045500-00-AA 050000-00-AA
RICA 204832-80.85 045500-00-AA 050000-00-RI
RICI 4761-15.00 045500-00-AA 050000-00-RI
AACA 212049-80.85 045500-00-AA 050000-00-AA
WCA 217158-80.85 045500-00-AA 050000-00-WC
WCS 64761-230.00 045500-00-AA 050000-00-WC
WCF 18491-2.60 045500-00-AA 050000-00-WC
AACF 18491-1.38 951500-95-AA 050000-00-WCWRITEOFF
First, this sample data really doesn't help. How do we know what stays corporate (AA) and what goes to the Atlanta Branch (AB) when everything above ends in AA?
Also, it would help if you provided DDL (CREATE TABLE statement(s)) for the table(s) involved, provided the sample data as a series of INSERT INTO statements (some people still use SQL Server 2005), and what your expected results should be based on the sample data provided.
It may help if you take the time to read the first article I reference below in my signature block regarding asking for help.
February 11, 2014 at 4:39 pm
Lynn Pettis (2/11/2014)
First, this sample data really doesn't help. How do we know what stays corporate (AA) and what goes to the Atlanta Branch (AB) when everything above ends in AA?Also, it would help if you provided DDL (CREATE TABLE statement(s)) for the table(s) involved, provided the sample data as a series of INSERT INTO statements (some people still use SQL Server 2005), and what your expected results should be based on the sample data provided.
It may help if you take the time to read the first article I reference below in my signature block regarding asking for help.
Thanks for following up with this Lynn.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 11, 2014 at 5:07 pm
Thank you for making it clearer how to post the code, I will do just that.
Thank You
February 12, 2014 at 9:15 am
I hope this is better??? Thank you for assistance in advance...
CREATE TABLE [dbo].[DICE_GLDDTAAC](
[ACCT_CODE] [varchar](15) NULL,
[DETL_PERIOD] [varchar](2) NULL,
[DETL_POSTING_DATE] [datetime] NULL,
[DETL_TRAN_NUM] [decimal](5, 0) NULL,
[DETL_SOURCE_JRL] [varchar](4) NULL,
[DETL_POSTING_REF] [varchar](10) NULL,
[DETL_DESC] [varchar](40) NULL,
[DETL_LAST_OPER] [varchar](3) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[DICE_GLDDTAAC](
(ACCT_CODE, DETL_PERIOD, DETL_POSTING_DATE, DETL_TRAN_NUM, Monthvalue)
SELECT [045500-00-AA ],[01],'Jul 1 2013 12:00AM',[51356],[045500-00-AA ],[Accounts Payable Check Register Update ],[1],[SMD]
SELECT [045500-00-AA ],[01],'Jul 1 2013 12:00AM',[51358],[045500-00-AA ],[Accounts Payable Check Register Update ],[1],[SMD]
SELECT [045500-00-AA ],[01],'Jul 1 2013 12:00AM',[51379],[045500-00-AA ],[Accounts Receivable Cash Receipts Update],[1],[TAY]
SELECT [045500-00-AA ],[01],'Jul 1 2013 12:00AM',[51391],[045500-00-AA ],[Accounts Receivable Cash Receipts Update],[1],[TAY]
SELECT [045500-00-AA ],[01],'Jul 1 2013 12:00AM',[51393],[045500-00-AA ],[Accounts Receivable Cash Receipts Update],[1],[TAY]
SELECT [045500-00-AA ],[01],'Jul 1 2013 12:00AM',[51395],[045500-00-AA ],[Accounts Receivable Cash Receipts Update],[1],[TAY]
SELECT [045500-00-AA ],[01],'Jul 1 2013 12:00AM',[51400],[045500-00-AA ],[Accounts Receivable Cash Receipts Update],[1],[TAY]
SELECT [045500-00-AA ],[01],'Jul 1 2013 12:00AM',[51401],[045500-00-AA ],[Accounts Receivable Cash Receipts Update],[1],[TAY]
SELECT [045500-00-AA ],[01],'Jul 1 2013 12:00AM',[51402],[045500-00-AA ],[Accounts Receivable Cash Receipts Update],[1],[TAY]
SELECT [045500-00-AA ],[01],'Jul 1 2013 12:00AM',[51403],[045500-00-AA ],[Accounts Receivable Cash Receipts Update],[1],[TAY]
SELECT [045500-00-AA ],[01],'Jul 2 2013 12:00AM',[51392],[045500-00-AA ],[Accounts Receivable Cash Receipts Update],[1],[TAY]
SELECT [045500-00-AA ],[01],'Jul 2 2013 12:00AM',[51410],[045500-00-AA ],[Accounts Receivable Cash Receipts Update],[1],[TAY]
SELECT [045500-00-AA ],[01],'Jul 2 2013 12:00AM',[51436],[045500-00-AA ],[Accounts Receivable Cash Receipts Update],[1],[TAY]
SELECT [045500-00-AA ],[01],'Jul 2 2013 12:00AM',[51437],[045500-00-AA ],[Accounts Receivable Cash Receipts Update],[1],[TAY]
--===== Set the identity insert back to normal
SET IDENTITY_INSERT [dbo].[DICE_GLDDTAAC](ON
February 12, 2014 at 9:23 am
Now, given that data you just posted, what do you want to happen? What must the end result look like?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply