Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

T-SQL Help with a loop (I believe) Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2013 2:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 3,360, Visits: 7,273
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1413336
Posted Tuesday, January 29, 2013 2:44 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 25, 2014 7:47 PM
Points: 388, Visits: 421
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.

  Post Attachments 
SqlSvrCntlExampleData.xlsx (4 views, 9.43 KB)
Post #1413346
Posted Tuesday, January 29, 2013 2:51 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 25, 2014 7:47 PM
Points: 388, Visits: 421
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?
Post #1413348
Posted Tuesday, January 29, 2013 2:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:56 AM
Points: 13,111, Visits: 11,947
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 Moden's 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)
Post #1413349
Posted Tuesday, January 29, 2013 3:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 6,158, Visits: 7,223
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
Post #1413376
Posted Thursday, January 31, 2013 12:44 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 25, 2014 7:47 PM
Points: 388, Visits: 421
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
Post #1414295
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse