X-Received: by 10.13.194.197 with SMTP id e188mr15970628ywd.24.1447382836238; Thu, 12 Nov 2015 18:47:16 -0800 (PST) X-Received: by 10.50.97.38 with SMTP id dx6mr10457igb.9.1447382836134; Thu, 12 Nov 2015 18:47:16 -0800 (PST) Path: csiph.com!au2pb.net!usenet.blueworldhosting.com!feeder01.blueworldhosting.com!peer02.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!border1.nntp.dca1.giganews.com!nntp.giganews.com!i2no1156658igv.0!news-out.google.com!l1ni2434igd.0!nntp.google.com!i2no1156657igv.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: microsoft.public.sqlserver.programming Date: Thu, 12 Nov 2015 18:47:15 -0800 (PST) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=2602:306:bcf4:c240:ad32:4118:4cfc:5a2f; posting-account=eTE9_AoAAAD1dS9O9Ccywd_vfKFzS40A NNTP-Posting-Host: 2602:306:bcf4:c240:ad32:4118:4cfc:5a2f References: User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: Subject: Re: Assigning Display Lines to Date Ranges From: --CELKO-- Injection-Date: Fri, 13 Nov 2015 02:47:16 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Lines: 85 X-Received-Bytes: 4499 X-Received-Body-CRC: 4246927499 Xref: csiph.com microsoft.public.sqlserver.programming:31280 Thank you for trying to do this right.Pleas e3learn thew ISO-11179 rules fo= r data element names. Also, you need to read and download the PDF for: http= s:--www.simple-talk.com-books-sql-books-119-sql-code-smells- We have no key, no constraints, etc. Is this what you were try to say?: Tab= les have keys; where were you on day one of RDBMS basics?=20 CREATE TABLE Tests (test_start_date DATE NOT NULL PRIMARY KEY,=20 test_end_date DATE NOT NULL,=20 CHECK(test_start_date <=3D test_end_date),=20 expected_line_value INTEGER=20 ); Please learn the current SQL syntax. Why did you force casting?=20 INSERT INTO Tests VALUES=20 ('2015-01-01', '2015-01-10', 1), ('2015-01-12', '2015-01-16', 1), ('2015-01-15', '2015-01-20', 2), ('2015-01-18', '2015-01-24', 1), ('2015-01-25', '2015-01-30', 1), ('2015-02-01', '2015-02-07', 1), ('2015-02-03', '2015-02-08', 2), ('2015-02-06', '2015-02-12', 3), ('2015-02-11', '2015-02-20', 1), ('2015-02-16', '2015-02-24', 2); =20 >> Note that I also have a Date Dimension table (and an Integer Dimension t= able if it becomes needed) << WHY? Where? >> I am trying to write a query to return the Line Number, as per the=20 expected results listed in the table above. This line number is=20 calculated as follows: << What is a "line number"? It sound likes a physical locator, but that would = be soooo wrong.=20 >> For each date range, in sequence of the test_start_date to test_end_date= , assign the minimum line number such that it does not have the same line = number as any prior date range for a date within the respective ranges. << unh? >>> If that doesn't make sense, I'll try to illustrate using the above data= . << Why did you fail to use ISO-8601 dates!? It is bitch for your free labor to= re-type your postings for you!=20 >> Note - all dates in dd/m format. << Gee, you should follow ISO-8601 rules for displaying temporal data (https:/= /xkcd.com/1179/). Why are you soooo special?=20 CREATE TABLE ReportRanges (report_name VARCHAR(30) NOT NULL PRIMARY KEY, report_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, report_end_date DATE NOT NULL, CONSTRAINT date_ordering CHECK (report_start_date <=3D report_end_date), etc); These reports can overlap; a fiscal quarter will be contained in the range = of its fiscal year. There can be gaps between them; we have to wait a year= between each "Annual Going out Of Business Sale!" and there might be long = stretches of time without any special sales. But we want to know their ran= ges so that the table is fairly constant once it is created.=20 The basic skeleton for use with these tables is SELECT R.report_name, << summary computations >> FROM ReportRanges AS R, [Events] AS E WHERE E.event_date BETWEEN R.report_start_date AND report_end_date AND R.report_name IN (<>) GROUP BY R.report_name; The <> would probably be events nested inside each other,= like fiscal quarters inside a fiscal year. While this is a useful and por= table programming trick, you need to consider replacing it with the newer O= LAP extensions to the GROUP BY clause such as ROLLUP and CUBE.=20