August 6, 2008 at 8:36 am
Can someone please provide the code or even the logic to this proc that i will use in my SSIS. An outline of the code explaning the logic will be much apperciated. Many many thanks in advance
SO here is the scenerio-- I will have two tables Header and Details tables. One header will have multiple details line . They both will have a serviceID to indentify the records.
Now, the Details will have multiple status indicator like N, H, A, S, T, . Now I have to build a Sproc to Logically find all the S details for each Header and output it to write to the flat files and change the status to T.
So here is the steps--
Find all service line from details table that belongs to one Service ID,
MAke sure that each of the service line belonging to one Service ID has Status S
Comibe this with Header tbl based on Service ID and output the results to flat file.
Change the status to T of all service line that were transmitted.
Now let's say, If one or any service line has status other than S , i cannot write to flat file. SO, If a ServiceID of 100 has five details Line in dEtails table and 4 has status of S and one A , i cannot write this information to flat file. SO i have to make sure that i do all kinds of checking before i start to combine details and headers tbl.
August 6, 2008 at 9:08 am
Is there any reason to not write a query (proc/view) in SQL, then use SSIS to export that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2008 at 9:51 am
Eventually, WE will do this through query, but the query will be in SSIS packages, becoz we have to check daily whether any record with ALL 'S' status is ready to write into flat files which will be sent to our customers.
If u could, Do u have teh outline for the logic to select those records described earlier
August 6, 2008 at 10:00 am
Can you put together a sample set of tables (create statements), sample data (as insert statements so we can copy, paste, and execute to load the tables), and the expected output based on the sample data (although I have an idea). Be sure the sample data covers all possibilities. As you are looking for the logic, i don't see a need for actual data/tables, but something that represents what you are trying to accomplish and that you can build on.
😎
August 6, 2008 at 10:16 am
Of course, here is some rough code I threw together that may help you figure out what you need to do.
create table #Header (
HeaderID int,
CustID int
);
create table #Detail (
HeaderID int,
DetailID int,
StatusCode char(1)
);
insert into #Header (HeaderID, CustID)
select 1, 1 union
select 2, 1 union
select 3, 1 union
select 4, 2 union
select 5, 2 union
select 6, 3 union
select 7, 3;
insert into #Detail (HeaderID, DetailID, StatusCode)
select 1,1,'S' union
select 1,2,'S' union
select 1,3,'A' union
select 2,1,'S' union
select 2,2,'S' union
select 2,3,'S' union
select 3,1,'S' union
select 4,1,'S' union
select 4,2,'A' union
select 4,3,'S' union
select 5,1,'S' union
select 5,2,'S' union
select 5,3,'S' union
select 6,1,'A' union
select 6,2,'A' union
select 6,3,'A' union
select 7,1,'S' union
select 7,2,'S';
select * from #Header;
select * from #Detail;
select
*
from
#Header h
inner join #Detail d
on (h.HeaderID = d.HeaderID)
where
not exists(select 1 from #Detail d1 where d1.HeaderID = d.HeaderID and d1.StatusCode <> 'S');
drop table #Header;
drop table #Detail;
😎
August 6, 2008 at 10:30 am
The problem is i don't have the data yet. i will try to put here together everything and explain to best of my ability.
Create tbl Header( This table will have all the
CLAIMID int not null, information about header
name varchar (60) null,
.....................
......................
...................
)
Create table Detail( this table will have all the
CLAIMID int not null, information about detail part
Name varchar (50) null,
linenumber char (05) null,
description varchar(20) null,
..............
............
status char(1) not null
) the status can be N, H, A, S, T,
SO now one header can have multiple service line with different status. SO lets say we have CLAIMID of 100 in Header and 5 line items for that ClaimID in the dtls table.
Four of the line item status are S and one H. Another CLaimID is 200 and all 3 line items in the dtl table have S.
SO select header info, combine this 3 detail info .
SOme points--
1. One Header can have Multiple Line Items
2. Mutiple line Items can hvae diff status belonging to Same Claim
3. Group All the line items that belong to one Claim.
4. Make Sure each line item belonging to one CLaim has Status of "S"
5. Combine this all line items to Header info
6. Ouput to flat file.
7. Logic must be applied to make sure that we don't do any extra processing of data since in detail table we might have many detail line items with different status( i.e. we don't want any line item that has status any other than "S" )
August 6, 2008 at 10:52 am
Your logic seems absolutely right.
Few other issues -----
HeaderID CustID HeaderID DetailID StatusCode
----------- ----------- ----------- ----------- ----------
2 1 2 1 S
2 1 2 2 S
2 1 2 3 S
3 1 3 1 S
7 3 7 1 S
7 3 7 2 S
The first row should only have header information.
and then each row followed by service line item for that row like----
HeaderID CustID HeaderID DetailID StatusCode
----------- ----------- ----------- ----------- ----------
2 1
2 2 S
2 3 S
2 1 S
3 1
3 1 1 S
7 3
7 3 1 S
7 3 2 S
ALSo MAKE SURE WE ONLY CHECK SERVICE LINE THAT HAS STATUS S, WE DON"T WANT to check status of CLaim ID that has any one service line other than S
WHat i mean is if CLAIMID 200 has 5 service line and one service line has status N, why check status of other 4 service line since one already has N.
Ay, by the way many many thanks to u for all the help
Thanks
August 13, 2008 at 10:10 am
Hey Lynn!!
May be u could help me out here too. I need to add 1 more logic here ...
Below is the code. I need to make sure that this column [EDI_CLM_SEQ_NBR] matches the total count for dtl table for each CLM_KEY. i.e. If [EDI_CLM_SEQ_NBR] has value 3 then there must be 3 details for that HDR. SO when i SELECT
SO one HDR with Clm_key 1111 and EDI_CLM_SEQ_NBR = 3 has three deatils, and I have to make sure that this 3 matches the count of CLM_KEY in the DTL tables. ( I Know u are thinking..., But in my case it is possible that user might go and alter the CLM_KEY in the detail
tbl)
SELECT
h.[EDI_CLM_ID]
,h.[EDI_CTL_TS]
,h.[EDI_CLM_SEQ_NBR],h.[VEND_NM]
,h.[REC_TYP_CD]
,h.[BILG_NPI_ID],h.[BILG_TAX_ID]
,h.[BILG_PROV_ZIP_CD]
,h.[VEND_CLM_ID]
,h.[EDI_CLM_ID] as [REF_CLM_NO]
,h.[PAT_FST_NM]
,h.[PAT_MID_NM]
,h.[PAT_LST_NM]
,h.[PAT_HCSC_ID]
,h.[PAT_MCAID_ID]
,h.[PAT_GRP_ID]
,h.[PAT_DOB]
,h.[BILL_AMT]
,h.[ADJ_IND]
,h.[RNDRG_NPI_ID]
,h.[RNDRG_TAXNMY_CD]
,h.[ENCTR_TYP_CD]
,h.[PD_AMT]
,d.[CLM_LN_NBR]
,d.[PROC_CD]
,d.[PROC_MDFYR_1_CD]
,d.[PROC_MDFYR_2_CD]
,d.[PROC_MDFYR_3_CD]
,d.[PROC_MDFYR_4_CD]
,d.[SVC_FR_DT]
,d.[SVC_TO_DT]
,h.[CLM_KEY]
FROM dbo.CLM_HDR AS h
INNER JOIN
CLM_LN AS d
ON (h.CLM_KEY = d.CLM_KEY)
WHERE NOT EXISTS (SELECT 1 AS Expr1 FROM CLM_LN d1
WHERE d1.CLM_KEY = d.CLM_KEY AND
d1.[CLM_LN_STA_CD] <> 'S')
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply