January 28, 2013 at 12:07 pm
Here is my very basic code below. The results are below that. What I need is a query that reads through all of the items and finds only those ID numbers and batches (Cgi.CHARGE_BATCH_ID & Cgi.CHARGE_ITEM_ID) where the Revenue Site is different from the other revenue sites of the grouped batch.
Based on the result set below, need a query to only find that line for charge item 18187 because the other items in the charge batch are all 2000... I would love to help with more code, but I am completely lost... Thank you in advance.
SELECT TOP ( 100 )
Cgi.CHARGE_BATCH_ID
, Cgi.CHARGE_ITEM_ID
, Cgi.REVENUE_SITE_ID
FROM
dbo.CHG_ITEM AS Cgi
WHERE
Cgi.CHARGE_BATCH_ID IS NOT NULL AND
Cgi.STATUS = 'POSTED'
ORDER BY
Cgi.CHARGE_BATCH_ID
, Cgi.CHARGE_ITEM_ID
, Cgi.REVENUE_SITE_ID
Result Set
CHARGE_BATCH_ID CHARGE_ITEM_ID REVENUE_SITE_ID
1 15929 1000
1 16930 1000
10 17186 2000
10 18187 5000
10 22188 2000
22 14189 3000
22 21190 3000
22 20191 3000
100 30282 4000
100 30283 4000
100 11284 4000
January 28, 2013 at 12:41 pm
How about some ddl and sample data? Read the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 28, 2013 at 12:48 pm
You know what, never mind. I'll go somewhere else for my answer. Thanks
January 28, 2013 at 12:50 pm
SQL_Enthusiast (1/28/2013)
You know what, never mind. I'll go somewhere else for my answer. Thanks
???
Anybody else is going to ask you for the same thing. There is no need for a loop to accomplish this but without enough details to understand the question, it is impossible to provide any code help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 28, 2013 at 12:57 pm
Something like this?
CREATE TABLE CHG_ITEM(
CHARGE_BATCH_ID int,
CHARGE_ITEM_IDint,
REVENUE_SITE_IDint)
INSERT INTO CHG_ITEM(CHARGE_BATCH_ID, CHARGE_ITEM_ID, REVENUE_SITE_ID)
VALUES
(1, 15929, 1000),
(1, 16930, 1000),
(10, 17186, 2000),
(10, 18187, 5000),
(10, 22188,2000),
(22, 14189, 3000),
(22, 21190,3000),
(22, 20191,3000),
(100, 30282, 4000),
(100, 30283, 4000),
(100, 11284, 4000);
WITH CTE AS(
SELECT
Cgi.CHARGE_BATCH_ID
, Cgi.CHARGE_ITEM_ID
, CASE WHEN COUNT( REVENUE_SITE_ID) OVER( PARTITION BY Cgi.CHARGE_BATCH_ID) <> COUNT( REVENUE_SITE_ID) OVER( PARTITION BY Cgi.CHARGE_BATCH_ID, Cgi.REVENUE_SITE_ID)
AND COUNT( REVENUE_SITE_ID) OVER( PARTITION BY Cgi.CHARGE_BATCH_ID, Cgi.REVENUE_SITE_ID) = 1
THEN Cgi.REVENUE_SITE_ID END AS REVENUE_SITE_ID
FROM
dbo.CHG_ITEM AS Cgi
--WHERE
-- Cgi.CHARGE_BATCH_ID IS NOT NULL AND
-- Cgi.STATUS = 'POSTED'
)
SELECT CHARGE_BATCH_ID
, CHARGE_ITEM_ID
, REVENUE_SITE_ID
FROM CTE
WHERE REVENUE_SITE_ID IS NOT NULL
ORDER BY
CHARGE_BATCH_ID
, CHARGE_ITEM_ID
, REVENUE_SITE_ID
January 28, 2013 at 12:58 pm
SQL_Enthusiast (1/28/2013)
You know what, never mind. I'll go somewhere else for my answer. Thanks
Really bad attitude.
January 29, 2013 at 1:46 pm
I admit, I was frustrated yesterday and I took it out on the forum. It just appears that with little effort on a post, I get the "best practices" post and with spending an hour putting every possible detail in place, I get the best practices post.
Anyway, my appologies.
I gave up on this query and moved on to other projects. Here I am, day two and I circled back and I'm still stuck. I tried some other things, including an Excel Pivot, but that ran all night on 1.2 million records, so that's not sustainable.
Let me try a little harder to explain the problem/goal here. I have millions of records in a CHG_ITEM table. Each record will have a REVENUE_SITE_ID and "could have" a CHARGE_BATCH_ID. If the STATUS = 'POSTED' is true, then the record "will have" a CHARGE_BATCH_ID. Starting at CLAIM, I need every claim number returned where ALL of the CHARGE_ITEM_ID records DO NOT HAVE the same REVENUE_SITE_ID.
For example
CLAIM 100 had 2 CHARGE_ITEM_ID records and CLAIM 101 had 2 CHARGE_ITEM_ID records
CLAIM 100 - CHARGE_ITEM_ID 5000 & 50550 have REVENUE_SITE_ID of 1000 and 1000 - SKIP this record
CLAIM 101 - CHARGE_ITEM_ID 25362 & 31025 have REVENUE_SITE_ID of 1000 and 1001 - Capture/Return this record to the result set.
Keep "looping" through the table until all CHG_ITEM rows have been reviewed.
Please tell me if you need anything else... I will be more than happy to research it and provide it. Thank you.
Here's code on the table joins
SELECT DISTINCT TOP ( 1000 )
c.CLAIM_NUMBER
--, cgi.CHARGE_ITEM_ID
--, Cgi.REVENUE_SITE_ID AS ChgItemRev
--, Ard.REVENUE_SITE_ID AS ClaimRev
FROM
dbo.CLAIM AS c
JOIN
dbo.AR_DETAIL AS Ard
ON c.SYS_ID = Ard.CLAIM_SYS_ID
JOIN
dbo.CHARGE_ON_CLAIM AS coc
ON coc.CLAIM_SYS_ID = c.SYS_ID
JOIN
dbo.CHG_ITEM AS Cgi
ON coc.CHG_ITEM_SYS_ID = Cgi.SYS_ID
WHERE
c.STATUS = 'A' AND
Cgi.STATUS = 'POSTED' AND
Cgi.REVENUE_SITE_ID <> Ard.REVENUE_SITE_ID --not correct logic per last minute change control ticket
-- new logic: Compare the charge item revenue sites to each other (not to the AR detail rev site)
ORDER BY
C.CLAIM_NUMBER
Here's other code I tried, but it also failed.
DECLARE @ci VARCHAR(15)
DECLARE @cb VARCHAR(15)
DECLARE @rev VARCHAR(10)
DECLARE @rowNum INT
DECLARE @maxrows INT
SELECT DISTINCT
@ci = CHARGE_ITEM_ID
, @cb = CHARGE_BATCH_ID
, @rev = REVENUE_SITE_ID
FROM
dbo.CHG_ITEM
WHERE
dbo.CHG_ITEM.CHARGE_BATCH_ID IS NOT NULL AND
dbo.CHG_ITEM.STATUS = 'POSTED'
SELECT
@maxrows = COUNT(*)
FROM
dbo.CHG_ITEM
WHERE
dbo.CHG_ITEM.CHARGE_BATCH_ID IS NOT NULL AND
dbo.CHG_ITEM.STATUS = 'POSTED'
WHILE @rowNum < @maxrows
BEGIN
SET @rowNum = @rowNum + 1
PRINT ( 'Distinct Rows:' + @ci )
SELECT DISTINCT
@ci = CHARGE_ITEM_ID
, @cb = CHARGE_BATCH_ID
, @rev = REVENUE_SITE_ID
FROM
dbo.CHG_ITEM
WHERE
dbo.CHG_ITEM.REVENUE_SITE_ID <> @rev
END
January 29, 2013 at 1:58 pm
Could you provide some sample data?
I'm sure you can use my query by tweaking it a little, but we don't have the complete information because now you're mentioning claims.
January 29, 2013 at 2:06 pm
SQL_Enthusiast (1/29/2013)
I admit, I was frustrated yesterday and I took it out on the forum. It just appears that with little effort on a post, I get the "best practices" post and with spending an hour putting every possible detail in place, I get the best practices post.Anyway, my appologies.
It's happened to the best of us. Don't sweat it too much. Just remember we can't see as clearly what's important and what's not, but if it isn't consumable, we can't use it for testing to see what you are getting.
Let me try a little harder to explain the problem/goal here. I have millions of records in a CHG_ITEM table. Each record will have a REVENUE_SITE_ID and "could have" a CHARGE_BATCH_ID. If the STATUS = 'POSTED' is true, then the record "will have" a CHARGE_BATCH_ID. Starting at CLAIM, I need every claim number returned where ALL of the CHARGE_ITEM_ID records DO NOT HAVE the same REVENUE_SITE_ID.
So, to clarify this a bit for me, in the example table built by Luis above which I'm going to use for all references, CHARGE_BATCH_ID could be NULL? Also, STATUS is in what table? It's not shown, neither is Claim.
For the final piece, in the sample set Luis built from (I assume) what you provided before there are no repeated Charge_Item_IDs, so what's the connector for a Charge_item_id to connect to another, or is the dataset incomplete?
For example
CLAIM 100 had 2 CHARGE_ITEM_ID records and CLAIM 101 had 2 CHARGE_ITEM_ID records
CLAIM 100 - CHARGE_ITEM_ID 5000 & 50550 have REVENUE_SITE_ID of 1000 and 1000 - SKIP this record
CLAIM 101 - CHARGE_ITEM_ID 25362 & 31025 have REVENUE_SITE_ID of 1000 and 1001 - Capture/Return this record to the result set.
Without a dataset describing what you're explaining here, I'm afraid I'm lost in figuring out what to code against.
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
January 29, 2013 at 2:13 pm
SQL_Enthusiast (1/29/2013)
I admit, I was frustrated yesterday and I took it out on the forum. It just appears that with little effort on a post, I get the "best practices" post and with spending an hour putting every possible detail in place, I get the best practices post.Anyway, my appologies.
Been there myself too. No worries.
With some sample data it seems there are least 3 people willing to jump in and help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2013 at 2:13 pm
Using the new data:
SQL_Enthusiast (1/29/2013)
For exampleCLAIM 100 had 2 CHARGE_ITEM_ID records and CLAIM 101 had 2 CHARGE_ITEM_ID records
CLAIM 100 - CHARGE_ITEM_ID 5000 & 50550 have REVENUE_SITE_ID of 1000 and 1000 - SKIP this record
CLAIM 101 - CHARGE_ITEM_ID 25362 & 31025 have REVENUE_SITE_ID of 1000 and 1001 - Capture/Return this record to the result set.
but still shooting in the dark.
WITH CTE AS(
SELECT claim,
charge_item_id,
revenue_site_id,
AVG( CAST( revenue_site_id AS decimal( 18, 8))) OVER( PARTITION BY claim) avg_revenue_site_id
FROM ( VALUES( 100, 5000, 1000),
( 100, 50550, 1000),
( 101, 25362, 1000),
( 101, 31025, 1001)) AS Claims(CLAIM,CHARGE_ITEM_ID,REVENUE_SITE_ID)
)
SELECT claim,
charge_item_id,
revenue_site_id
FROM CTE
WHERE revenue_site_id <> avg_revenue_site_id
January 29, 2013 at 2:44 pm
Sorry for the last minute addtion. Thank you for looking at this again. Here is the same data in Excel. Rows 7 & 8 I would expect to see returned while the rest are skipped.
January 29, 2013 at 2:51 pm
So, to clarify this a bit for me, in the example table built by Luis above which I'm going to use for all references, CHARGE_BATCH_ID could be NULL? Also, STATUS is in what table? It's not shown, neither is Claim.
The logic needs to be: CHARGE_BATCH_ID IS NOT NULL. In order to have a claim, there has to be a batch (CHG ITEM ID's are grouped into a Charge Batch, a Chg Batch is posted creating a billing batch, a billing batch is compiled to create a claim).
no repeated Charge_Item_IDs
Correct, Charge_Item_Id's are always unique.
so what's the connector for a Charge_item_id to connect to another
Each Charge_Item_Id will share the same CHARGE_BATCH_ID. Does that help?
January 29, 2013 at 2:54 pm
SQL_Enthusiast (1/29/2013)
Sorry for the last minute addtion. Thank you for looking at this again. Here is the same data in Excel. Rows 7 & 8 I would expect to see returned while the rest are skipped.
Count me out of this one. I have tried to have you see this from the eyes of somebody offering their time to help you once again post information that is not letting us run queries. We don't know your tables and we are not familiar with your business.
We are nearly 15 posts into this and still we can't actually run a query.
Post something in an actually consumable format and you will find people helping and NOT mentioning articles about how to post. You should read it sometime so you have an idea of what we mean.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2013 at 3:57 pm
SQL_Enthusiast (1/29/2013)
Does that help?
I... think so.
Tell me if this does basically what you're looking for, and where, if anywhere, it goes awry:
DECLARE @CHG_ITEM TABLE(
CHARGE_BATCH_ID int,
CHARGE_ITEM_IDint,
REVENUE_SITE_IDint)
INSERT INTO @CHG_ITEM(CHARGE_BATCH_ID, CHARGE_ITEM_ID, REVENUE_SITE_ID)
VALUES
(1, 15929, 1000),
(1, 16930, 1000),
(10, 17186, 2000),
(10, 18187, 5000),
(10, 22188,2000),
(22, 14189, 3000),
(22, 21190,3000),
(22, 20191,3000),
(100, 30282, 4000),
(100, 30283, 4000),
(100, 11284, 4000);
SELECT
CHARGE_BATCH_ID
FROM
(SELECT DISTINCT
CHARGE_BATCH_ID,
REVENUE_SITE_ID
FROM
@CHG_ITEM
WHERE
CHARGE_BATCH_ID IS NOT NULL
) AS drv
GROUP BY
CHARGE_BATCH_ID
HAVING
COUNT(*) > 1
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply