tiny bit of help needed with report

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

tiny bit of help needed with report

Lisa Peel
Hello

I apologise for the simplicity of this question but could really use some help.

I want to use the Hourly report in the KOHA wiki for ‘Checkouts by Hour in Selected Date Range’ as below

SELECT YEAR(statistics.datetime) AS Year, MONTH(statistics.datetime) AS Month, DAY(statistics.datetime) AS Day, HOUR(statistics.datetime) AS Hour,
count(*) AS Checkouts
FROM statistics
WHERE statistics.datetime BETWEEN '2014-01-06 13:00:59' AND '2014-01-09 16:59:59'
AND statistics.type = 'issue'
GROUP BY Year, Month, Day, Hour

However, I also need to add branch code as well so I can determine the number of checkouts by library but I am struggling to know what extra code to add? My knowledge of SQL is very, very limited and any help would be really appreciated

Regards

Lisa

Mrs Lisa Peel
Head of Learning Resources, SEG Services
Swiss Education Group
________
Avenue des Alpes 62
CH-1820 Montreux, Switzerland
Mobile: +41 (0)79 228 0151<tel://+41(0)792280151>
Skype: biblio1897<skype://biblio1897>
www.swisseducation.com<https://www.swisseducation.com/en/>
Facebook<https://www.facebook.com/swisseducationgroup> | Instagram<https://www.instagram.com/swisseducationgroup/> | Linkedin<https://www.linkedin.com/company/seg-swiss-education-group>

