troubleshooting Question

How to design database with rolling time?

Avatar of sassy168
sassy168 asked on
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2005
1 Comment1 Solution294 ViewsLast Modified:
i would like to design a database that will capture data for every hour of the day, and then display a rolling 12 hours. WHat would be the best way to do this? I have tableA where i capture all the data, and tableB with all the time information there. So in the tableA, i link it to table B with time_id....but when it turns to midnight that's where all my issues are. I can't display them in the correct order. Do you recommend adding dates with every hour of the day instead of using a table for dates? i hope i make sense.
TableA:
      7/28/2008 4:46:06 PM      1      24      0      0            1799      
      7/28/2008 4:46:06 PM      1      25      0      0            1800      
      7/28/2008 4:46:06 PM      1      26      0      0            1801      
      7/28/2008 4:46:06 PM      1      27      0      0            1802      
      7/28/2008 4:46:06 PM      1      28      0      0            1803      
      7/28/2008 4:46:06 PM      1      29      0      0            1804      
      7/28/2008 4:46:06 PM      1      30      0      0            1805      
      7/28/2008 4:46:06 PM      1      31      0      0            1806      
      7/28/2008 4:46:06 PM      1      32      0      0            1807      
      7/28/2008 4:46:06 PM      1      33      0      0            1808      
      7/28/2008 4:46:06 PM      1      34      0      0            1809      
      7/28/2008 4:46:06 PM      1      35      0      0            1810      
      7/28/2008 4:46:06 PM      1      36      0      0            1811      
      7/28/2008 4:46:06 PM      1      37      0      0            1812      
      7/28/2008 4:46:06 PM      1      38      0      0            1813      
      7/28/2008 4:46:06 PM      1      39      0      0            1814      
      7/28/2008 4:46:06 PM      1      40      0      0            1815      
      7/28/2008 4:46:06 PM      1      41      0      0            1816      
      7/28/2008 4:46:06 PM      1      42      0      0            1817      
      7/28/2008 4:46:06 PM      1      43      0      0            1818      
      7/29/2008 4:46:06 PM      1      46      0      0            1821      


TableB:
      24      04:00 AM-05:00 AM      day      4 AM      4
      25      05:00 AM-06:00 AM      day      5 AM      5
      26      06:00 AM-07:00 AM      day      6 AM      6
      27      07:00 AM-08:00 AM      day      7 AM      7
      28      08:00 AM-09:00 AM      day      8 AM      8
      29      09:00 AM-10:00 AM      day      9 AM      9
      30      10:00 AM-11:00 AM      day      10 AM      10
      31      11:00 AM-12:00 PM      day      11 AM      11
      32      12:00 PM-01:00 PM      day      12 AM      12
      33      01:00 PM-02:00 PM      day      1 PM      13
      34      02:00 PM-03:00 PM      day      2 PM      14
      35      03:00 PM-04:00 PM      day      3 PM      15
      36      04:00 PM-05:00 PM      night      4 PM      16
      37      05:00 PM-06:00 PM      night      5 PM      17
      38      06:00 PM-07:00 PM      night      6 PM      18
      39      07:00 PM-08:00 PM      night      7 PM      19
      40      08:00 PM-09:00 PM      night      8 PM      20
      41      09:00 PM-10:00 PM      night      9 PM      21
      42      10:00 PM-11:00 PM      night      10 PM      22
      43      11:00 PM-12:00 AM      night      11 PM      23
      44      12:00 AM-01:00 AM      night      12 AM      24
      45      01:00 AM-02:00 AM      night      1 AM      1
      46      02:00 AM-03:00 AM      night      2 AM      2
      47      03:00 AM-04:00 AM      night      3 AM      3
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros