SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL Help with a loop (I believe)


T-SQL Help with a loop (I believe)

Author
Message
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16200 Visits: 19059
Using the new data:
SQL_Enthusiast (1/29/2013)

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.

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




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
SQL_Enthusiast
SQL_Enthusiast
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 422
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.
Attachments
SqlSvrCntlExampleData.xlsx (14 views, 9.00 KB)
SQL_Enthusiast
SQL_Enthusiast
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 422
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?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25797 Visits: 17509
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8539 Visits: 7660
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_ID int,
REVENUE_SITE_ID int)

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




- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
SQL_Enthusiast
SQL_Enthusiast
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 422
Just a few minor tweaks in the WITH CTE code and you hit the nail right on the head. I thank you very much for your effort and time as Luis Cazares has saved the day!

THANK YOU
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search