Wednesday, August 21, 2013

[SQL Server] Custom Ranking?

[SQL Server] Custom Ranking?

Custom Ranking?

Posted: 21 Aug 2013 02:27 PM PDT

Hello everyone.This is my first post. I think this a wonderful resource and I hope to answer other people's questions myself one day.I have a simple question, i guess. I want to rank a set of records but I need to use a custom rank, rather than by alphabetical or numeric sortI have a table, for example:ID Violation----------------ID1 WarningID2 1st CitationID2 2nd CitationID2 3rd CitationID3 WarningID4 WarningID5 1st CitationID6 2nd CitationI want my result set to be a count of ID's grouped by severity, but only by max severity.In other words, the results for this should be:Warning 31st Citation 12nd Citation 13rd Citation 1Maybe I'm sleepy but every solution I've come up with keeps counting those citations for ID2.Does anyone have any insight on this?Thanks in advance

Combine related row from 2 tables into single row

Posted: 21 Aug 2013 08:34 AM PDT

Hi,I have two tables Projects and FundingSourcesHow can i create a summary with each project per row with funding sources added?[quote]Projects:[b]ProjectID, Name, Status, StartDt, EndDt[/b]R101, "Rail project", "NotStarted", "08/21/13", "08/21/14"A201, "Aviation project", "Started", "06/01/13", "06/30/14"B301, "BikeTransit project","Started"03/01/13", "03/30/14""FundingSources:[b]ID, ProjectID, Agency, Amount, Approved[/b]1, R101, XYZ, $30,000, Y2, R101, ABC, $50,000, N3, A201, LML, $100,000, NResult:[b]ProjectID, Name , Status ,Agency1 ,Amount1 ,Agency2 , Amount2,[/b] R101 ,"Rail project" , NotStarted ,XYZ ,$30,000 ,ABC ,$50,000 A201 ,"Aviation project" , Started ,LML ,$100,000 , , B301 ,"BikeTransit " ,Started , , , ,[/quote]Appreciate any input.

Marking duplicate rows

Posted: 20 Aug 2013 08:02 PM PDT

I have this query for marking duplicate rows. It works on a small database but takes more than 2 days to run if the db is large. Is there a way of making this execute faster. Thank youUPDATE DSObject_tableSET Object_isDeleted = 1where handle_id IN(select handle_id from DSObject_table as dsowhere exists (select Civil_Case__original_file_name, Civil_Case_Files_size,Count(handle_id) from DSObject_table where DSObject_table.Civil_Case__original_file_name = dso.Civil_Case__original_file_name and DSObject_table.Civil_Case_Files_size = dso.Civil_Case_Files_size group by DSObject_table.Civil_Case__original_file_name, DSObject_table.Civil_Case_Files_size having count(DSObject_table.handle_id) > 1)) and DSObject_table.handle_id NOT IN ( SELECT Min(handle_id) FROM DSObject_table AS dso where exists (select Civil_Case__original_file_name, Civil_Case_Files_size,Count(handle_id) from DSObject_table where DSObject_table.Civil_Case__original_file_name = dso.Civil_Case__original_file_name and DSObject_table.Civil_Case_Files_size = dso.Civil_Case_Files_size group by DSObject_table.Civil_Case__original_file_name, DSObject_table.Civil_Case_Files_size having count(DSObject_table.handle_id) > 1) group by Civil_Case__original_file_name, Civil_Case_Files_size);

Add a record to a replicated table.....

Posted: 20 Aug 2013 09:59 PM PDT

HiI am quite new to replication but i did my research and have setup transactional replication between two servers and it is has been working well....until now.I am trying to add a record to a particular table that is marked for replication on the publisher side and i receive the following error.[b]Error message : 'Could not find stored procedure dbo.sp_MSgetreplnick' [/b]Any ideas on this? Im assuming this is a system SP of some kind and it has something to do with replicationAny advice would be much appreciated.Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog