How do I assign the correct date ?

Question

How do I assign the correct date ?

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

chris.evans 94907 (9/12/2016)
Hi Alan,
Thanks for the reply.
The script you provided returns the following results:
calDate workingDay nextWorkingDay DtDIFF
12/09/2016 Y 12/09/2016 0
11/09/2016 N 12/09/2016 0
10/09/2016 N 12/09/2016 0
09/09/2016 Y 09/09/2016 3
08/09/2016 Y 08/09/2016 4
07/09/2016 Y 07/09/2016 5
06/09/2016 Y 06/09/2016 6
However, rather than an exact DATEDIFF I only need a count of workingDay = 'Y' so for calDate 09/09/2016 the DtDIFF value should be 1 rather than 3 and for calDate 08/09/2016 the DtDIFF value should be 2 rather than 4
Is this possible ?
Thanks again

Does changing RANK() to DENSE_RANK() give you what you need?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

chris.evans 94907 Old Hand Points: 300 More actions · Answer 2

chris.evans 94907

Old Hand

Points: 300

September 12, 2016 at 8:59 am

#1900341

Fantastic - that worked thanks Alan 🙂

Joe Celko SSCertifiable Points: 5749 More actions · Answer 3

>> I have a table of calendar dates with a Y/N flag to denote whether or not the date is a working day. <<

We do not like to write with bit flags in RDBMS and SQL; that was assembly language programming. Everybody has been telling you to use ISO 8601 date formats; this is really good advice because the only one allowed in ANSI/ISO standard SQL.

>> I wish to add a new column with the following criteria:

If the WorkingDay = 'Y' then I want to see the same CALDATE

If the WorkingDay = 'N' then I want to see the next CALDATE where the WorkingDay = 'Y' <<

Since this is subject to change, it is not a good idea to add a new column in physically materialized something that can be very easily computed.

CREATE TABLE Calendar

(cal_date DATE NOT NULL PRIMARY KEY,

ordinal_workday_nbr INTEGER NOT NULL);

The ordinal_workday is the sequential number over the entire table of that workday. Assuming that you take Saturday and Sunday, this would mean that those days in each week would have the same ordinal_workday_nbr as the preceding Friday. But it works for a block of any size holidays. Here is a made up example:

INSERT INTO Calendar

VALUES

('2016-08-19', 49),

('2016-08-18', 48),

('2016-08-17', 47),

('2016-08-16', 46),

('2016-08-15', 45),

('2016-08-14', 45),

('2016-08-13', 45),

..

Obviously, the number of workdays between two dates his computer with a simple subtraction. But likewise, the next working date after day (n) is day (n+1), and the previous work date is (n-1).

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

drew.allen SSC Guru Points: 76985 More actions · Answer 4

CELKO (9/13/2016)
We do not like to write with bit flags in RDBMS and SQL; that was assembly language programming.

This is T-SQL, not some idealized theoretical version of SQL. T-SQL does not have the Boolean data type that is required to implement your idealized version of SQL. If you don't like people using BIT data types, convince Microsoft to implement the Boolean data type, instead of beating up on people who are doing the best they can with the tools that they have.

Everybody has been telling you to use ISO 8601 date formats; this is really good advice because the only one allowed in ANSI/ISO standard SQL.

Actually, the SQL standards support TWO date formats, not one. 'YYYYMMDD hh:mm:ss' and 'YYYY-MM-DDThh:mm:ss'. (I'm not showing all the decimal places for the seconds.)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Joe Celko SSCertifiable Points: 5749 More actions · Answer 5

Actually, the SQL standards support TWO date formats, not one. 'YYYYMMDD hh:mm:ss' and 'YYYY-MM-DDThh:mm:ss'. (I'm not showing all the decimal places for the seconds.)

Nope. While the ISO 8601 standard has several different display formats, the ANSI/ISO standard SQL allows only one; "yyyy-mm-dd <space>hh:mm:ss" Notice the space and not the T separator. We did not we did not want options. I would actually like to see the T separator for the practical reason. This would let you write a timestamp as a single string of characters, without the spaces. But that is not how we voted.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL