I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:
PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
<...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
<...>
200602 2005-12-18 75
CREATE TABLE "dbo"."tblDates "
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)
INSERT INTO tblDates VALUES('200505' ,2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602' ,2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603' ,2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604' ,2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605' ,2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702' ,2006-07-05,2006-10-02,2006-12-18) 7 8395
rcamarda (ro*****@hotmai l.com) writes:
I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:
PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87 <...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1 <...>
200602 2005-12-18 75
CREATE TABLE "dbo"."tblDates "
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)
INSERT INTO tblDates VALUES('200505' ,2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602' ,2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603' ,2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604' ,2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605' ,2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702' ,2006-07-05,2006-10-02,2006-12-18)
Thanks for posting table definition and data. However, I would appreciate
if you also tested your repro script before you post. I was puzzled not
getting any rows back first from my query, but then I realised that
2005-04-12 2005-09-12. (Run the above folliwed by a SELECT on the
table to see why.)
Anyway, as I said in another newsgroup, you need a table of numbers. Here
is a way to create such a table with a million numbers:
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0
Given this table, we can write this query:
SELECT d.PERIOD, dateadd(DAY, n.Number - 1, d.START_DT),
datediff(DAY, d.MID_DT, dateadd(DAY, n.Number - 1, d.START_DT))
FROM tblDates d
CROSS JOIN Numbers n
WHERE dateadd(DAY, n.Number - 1, d.START_DT)
BETWEEN d.START_DT AND d.END_DT
ORDER BY d.PERIOD, 2
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Erland,
Sorry about the insert, i see that its returning what I did not expect.
Originally I had the dates quoted, but sql bawked at that. I've been
trying to fix the insert, but after trying cast and convert, it still
wont go.
This gives error about conversion:
INSERT INTO tblDates VALUES ( '200602',cast(' 2005-07-06' AS
DATETIME),CAST( '2005-10-03' AS DATETIME), CAST('2005-12-18' AS
DATETIME))
AH! Finally got this to work:
INSERT INTO tblDates VALUES ('200505' ,convert(dateti me,
'2005-04-12'),convert(da tetime,'2005-07-05'),
convert(datetim e,'2005-09-12' ))
Im still trying to grasp the use of the numbers table. I have a D_Day
table that is the days from 1900 - 2100. Could that be used somehow?
(1900-01-01 has a surrogate key of 1 and 1900-01-02 is 2 and so forth)
You solution works, which I am appreciative of, tho it will take me
working with the code to figure out why :)
Thanks for teaching me something new!
Rob
Erland Sommarskog wrote:
rcamarda (ro*****@hotmai l.com) writes:
I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:
PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
<...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
<...>
200602 2005-12-18 75
CREATE TABLE "dbo"."tblDates "
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)
INSERT INTO tblDates VALUES('200505' ,2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602' ,2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603' ,2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604' ,2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605' ,2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702' ,2006-07-05,2006-10-02,2006-12-18)
Thanks for posting table definition and data. However, I would appreciate
if you also tested your repro script before you post. I was puzzled not
getting any rows back first from my query, but then I realised that
2005-04-12 2005-09-12. (Run the above folliwed by a SELECT on the
table to see why.)
Anyway, as I said in another newsgroup, you need a table of numbers. Here
is a way to create such a table with a million numbers:
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0
Given this table, we can write this query:
SELECT d.PERIOD, dateadd(DAY, n.Number - 1, d.START_DT),
datediff(DAY, d.MID_DT, dateadd(DAY, n.Number - 1, d.START_DT))
FROM tblDates d
CROSS JOIN Numbers n
WHERE dateadd(DAY, n.Number - 1, d.START_DT)
BETWEEN d.START_DT AND d.END_DT
ORDER BY d.PERIOD, 2
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
rcamarda (ro*****@hotmai l.com) writes:
Sorry about the insert, i see that its returning what I did not expect.
Originally I had the dates quoted, but sql bawked at that. I've been
trying to fix the insert, but after trying cast and convert, it still
wont go.
This gives error about conversion:
INSERT INTO tblDates VALUES ( '200602',cast(' 2005-07-06' AS
Yes, the above format could fail. There are three date formats in SQL
Server that are safe:
YYYYMMDD
YYYYMMDDTHH:MM: SS[.fff]
YYYY-MM-DDZ
Here T and Z stand for themselves.
Other formats are interpretated depending on DATEFORMAT and LANGUAGE
setting, and can fail or produced unexpected results if you don't know
what is going on.
Im still trying to grasp the use of the numbers table. I have a D_Day
table that is the days from 1900 - 2100. Could that be used somehow?
(1900-01-01 has a surrogate key of 1 and 1900-01-02 is 2 and so forth)
Yes, that dates table is essentially a table of numbers with a different
names. In fact, it appears that it has all the numbers as well!
I used a table of numbers, as numbers is the more general concept and
can be used in more places. But in fact, I added a table of dates to
our system before I added a table of numbers.
I leave it as an exercise to you how to use the dates table instead.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
On Sun, 20 Aug 2006 12:38:11 +0000 (UTC), Erland Sommarskog
<es****@sommars kog.sewrote:
>rcamarda (ro*****@hotmai l.com) writes:
>I wish to build a table based on values from another table. I need to populate a table between two dates from another table. Using the START_DT and END_DT, create records between those dates. I need a new column that is the days between the date and the MID_DT The data I wish to end with would look something like this:
PERIOD DATE DAY_NO 200602 2005-07-06 -89 200602 2005-07-07 -88 200602 2005-07-08 -87 <...> 200602 2005-10-02 -2 200602 2005-10-03 -1 200602 2005-10-04 0 200602 2005-10-05 1 <...> 200602 2005-12-18 75
[snip]
>INSERT INTO tblDates VALUES('200602' ,2005-07-06,2005-10-03,2005-12-18)
>Anyway, as I said in another newsgroup, you need a table of numbers. Here is a way to create such a table with a million numbers:
What are the pros and cons of relying on such a table vs. using a
WHILE loop? Based on Rob's context of student registrations, let's
assume we're talking about a maximum of 300 iterations per row in
the original tblDates table.
Ed Murphy (em*******@soca l.rr.com) writes:
What are the pros and cons of relying on such a table vs. using a
WHILE loop? Based on Rob's context of student registrations, let's
assume we're talking about a maximum of 300 iterations per row in
the original tblDates table.
The one risk with a table of numbers is that if you run of numbers, you
will get an incorrect result. That is one reason why I'm reluctant to
use it, if there are alternative solutions. But for a case like this,
when you need to fill up a space, a table of numbers - or dates - is what
you need.
A loop is more complex to program, and easier go wrong. And as a generic
solution, you face scalability problems.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
On Mon, 21 Aug 2006 08:05:49 +0000 (UTC), Erland Sommarskog
<es****@sommars kog.sewrote:
>Ed Murphy (em*******@soca l.rr.com) writes:
>What are the pros and cons of relying on such a table vs. using a WHILE loop? Based on Rob's context of student registrations, let's assume we're talking about a maximum of 300 iterations per row in the original tblDates table.
The one risk with a table of numbers is that if you run of numbers, you will get an incorrect result. That is one reason why I'm reluctant to use it, if there are alternative solutions. But for a case like this, when you need to fill up a space, a table of numbers - or dates - is what you need.
A loop is more complex to program, and easier go wrong.
I disagree, but then I have somewhat more experience with imperative
than functional programming. Consider:
x = first_date
while x <= last_date
insert x, datediff(x, mid_date) into <table>
x = dateadd(x, 1)
end while
versus
select dateadd(first_d ate, n), n - datediff(mid_da te, first_date)
into <table>
from numbers
where n between 0 and datediff(end_da te, first_date)
Okay, "where n between <limits>" makes sense as an analogue to a while
loop, but that stuff in line 1 looks like the stuff of headaches.
And as a generic solution, you face scalability problems.
I kind of figured. The query seems easy to get wrong, though, if
you're not familiar with the pattern; I first wrote it as "where
dateadd(first_d ate, n) between first_date and last_date", but that
seems like it'd be a good bit slower.
Ed Murphy (em*******@soca l.rr.com) writes:
I disagree, but then I have somewhat more experience with imperative
than functional programming. Consider:
x = first_date
while x <= last_date
insert x, datediff(x, mid_date) into <table>
x = dateadd(x, 1)
end while
versus
select dateadd(first_d ate, n), n - datediff(mid_da te, first_date)
into <table>
from numbers
where n between 0 and datediff(end_da te, first_date)
Okay, "where n between <limits>" makes sense as an analogue to a while
loop, but that stuff in line 1 looks like the stuff of headaches.
Loops are particularly prone to two sorts of errors:
* They goes on forever, could be because of a sloppy mistake, of because the
logic is complicated.
* One-off errors because of incorrect loop conditions.
One-off errors are easy to make with set-based queries as well, but the
risk of infinite loops is nothing you have to lose sleep over.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Thomas R. Hummel |
last post by:
Hello all,
I am trying to write a query that compares a member's enrollment period
with the products that their group has had during that period (all
members belong to a group and the products that the member has are
based on that group). I need to get the date range for all products
that the member had during their enrollment.
Here are a few rules:
- In the source table there are some group products that have two
|
by: fak |
last post by:
I have a workorder entry form. There is a table that contains
workorder information and another table that contains work dates that
are closed (fully booked). When the workorder form is being filled
in, I need the workorder date field to check if it is a "closed" date
(one listed in the other table). I was trying to use an AfterUpdate
on the workorder date, but I am uncertain how to lookup that field in
the other table and not allow work...
|
by: Sami |
last post by:
Please bear with me, and if you answer this question, please do it
step by step. I am new at Access, not at all sophisticated. I am
using Office XP. This will need to be read in Access for Office 2000.
I am creating a database to track student athletes. I have created
the following tables. The table title is to the far left, with fields
under each. The common field will be the StudentID field, which is
their student number assigned...
|
by: rugger81 |
last post by:
I am currently working in the sql server 2000 environment and I want to
write a function to pull all dates within a given date range. I have
created several diferent ways to do this but I am unsatisfied with
them. Here is what I have so far:
declare @Sdate as datetime
declare @Edate as datetime
set @SDate = '07/01/2006'
set @EDate = '12/31/2006'
|
by: Joanie |
last post by:
I have a form that records dates of unavailability for a worker.
Based on what is entered in the simple table behind the form, many calculations take place to create employee "load" balance. Each day needs to be inserted as a separate row on the table.
Right now the users enter each date as a separate row.
They want to just be able to enter a range.
How do I add the multiple rows from the date range entered on the form AND exclude weekends?
| |
by: norma.j.hildebrand |
last post by:
I have a database that has a field (performance standard), every
year the standard changed which was not a problem since we start out
each year with a blank database and just change the standards based on
the prior years performance.
Now they threw me a curve ball... They want to change the standards
every quarter. This standard is matched up with an item that is
referenced in a daily production table to figure out our percentage of...
|
by: jaccess |
last post by:
Hello all,
I am trying to create a running total based on a specific date range that is to be entered into a form.
I currently have the form set up with 2 text boxes (date1 and date2) which are the input for my start and end date, a table (tblALLBCPIC) with a DATE field and ORDPCK field (what I need a running sum of). There are other fields in the table but they shouldn’t matter.
Here is the SQL for my query so far:
SELECT...
|
by: Vinda |
last post by:
Hi Bytes,
Using a previous question as a base Access 2000 Inserting multiple rows based on a date range.
I also wanted to insert multiple rows into a table according to a date range supplied by a user (eg txtRDateStart & txtRDateEnd). The script was envisaged to be able to also gather a time and text description that would be repeated within each row.
For example:
The user would enter...
Start Date: 13/03/2010
End Date: 17/03/2010...
|
by: CD Tom |
last post by:
Here's my problem. I have a person in New Zealand that I'm having problems with the date. Everything works fine except I have a date field in one form that updates another table with the date. I'm using an Update statement to update the table, if I look at the date format that is being sent it is in the correct format DD/MM/YYYY but when I bring up the other table the date is in the format MM/DD/YYYY If I enter the date into that table it...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |