Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
mjimenezh
Yak Posting Veteran
81 Posts |
Posted - 2013-07-24 : 11:58:22
|
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.docnumberFROM 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 NULLWHERE (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.docnumberORDER BY dbo.gbkmut.artcode, lastmoveI 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, DateNowFROM dbo.Vw_LastMoveGROUP BY artcode, aantal, docnumber, LastMove, DayLastMoveUntilNow, DateNowORDER BY artcodePlease I need your help |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-24 : 13:13:04
|
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 |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2013-07-24 : 14:06:25
|
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 subselectpink=code moved outside of subselectblack="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 neededorange=brackets are not necessary hereps.: There might be syntax error(s) as I don't have access to my database at the moment (notepad to the rescue ) |
|
|
mjimenezh
Yak Posting Veteran
81 Posts |
Posted - 2013-07-24 : 14:46:52
|
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
|
|
|
mjimenezh
Yak Posting Veteran
81 Posts |
Posted - 2013-07-24 : 14:59:18
|
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 subselectpink=code moved outside of subselectblack="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 neededorange=brackets are not necessary hereps.: There might be syntax error(s) as I don't have access to my database at the moment (notepad to the rescue )
|
|
|
mjimenezh
Yak Posting Veteran
81 Posts |
Posted - 2013-07-24 : 15:24:02
|
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 ORDER00CBWT 2013-02-20 00:00:00.000 4 PedComp: 323300CBWT 2013-04-22 00:00:00.000 35 PedComp: 644000CBWT 2013-04-19 00:00:00.000 3 PedComp: 635800CBWT 2013-04-19 00:00:00.000 2 PedComp: 635700CBWT 2013-01-29 00:00:00.000 4 PedComp: 211500JLPMB 2013-07-11 00:00:00.000 1 PedComp: 1108400JLPMB 2013-04-12 00:00:00.000 1 PedComp: 599300JLPMB 2013-01-08 00:00:00.000 1 PedComp: 115200JLPMB 2013-05-23 00:00:00.000 1 PedComp: 835100JLPMB 2013-02-05 00:00:00.000 5 PedComp: 2485 |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2013-07-24 : 15:28:25
|
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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-24 : 15:29:06
|
SELECT ARTCODE, LASTMOVE, DATE, QUANTITY, SALES, ORDERFROM (SELECT ARTCODE LASTMOVE DATE QUANTITY SALES ORDER, ROW_NUMBER() OVER (PARTITION BY ARTCODE ORDER BY DATE DESC) AS rn FROM {Your view here}) AS dWHERE rn = 1 Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
mjimenezh
Yak Posting Veteran
81 Posts |
Posted - 2013-07-24 : 16:00:13
|
Wow, This code works, thank you so much I really appreciate your help.quote: Originally posted by SwePeso SELECT ARTCODE, LASTMOVE, DATE, QUANTITY, SALES, ORDERFROM (SELECT ARTCODE LASTMOVE DATE QUANTITY SALES ORDER, ROW_NUMBER() OVER (PARTITION BY ARTCODE ORDER BY DATE DESC) AS rn FROM {Your view here}) AS dWHERE rn = 1 Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
|
|
|
|
|
|
|
|