Combined SQL Query

Databases
1

Is there a way of combing some additional sql into this query.

Each of the ‘guides’ can attend multiple sessions on a particular day. I want to count the number of unique days (which the query does not do), and also the total number of sessions for each guide overall, which the query below does do.

SELECT Count(sessions.Event_Date), sessions_link.Guide_Name, guide_details.Gift_Aid, guide_details.Post_Code FROM guide_details INNER JOIN sessions_link ON sessions_link.ID_Guide = guide_details.ID_Guide INNER JOIN sessions ON sessions_link.Session_ID = sessions.ID_Event WHERE guide_details.Gift_Aid = '1' AND sessions.Event_Date BETWEEN '2014-01-01' AND '2014-12-31' GROUP BY sessions_link.Guide_Name, guide_details.Gift_Aid, guide_details.Post_Code

This give me for example
31 Ceri 1 BS9
42 David 1 BS32
34 Mary 1 BS16

Another query ‘splits up’ the sessions, but for example I want the answer ‘17’ for the number of days as ‘Ceri’ did 17 unique days, on 14 days she did a second session.

SELECT guide_details.Gift_Aid, Count(sessions.Event_Date), sessions_link.Guide_Name, guide_details.Post_Code, sessions.Event_Session FROM guide_details INNER JOIN sessions_link ON sessions_link.ID_Guide = guide_details.ID_Guide INNER JOIN sessions ON sessions_link.Session_ID = sessions.ID_Event WHERE guide_details.Gift_Aid = '1' AND sessions.Event_Date BETWEEN '2014-01-01' AND '2014-12-31' GROUP BY sessions_link.Guide_Name, guide_details.Gift_Aid, guide_details.Post_Code, sessions.Event_Session

This gives me
1 17 Ceri BS9 10
1 14 Ceri BS9 13
1 21 David BS16 10
1 21 David BS16 13
1 18 Mary BS32 10
1 16 Mary BS32 13

From the above I would require for example;

1 17 Ceri BS9
1 21 David BS9
1 18 Mary BS32

Any pointers in the right direction in combining (if possible) the queries would be helpful.

Thanks in advance.

2

off topic to your initial question, but wanted to share :slight_smile:

for this code:

sessions.Event_Date BETWEEN '2014-01-01' AND '2014-12-31'

you should change to:

sessions.Event_Date BETWEEN '2014-01-01' AND '2014-12-31 23:59:59'

the reason being is that in your end date, you are not specifying time stamp in which the system by default will assign 00:00:00. When this happens, you are omitting the data that occurred on 2014-12-31.

if you did not want to supply time stamp, you could also change the end date to 2015-01-01 which would be translated as 2015-01-01 00:00:00

3

Rich, I am a little confused by that as when I just run the query as I have it (in Navicat) I get the 4 records that are on the last date specified as I would expect so it appears to be working as it is.

My data source is MySQL and the Event_Date field is a ‘date field’ as opposed to timestamp.

4

I am using MS SQL for this, but I believe this would carry over to MYSQL.

lets create a temp table of 500 rows and add a date stamp with an increment of 1 hour per row.

[code]create table #T_TMP_1 (r_id int, r_date datetime)

declare @startInt int, @endInt int
set @startInt = 1
set @endInt = 500

while (@startInt <= @endInt)
begin
insert into #T_TMP_1(r_id, r_date) values (@startInt, DATEADD(hour, @startInt, getdate()))
set @startInt = @startInt + 1
end[/code]

Now, if we get the newest temp table, it would be 2014-11-11

if we run the following query:

select CONVERT(char(8), r_date, 112), COUNT(1) [row_count] from #T_TMP_1 where r_date between '2014-01-01' and '2014-10-11' group by CONVERT(char(8), r_date, 112) order by 1 desc

you will see that the newest date is 2014-11-10

but if we run this code:

select CONVERT(char(8), r_date, 112), COUNT(1) [row_count] from #T_TMP_1 where r_date between '2014-01-01' and '2014-10-11 23:59:59' group by CONVERT(char(8), r_date, 112) order by 1 desc

newest date is 2014-11-11 with the group by counts.

5

grrr… very annoying I cant edit.

newest date should be 2014-10-11 and not 2014-11-11

6

ok… I understand what is happening.

because you are only inserting date, the system would assign 00:00:00 for time. The last date would appear because of the 00:00:00

if you ever did add the time value… you would need to add the 23:59:59 to the end date of the between statement.