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 Monday, January 28, 2013 12:07 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
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
[color=#033]10 18187 5000[/color]
10 22188 2000
22 14189 3000
22 21190 3000
22 20191 3000
100 30282 4000
100 30283 4000
100 11284 4000

Post #1412594
Posted Monday, January 28, 2013 12:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:00 PM
Points: 13,224, Visits: 12,068
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 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 #1412610
Posted Monday, January 28, 2013 12:48 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
You know what, never mind. I'll go somewhere else for my answer. Thanks
Post #1412613
Posted Monday, January 28, 2013 12:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:00 PM
Points: 13,224, Visits: 12,068
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 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 #1412614
Posted Monday, January 28, 2013 12:57 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: Yesterday @ 10:52 PM
Points: 3,476, Visits: 7,514
Something like this?

CREATE TABLE CHG_ITEM(
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);
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




Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1412616
Posted Monday, January 28, 2013 12:58 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: Yesterday @ 10:52 PM
Points: 3,476, Visits: 7,514
SQL_Enthusiast (1/28/2013)
You know what, never mind. I'll go somewhere else for my answer. Thanks

Really bad attitude.



Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1412618
Posted Tuesday, January 29, 2013 1:46 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
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

Post #1413324
Posted Tuesday, January 29, 2013 1:58 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: Yesterday @ 10:52 PM
Points: 3,476, Visits: 7,514
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.



Luis C.
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?

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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:44 AM
Points: 6,235, Visits: 7,371
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.



- 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 #1413333
Posted Tuesday, January 29, 2013 2:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:00 PM
Points: 13,224, Visits: 12,068
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 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 #1413334
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse