Finding a Container for the Ticket

Question

Post reply

Finding a Container for the Ticket

nagarajanmm-699934

SSC-Addicted

Points: 421
More actions
May 21, 2008 at 8:58 am

#187992

Hi,
I have a requirement for the Production Support system:
1) Container Table: Tickets will be mapped to a Container. Container will have information about the Resources, Logics, Shifts to work on the ticket and close that.
Container IDNameAttr_IDAttribute_Value
Show-Stopper1Priority1High
Show-Stopper1Severity2High
Show-Stopper1Summary3Show Stopper
Medium Issue2Priority1Medium
Medium Issue2Severity2Medium
Low Tkt Issue3Priority1Low
Low Tkt Issue3Severity2Low
2) Ticket Table
Ticket_IDPrioritySeveritySummary
1HighHighShow Stopper
2HighHighPage Crash
3LowLowJavascript error
4MediumLowIcons are not loading
Note: The Attributes in the Container Table and Ticket tables are more than shown in example.
When a Ticket is raised, I need to find which Container will be suitable for this ticket and place the ticket in that container.
I did not find a logic to do this. Can someone help me in arriving at a logic for this?
Thanks,
Nags

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

It looks to me like you could join on the various criteria of the ticket and the container.

select Container.ID

from dbo.Ticket

inner join dbo.Container

on ticket.severity = container.severity

and ticket.priority = container.priority

Something like that looks like it should work, if you build the tables correctly.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

nagarajanmm-699934 SSC-Addicted Points: 421 More actions · Answer 2

Hi,

Thanks for the reply.

My problem here is the Attributes in the Container are dynamic. Also the Ticket information would be spread across multiple tables. In that case, I need to run through the ticket under all Containers for a match.

I can give you an example for this: Consider you are setting 10 Rules for incoming mails in Outlook, the USer who is creating the Rules have freedom to Build Rules on any message criteria. The incoming mail sits in the respective folder for which the Rules has got matched.

I have a similar requirement where the Ticket has to sit in a relevant Container.

I can think of looping the Ticket values in each of the Container with a cursor and get the matchings. Do you have some pattern/ new function in SQL for this kind of functionality?

Thanks,

Nags

GSquared SSC Guru Points: 260824 More actions · Answer 3

nagarajanmm (5/22/2008)
Hi,
Thanks for the reply.
My problem here is the Attributes in the Container are dynamic. Also the Ticket information would be spread across multiple tables. In that case, I need to run through the ticket under all Containers for a match.
I can give you an example for this: Consider you are setting 10 Rules for incoming mails in Outlook, the USer who is creating the Rules have freedom to Build Rules on any message criteria. The incoming mail sits in the respective folder for which the Rules has got matched.
I have a similar requirement where the Ticket has to sit in a relevant Container.
I can think of looping the Ticket values in each of the Container with a cursor and get the matchings. Do you have some pattern/ new function in SQL for this kind of functionality?
Thanks,
Nags

Yes. Set up the rules as data in columns and rows, and join to them. Don't use a cursor, just join on the criteria you want.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Nags,

As GS has mentioned, you do not need a cursor or loop to do this. In fact, what you are trying to do does not sound overly difficult. If you want more help on this, post your table DDL, some sample data, and your expected results.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

nagarajanmm-699934 SSC-Addicted Points: 421 More actions · Answer 5

Thanks for your suggestions. I guess simple join would work out for this scenario. I would reach out to this forum in case if I face problem on mapping for complex container filter criteria.

Regards,

Nags.