{"action":"created","issue":{"url":"https://api.github.com/repos/ebmdatalab/prescribing-queries/issues/1","repository_url":"https://api.github.com/repos/ebmdatalab/prescribing-queries","labels_url":"https://api.github.com/repos/ebmdatalab/prescribing-queries/issues/1/labels{/name}","comments_url":"https://api.github.com/repos/ebmdatalab/prescribing-queries/issues/1/comments","events_url":"https://api.github.com/repos/ebmdatalab/prescribing-queries/issues/1/events","html_url":"https://github.com/ebmdatalab/prescribing-queries/issues/1","id":184158802,"number":1,"title":"Diabetes by year and CCG per list size","user":{"login":"sebbacon","id":211271,"avatar_url":"https://avatars.githubusercontent.com/u/211271?v=3","gravatar_id":"","url":"https://api.github.com/users/sebbacon","html_url":"https://github.com/sebbacon","followers_url":"https://api.github.com/users/sebbacon/followers","following_url":"https://api.github.com/users/sebbacon/following{/other_user}","gists_url":"https://api.github.com/users/sebbacon/gists{/gist_id}","starred_url":"https://api.github.com/users/sebbacon/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sebbacon/subscriptions","organizations_url":"https://api.github.com/users/sebbacon/orgs","repos_url":"https://api.github.com/users/sebbacon/repos","events_url":"https://api.github.com/users/sebbacon/events{/privacy}","received_events_url":"https://api.github.com/users/sebbacon/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":7,"created_at":"2016-10-20T07:55:32Z","updated_at":"2016-10-20T14:27:11Z","closed_at":null,"body":"> You have some very helpful data on the diabetes drug spend by CCG, but I wondered whether you had this available in an excel version rather than images? I’m particularly interested in the total 2015 and 2016 diabetes drug spend by CCG rather than monthly.\r\n>\r\n>  ...This is the link I was searching on the site https://openprescribing.net/analyse/#org=CCG&numIds=6.1&denom=total_list_size&selectedTab=summary\r\n"},"comment":{"url":"https://api.github.com/repos/ebmdatalab/prescribing-queries/issues/comments/255121480","html_url":"https://github.com/ebmdatalab/prescribing-queries/issues/1#issuecomment-255121480","issue_url":"https://api.github.com/repos/ebmdatalab/prescribing-queries/issues/1","id":255121480,"user":{"login":"lmfrench","id":15875191,"avatar_url":"https://avatars.githubusercontent.com/u/15875191?v=3","gravatar_id":"","url":"https://api.github.com/users/lmfrench","html_url":"https://github.com/lmfrench","followers_url":"https://api.github.com/users/lmfrench/followers","following_url":"https://api.github.com/users/lmfrench/following{/other_user}","gists_url":"https://api.github.com/users/lmfrench/gists{/gist_id}","starred_url":"https://api.github.com/users/lmfrench/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lmfrench/subscriptions","organizations_url":"https://api.github.com/users/lmfrench/orgs","repos_url":"https://api.github.com/users/lmfrench/repos","events_url":"https://api.github.com/users/lmfrench/events{/privacy}","received_events_url":"https://api.github.com/users/lmfrench/received_events","type":"User","site_admin":false},"created_at":"2016-10-20T14:27:11Z","updated_at":"2016-10-20T14:27:11Z","body":"We will need to extend the months if we want to include FY, I did try & clarify about whether she wanted FY to start with, and she did say calendar year\r\n\r\n```sql\r\nSELECT\r\n  pct AS CCG,\r\n  name AS CCG_Name,\r\n  SUM(items) AS items,\r\n  SUM(actual_cost) AS actual_cost\r\n  \r\n  \r\nFROM\r\n  ebmdatalab.hscic.prescribing AS presc\r\n  \r\nLEFT JOIN ebmdatalab.hscic.eccg AS eccg\r\n  ON (org_code=presc.pct)\r\n  \r\n  \r\n  WHERE\r\n (month BETWEEN TIMESTAMP('2015-01-01') AND TIMESTAMP('2015-12-31'))\r\n  \r\nGROUP BY\r\n CCG_Name,\r\n CCG\r\n \r\nORDER BY\r\n  CCG\r\n```\r\n\r\nShe also asked for total prescribing costs:\r\n```sql\r\nSELECT\r\n  pct AS CCG,\r\n  name AS CCG_Name,\r\n  SUM(items) AS items,\r\n  SUM(actual_cost) AS actual_cost\r\n  \r\n  \r\nFROM\r\n  ebmdatalab.hscic.prescribing AS presc\r\n  \r\nLEFT JOIN ebmdatalab.hscic.eccg AS eccg\r\n  ON (org_code=presc.pct)\r\n  \r\n  \r\n  WHERE\r\n (month BETWEEN TIMESTAMP('2015-01-01') AND TIMESTAMP('2015-12-31'))\r\n  \r\nGROUP BY\r\n CCG_Name,\r\n CCG\r\n \r\nORDER BY\r\n  CCG\r\n```\r\n"}}
{"actor":{"display_login":"lmfrench"}}