Access SQL Rolling 12 months Distinct Count -


i have 2 tables in ms access, 1 dates list of months , years, eg:

monthnumber yearnumber 1 2013 2 2013 3 2013

the second table bedfile lists clients namecode, date, month, , year of stay, eg:

namecode date month year bob 1/1/2013 1 2013 joe 1/1/2013 1 2013 bob 1/2/2013 1 2013 joe 1/2/2013 1 2013 eric 1/2/2013 1 2013 bob 2/15/2013 2 2013 joe 2/16/2013 2 2013 bob 2/1/2014 2 2014 joe 2/1/2014 2 2014

what i'm looking for

i'm trying make query counts distinct namecodes twelve months prior, eg:

monthresults yearresults countresults 1 2013 3 2 2013 3 2 2014 2

this rolling query, meaning results column display count of namecodes found within 12 months prior.

my first sql attempt is:

select dates.monthnumber, dates.yearnumber, ( select count(a.namecode) (select distinct bedfile.namecode bedfile bedfile.date between dateserial(dates.yearnumber-1, dates.monthnumber, datepart("d", bedfile.date)) , dateserial(dates.yearnumber, dates.monthnumber, datepart("d", bedfile.date)) ) ) dates group dates.yearnumber, dates.monthnumber

when run this, asks me dates.yearnumber , dates.monthnumer are. if input numbers (say, 2013 , 1 respectively) gives me distinct count month.

my second attempt this:

select count(a.namecode), dates.monthnumber, dates.yearnumber (select distinct bedfile.namecode, bedfile.month, bedfile.year bedfile bedfile.date between dateadd("yyyy", -1, bedfile.date) , bedfile.date ) inner join dates on (a.month=dates.monthnumber) , (a.year=dates.yearnumber) group dates.yearnumber, dates.monthnumber;

this second query gives me distinct counts, each month, rather sum of previous 12.

does know how me create i'm looking for? possible in access sql query?

select     first_date,     second_date,     count(name) num_distinct,     name from(     select          distinct b.namecode name,         b.date first_date,         dateadd("yyyy",-1,[b.date]) second_date     bedfile b     b.date between dateadd("yyyy",-1,[b.date]) , b.date ) group name

not sure if correct since i've never used ms access sql seems work

edit:

you said query

select count(a.namecode), dates.monthnumber, dates.yearnumber     (select distinct bedfile.namecode, bedfile.month, bedfile.year     bedfile     bedfile.date     between dateadd("yyyy", -1, bedfile.date) , bedfile.date    )   inner join dates  on (a.month=dates.monthnumber) , (a.year=dates.yearnumber) group dates.yearnumber, dates.monthnumber;

returns distinct count per month why not wrap select selects sum() of count sum per year? another thing can remove group dates.monthnumber.... because going split data month instead of showing year.

i recommend grouping year it's less sql write , easier read.


Comments

Post a Comment

Popular posts from this blog

php - SPIP: From Tag directly to an article -

i work spip , plugin called tagsphere works fine tags in spip. have tag groupe put 1 article tag. when click on tag in tagsphere send me before menu can see articles linked tag. want send me directly article without see tag menu before. here code model of thagsphere: <div id="tagsphere-#env{id_article}"> <ul> <boucle_mot(mots){id_groupe ?}> <li>[<a href="#url_mot">(#titre)</a>]</li> </boucle_mot> </ul> </div> anybody idea? if want skip tag page, have link directly article. <b_article> block prevents display tag if no article linked. <div id="tagsphere-#env{id_article}"> <ul> <boucle_mot(mots){id_groupe ?}> <b_article> <li> <a<boucle_article(articles){...
Read more

jquery - isAjaxRequest always return false -

i got error message 404,'model has not been saved' following code my action controller public function actionextend($id) { $model=$this->loadmodel($id); if(yii::app()->request->isajaxrequest) { $model->todate = date('y-m-d h:i:s a',(strtotime('next month',strtotime(date('y-01-d'))))); if($model->save()) { yii::app()->end(); } else { throw new chttpexception(404,'model has not been saved'); } } } the jquery call $("#buttonextend<?php echo $this->post_row;?>").click(function(e){ $.ajax({ type: "post", url: "<?php echo yii::app()->createurl('post/extend', array('id' => $data['id'])); ?>", }); }); why not ajax call ? thank help i found response, missed } , function ...
Read more

ruby on rails - In a controller spec, how to find a specific tag in the generated view? -

i know integration tests preferred need ran in controller test, i'm testing gem injecting html code in view, xhr can't run in feature spec (if can, please explain me how :) ) so rspec controller tests can assert selector present (with capybara) : response.body.should have_selector('#foobar') has_selector? call all method capybara find selector. what want last child of body , assert id in particular. afaik it's not possible have_selector. what : all('body:first-child').first.id.should == '#foobar' however, capybara dsl, defined (more or less): def all(*args) page.all(*args) end and page empty unless use visit it's integrations specs. how can use capybara all method inside rspec controller test ? i can't test right after googling seems trick def page capybara::node::simple.new(response.body) end source
Read more