[http://signature.swisseducation.com/SEG2019.jpg]<https://www.swisseducation.com/en/>

Confidentiality and Disclaimer:
This message contains confidential information and is intended only for the individual named.
Email messages are provided for informational purposes, therefore the sender does not accept
liability for any errors or omissions in the contents of this message.
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: tiny bit of help needed with report

rogan
Hi Lisa,

If you're just looking to use the branch column in statistics you can add
it to your select list and then to the group by.  So the select line
would become something like this:

SELECT YEAR(statistics.datetime) AS Year, MONTH(statistics.datetime) AS
Month, DAY(statistics.datetime) AS Day, HOUR(statistics.datetime) AS Hour,
branch, count(*) AS Checkouts
FROM statistics
WHERE statistics.datetime BETWEEN '2014-01-06 13:00:59' AND '2014-01-09
16:59:59'
AND statistics.type = 'issue'
GROUP BY 1, 2, 3, 4;



On Fri, May 15, 2020 at 9:48 AM Lisa Peel <[hidden email]> wrote:

> Hello
>
> I apologise for the simplicity of this question but could really use some
> help.
>
> I want to use the Hourly report in the KOHA wiki for ‘Checkouts by Hour in
> Selected Date Range’ as below
>
> SELECT YEAR(statistics.datetime) AS Year, MONTH(statistics.datetime) AS
> Month, DAY(statistics.datetime) AS Day, HOUR(statistics.datetime) AS Hour,
> count(*) AS Checkouts
> FROM statistics
> WHERE statistics.datetime BETWEEN '2014-01-06 13:00:59' AND '2014-01-09
> 16:59:59'
> AND statistics.type = 'issue'
> GROUP BY Year, Month, Day, Hour
>
> However, I also need to add branch code as well so I can determine the
> number of checkouts by library but I am struggling to know what extra code
> to add? My knowledge of SQL is very, very limited and any help would be
> really appreciated
>
> Regards
>
> Lisa
>
> Mrs Lisa Peel
> Head of Learning Resources, SEG Services
> Swiss Education Group
> ________
> Avenue des Alpes 62
> CH-1820 Montreux, Switzerland
> Mobile: +41 (0)79 228 0151<tel://+41(0)792280151>
> Skype: biblio1897<skype://biblio1897>
> www.swisseducation.com<https://www.swisseducation.com/en/>
> Facebook<https://www.facebook.com/swisseducationgroup> | Instagram<
> https://www.instagram.com/swisseducationgroup/> | Linkedin<
> https://www.linkedin.com/company/seg-swiss-education-group>
>
> [http://signature.swisseducation.com/SEG2019.jpg]<
> https://www.swisseducation.com/en/>
>
> Confidentiality and Disclaimer:
> This message contains confidential information and is intended only for
> the individual named.
> Email messages are provided for informational purposes, therefore the
> sender does not accept
> liability for any errors or omissions in the contents of this message.
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> [hidden email]
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: tiny bit of help needed with report

Lisa Peel
Thanks Rogan, that was perfect!!

Lisa

Mrs Lisa Peel
Head of Learning Resources, SEG Services
Swiss Education Group
________
Avenue des Alpes 62
CH-1820 Montreux, Switzerland
Mobile: +41 (0)79 228 0151<tel://+41(0)792280151>
Skype: biblio1897<skype://biblio1897>
www.swisseducation.com<https://www.swisseducation.com/en/>
Facebook<https://www.facebook.com/swisseducationgroup> | Instagram<https://www.instagram.com/swisseducationgroup/> | Linkedin<https://www.linkedin.com/company/seg-swiss-education-group>

[http://signature.swisseducation.com/SEG2019.jpg]<https://www.swisseducation.com/en/>

Confidentiality and Disclaimer:
This message contains confidential information and is intended only for the individual named.
Email messages are provided for informational purposes, therefore the sender does not accept
liability for any errors or omissions in the contents of this message.

From: "[hidden email]" <[hidden email]>
Date: Friday, 15 May 2020 at 16:13
To: Lisa Peel <[hidden email]>
Cc: "[hidden email]" <[hidden email]>
Subject: Re: [Koha] tiny bit of help needed with report

Hi Lisa,

If you're just looking to use the branch column in statistics you can add it to your select list and then to the group by.  So the select line would become something like this:

SELECT YEAR(statistics.datetime) AS Year, MONTH(statistics.datetime) AS Month, DAY(statistics.datetime) AS Day, HOUR(statistics.datetime) AS Hour,
branch, count(*) AS Checkouts
FROM statistics
WHERE statistics.datetime BETWEEN '2014-01-06 13:00:59' AND '2014-01-09 16:59:59'
AND statistics.type = 'issue'
GROUP BY 1, 2, 3, 4;



On Fri, May 15, 2020 at 9:48 AM Lisa Peel <[hidden email]<mailto:[hidden email]>> wrote:
Hello

I apologise for the simplicity of this question but could really use some help.

I want to use the Hourly report in the KOHA wiki for ‘Checkouts by Hour in Selected Date Range’ as below

SELECT YEAR(statistics.datetime) AS Year, MONTH(statistics.datetime) AS Month, DAY(statistics.datetime) AS Day, HOUR(statistics.datetime) AS Hour,
count(*) AS Checkouts
FROM statistics
WHERE statistics.datetime BETWEEN '2014-01-06 13:00:59' AND '2014-01-09 16:59:59'
AND statistics.type = 'issue'
GROUP BY Year, Month, Day, Hour

However, I also need to add branch code as well so I can determine the number of checkouts by library but I am struggling to know what extra code to add? My knowledge of SQL is very, very limited and any help would be really appreciated

Regards

Lisa

Mrs Lisa Peel
Head of Learning Resources, SEG Services
Swiss Education Group
________
Avenue des Alpes 62
CH-1820 Montreux, Switzerland
Mobile: +41 (0)79 228 0151<tel://+41(0)792280151>
Skype: biblio1897<skype://biblio1897>
www.swisseducation.com<http://www.swisseducation.com><https://www.swisseducation.com/en/>
Facebook<https://www.facebook.com/swisseducationgroup> | Instagram<https://www.instagram.com/swisseducationgroup/> | Linkedin<https://www.linkedin.com/company/seg-swiss-education-group>

[http://signature.swisseducation.com/SEG2019.jpg]<https://www.swisseducation.com/en/>

Confidentiality and Disclaimer:
This message contains confidential information and is intended only for the individual named.
Email messages are provided for informational purposes, therefore the sender does not accept
liability for any errors or omissions in the contents of this message.
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]<mailto:[hidden email]>
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: tiny bit of help needed with report

vikram zadgaonkar-2
In reply to this post by Lisa Peel
Hello Lisa,
You may try following:

SELECT YEAR(statistics.datetime) AS Year, MONTH(statistics.datetime) AS
Month, DAY(statistics.datetime) AS Day, HOUR(statistics.datetime) AS Hour,
count(*) AS Checkouts
FROM statistics
left join items on statistics.itemnumber=items.itemnumber
WHERE statistics.datetime BETWEEN <<From date|date>> AND <<To date|date>>
AND statistics.type = 'issue' and items.holdingbranch=<<Branch|branches>>
GROUP BY Year, Month, Day, Hour
Vikram Zadgaonkar


On Fri, May 15, 2020 at 7:18 PM Lisa Peel <[hidden email]> wrote:

> Hello
>
> I apologise for the simplicity of this question but could really use some
> help.
>
> I want to use the Hourly report in the KOHA wiki for ‘Checkouts by Hour in
> Selected Date Range’ as below
>
> SELECT YEAR(statistics.datetime) AS Year, MONTH(statistics.datetime) AS
> Month, DAY(statistics.datetime) AS Day, HOUR(statistics.datetime) AS Hour,
> count(*) AS Checkouts
> FROM statistics
> WHERE statistics.datetime BETWEEN '2014-01-06 13:00:59' AND '2014-01-09
> 16:59:59'
> AND statistics.type = 'issue'
> GROUP BY Year, Month, Day, Hour
>
> However, I also need to add branch code as well so I can determine the
> number of checkouts by library but I am struggling to know what extra code
> to add? My knowledge of SQL is very, very limited and any help would be
> really appreciated
>
> Regards
>
> Lisa
>
> Mrs Lisa Peel
> Head of Learning Resources, SEG Services
> Swiss Education Group
> ________
> Avenue des Alpes 62
> CH-1820 Montreux, Switzerland
> Mobile: +41 (0)79 228 0151<tel://+41(0)792280151>
> Skype: biblio1897<skype://biblio1897>
> www.swisseducation.com<https://www.swisseducation.com/en/>
> Facebook<https://www.facebook.com/swisseducationgroup> | Instagram<
> https://www.instagram.com/swisseducationgroup/> | Linkedin<
> https://www.linkedin.com/company/seg-swiss-education-group>
>
> [http://signature.swisseducation.com/SEG2019.jpg]<
> https://www.swisseducation.com/en/>
>
> Confidentiality and Disclaimer:
> This message contains confidential information and is intended only for
> the individual named.
> Email messages are provided for informational purposes, therefore the
> sender does not accept
> liability for any errors or omissions in the contents of this message.
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> [hidden email]
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha