June 27, 2012 at 3:36 pm
Let first apologize since I have no idea how to start this small project. I have no code, because I'm lost. Therefore, I attached an image of a Visio I put together to help explain the requirement.
Logic: If a record in CHARGE has 3 corresponding CHARGE_LINES: Line 1 = BILLED, Line 2 = PROCESSING, and Line 3 = BILLED // Then ignore it and fetch the next CHARGE record. If the next CHARGE record has 4 corresponding CHARGE_LINES, and all four are = BILLED, capture that CHARGE.CHG_NUMBER and CHARGE.GUID and go to the next record. Continue this throughout the entire CHARGE table.
Additional Logic: Every line in CHARGE has a STATUS. The STATUS must equal Paid
Additional Logic: Every line in CHARGE_LINE has a STATUS. The STATUS must equal BILLED
I need two rows extracted from the DB just with the CHG.CHG_NUMBER and CHG.GUID. These go into a temp table to be called by another query.
June 27, 2012 at 9:14 pm
Please don't mind Joe - he must be late for his anger management class. He's certainly not representative of the rest of the fine posters on here, so try not to take his attacks to heart.
That said - your request does need some help in terms of clarity. I get the gist of what you're asking for, but the specific structure is important to understand in order to help. If you could provide some actual *sample* records so that we can understand your requirements (remember we can't see the data you're dealing with).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 28, 2012 at 9:49 am
Thank you Matt,
I really appreciate your patience and help. I have put together a spreadsheet with some sample data and highlighted examples of what I want to get... Please let me know if you need anything else.
June 28, 2012 at 10:58 am
from your excel i can recomend you get rid of the line_on_charge table and have a composite key of charge_guid (although i may look at going to an int or bigint identity) and line number. as it is currently set up you add a third GUID to your storage requirements as well as duplicating the create and mod dates and user for each charge_line. so the tables become as follows:
CREATE TABLE CHARGE(
Charge_ID BIGINT Identity(1,1) PRIMARY KEY CLUSTERED, --since you have a charge number just use that as the primary key
ChargeStatus CHAR(4)NOT NULL,
CHG_Date DATE NOT NULL DEFAULT GETDATE(), --since all your times are midnight we can save on space
Create_Date DATE NOT NULL DEFAULT GETDATE(), --can probably get rid of this one since its the same as the CHG_Date
MOD_Date DATE NOT NULL DEFAULT GETDATE(),
Created_User VARCHAR(16) NOT NULL,
MOD_User VARCHAR(16),
CONSTRAINT CK_Status CHECK (ChargeStatus IN ('OPEN','PAID'))
)
CREATE TABLE CHARGE_LINES(
Charge_ID INT NOT NULL,
Line_Number INT NOT NULL,
Status VARCHAR(12),
Create_Date DATE NOT NULL DEFAULT GETDATE(),
MOD_Date DATE NOT NULL DEFAULT GETDATE(),
Created_User VARCHAR(16) NOT NULL,
MOD_User VARCHAR(16),
CONSTRAINT PK_Charge_Lines PRIMARY KEY CLUSTERED (Charge_ID, Line_Number),
CONSTRAINT FK_Charge_ID FOREIGN KEY (Charge_ID) REFERENCES CHARGE(Charge_ID)
)
however i think you are just looking for the paid status in charge so the query does not even need to hit the charge_lines table
SELECT Charge_ID FROM CHARGE WHERE Status = 'PAID'
EDIT: Added constraints and corrected a couple of issues using SQL Reserved words.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
June 28, 2012 at 11:07 am
EDIT: misread the requirements at first - disregard this post.
******************************************************
That does paint a better picture on a logical basis, seems to expose another issue, which is that you're trying to rely on physical order to relate the charge to the charge lines.
As in - based on the example provided, there's no data element in Charge_lines that relates any of the lineitems to each other OR relates them to a corresponding CHARGE entry. The only thing you seem to be able to use based on the spreadsheet is that the first "set" of charge lines relate to the first CHARGE entry, i.e. physical order is the linkage.
That's a major problem, because tables don't have a physical order. Meaning - there's nothing that implicitly tracks insert order, nor is there any guarantee that assuming row X was inserted before row Y that they will be returned in that order. In order for that to work you would need use an ORDER BY clause (i.e. some set of columns that return the charge lines in the exact order you're showing).
Looking at your spreadsheet - how is it that you know that row 21 "belongs" with rows 19 and 20, rather than being late and should be associated to the 17-18 set? How are you even able to get this list, with the ordering? If I were to resort the lines in Charge_lines by , say, create_date, would you be able to come up with the correct associations?
I have a feeling there are extra columns missing from what you've been given. I'd start by asking whatever OTHER system is gving you this data to add in the relationship to these two tables: they just don't have to be part of the final output.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 28, 2012 at 11:20 am
Wait you're starting with all three tables, not BUILDING the third table. That's much better. Never mind my previous post.
I think hector is on the right track. That said if you can't rely on the CHARGE status to be up to date, you could use:
Select chg_number
from charge
where status='Paid' and
GUID in (select charge_guid from line_on_charge
join charge_lines
group by charge_guid
having count(charge_lines.guid)>0 and
count(charge_lines.guid)=
sum(case when
charge_line.status='Billed' then 1 end)
)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 28, 2012 at 1:24 pm
Thank you very much. The results seem to be what I', looking for. The only thing, the group by used the wrong GUID. Switched it to charge_lines_guid and it work. Thank you again for the help and patience.
Msg 164, Level 15, State 1, Line 16
Each GROUP BY expression must contain at least one column that is not an outer reference.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply