Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31280

Re: Assigning Display Lines to Date Ranges

Show all headers | View raw

Thank you for trying to do this right.Pleas e3learn thew ISO-11179 rules for data element names. Also, you need to read and download the PDF for: https:--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?: Tables have keys; where were you on day one of RDBMS basics? 

CREATE TABLE Tests
(test_start_date DATE NOT NULL PRIMARY KEY, 
 test_end_date DATE NOT NULL, 
 CHECK(test_start_date <= test_end_date), 
 expected_line_value INTEGER 
);

Please learn the current SQL syntax. Why did you force casting? 

INSERT INTO Tests
 VALUES 
('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);
 
>> Note that I also have a Date Dimension table (and an Integer Dimension table if it becomes needed) <<

WHY? Where?

>> I am trying to write a query to return the Line Number, as per the 
expected results listed in the table above. This line number is 
calculated as follows: <<

What is a "line number"? It sound likes a physical locator, but that would be soooo wrong. 

>> 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! 

>> 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? 

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 <= 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 ranges so that the table is fairly constant once it is created. 

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 (<<report name list>>)
 GROUP BY R.report_name;

The <<report name list>> would probably be events nested inside each other, like fiscal quarters inside a fiscal year.  While this is a useful and portable programming trick, you need to consider replacing it with the newer OLAP extensions to the GROUP BY clause such as ROLLUP and CUBE. 

Back to microsoft.public.sqlserver.programming | Previous | Next — Previous in thread | Find similar

Thread

Assigning Display Lines to Date Ranges Michael Cole <invalid@invalid.com> - 2015-11-12 11:25 +1100
  Re: Assigning Display Lines to Date Ranges Erland Sommarskog <esquel@sommarskog.se> - 2015-11-12 22:44 +0100
    Re: Assigning Display Lines to Date Ranges Michael Cole <invalid@invalid.com> - 2015-11-13 09:01 +1100
      Re: Assigning Display Lines to Date Ranges Erland Sommarskog <esquel@sommarskog.se> - 2015-11-13 14:12 +0100
        Re: Assigning Display Lines to Date Ranges Erland Sommarskog <esquel@sommarskog.se> - 2015-11-13 23:18 +0100
          Re: Assigning Display Lines to Date Ranges Michael Cole <invalid@invalid.com> - 2015-11-16 11:34 +1100
  Re: Assigning Display Lines to Date Ranges --CELKO-- <jcelko212@earthlink.net> - 2015-11-12 18:47 -0800

csiph-web