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
SQL_Enthusiast
SQL_Enthusiast
SSC Eights!
SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)

Group: General Forum Members
Points: 922 Visits: 422
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


Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

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

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)
SQL_Enthusiast
SQL_Enthusiast
SSC Eights!
SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)

Group: General Forum Members
Points: 922 Visits: 422
You know what, never mind. I'll go somewhere else for my answer. Thanks
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

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

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)
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42898 Visits: 19847
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.
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
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42898 Visits: 19847
SQL_Enthusiast (1/28/2013)
You know what, never mind. I'll go somewhere else for my answer. Thanks

Really bad attitude.


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 Eights!
SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)

Group: General Forum Members
Points: 922 Visits: 422
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


Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42898 Visits: 19847
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.
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
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21271 Visits: 7660
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

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

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)
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