Monday, August 5, 2013

[SQL Server] Get last transaction from multiple groups of records

[SQL Server] Get last transaction from multiple groups of records

Get last transaction from multiple groups of records

Posted: 05 Aug 2013 02:31 AM PDT

I need to pull the last transaction that occurred before a specific type of transaction, using the transaction dates. It should return 2 records, the Last_Dt for c_Dt 2010-09-06 and the Last_Dt for c_Dt 2010-12-22, but is returning all the records for both groups:c_id c_Dt Last_Dt c_rep123 2010-09-06 2010-09-06 25456 2010-09-06 2010-08-06 23789 2010-09-06 2010-07-06 25123 2011-12-06 2011-09-06 25456 2011-12-06 2011-08-06 23789 2011-12-06 2011-07-06 25This is the code:SELECT DISTINCT a.c_id, b.c_Dt, MAX(d.c_dt) AS LastDt, a.c_repFROM tbl_c_master AS a INNER JOIN tbl_s_det AS d ON a.c _id = d.c_id INNER JOIN(SELECT c_id, c_rep, MIN(DISTINCT c_dt) AS CpDt FROM vw_C_Pd GROUP BY c_id, s_code, c_repHAVING (s_code = '01') OR (s_code = '10') OR (s_code LIKE '14') OR (s_code = '24') OR (s_code = '20')) AS b a.c_id = b.c_idGROUP BY a.c_id, b.c_Dt, a.c_rep, d.c_dt HAVING (a.c_id = b.c_id) AND (MAX(d.c_dt) < b. CpDt)ORDER BY a.c_id, b.c_DtI thought MAX would do it, but it made no difference. I also tried with a CTE but it came up empty (no records returned). Any suggestions greatly appreciated!

Collation issues

Posted: 04 Aug 2013 11:24 PM PDT

Hello all,My first post here.I am a total newb and have been battling my way through setting up a slightly more robust membership system than is provided out-the-box using MS Visual Studio 2010.I have reached the point where everything that I need from it has been achieved.However, when I transfer the SQL Server Xpress DB from my local machine to a SQL Server 2008 DB at my web host (shared server) I receive collation errors.The process I am following is:1. The "Publish to Provider" option on the Xpress DB from within VS2010. An error free script is generated2. Using SQL Management Studio I then execute this script to the SQL Server 2008 DB on my shared server.The errors reported are:[i]Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 53Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 58Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 87Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 92Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 48Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 52Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 79Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 83Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 93Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 15151, Level 16, State 1, Line 1Cannot find the object 'aspnet_UsersInRoles_AddUsersToRoles', because it does not exist or you do not have permission.Msg 15151, Level 16, State 1, Line 1Cannot find the object 'aspnet_UsersInRoles_RemoveUsersFromRoles', because it does not exist or you do not have permission.[/i]I have no idea where to start starting this little lot out :Wow:Any help appreciated.Tx,Martin

Get last transaction from multiple groups of records

Posted: 05 Aug 2013 02:28 AM PDT

DUPLICATE POST - NEED TO DELETE

Stored Procedure syntax

Posted: 05 Aug 2013 12:38 AM PDT

Hello All,I'm using the procedure to return results to an Access 2010 .adp:I would like to return a result if the user enters the following:The search criteria SubjectNumber = 'UK01' would return all SubjectNumbers starting with 'UK001...'. and so on. However, no matter how I write the SQL, it will only return a result for a complete SubjectNumber ('UK010001').I have tried the following in an SQL Server query, which returns the result I expect. So what's wrong with the SQL in my stored procedure?Thanks for any help.[code="sql"]select *from tblPersonwhere SubjectNumber like '%'+ 'UK' +'%'[/code]Stored Procedure:[code="sql"]@SubjectNumber char(9)ASSELECT PersonID, SubjectNumber, NHSNo, PostcodeFROM tblPersonWHERE (@SubjectNumber IS NULL OR SubjectNumber LIKE '%' + @SubjectNumber + '%')[/code]

concatenate ' and % in like estatement

Posted: 04 Aug 2013 08:41 PM PDT

Hi, I have a stored procedure for search data.I want add % to it, so we are not have to search with exact values.I already tried something like N'' + % + @name + % + '' but it throw exception.This code works fine with exact values.[code="sql"]PROCEDURE search(@name nvarchar(20)=null)select * from TableNamewhere FirstName like N'' + @name + ''[/code]Thank you for help.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog