|
|
You're in the
sql server programming
group:tricky query?
sql server programming:
you need to use a calendar table, as follows: select * from calendar order by d d ------------------------------------------------------ 2006-01-01 00:00:00.000 2006-01-02 00:00:00.000 2006-01-03 00:00:00.000 2006-01-04 00:00:00.000 2006-01-05 00:00:00.000 (snip) search on aspfaq.com for a great explanation on setting up a calendar table. Having a calendar table, your query is a snap: insert into additions values('20060101', 100) insert into additions values('20060201', 100) insert into additions values('20060401', 100) insert into additions values('20060501', 100) insert into unloads values('20060315', 100) select d, (select count(*) from additions a where a.logdatetime < d and not exists( select 1 from unloads u where u.logdatetime between a.logdatetime and d ) ) cnt from calendar order by d d cnt ------------------------------------------------------ ----------- 2006-01-01 00:00:00.000 0 2006-01-02 00:00:00.000 1 2006-01-03 00:00:00.000 1 (snip) 2006-01-31 00:00:00.000 1 2006-02-01 00:00:00.000 1 2006-02-02 00:00:00.000 2 2006-02-03 00:00:00.000 2 (snip) 2006-03-13 00:00:00.000 2 2006-03-14 00:00:00.000 2 2006-03-15 00:00:00.000 0 2006-03-16 00:00:00.000 0 (snip) 2006-03-31 00:00:00.000 0 2006-04-01 00:00:00.000 0 2006-04-02 00:00:00.000 1 2006-04-03 00:00:00.000 1 (snip) 2006-04-30 00:00:00.000 1 2006-05-01 00:00:00.000 1 2006-05-02 00:00:00.000 2 2006-05-03 00:00:00.000 2 (snip)
Check out this information about using a Calendar table.
http://www.aspfaq.com/show.asp?id=2519 -- Arnie Rowland* "To be successful, your heart must accompany your knowledge." [quoted text, click to view] "Lisa Pearlson" <no@spam.plz> wrote in message news:%23rRQ8ZfpGHA.1600@TK2MSFTNGP04.phx.gbl... > Hi, > > Imagine I have 2 different tables, keeping a log of additions of items to > some container, and another that keeps a log of when those containers were > emptied. > > For example, simplified: > > CREATE TABLE additions ( > logdatetime DATETIME NOT NULL, > boxnumber INT NOT NULL > ); > > CREATE TABLE unloads ( > logdatetime DATETIME NOT NULL, > boxnumber INT NOT NULL > ); > > Imagine these represent a box that items are put in.. each time an item is > put into this box, a log is collected, and once in a while, the log, kept > by a hardware device, is sent to the database via a communications > interface. This means that in a short time, several INSERT's are made into > additions.. once in a while, the box is emptied, and this ends up being > logged in the second table.. explaining why these types of data are not > put in the same table, is a different story I do not with to elaborate on. > But there's a good enough reason for it. > > Now imagine the contents of these tables being something like this: > > table additions: > logdatetime boxnumber > 2006-01-01 12:00:00 100 > 2006-02-01 12:00:00 100 > 2006-04-01 12:00:00 100 > 2006-05-01 12:00:00 100 > > table unloads (empty events): > 2006-03-15 12:00:00 100 > > > So in january, february, april and may there were additions, but on march > the container was emptied. > In reality, there are several events every day.. what I need now is to > display for each day the total number of items in a a container. So I have > to add up the additions to know how many items there were at any > particular date, (including dates on which there were no events at all). > > So on May 10th, for example, there were 2 items . But on May 16 there were > 0, because on May 15, there was an unload event, that emptied the whole > container. > > How do I put this in a query? > I'm guessing first I join both tables in a SELECT .. UNION SELECT .. > statement, then sort it on logdate, so I have a chronological list of > events: > SELECT * FROM (SELECT logdatetime,boxnumber,'added' FROM additions > UNION > SELECT logdatetime,boxnumber,'flush' FROM unloads) x ORDER BY logdatetime > > logdatetime boxnumber type > 2006-01-01 12:00:00 100 added <-- box 100 has 1 item > 2006-02-01 12:00:00 100 added <-- box 100 has 2 items > 2006-03-15 12:00:00 100 flush <-- box 100 has 0 > items > 2006-04-01 12:00:00 100 added <-- box 100 has 1 item > 2006-05-01 12:00:00 100 added <-- box 100 has 2 items > > Now, I need to create a query that lists all items, as sums of previous > items (incremental) up until the previous flush: > > logdatetime boxnumber totals > 2006-01-01 12:00:00 100 1 > 2006-02-01 12:00:00 100 2 > 2006-03-15 12:00:00 100 0 > 2006-04-01 12:00:00 100 1 > 2006-05-01 12:00:00 100 2 > > Such query would already help me out a lot, but additionally, if possible, > I would like to be able to select ANY range of dates, and have a totals > displayed for EACH DAY.. whether actual records exist on this day or not. > > So, looking at period 2006-01-01 until 2006-01-07: > > logdata boxnumber totals > 2006-01-01 00:00:00 100 0 <-- prior to 12:00 > 2006-01-02 00:00:00 100 1 <-- since 2006-01-01 12:00:00 > 2006-01-03 00:00:00 100 1 > 2006-01-04 00:00:00 100 1 > 2006-01-05 00:00:00 100 1 > 2006-01-06 00:00:00 100 1 > 2006-01-07 00:00:00 100 1 > > > So, how do I incrementally count up the totals for all recent additions, > since the last flush? > > Lisa >
You need a calendar table. I also assumed the existince of a Boxes
table, one row for each boxnumber. This is an attempt to handle the "any range of dates" version. It is untested. SELECT C.CalDate as logdate, B.boxnumber, (select count(*) from additions as A where A.boxnumber = B.boxnumber and A.logdatetime <= C.CalDate and A.logdatetime > (select max(logdatetime) from unloads as U where U.boxnumber = B.boxnumber and U.A.logdatetime <= C.CalDate)) FROM Boxes as B CROSS JOIN Calendar as C WHERE C.CalDate between '1 Jan 2006' and '31 Mar 2006' Roy Harvey Beacon Falls, CT On Wed, 12 Jul 2006 23:03:54 +0200, "Lisa Pearlson" <no@spam.plz> [quoted text, click to view] wrote:
>Hi, > >Imagine I have 2 different tables, keeping a log of additions of items to >some container, and another that keeps a log of when those containers were >emptied. > >For example, simplified: > >CREATE TABLE additions ( >logdatetime DATETIME NOT NULL, >boxnumber INT NOT NULL >); > >CREATE TABLE unloads ( >logdatetime DATETIME NOT NULL, >boxnumber INT NOT NULL >); > >Imagine these represent a box that items are put in.. each time an item is >put into this box, a log is collected, and once in a while, the log, kept by >a hardware device, is sent to the database via a communications interface. >This means that in a short time, several INSERT's are made into additions.. >once in a while, the box is emptied, and this ends up being logged in the >second table.. explaining why these types of data are not put in the same >table, is a different story I do not with to elaborate on. But there's a >good enough reason for it. > >Now imagine the contents of these tables being something like this: > >table additions: >logdatetime boxnumber >2006-01-01 12:00:00 100 >2006-02-01 12:00:00 100 >2006-04-01 12:00:00 100 >2006-05-01 12:00:00 100 > >table unloads (empty events): >2006-03-15 12:00:00 100 > > >So in january, february, april and may there were additions, but on march >the container was emptied. >In reality, there are several events every day.. what I need now is to >display for each day the total number of items in a a container. So I have >to add up the additions to know how many items there were at any particular >date, (including dates on which there were no events at all). > >So on May 10th, for example, there were 2 items . But on May 16 there were >0, because on May 15, there was an unload event, that emptied the whole >container. > >How do I put this in a query? >I'm guessing first I join both tables in a SELECT .. UNION SELECT .. >statement, then sort it on logdate, so I have a chronological list of >events: >SELECT * FROM (SELECT logdatetime,boxnumber,'added' FROM additions >UNION >SELECT logdatetime,boxnumber,'flush' FROM unloads) x ORDER BY logdatetime > >logdatetime boxnumber type >2006-01-01 12:00:00 100 added <-- box 100 has 1 item >2006-02-01 12:00:00 100 added <-- box 100 has 2 items >2006-03-15 12:00:00 100 flush <-- box 100 has 0 items >2006-04-01 12:00:00 100 added <-- box 100 has 1 item >2006-05-01 12:00:00 100 added <-- box 100 has 2 items > >Now, I need to create a query that lists all items, as sums of previous >items (incremental) up until the previous flush: > >logdatetime boxnumber totals >2006-01-01 12:00:00 100 1 >2006-02-01 12:00:00 100 2 >2006-03-15 12:00:00 100 0 >2006-04-01 12:00:00 100 1 >2006-05-01 12:00:00 100 2 > >Such query would already help me out a lot, but additionally, if possible, I >would like to be able to select ANY range of dates, and have a totals >displayed for EACH DAY.. whether actual records exist on this day or not. > >So, looking at period 2006-01-01 until 2006-01-07: > >logdata boxnumber totals >2006-01-01 00:00:00 100 0 <-- prior to 12:00 >2006-01-02 00:00:00 100 1 <-- since 2006-01-01 12:00:00 >2006-01-03 00:00:00 100 1 >2006-01-04 00:00:00 100 1 >2006-01-05 00:00:00 100 1 >2006-01-06 00:00:00 100 1 >2006-01-07 00:00:00 100 1 > > >So, how do I incrementally count up the totals for all recent additions, >since the last flush? > >Lisa
Hi,
Juz in case you don't want to use a calender table and want the result to be generated for each of the boxnumber, you can try something like this (I have added a few more rows to include mutiple box numbers) /* insert into additions values('20060104', 200) insert into additions values('20060204', 200) insert into additions values('20060404', 200) insert into additions values('20060504', 200) insert into unloads values('20060320', 200) */ declare @start datetime, @end datetime set @start = '2006-01-01' set @end = '2006-05-02' declare @no_of_Days int set @no_of_days = datediff(dd,@start,@end) + 1 set rowcount @no_of_days select identity(int,0,1) as dy into #temp from sysobjects a, sysobjects b set rowcount 0 select boxnumber, @start + dy as chkdate, sum(case when logdatetime <= @start + dy then isadd else 0 end) from #temp, (select logdatetime,boxnumber, 1 as isadd from additions union all select logdatetime,boxnumber, -1 as isadd from unloads ) as b where @start + dy <= @end group by boxnumber, dy order by boxnumber,dy drop table #temp Hope this helps. -- -Omnibuzz (The SQL GC) http://omnibuzz-sql.blogspot.com/
Hi,
Imagine I have 2 different tables, keeping a log of additions of items to some container, and another that keeps a log of when those containers were emptied. For example, simplified: CREATE TABLE additions ( logdatetime DATETIME NOT NULL, boxnumber INT NOT NULL ); CREATE TABLE unloads ( logdatetime DATETIME NOT NULL, boxnumber INT NOT NULL ); Imagine these represent a box that items are put in.. each time an item is put into this box, a log is collected, and once in a while, the log, kept by a hardware device, is sent to the database via a communications interface. This means that in a short time, several INSERT's are made into additions.. once in a while, the box is emptied, and this ends up being logged in the second table.. explaining why these types of data are not put in the same table, is a different story I do not with to elaborate on. But there's a good enough reason for it. Now imagine the contents of these tables being something like this: table additions: logdatetime boxnumber 2006-01-01 12:00:00 100 2006-02-01 12:00:00 100 2006-04-01 12:00:00 100 2006-05-01 12:00:00 100 table unloads (empty events): 2006-03-15 12:00:00 100 So in january, february, april and may there were additions, but on march the container was emptied. In reality, there are several events every day.. what I need now is to display for each day the total number of items in a a container. So I have to add up the additions to know how many items there were at any particular date, (including dates on which there were no events at all). So on May 10th, for example, there were 2 items . But on May 16 there were 0, because on May 15, there was an unload event, that emptied the whole container. How do I put this in a query? I'm guessing first I join both tables in a SELECT .. UNION SELECT .. statement, then sort it on logdate, so I have a chronological list of events: SELECT * FROM (SELECT logdatetime,boxnumber,'added' FROM additions UNION SELECT logdatetime,boxnumber,'flush' FROM unloads) x ORDER BY logdatetime logdatetime boxnumber type 2006-01-01 12:00:00 100 added <-- box 100 has 1 item 2006-02-01 12:00:00 100 added <-- box 100 has 2 items 2006-03-15 12:00:00 100 flush <-- box 100 has 0 items 2006-04-01 12:00:00 100 added <-- box 100 has 1 item 2006-05-01 12:00:00 100 added <-- box 100 has 2 items Now, I need to create a query that lists all items, as sums of previous items (incremental) up until the previous flush: logdatetime boxnumber totals 2006-01-01 12:00:00 100 1 2006-02-01 12:00:00 100 2 2006-03-15 12:00:00 100 0 2006-04-01 12:00:00 100 1 2006-05-01 12:00:00 100 2 Such query would already help me out a lot, but additionally, if possible, I would like to be able to select ANY range of dates, and have a totals displayed for EACH DAY.. whether actual records exist on this day or not. So, looking at period 2006-01-01 until 2006-01-07: logdata boxnumber totals 2006-01-01 00:00:00 100 0 <-- prior to 12:00 2006-01-02 00:00:00 100 1 <-- since 2006-01-01 12:00:00 2006-01-03 00:00:00 100 1 2006-01-04 00:00:00 100 1 2006-01-05 00:00:00 100 1 2006-01-06 00:00:00 100 1 2006-01-07 00:00:00 100 1 So, how do I incrementally count up the totals for all recent additions, since the last flush? Lisa
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |