Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

sort formula 2

Status
Not open for further replies.
tonyvee1973

tonyvee1973

IS-IT--Management
Oct 22, 2009
156
GB
Hiya
I need to sort some data and have built a query but need to get it right as this wont work. Can someone see where i am going wrong?
Think the issue is where i have put the "where POD_QTYRESERVED>0" is the wrong place as if i delete it the query works ok.
Thanks as always

Select POP_DETAIL.POD_STOCK_CODE,
POP_DETAIL.POD_QTYRESERVED,
POP_DETAIL.POD_REQDATE,
Row_Number() Over (Partition By POD_STOCK_CODE Order By POD_REQDATE DESC) As RowId where POD_QTYRESERVED>0
From POP_DETAIL
 
Sort by date Sort by votes
Code: 
Select POP_DETAIL.POD_STOCK_CODE,
       POP_DETAIL.POD_QTYRESERVED,
       POP_DETAIL.POD_REQDATE,
       Row_Number() Over (Partition By POD_STOCK_CODE Order By POD_REQDATE DESC) As RowId
From POP_DETAIL
where POD_QTYRESERVED>0
?

Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
Hiya and thanks.
However this only returns results which have a POD_QTYRESERVED of more than zero.
I need to return ALL results but only sort the records with more than zero by date. Can this be done or am i looking at this the wrong way?
 
Upvote 0 Downvote
What will happen with these records with POD_QTYRESERVED = 0?

Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
Code: 
Select POP_DETAIL.POD_STOCK_CODE,
       POP_DETAIL.POD_QTYRESERVED,
       POP_DETAIL.POD_REQDATE,
       Row_Number() Over (Partition By POD_STOCK_CODE Order By POD_REQDATE DESC) As RowId
From POP_DETAIL
ORDER BY CASE WHEN POD_QTYRESERVED>0 THEN RowId ELSE 9999999 END

NOT TESTED!


Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
Hiya
All records are to be exported but we need to show qty in pod_qtyreserved and the correspondingv earliest date in pod_reqdate of and of each line where pod_qtyreserved>0
does this make sense?
 
Upvote 0 Downvote
Please, provide some example data and desired result from it.

Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
Hi

Ok, i have placed below a small piece of the query and the results (I used stock code 36005 as an example to cut the result list down so i can post here)

Select POP_DETAIL.POD_STOCK_CODE,
POP_DETAIL.POD_QTYRESERVED,
POP_DETAIL.POD_REQDATE,
Row_Number() Over (Partition By POD_STOCK_CODE Order By POD_REQDATE ASC) As RowId
From POP_DETAIL
where pod_stock_code = '36005'



Results:

POD_STOCK_CODE POD_QTYRESERVED POD_REQDATE ROWID
36005 0 2009/06/11 00:00:00 1
36005 0 2009/09/21 00:00:00 2
36005 0 2009/09/28 00:00:00 3
36005 0 2009/10/06 00:00:00 4
36005 0 2009/12/01 00:00:00 5
36005 0 2009/12/06 00:00:00 6
36005 0 2009/12/28 00:00:00 7
36005 0 2010/01/05 00:00:00 8
36005 0 2010/01/25 00:00:00 9
36005 0 2010/05/09 00:00:00 10
36005 0 2010/05/15 00:00:00 11
36005 0 2010/05/27 00:00:00 12
36005 0 2010/06/06 00:00:00 13
36005 0 2010/06/19 00:00:00 14
36005 0 2010/07/06 00:00:00 15
36005 0 2010/07/24 00:00:00 16
36005 0 2010/08/25 00:00:00 17
36005 0 2010/09/06 00:00:00 18
36005 0 2010/09/12 00:00:00 19
36005 0 2010/10/19 00:00:00 20
36005 0 2011/03/30 00:00:00 21
36005 0 2011/04/06 00:00:00 22
36005 0 2011/04/20 00:00:00 23
36005 0 2011/04/24 00:00:00 24
36005 0 2011/04/27 00:00:00 25
36005 0 2011/05/03 00:00:00 26
36005 0 2011/05/09 00:00:00 27
36005 0 2011/06/06 00:00:00 28
36005 0 2011/06/28 00:00:00 29
36005 0 2011/07/07 00:00:00 30
36005 0 2011/07/20 00:00:00 31
36005 0 2011/10/01 00:00:00 32
36005 0 2011/10/09 00:00:00 33
36005 0 2011/10/30 00:00:00 34
36005 0 2011/11/21 00:00:00 35
36005 0 2011/12/11 00:00:00 36
36005 0 2011/12/25 00:00:00 37
36005 0 2012/02/02 00:00:00 38
36005 0 2012/02/13 00:00:00 39
36005 0 2012/06/09 00:00:00 40
36005 0 2012/08/24 00:00:00 41
36005 0 2012/08/31 00:00:00 42
36005 0 2012/09/07 00:00:00 43
36005 0 2012/10/05 00:00:00 44
36005 0 2012/10/16 00:00:00 45
36005 0 2012/10/25 00:00:00 46
36005 0 2012/11/30 00:00:00 47
36005 0 2012/12/13 00:00:00 48
36005 0 2013/01/19 00:00:00 49
36005 0 2013/05/16 00:00:00 50
36005 0 2013/05/18 00:00:00 51
36005 0 2013/05/23 00:00:00 52
36005 1260 2013/05/29 00:00:00 53
36005 3600 2013/08/31 00:00:00 54

I am using the query above as a sub query to produce a list as above. From that large list i am pulling one line per stock code to show when stock is coming in (POD_REQDATE) and the associated stock due in (POD_QTYRESERVED)
I need to show the line with the nearest upcoming date with associated qty due in.
When i run the query with my code i get zeroes as its pulling row1 when i need it to be pulling the line with the earliest date (36005, 1260, 2013/05/29 00:00:00) i know this date has passed but didnt work a week ago either.


 
Upvote 0 Downvote
Please,
do not describe it. Give me some sample data and what you want as a result from it.

i.e.
Code: 
POD_STOCK_CODE POD_QTYRESERVED POD_REQDATE
--------------------------------------------------
36005             0            2013/01/19 00:00:00
36005             0            2013/05/16 00:00:00
36005             0            2013/05/18 00:00:00
36005             0            2013/05/23 00:00:00
36005             1260         2013/05/29 00:00:00
36005             3600         2013/08/31 00:00:00

36006             0            2013/01/19 00:00:00
36006             432          2013/03/15 00:00:00
36006             0            2013/04/18 00:00:00
36006             789          2013/05/21 00:00:00
36006             1260         2013/05/29 00:00:00
36006             3600         2013/07/31 00:00:00
What you want as a result from these records?

Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
apologies, i would like the result data to look like below:

36005 1260 29/05/2013 00:00 1
36005 3600 31/08/2013 00:00 2
36005 0 11/06/2009 00:00 3
36005 0 21/09/2009 00:00 4
36005 0 28/09/2009 00:00 5
36005 0 06/10/2009 00:00 6
36005 0 01/12/2009 00:00 7
36005 0 06/12/2009 00:00 8
36005 0 28/12/2009 00:00 9
36005 0 05/01/2010 00:00 10
36005 0 25/01/2010 00:00 11
36005 0 09/05/2010 00:00 12
36005 0 15/05/2010 00:00 13
36005 0 27/05/2010 00:00 14
36005 0 06/06/2010 00:00 15
36005 0 19/06/2010 00:00 16
36005 0 06/07/2010 00:00 17
36005 0 24/07/2010 00:00 18
36005 0 25/08/2010 00:00 19
36005 0 06/09/2010 00:00 20
36005 0 12/09/2010 00:00 21
36005 0 19/10/2010 00:00 22
36005 0 30/03/2011 00:00 23
36005 0 06/04/2011 00:00 24
36005 0 20/04/2011 00:00 25
36005 0 24/04/2011 00:00 26
36005 0 27/04/2011 00:00 27
36005 0 03/05/2011 00:00 28
36005 0 09/05/2011 00:00 29
36005 0 06/06/2011 00:00 30
36005 0 28/06/2011 00:00 31
36005 0 07/07/2011 00:00 32
36005 0 20/07/2011 00:00 33
36005 0 01/10/2011 00:00 34
36005 0 09/10/2011 00:00 35
36005 0 30/10/2011 00:00 36
36005 0 21/11/2011 00:00 37
36005 0 11/12/2011 00:00 38
36005 0 25/12/2011 00:00 39
36005 0 02/02/2012 00:00 40
36005 0 13/02/2012 00:00 41
36005 0 09/06/2012 00:00 42
36005 0 24/08/2012 00:00 43
36005 0 31/08/2012 00:00 44
36005 0 07/09/2012 00:00 45
36005 0 05/10/2012 00:00 46
36005 0 16/10/2012 00:00 47
36005 0 25/10/2012 00:00 48
36005 0 30/11/2012 00:00 49
36005 0 13/12/2012 00:00 50
36005 0 19/01/2013 00:00 51
36005 0 16/05/2013 00:00 52
36005 0 18/05/2013 00:00 53
36005 0 23/05/2013 00:00 54

Anything with a value in POD_QTYRESERVED i would like to be sorted earliest date first, then under that i need everything else
Many thanks and i hope this helps
 
Upvote 0 Downvote
Is this what you want?
Code: 
DECLARE @test TABLE (POD_STOCK_CODE int, POD_QTYRESERVED int, POD_REQDATE datetime)

INSERT INTO @test VALUES (36005, 0,    '20130119')
INSERT INTO @test VALUES (36005, 0,    '20130516')
INSERT INTO @test VALUES (36005, 0,    '20130518')
INSERT INTO @test VALUES (36005, 0,    '20130523')
INSERT INTO @test VALUES (36005, 1260, '20130529')
INSERT INTO @test VALUES (36005, 3600, '20130831')

INSERT INTO @test VALUES (36006, 0   , '20130119')
INSERT INTO @test VALUES (36006, 432 , '20130315')
INSERT INTO @test VALUES (36006, 0   , '20130418')
INSERT INTO @test VALUES (36006, 789 , '20130521')
INSERT INTO @test VALUES (36006, 1260, '20130529')
INSERT INTO @test VALUES (36006, 3600, '20130731')



Select POD_STOCK_CODE,
       POD_QTYRESERVED,
       POD_REQDATE,
       Row_Number() Over (Partition By POD_STOCK_CODE Order By CASE WHEN POD_QTYRESERVED > 0 THEN 0 ELSE 1 END, POD_REQDATE) As RowId
From @test

Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
Hi
When i enter your select statement into my query it only returns lines with a value in POD_QTYRESERVED. If there is a 0 value in there it omits the line and i need to see the lines with zero also.
Sorry to be such a headache but appreciate the help
 
Upvote 0 Downvote
Hi, sorry, my error as i didnt put in correctly.
Results below, still doesnt seem to sort right.

36005 0 00:00.0
36005 1260 00:00.0
36005 3600 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
36005 0 00:00.0
 
Upvote 0 Downvote
That works fine for me:
Code: 
DECLARE @test TABLE (POD_STOCK_CODE int, POD_QTYRESERVED int, POD_REQDATE datetime)
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005,1260,'00:00:00')
INSERT INTO @test VALUES (36005,3600,'00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')
INSERT INTO @test VALUES (36005, 0, '00:00:00')


Select POD_STOCK_CODE,
       POD_QTYRESERVED,
       POD_REQDATE,
       Row_Number() Over (Partition By POD_STOCK_CODE Order By CASE WHEN POD_QTYRESERVED > 0 THEN 0 ELSE 1 END, POD_REQDATE) As RowId
From @test

Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
Hi
The sorting doesnt sort by date order, i have pasted the results below again with the date included.
Also there is a zero QTY_RESERVED on row 1 which i dont want there on row 1.

36005 0 2012/08/31 00:00
36005 1260 2013/05/29 00:00
36005 3600 2013/08/31 00:00
36005 0 2009/10/06 00:00
36005 0 2010/01/25 00:00
36005 0 2010/05/09 00:00
36005 0 2010/06/19 00:00
36005 0 2010/07/24 00:00
36005 0 2010/08/25 00:00
36005 0 2010/09/06 00:00
36005 0 2010/09/12 00:00
36005 0 2011/04/24 00:00
36005 0 2011/04/27 00:00
36005 0 2011/06/28 00:00
36005 0 2011/10/01 00:00
36005 0 2011/10/09 00:00
36005 0 2012/10/05 00:00
36005 0 2012/10/16 00:00
36005 0 2012/10/25 00:00
36005 0 2012/11/30 00:00
36005 0 2012/12/13 00:00
36005 0 2012/02/13 00:00
36005 0 2011/05/03 00:00
36005 0 2011/10/30 00:00
36005 0 2010/07/06 00:00
36005 0 2011/06/06 00:00
36005 0 2013/05/16 00:00
36005 0 2013/05/23 00:00
36005 0 2012/08/24 00:00
36005 0 2012/06/09 00:00
36005 0 2012/09/07 00:00
36005 0 2011/11/21 00:00
36005 0 2011/12/11 00:00
36005 0 2011/03/30 00:00
36005 0 2011/04/06 00:00
36005 0 2011/04/20 00:00
36005 0 2011/05/09 00:00
36005 0 2012/02/02 00:00
36005 0 2010/10/19 00:00
36005 0 2011/07/07 00:00
36005 0 2011/12/25 00:00
36005 0 2010/06/06 00:00
36005 0 2009/12/28 00:00
36005 0 2010/01/05 00:00
36005 0 2010/05/27 00:00
36005 0 2009/09/21 00:00
36005 0 2009/09/28 00:00
36005 0 2010/05/15 00:00
36005 0 2009/12/01 00:00
36005 0 2009/12/06 00:00
36005 0 2009/06/11 00:00
36005 0 2011/07/20 00:00
36005 0 2013/01/19 00:00
36005 0 2013/05/18 00:00
 
Upvote 0 Downvote
OK, from THIS data, what you want as a result:
Code: 
36005    0 2012/08/31 00:00
36005 1260 2013/05/29 00:00
36005 3600 2013/08/31 00:00
36005    0 2009/10/06 00:00
36005    0 2010/01/25 00:00
36005    0 2010/05/09 00:00
36005    0 2010/06/19 00:00
Please, do not post the whole data again. I want to know from these 7 records what result are you expect?

Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
36005 1260 29/05/2013 00:00
36005 3600 31/08/2013 00:00
36005 0 06/10/2009 00:00
36005 0 25/01/2010 00:00
36005 0 09/05/2010 00:00
36005 0 19/06/2010 00:00
36005 0 31/08/2012 00:00

Anything with a value >0 in POD_QTYRESERVED to be sorted by earliest date first.
Anything with a value of 0 or <0 in POD_QTYRESERVED to be listed underneath
Many thanks
 
Upvote 0 Downvote
Code: 
DECLARE @test TABLE (POD_STOCK_CODE int, POD_QTYRESERVED int, POD_REQDATE datetime)
INSERT INTO @test VALUES (36005, 0, '20120831')
INSERT INTO @test VALUES (36005,1260,'20130529')
INSERT INTO @test VALUES (36005,3600,'20130831')
INSERT INTO @test VALUES (36005, 0, '20091006')
INSERT INTO @test VALUES (36005, 0, '20100125')
INSERT INTO @test VALUES (36005, 0, '20100619')

Select POD_STOCK_CODE,
       POD_QTYRESERVED,
       POD_REQDATE,
       Row_Number() Over (Partition By POD_STOCK_CODE Order By CASE WHEN POD_QTYRESERVED > 0 THEN 0 ELSE 1 END, POD_REQDATE) As RowId
From @test
Copy and paste THIS query in SSMS.

Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
Must be something in my data as when i paste your query in i get the results below:

36005 0 2012-08-31 00:00:00.000
36005 1260 2013-05-29 00:00:00.000
36005 3600 2013-08-31 00:00:00.000
36005 0 2009-10-06 00:00:00.000
36005 0 2010-01-25 00:00:00.000
36005 0 2010-05-09 00:00:00.000
36005 0 2010-06-19 00:00:00.000
 
Upvote 0 Downvote
Try adding an order by clause.

Code: 
Order By RowId


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

jchewsmith
  • Locked
  • Question
Crystal formula to find the prior month end date from today
Replies
7
Views
75
jchewsmith
jchewsmith
dkaplan
  • Locked
  • Question
proc gets different result on different servers -- looses sort 1
Replies
2
Views
59
Andrzejek
Andrzejek
rrmcguire
  • Locked
  • Question
case formula to rename columns
2
Replies
21
Views
102
Andrzejek
Andrzejek
jchewsmith
  • Locked
  • Question
formula to find number of days from two datetime fields in SQL 2005 1
Replies
1
Views
32
Olaf Doschke
Olaf Doschke
codrutza
  • Locked
  • Question
group sort issue
Replies
2
Views
37
Olaf Doschke
Olaf Doschke

Part and Inventory Search

Sponsor

Back
Top