SQL Time Table

I have a time table. I am trying to show every record in 15 minute intervals.

SELECT TimeID, CAST(Minute AS smallint) as IntMin, CAST(Second AS smallint) as IntSec, StandardTime from dim_Time

Open in new window

LVL 1
RecipeDanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
And what's the Problem?
0
RecipeDanAuthor Commented:
I have data that looks like this:

TimeID	Time	Hour	MilitaryHour	Minute	Second	AmPm	StandardTime
1	00:00:00	00	00	00	00	AM	12:00:00 AM
2	00:00:01	00	00	00	01	AM	12:00:01 AM
3	00:00:02	00	00	00	02	AM	12:00:02 AM
4	00:00:03	00	00	00	03	AM	12:00:03 AM
5	00:00:04	00	00	00	04	AM	12:00:04 AM

Open in new window


I want to show all records with 15 minute intervals. So the data looks like this:

12:00
12:15
12:30
12:45
1:00
1:15 etc..
0
PortletPaulfreelancerCommented:
use the Minute field

below I have used [n] but you would use [minute]

with cte as (
          select 0 as n union all
          select 14 union all
          select 15 union all
          select 24 union all
          select 30 union all
          select 44 union all
          select 45
          )
select
*
from CTE
where n % 15 = 0


|  N |
|----|
|  0 |
| 15 |
| 30 |
| 45 |
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RecipeDanAuthor Commented:
That works but is not what I am looking for. I have a time table that lists all the times in a day from 12:00 am to 11:59 pm. What I want is a list of times that end in 15 minute intervals.

12:00
12;15
12:30
12:45
1:00
1:15
1:30
1:45 etc..
0
PortletPaulfreelancerCommented:
This is how I though you might use the % 15:

select [time] from dim_time where [minute] % 15 = 0;

select [StandardTime]  from dim_time where [minute] % 15 = 0;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RecipeDanAuthor Commented:
You are correct. I misunderstood you. Thank you for your assistance. For reference here is the code, I decided to use military time.

SELECT MilitaryHour + ':' + CASE WHEN vartime = '0' THEN '00' ELSE vartime END AS vartime
FROM
(
select distinct MilitaryHour, CAST(inttime as varchar) as vartime
from
(
select cast(minute as smallint) as inttime, MilitaryHour from dim_time
)
t1
where inttime % 15 = 0
)
t2
order by vartime

Open in new window

0
PortletPaulfreelancerCommented:
I see.

thanks.

As this is a dimension table, does this imply there is substantial volume when you perform queries?

If this is true, and you have indexes on the string field called [minute]

Then you might be better off simply using an IN() like this:

SELECT
      MilitaryHour
FROM dim_time
WHERE [minute] IN ('00', '15', '30', '45')


sorry for the change of mind.
(Most dim tables I've used have used integers extensively, I had this in my mind. )
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.