[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

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#
0
thayduck
Asked:
thayduck
  • 2
1 Solution
 
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
 
thayduckAuthor 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
 
thayduckAuthor 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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now