SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sql 2008 r/2 grouping by max date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mjimenezh
Yak Posting Veteran

Mexico
61 Posts

Posted - 07/24/2013 :  11:58:22  Show Profile  Reply with Quote
Hi, i'm learning the sql programming and i love it, I have a sql view (#1) that shows me something like this :

ARTCODE |QUANTITY | LASTMOVE |
articulo1 | 5 | 2013-01-01|
articulo1 | 2 | 2013-05-07|
articulo2 | 1 | 2013-07-19|
articulo3 | 6 | 2013-06-16|

and I need just grouping by artcode and showing only the max(lastmove) the most recent date by item (artcode)like this :

ARTCODE |QUANTITY | LASTMOVE |
articulo1 | 2 | 2013-05-07|
articulo2 | 1 | 2013-07-19|
articulo3 | 6 | 2013-06-16|

This is my code :

SELECT TOP (100) PERCENT dbo.gbkmut.artcode, MAX(dbo.gbkmut.datum) AS LastMove, GETDATE() AS DateNow, DATEDIFF(DAY, MAX(dbo.gbkmut.datum), GETDATE())
AS DayLastMoveUntilNow, dbo.gbkmut.aantal, dbo.gbkmut.docnumber
FROM dbo.gbkmut WITH (NOLOCK) INNER JOIN
dbo.grtbk WITH (NOLOCK) ON dbo.grtbk.reknr = dbo.gbkmut.reknr LEFT OUTER JOIN
dbo.humres AS SM WITH (NOLOCK) ON SM.res_id = dbo.gbkmut.sysmodifier LEFT OUTER JOIN
dbo.humres AS SC WITH (NOLOCK) ON SC.res_id = dbo.gbkmut.syscreator LEFT OUTER JOIN
dbo.cicmpy WITH (NOLOCK) ON dbo.cicmpy.debnr = dbo.gbkmut.debnr AND dbo.cicmpy.debnr IS NOT NULL AND dbo.gbkmut.debnr IS NOT NULL LEFT OUTER JOIN
dbo.cicmpy AS c1 WITH (NOLOCK) ON c1.crdnr = dbo.gbkmut.crdnr AND c1.crdnr IS NOT NULL AND dbo.gbkmut.crdnr IS NOT NULL LEFT OUTER JOIN
dbo.humres WITH (NOLOCK) ON dbo.gbkmut.res_id = dbo.humres.res_id LEFT OUTER JOIN
dbo.cicmpy AS c3 WITH (NOLOCK) ON dbo.gbkmut.orderdebtor = c3.cmp_wwn AND dbo.gbkmut.orderdebtor IS NOT NULL AND c3.cmp_wwn IS NOT NULL
LEFT OUTER JOIN
dbo.magaz WITH (NOLOCK) ON dbo.magaz.magcode = dbo.gbkmut.warehouse AND dbo.magaz.magcode IS NOT NULL AND dbo.gbkmut.warehouse IS NOT NULL
LEFT OUTER JOIN
dbo.Items WITH (NOLOCK) ON dbo.Items.ItemCode = dbo.gbkmut.artcode AND dbo.Items.ItemCode IS NOT NULL AND dbo.gbkmut.artcode IS NOT NULL
LEFT OUTER JOIN
dbo.kstpl WITH (NOLOCK) ON dbo.gbkmut.kstplcode = dbo.kstpl.kstplcode AND dbo.gbkmut.kstplcode IS NOT NULL AND dbo.kstpl.kstplcode IS NOT NULL
LEFT OUTER JOIN
dbo.kstdr WITH (NOLOCK) ON dbo.gbkmut.kstdrcode = dbo.kstdr.kstdrcode AND dbo.gbkmut.kstdrcode IS NOT NULL AND dbo.kstdr.kstdrcode IS NOT NULL
WHERE (dbo.gbkmut.transtype IN ('N', 'C', 'P', 'F')) AND (dbo.gbkmut.transsubtype = 'B') AND (dbo.grtbk.bal_vw IN ('W')) AND (dbo.gbkmut.ReminderCount <= 15)
GROUP BY dbo.gbkmut.artcode, dbo.gbkmut.aantal, dbo.gbkmut.docnumber
ORDER BY dbo.gbkmut.artcode, lastmove

I did a second view (#2) because I didn't know if in this view (#1) I could grouping by the code with the lastmove but in this second view I can groupyn I don't know my error, this is the code and this code shows me the same of the view #1:

SELECT TOP (100) PERCENT artcode, aantal AS Quantity, docnumber, MAX(LastMove) AS LastMove, DayLastMoveUntilNow, DateNow
FROM dbo.Vw_LastMove
GROUP BY artcode, aantal, docnumber, LastMove, DayLastMoveUntilNow, DateNow
ORDER BY artcode

Please I need your help

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/24/2013 :  13:13:04  Show Profile  Reply with Quote
What are you trying to achieve with your first view, I think there is room to improve your query.
Would you be able to post your DDLs and some example data following these guidelines: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

343 Posts

Posted - 07/24/2013 :  14:06:25  Show Profile  Reply with Quote
If I read the question right, this suggestion might work for you:

SELECT artcode
      ,LastMove
      ,GETDATE() AS DateNow
      ,DATEDIFF(DAY, LastMove, GETDATE()) AS DayLastMoveUntilNow
      ,aantal
      ,docnumber
  FROM (SELECT TOP (100) PERCENT dbo.gbkmut.artcode
              ,MAX(dbo.gbkmut.datum) AS LastMove
              ,GETDATE() AS DateNow
              ,DATEDIFF(DAY, MAX(dbo.gbkmut.datum), GETDATE()) AS DayLastMoveUntilNow
              ,dbo.gbkmut.aantal
              ,dbo.gbkmut.docnumber
          FROM dbo.gbkmut
          WITH (NOLOCK)
               INNER JOIN dbo.grtbk
                     WITH (NOLOCK)
                       ON dbo.grtbk.reknr = dbo.gbkmut.reknr
               LEFT OUTER JOIN dbo.humres AS SM
                          WITH (NOLOCK)
                            ON SM.res_id = dbo.gbkmut.sysmodifier
               LEFT OUTER JOIN dbo.humres AS SC
                          WITH (NOLOCK)
                            ON SC.res_id = dbo.gbkmut.syscreator
               LEFT OUTER JOIN dbo.cicmpy
                          WITH (NOLOCK)
                            ON dbo.cicmpy.debnr = dbo.gbkmut.debnr
                           AND dbo.cicmpy.debnr IS NOT NULL
                           AND dbo.gbkmut.debnr IS NOT NULL
               LEFT OUTER JOIN dbo.cicmpy AS c1
                          WITH (NOLOCK)
                            ON c1.crdnr = dbo.gbkmut.crdnr
                           AND c1.crdnr IS NOT NULL
                           AND dbo.gbkmut.crdnr IS NOT NULL
               LEFT OUTER JOIN dbo.humres
                          WITH (NOLOCK)
                            ON dbo.gbkmut.res_id = dbo.humres.res_id
               LEFT OUTER JOIN dbo.cicmpy AS c3
                          WITH (NOLOCK)
                            ON dbo.gbkmut.orderdebtor = c3.cmp_wwn
                           AND dbo.gbkmut.orderdebtor IS NOT NULL
                           AND c3.cmp_wwn IS NOT NULL
               LEFT OUTER JOIN dbo.magaz
                          WITH (NOLOCK)
                            ON dbo.magaz.magcode = dbo.gbkmut.warehouse
                           AND dbo.magaz.magcode IS NOT NULL
                           AND dbo.gbkmut.warehouse IS NOT NULL
               LEFT OUTER JOIN dbo.Items
                          WITH (NOLOCK)
                            ON dbo.Items.ItemCode = dbo.gbkmut.artcode
                           AND dbo.Items.ItemCode IS NOT NULL
                           AND dbo.gbkmut.artcode IS NOT NULL
               LEFT OUTER JOIN dbo.kstpl
                          WITH (NOLOCK)
                            ON dbo.gbkmut.kstplcode = dbo.kstpl.kstplcode
                           AND dbo.gbkmut.kstplcode IS NOT NULL
                           AND dbo.kstpl.kstplcode IS NOT NULL
               LEFT OUTER JOIN dbo.kstdr
                          WITH (NOLOCK)
                            ON dbo.gbkmut.kstdrcode = dbo.kstdr.kstdrcode
                           AND dbo.gbkmut.kstdrcode IS NOT NULL
                           AND dbo.kstdr.kstdrcode IS NOT NULL
         WHERE (dbo.gbkmut.transtype IN ('N', 'C', 'P', 'F'))
           AND (dbo.gbkmut.transsubtype = 'B')
           AND (dbo.grtbk.bal_vw IN ('W'))
           AND (dbo.gbkmut.ReminderCount <= 15)
         GROUP BY dbo.gbkmut.artcode
                 ,dbo.gbkmut.aantal
                 ,dbo.gbkmut.docnumber
         ORDER BY dbo.gbkmut.artcode
                 ,lastmove
       ) AS tmp
 ORDER BY artcode
         ,lastmove

green=outside of the subselect
pink=code moved outside of subselect
black="order by" also on the outside of subselect, as we can't expect the engine to keep the sort order (it probably will, but MS don't guarantee it)
red=let the optimizer decide whether or not lock is necessary, so normally this is not needed
orange=brackets are not necessary here


ps.: There might be syntax error(s) as I don't have access to my database at the moment (notepad to the rescue )
Go to Top of Page

mjimenezh
Yak Posting Veteran

Mexico
61 Posts

Posted - 07/24/2013 :  14:46:52  Show Profile  Reply with Quote
I don't know about sql so I did the second view because someone said me that in the original view this was impossible, I don't know how I can make the ddls that you mencioned, the only I need from the first view is that this view shows me all the records of every item and i'm trying to show only the last movement of every item and the quantity sold.

Thanks for your help.

quote:
Originally posted by MuMu88

What are you trying to achieve with your first view, I think there is room to improve your query.
Would you be able to post your DDLs and some example data following these guidelines: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

mjimenezh
Yak Posting Veteran

Mexico
61 Posts

Posted - 07/24/2013 :  14:59:18  Show Profile  Reply with Quote
I did it that you said but still without chooosing the artcode and his last movement, its showing me all the transactions and dates of every artcode, this Is the code with all I understood about your suggestions :

SELECT artcode
      ,LastMove
      ,aantal
      ,docnumber
  FROM (SELECT TOP (100) PERCENT dbo.gbkmut.artcode
              ,MAX(dbo.gbkmut.datum) AS LastMove
              ,dbo.gbkmut.aantal
              ,dbo.gbkmut.docnumber
          FROM dbo.gbkmut
          WITH (NOLOCK)
               INNER JOIN dbo.grtbk
                     WITH (NOLOCK)
                       ON dbo.grtbk.reknr = dbo.gbkmut.reknr
               LEFT OUTER JOIN dbo.humres AS SM
                          WITH (NOLOCK)
                            ON SM.res_id = dbo.gbkmut.sysmodifier
               LEFT OUTER JOIN dbo.humres AS SC
                          WITH (NOLOCK)
                            ON SC.res_id = dbo.gbkmut.syscreator
               LEFT OUTER JOIN dbo.cicmpy
                          WITH (NOLOCK)
                            ON dbo.cicmpy.debnr = dbo.gbkmut.debnr
                           AND dbo.cicmpy.debnr IS NOT NULL
                           AND dbo.gbkmut.debnr IS NOT NULL
               LEFT OUTER JOIN dbo.cicmpy AS c1
                          WITH (NOLOCK)
                            ON c1.crdnr = dbo.gbkmut.crdnr
                           AND c1.crdnr IS NOT NULL
                           AND dbo.gbkmut.crdnr IS NOT NULL
               LEFT OUTER JOIN dbo.humres
                          WITH (NOLOCK)
                            ON dbo.gbkmut.res_id = dbo.humres.res_id
               LEFT OUTER JOIN dbo.cicmpy AS c3
                          WITH (NOLOCK)
                            ON dbo.gbkmut.orderdebtor = c3.cmp_wwn
                           AND dbo.gbkmut.orderdebtor IS NOT NULL
                           AND c3.cmp_wwn IS NOT NULL
               LEFT OUTER JOIN dbo.magaz
                          WITH (NOLOCK)
                            ON dbo.magaz.magcode = dbo.gbkmut.warehouse
                           AND dbo.magaz.magcode IS NOT NULL
                           AND dbo.gbkmut.warehouse IS NOT NULL
               LEFT OUTER JOIN dbo.Items
                          WITH (NOLOCK)
                            ON dbo.Items.ItemCode = dbo.gbkmut.artcode
                           AND dbo.Items.ItemCode IS NOT NULL
                           AND dbo.gbkmut.artcode IS NOT NULL
               LEFT OUTER JOIN dbo.kstpl
                          WITH (NOLOCK)
                            ON dbo.gbkmut.kstplcode = dbo.kstpl.kstplcode
                           AND dbo.gbkmut.kstplcode IS NOT NULL
                           AND dbo.kstpl.kstplcode IS NOT NULL
               LEFT OUTER JOIN dbo.kstdr
                          WITH (NOLOCK)
                            ON dbo.gbkmut.kstdrcode = dbo.kstdr.kstdrcode
                           AND dbo.gbkmut.kstdrcode IS NOT NULL
                           AND dbo.kstdr.kstdrcode IS NOT NULL
         WHERE (dbo.gbkmut.transtype IN ('N', 'C', 'P', 'F'))
           AND (dbo.gbkmut.transsubtype = 'B')
           AND (dbo.grtbk.bal_vw IN ('W'))
           AND (dbo.gbkmut.ReminderCount <= 15)
         GROUP BY dbo.gbkmut.artcode
                 ,dbo.gbkmut.aantal
                 ,dbo.gbkmut.docnumber
         ORDER BY dbo.gbkmut.artcode
                 ,lastmove
       ) AS tmp
 ORDER BY artcode
         ,lastmove


Thanks a lot for your help and patience.





quote:
Originally posted by bitsmed

If I read the question right, this suggestion might work for you:

SELECT artcode
      ,LastMove
      ,GETDATE() AS DateNow
      ,DATEDIFF(DAY, LastMove, GETDATE()) AS DayLastMoveUntilNow
      ,aantal
      ,docnumber
  FROM (SELECT TOP (100) PERCENT dbo.gbkmut.artcode
              ,MAX(dbo.gbkmut.datum) AS LastMove
              ,GETDATE() AS DateNow
              ,DATEDIFF(DAY, MAX(dbo.gbkmut.datum), GETDATE()) AS DayLastMoveUntilNow
              ,dbo.gbkmut.aantal
              ,dbo.gbkmut.docnumber
          FROM dbo.gbkmut
          WITH (NOLOCK)
               INNER JOIN dbo.grtbk
                     WITH (NOLOCK)
                       ON dbo.grtbk.reknr = dbo.gbkmut.reknr
               LEFT OUTER JOIN dbo.humres AS SM
                          WITH (NOLOCK)
                            ON SM.res_id = dbo.gbkmut.sysmodifier
               LEFT OUTER JOIN dbo.humres AS SC
                          WITH (NOLOCK)
                            ON SC.res_id = dbo.gbkmut.syscreator
               LEFT OUTER JOIN dbo.cicmpy
                          WITH (NOLOCK)
                            ON dbo.cicmpy.debnr = dbo.gbkmut.debnr
                           AND dbo.cicmpy.debnr IS NOT NULL
                           AND dbo.gbkmut.debnr IS NOT NULL
               LEFT OUTER JOIN dbo.cicmpy AS c1
                          WITH (NOLOCK)
                            ON c1.crdnr = dbo.gbkmut.crdnr
                           AND c1.crdnr IS NOT NULL
                           AND dbo.gbkmut.crdnr IS NOT NULL
               LEFT OUTER JOIN dbo.humres
                          WITH (NOLOCK)
                            ON dbo.gbkmut.res_id = dbo.humres.res_id
               LEFT OUTER JOIN dbo.cicmpy AS c3
                          WITH (NOLOCK)
                            ON dbo.gbkmut.orderdebtor = c3.cmp_wwn
                           AND dbo.gbkmut.orderdebtor IS NOT NULL
                           AND c3.cmp_wwn IS NOT NULL
               LEFT OUTER JOIN dbo.magaz
                          WITH (NOLOCK)
                            ON dbo.magaz.magcode = dbo.gbkmut.warehouse
                           AND dbo.magaz.magcode IS NOT NULL
                           AND dbo.gbkmut.warehouse IS NOT NULL
               LEFT OUTER JOIN dbo.Items
                          WITH (NOLOCK)
                            ON dbo.Items.ItemCode = dbo.gbkmut.artcode
                           AND dbo.Items.ItemCode IS NOT NULL
                           AND dbo.gbkmut.artcode IS NOT NULL
               LEFT OUTER JOIN dbo.kstpl
                          WITH (NOLOCK)
                            ON dbo.gbkmut.kstplcode = dbo.kstpl.kstplcode
                           AND dbo.gbkmut.kstplcode IS NOT NULL
                           AND dbo.kstpl.kstplcode IS NOT NULL
               LEFT OUTER JOIN dbo.kstdr
                          WITH (NOLOCK)
                            ON dbo.gbkmut.kstdrcode = dbo.kstdr.kstdrcode
                           AND dbo.gbkmut.kstdrcode IS NOT NULL
                           AND dbo.kstdr.kstdrcode IS NOT NULL
         WHERE (dbo.gbkmut.transtype IN ('N', 'C', 'P', 'F'))
           AND (dbo.gbkmut.transsubtype = 'B')
           AND (dbo.grtbk.bal_vw IN ('W'))
           AND (dbo.gbkmut.ReminderCount <= 15)
         GROUP BY dbo.gbkmut.artcode
                 ,dbo.gbkmut.aantal
                 ,dbo.gbkmut.docnumber
         ORDER BY dbo.gbkmut.artcode
                 ,lastmove
       ) AS tmp
 ORDER BY artcode
         ,lastmove

green=outside of the subselect
pink=code moved outside of subselect
black="order by" also on the outside of subselect, as we can't expect the engine to keep the sort order (it probably will, but MS don't guarantee it)
red=let the optimizer decide whether or not lock is necessary, so normally this is not needed
orange=brackets are not necessary here


ps.: There might be syntax error(s) as I don't have access to my database at the moment (notepad to the rescue )

Go to Top of Page

mjimenezh
Yak Posting Veteran

Mexico
61 Posts

Posted - 07/24/2013 :  15:24:02  Show Profile  Reply with Quote
This is all I get with this view and I just need to show the last movement of every artcode

ARTCODE LASTMOVE DATE QUANTITY SALES ORDER
00CBWT 2013-02-20 00:00:00.000 4 PedComp: 3233
00CBWT 2013-04-22 00:00:00.000 35 PedComp: 6440
00CBWT 2013-04-19 00:00:00.000 3 PedComp: 6358
00CBWT 2013-04-19 00:00:00.000 2 PedComp: 6357
00CBWT 2013-01-29 00:00:00.000 4 PedComp: 2115
00JLPMB 2013-07-11 00:00:00.000 1 PedComp: 11084
00JLPMB 2013-04-12 00:00:00.000 1 PedComp: 5993
00JLPMB 2013-01-08 00:00:00.000 1 PedComp: 1152
00JLPMB 2013-05-23 00:00:00.000 1 PedComp: 8351
00JLPMB 2013-02-05 00:00:00.000 5 PedComp: 2485
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

343 Posts

Posted - 07/24/2013 :  15:28:25  Show Profile  Reply with Quote
I think I understand your question now.
Try this:

