How can I cross join the following query results with a table of dates

Question

I am looking for a query which gives me the daily playing time. The start (first_date) and end date(last_update) are given as shown in the Table. The following query gives me the sum of playing time on given date. How can I extend it to get a table from first day to last day and plot the query data in it and show 0 on dates when no game is played.

SELECT startTime, SUM(duration) as sum
FROM myTable
WHERE startTime = endTime
GROUP BY startTime

Your query would not appear to do what you claim. – Gordon Linoff Aug 25 '15 at 22:48 — Gordon Linoff, Aug 25 '15 at 22:48
I edited my question, is it more clear now? – Dr. Mian Aug 25 '15 at 22:57 — Dr. Mian, Aug 25 '15 at 22:57

score 2 · Answer 1 · edited May 23 '17 at 12:22

2

To show date when no one play you will need create a table days with a date field day so you could do a left join. (100 years is only 36500 rows).

Using select Generate days from date range

This use store procedure in MSQL

I will assume if a play pass the midnight a new record begin. So I could simplify my code and remove the time from datetime field

SELECT d.day, SUM(duration) as sum
FROM 
    days d
    left join myTable m
         on CONVERT(date, m.starttime) = d.day
GROUP BY d.day

edited May 23 '17 at 12:22

Community

1
1

answered Aug 25 '15 at 23:10

Juan Carlos Oropeza

47,252
12
78
118

I am new to sql server, can you please complete the query for me? – Dr. Mian Aug 26 '15 at 10:16
what part? What about my assumption if a game pass midnight how is that record created? – Juan Carlos Oropeza Aug 26 '15 at 13:16
Unfortunately, the midnight is not marked. so if a game starts at 11pm then it might finish at 1am. Day have to be manually calculated. Part) getting the day table in your query – Dr. Mian Aug 26 '15 at 13:19
And what part you need help? Can you prepare a small [SQLFiddle](http://sqlfiddle.com/#!3/5c2bd/3) with your data for me? And I try to write the query you need later this day – Juan Carlos Oropeza Aug 26 '15 at 13:25
Can you please check here http://stackoverflow.com/questions/32228349/how-to-make-time-per-day-from-variable-segments-of-tracktime-in-sql-server – Dr. Mian Aug 26 '15 at 13:43
What should I check there? Will you post the sqlFiddle? – Juan Carlos Oropeza Aug 26 '15 at 14:57
Using [Generate days from date range](http://stackoverflow.com/questions/2157282/generate-days-from-date-range) you could just use the subquery rather than making a separate table. Though, that would slightly negatively would impact performance, and it would make the query more complicated. – Reed Aug 26 '15 at 15:33
1

@Jakar I just give him the options. But you are right creating the table is the best performance. And I said 35000 rows in a table is just pennies. – Juan Carlos Oropeza Aug 26 '15 at 15:36

score 0 · Answer 2 · answered Aug 25 '15 at 23:09

0

If I understand correctly, you could try:

SELECT SUM(duration) AS duration, date
FROM myTable
WHERE date <= 20140430 
    AND date => 20140401
GROUP BY date

This would get the total time played for each date between april 1 and april 30
As far as showing 0 for dates not in the table, I don't know.

Also, the table you posted doesn't show a duration column, but the query you posted does, so I went ahead and used it.

answered Aug 25 '15 at 23:09

Reed

14,703
8
66
110

Picture updated. Yes, but I want to display all the dates and 0 for dates on which no game is played. – Dr. Mian Aug 26 '15 at 10:49

How can I cross join the following query results with a table of dates

2 Answers2