[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

SQL Query Help

Hello,

Not sure what the best way to word this is so i will give an example of the data set i have is and what i am looking to produce...



ID Field           Date
----------------------------
1                 1/1/2005
2                 2/25/2005
2                 3/11/2005
3                 8/19/2005
4                 10/10/2005
4                 11/10/2005
5                 12/20/2004
5                 1/19/2005
6                 9/4/2005
7                 7/14/2005

Desired Reults...


ID Field           Date              Number
---------------------------------------------
1                 1/1/2005             1
2                 2/25/2005           1
2                 3/11/2005           2
3                 8/19/2005           1
4                 10/10/2005         1
4                 11/10/2005         2
5                 12/20/2004         1
5                 1/19/2005           2
6                 9/4/2005            1
7                 7/14/2005           1


Notice the ID's can repeat for a second time with a diffrent date (the table is keyed off date AND ID).  The "1" is easy to find by simply doing a "Min(Date)".  But the "2" is harder because i can't do a Max function (some ID's just have one date).  Anyone know a clever way to do this.

Thanks

-Navicerts
0
Navicerts
Asked:
Navicerts
  • 3
1 Solution
 
rafranciscoCommented:
Try this:

SELECT A.[ID], A.[Date], (SELECT COUNT(*) FROM YourTable B WHERE A.[ID] = B.[ID] AND A.[Date] <= B.[Date]) AS Number
FROM YourTable A
0
 
ispalenyCommented:
select [ID Field],Date,id=identity(int,1,1)
into #q
from YourTable
order by [ID Field],Date

select A.[ID Field],A.Date.A.id-mid+1
from #q A
join (
 select [ID Field],Date,min(id) mid
 from #q
 group by [ID Field],Date
) B on A.[ID Field]=B.[ID Field] and A.Date=B.Date
0
 
ispalenyCommented:
OK, sort on Date

select [ID Field],id=identity(int,1,1)
into #q
from YourTable
order by [ID Field]

select A.[ID Field],A.Date.A.id-mid+1
from #q A
join (
 select [ID Field],Date,min(id) mid
 from #q
 group by [ID Field]
) B on A.[ID Field]=B.[ID Field]
0
 
ispalenyCommented:
OK, sort on Date
3rd trial

select [ID Field],id=identity(int,1,1)
into #q
from YourTable
order by [ID Field]

select A.[ID Field],A.Date.A.id-mid+1
from #q A
join (
 select [ID Field],min(id) mid
 from #q
 group by [ID Field]
) B on A.[ID Field]=B.[ID Field]
0
 
NavicertsAuthor Commented:
Went with the first count'ing result, seemed simple and fast.

Thanks you both for the input!

-Navicerts
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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