SELECT dbo.gbkmut.artcode
      ,tmp.LastMove
      ,GETDATE() AS DateNow
      ,DATEDIFF(DAY, tmp.LastMove, GETDATE()) AS DayLastMoveUntilNow
      ,dbo.gbkmut.aantal
      ,dbo.gbkmut.docnumber
  FROM (SELECT TOP (100) PERCENT dbo.gbkmut.artcode
              ,MAX(dbo.gbkmut.datum) AS LastMove
          FROM dbo.gbkmut
          WITH (NOLOCK)
               INNER JOIN dbo.grtbk
                     WITH (NOLOCK)
                       ON dbo.grtbk.reknr = dbo.gbkmut.reknr
         WHERE (dbo.gbkmut.transtype IN ('N', 'C', 'P', 'F'))
           AND (dbo.gbkmut.transsubtype = 'B')
           AND (dbo.grtbk.bal_vw IN ('W'))
           AND (dbo.gbkmut.ReminderCount <= 15)
         GROUP BY dbo.gbkmut.artcode
         ORDER BY dbo.gbkmut.artcode
                 ,LastMove
       ) AS tmp
       INNER JOIN dbo.gbkmut
             WITH (NOLOCK)
               ON dbo.gbkmut.artcode=tmp.artcode
              AND dbo.gbkmut.datum=tmp.LastMove
       INNER JOIN dbo.grtbk
             WITH (NOLOCK)
               ON dbo.grtbk.reknr = dbo.gbkmut.reknr
       LEFT OUTER JOIN dbo.humres AS SM
                  WITH (NOLOCK)
                    ON SM.res_id = dbo.gbkmut.sysmodifier
       LEFT OUTER JOIN dbo.humres AS SC
                  WITH (NOLOCK)
                    ON SC.res_id = dbo.gbkmut.syscreator
       LEFT OUTER JOIN dbo.cicmpy
                  WITH (NOLOCK)
                    ON dbo.cicmpy.debnr = dbo.gbkmut.debnr
                   AND dbo.cicmpy.debnr IS NOT NULL
                   AND dbo.gbkmut.debnr IS NOT NULL
       LEFT OUTER JOIN dbo.cicmpy AS c1
                  WITH (NOLOCK)
                    ON c1.crdnr = dbo.gbkmut.crdnr
                   AND c1.crdnr IS NOT NULL
                   AND dbo.gbkmut.crdnr IS NOT NULL
       LEFT OUTER JOIN dbo.humres
                  WITH (NOLOCK)
                    ON dbo.gbkmut.res_id = dbo.humres.res_id
       LEFT OUTER JOIN dbo.cicmpy AS c3
                  WITH (NOLOCK)
                    ON dbo.gbkmut.orderdebtor = c3.cmp_wwn
                   AND dbo.gbkmut.orderdebtor IS NOT NULL
                   AND c3.cmp_wwn IS NOT NULL
       LEFT OUTER JOIN dbo.magaz
                  WITH (NOLOCK)
                    ON dbo.magaz.magcode = dbo.gbkmut.warehouse
                   AND dbo.magaz.magcode IS NOT NULL
                   AND dbo.gbkmut.warehouse IS NOT NULL
       LEFT OUTER JOIN dbo.Items
                  WITH (NOLOCK)
                    ON dbo.Items.ItemCode = dbo.gbkmut.artcode
                   AND dbo.Items.ItemCode IS NOT NULL
                   AND dbo.gbkmut.artcode IS NOT NULL
       LEFT OUTER JOIN dbo.kstpl
                  WITH (NOLOCK)
                    ON dbo.gbkmut.kstplcode = dbo.kstpl.kstplcode
                   AND dbo.gbkmut.kstplcode IS NOT NULL
                   AND dbo.kstpl.kstplcode IS NOT NULL
       LEFT OUTER JOIN dbo.kstdr
                  WITH (NOLOCK)
                    ON dbo.gbkmut.kstdrcode = dbo.kstdr.kstdrcode
                   AND dbo.gbkmut.kstdrcode IS NOT NULL
                   AND dbo.kstdr.kstdrcode IS NOT NULL
 WHERE (dbo.gbkmut.transtype IN ('N', 'C', 'P', 'F'))
   AND (dbo.gbkmut.transsubtype = 'B')
   AND (dbo.grtbk.bal_vw IN ('W'))
   AND (dbo.gbkmut.ReminderCount <= 15)
 ORDER BY dbo.gbkmut.artcode
         ,tmp.LastMove
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30175 Posts

Posted - 07/24/2013 :  15:29:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT ARTCODE, LASTMOVE, DATE, QUANTITY, SALES, ORDER
FROM (SELECT ARTCODE LASTMOVE DATE QUANTITY SALES ORDER, ROW_NUMBER() OVER (PARTITION BY ARTCODE ORDER BY DATE DESC) AS rn FROM {Your view here}) AS d
WHERE rn = 1


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

mjimenezh
Yak Posting Veteran

Mexico
61 Posts

Posted - 07/24/2013 :  16:00:13  Show Profile  Reply with Quote
Wow, This code works, thank you so much I really appreciate your help.



quote:
Originally posted by SwePeso

SELECT ARTCODE, LASTMOVE, DATE, QUANTITY, SALES, ORDER
FROM (SELECT ARTCODE LASTMOVE DATE QUANTITY SALES ORDER, ROW_NUMBER() OVER (PARTITION BY ARTCODE ORDER BY DATE DESC) AS rn FROM {Your view here}) AS d
WHERE rn = 1


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.55 seconds. Powered By: Snitz Forums 2000