Can I Use Sql 2008 View To Rearrange Table

Cust           Date           Comment  
123456A   20140701     To store
123456B   20140705     Late payer
123456F    20140624     On time
234567Z    20140520     OK
345678P    20140301     Bad customer
456789H   20140701     Good Customer
456789A   20140523     Slow Payer

 I need for this above table  to have a view so only below records appear:

Cust           Date           Comment  
123456B   20140705     Late payer
234567Z    20140520     OK
345678P    20140301     Bad customer
456789H   20140701     Good Customer


I need to get the most current date record by the first 6 digits of the customer#
thayduckProgrammer AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pooja Katiyar VermaCommented:
create view TEST as
select * from mytable b where columndate =
select  max(columndate) from mytable a where a.customerid=b.customrid
group by substring(customerid, 1,6) )
0
Scott PletcherSenior DBACommented:
--CREATE VIEW view_name AS
SELECT *
FROM (
    SELECT
        tn.Cust, tn.Date, tn.Comment,
    ROW_NUMBER() OVER(PARTITION BY tn.Cust ORDER BY tn.Date DESC) AS row_num
    FROM dbo.tablename tn
) AS derived
WHERE
    row_num = 1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thayduckProgrammer AnalystAuthor Commented:
Pooja Katiyar Verma:

WHERE     (gpdate =
                          (SELECT     MAX(gpdate) AS Expr1
                            FROM          dbo.tbl_rmcore_gpactv AS a
                            WHERE      (SUBSTRING(gpcustno, 1, 6) = SUBSTRING(b.gpcustno, 1, 6))
                            GROUP BY SUBSTRING(gpcustno, 1, 6)))

Your code left duplicate custno in view. Did not want that. After making above change, I still got some duplicate custno when the gpdate's were exactly the same. Also, not sure if it matters, your view took 3-5 minutes to create.


ScottPletcher:

--CREATE VIEW view_name AS
SELECT *
FROM (
    SELECT
        tn.Cust, tn.Date, tn.Comment,
    ROW_NUMBER() OVER(PARTITION BY substring(tn.Cust,1,6) ORDER BY tn.Date DESC) AS row_num
    FROM dbo.tablename tn
) AS derived
WHERE
    row_num = 1


Your code also left duplicate custno in view. After making above change all duplicates were gone and view created in a few seconds.


It was probably in the way I explained it that caused the duplicates.

Anyway, I will award points to Scott since the final results were correct for what I needed after minor change.
Pooja, even after minor change, your view still had some duplicate custno.
Also, not sure why, but Scotts view created so much faster.
0
thayduckProgrammer AnalystAuthor Commented:
After minor change ( ROW_NUMBER() OVER(PARTITION By substring(tn.Cust,1,6) ORDER BY tn.Date DESC) AS row_num )
your code did the trick.

Thanks to both of you for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.