complex query requirement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

complex query requirement

Author

Topic

jwaz1999
Starting Member

12 Posts

Posted - 2010-02-03 : 13:35:14

Hello,

I have a complex query requirement.

I need to accomplish the following.

I have a table with billing ID's. I also have a log table which stores multiple billing ID's (as a referenece per record. They are stored comma delimited in one record (3224, 5433, 1232) when they are updated together as a single transaction) I need to be able to query this table to see if a billing ID has been updated as part of a query, and return a boolean value if the record has been updated.

What is the best approach to solve this issue.

here is what I've done so far.

SELECT BILLING_ID, ACCOUNT, dbo.FN_CHECK_BILLING_LOG(BILLING_ID, ACCOUNT) IS_UPDATED
FROM BILLING
WHERE ACCOUNT = @ACCOUNT

I then created the user defined function to check for a match but I'm not sure how to combine the rows, split them into a temp table, then check for a match.

An example of the log table data would contain the following billing record ID references as multiple records in the following format:

UPDATE_NOTES, BILLING_IDS

'NOTES ARE HERE', '5682, 5680, 5679'
'NOTES ARE HERE', '5691, 5681'

How can I combine these into one record so I can split them into a temp database, then query the temp database to look for a billing_id match?

Thanks in advance.

Jeff

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 13:39:28

so you just need to combine all id values in the log table to single list and then look for match in them?

jwaz1999
Starting Member

12 Posts

Posted - 2010-02-03 : 13:41:26

quote:
Originally posted by visakh16

so you just need to combine all id values in the log table to single list and then look for match in them?

Yes. I need to do it in an inline query though, but that is what I need to do. Would a pivot table work here?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 13:48:15

what about this?


DECLARE @BillingList varchar(8000)

SELECT @BillingList = coalesce(@BillingList+',' ,'') 
FROM LOG_TABLE 

SELECT BILLING_ID, ACCOUNT, 
CASE WHEN ',' + @BillingList LIKE '%,' + CAST(BILLING_ID AS varchar(10)) + ',%' THEN 1 ELSE 0 END AS IS_UPDATED
FROM BILLING
WHERE ACCOUNT = @ACCOUNT

jwaz1999
Starting Member

12 Posts

Posted - 2010-02-03 : 13:53:45

quote:

Originally posted by visakh16

what about this?


DECLARE @BillingList varchar(8000)

SELECT @BillingList = coalesce(@BillingList+',' ,'') 
FROM LOG_TABLE 

SELECT BILLING_ID, ACCOUNT, 
CASE WHEN ',' + @BillingList LIKE '%,' + CAST(BILLING_ID AS varchar(10)) + ',%' THEN 1 ELSE 0 END AS IS_UPDATED
FROM BILLING
WHERE ACCOUNT = @ACCOUNT

I think I would be concerned using a variable to store the billing list since this table holds millions if not billions of records. My is also more complex than that. The column storing the billing_id's in the log table also has other data, I'm parsing it using the following code:
CHARINDEX(CONVERT(VARCHAR(50), @BILLING_ID),
SUBSTRING(DESCRIPTION, CHARINDEX(CONVERT(VARCHAR(50), 'Record(s) '), DESCRIPTION) + 10,
LEN(DESCRIPTION) - CHARINDEX(CONVERT(VARCHAR(50), 'Record(s) '), DESCRIPTION) + 1)

This pulls out just the billing ids, then I need to combine them into one field.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 14:02:58

[code]
CREATE TABLE #BILLING_LIST
(ID int identity(1,1),
BillingIDList varchar(max))

INSERT #BILLING_LIST(BillingIDList)
SELECT BILLING_IDS
FROM LOG_TABLE

SELECT b.BILLING_ID, b.ACCOUNT,
CASE WHEN COALESCE(b.CntVal,0) > 0 THEN 1 ELSE 0 END AS IS_UPDATED
FROM BILLING b
OUTER APPLY (SELECT COUNT(ID) AS CntVal
FROM #BILLING_LIST
WHERE ',' + BillingIDList + ',' LIKE '%,' + CAST(BILLING_ID AS varchar(10)) + ',%'
)b1
WHERE b.ACCOUNT = @ACCOUNT
[/code]

jwaz1999
Starting Member

12 Posts

Posted - 2010-02-03 : 14:05:21

quote:

Originally posted by visakh16


CREATE TABLE #BILLING_LIST
(ID int identity(1,1),
BillingIDList varchar(max))

INSERT #BILLING_LIST(BillingIDList)
SELECT BILLING_IDS
FROM LOG_TABLE 

SELECT b.BILLING_ID, b.ACCOUNT, 
CASE WHEN COALESCE(b.CntVal,0) > 0  THEN 1 ELSE 0 END AS IS_UPDATED
FROM BILLING b
OUTER APPLY (SELECT COUNT(ID) AS CntVal
             FROM #BILLING_LIST
             WHERE ',' + BillingIDList + ',' LIKE '%,' + CAST(BILLING_ID AS varchar(10)) + ',%'
             )b1
WHERE b.ACCOUNT = @ACCOUNT

Let me try what you just created to see if it works.

jwaz1999
Starting Member

12 Posts

Posted - 2010-02-03 : 14:18:10

It only matches the first value. As test, I used the following data in my temp table:
'5682, 5680, 5679'

It matched the 5682, but didn't match the 5680, or 5679 records. They both returned as 0 for IS_UPDATED column/

jwaz1999
Starting Member

12 Posts

Posted - 2010-02-03 : 14:25:27

There's another problem with the query. the query also matches billing id 682 even though the match should be 5682 only. I don't think you can use like statements here. The data needs to be split into individual records first, so that the like operator doesn't match only part of the billing id

jwaz1999
Starting Member

12 Posts

Posted - 2010-02-03 : 15:13:37

What I really need help on is how to pivot two records into a single column:

For example:
'NOTES ARE HERE', '5682, 5680, 5679'
'NOTES ARE HERE', '5691, 5681'

I need the id columns to be pivoted to one column as:
'5682, 5680, 5679, 5691, 5681'

Any pivot table experts out there?

Here is my query that isn't working:

SELECT * FROM
(
SELECT SUBSTRING(DESCRIPTION, CHARINDEX(CONVERT(VARCHAR(50), 'Record(s) '), DESCRIPTION) + 10,
LEN(DESCRIPTION) - CHARINDEX(CONVERT(VARCHAR(50), 'Record(s) '), DESCRIPTION) + 1) AS TEMP
FROM CS_LOG
WHERE
LOG_TYPE_ID = 4
) PS
PIVOT
(
MAX(TEMP) FOR TEMP IN ([newColumn])
)AS PVT

Thanks,
Jeff

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 03:40:46

quote:
Originally posted by jwaz1999

It only matches the first value. As test, I used the following data in my temp table:
'5682, 5680, 5679'

It matched the 5682, but didn't match the 5680, or 5679 records. They both returned as 0 for IS_UPDATED column/

it should match so long as your bill ids field has single values and list has comma separated ones.

eg: for condition 5680 the below comparison becomes

',' + BillingIDList + ',' LIKE '%,' + CAST(BILLING_ID AS varchar(10)) + ',%'

',' + '5682, 5680, 5679' + ',' LIKE '%,' + '5680' + ',%'

which will become

',5682, 5680, 5679,' LIKE '%,5680,%'
which will hold good. so not sure why its not working for you

also above will never match 682 as you've preceding and suceeding commas on each value

jwaz1999
Starting Member

12 Posts

Posted - 2010-02-04 : 12:35:25

I was able to solve this with a complex xml path query and using derived tables rather than temp tables.

Thanks for the suggestions however.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources