Solved

How to pick tje last record for particular group with particular condition.

Posted on 2010-09-11
2
331 Views
Last Modified: 2012-05-10
Table name - Txnenrollment
Fields - enrollmentdate,villagecode,familyid,maincardserialno,enrolled and personalized
Table name - Villagemaster
Created with this manner
select * from txnenrollment where enrolled=1
order by enrollmentdate,villagecode,maincardserialno desc
If enrolled=1 and personalized=1 then maincardserialno is not blank
if enrolled=1 and personalized=0 then maincardserialno is blank
Suppose for enrollmentdate - d1, villagdecode - v2, there are 4 records
out of which record 1 and 2 have conditions as enrolled=1 and personalized=1 that means maincardserialno is filled.
And 3 and 4 record satisfy the conditon of enrolled=1 and personalized=0.
I want to fill that record the maincardserialno with the maincardserialno of record2 +17 by checking that new maincardserialno generated record 3 is unique in whole table.
if  not then add 1 more 17 to maincardserialno and update record3.

This way the question is how to pick the last record.
0
Comment
Question by:searchsanjaysharma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 33656640
Is this a variation on your previous question:
http://www.experts-exchange.com/Q_26465942.html

Or did you truly not find an answer?

Reading this and your other question regarding making the serial no unique, I am suspecting that you should use the first method presented by matthewspatrick which is to have a derived table with the MAX; however, it would appear that you need this on the serial no field and not the date field.  Furthermore, you would not include this max in the JOIN but instead simply use its value in selection if the serial no of current row is NULL or Blank.  

An alternative, would be to use sub query only on the rows that had NULL or Blank via a similar case statement.  

It will probably be good to know if the actual records are NULL or '' (empty or blank string).

Your case statement could look something like:

case when maincardserialno is null
   then -- our calculation here
   else maincardserialno -- leave serial no in place
end

Here is a technique for adding 17 to the serial number without having to do on last 2 characters only and having to worry about going above 100.

declare @ttmaincardserialno varchar(100);
set @ttmaincardserialno='006000005023015007000000002000134184';

select replicate('0', patindex('%[^0]%', @ttmaincardserialno)-1)+convert(char(36), convert(numeric(36,0), @ttmaincardserialno)+17)

So putting it all together:


;with t
as
(
   select * 
   from txnenrollment 
   where enrolled=1
)
select t.enrollmentdate, t.villagecode
     , case when t.maincardserialno is null
          then replicate('0', patindex('%[^0]%', tmax.maincardserialno)-1)+convert(char(36), convert(numeric(36,0), tmax.maincardserialno)+17)
          else t.maincardserialno
       end as maincardserialno
from t
join (
   select villagecode
        , max(maincardserialno) as maincardserialno
   from t
   group by villagecode
) tmax on tmax.villagecode = t.villagecode
order by enrollmentdate, villagecode, maincardserialno desc
;

Open in new window

0
 

Author Closing Comment

by:searchsanjaysharma
ID: 33944020
ok
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question