473,325 Members | 2,712 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Permutate rows based on table

Hi,
I wish to create new rows of data based on a source table. Example: I
have a file that contains a SESSION (time roughly a calendar quarter
for a University), START_DT, END_DT.
I want to create rows that would be for each session and each day, so
for session 200102 that starts 09/10/2000 and ends 12/15/2000 I want 96
rows, with 200102 for Session, and the 96 days in DAY.

-- Source table:
CREATE TABLE [F___Example_Date] (
[SESSION_ID] [numeric](19, 0) NOT NULL ,
[START_DT] [datetime] NULL ,
[END_DT] [datetime] NULL
) ON [PRIMARY]

-- Sample data :
INSERT INTO [DS_V5_Source].[dbo].[F___Example_Date]([SESSION_ID],
[START_DT], [END_DT])
VALUES(200102, '2000-09-10', '2000-12-15')
INSERT INTO [DS_V5_Source].[dbo].[F___Example_Date]([SESSION_ID],
[START_DT], [END_DT])
VALUES(200103, '2001-01-04', '2001-03-26')

-- Example Target file to be populated:
CREATE TABLE [Target_Date] (
[SESSION_ID] [numeric](19, 0) NOT NULL ,
[Day] [datetime] not null
) ON [PRIMARY]
GO

-- Example of inserts to that represent the desired results (my insert
if for illustration only, not intended to be elegant)
INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])
VALUES(200102,'2000-09-10')
INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])
VALUES(200102,'2000-09-11')
INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])
VALUES(200102,'2000-09-12')
INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])
VALUES(200102,'2000-09-13')
-- <... and so forth for all days between 9/10/2000 and 12/15/2000...>
INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])
VALUES(200102,'2000-12-15')
My need is getting into a new area of my SQL experiance and I'm not
sure how to approach solving this problem. I'm confident once I learn
how to solve this, I will be able to do a lot more with SQL.
TIA
Rob

Jul 23 '05 #1
3 1315
A common approach is to create a Calendar table to support custom
calendar information. Typically one row per day for the lifetime of
your data then just add columns for the date-sensitive information of
interest:

CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY, session_id INTEGER NOT NULL
DEFAULT 0)

INSERT INTO Calendar (caldate) VALUES ('20000101')

WHILE (SELECT MAX(caldate) FROM Calendar)<'21001231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar

UPDATE Calendar
SET session_id =
COALESCE(
(SELECT F.session_id
FROM F___Example_Date AS F
WHERE Calendar.caldate BETWEEN F.start_dt AND F.end_dt)
,0)
WHERE EXISTS
(SELECT *
FROM F___Example_Date AS F
WHERE Calendar.caldate BETWEEN F.start_dt AND F.end_dt)

F___Example_Date is now redundant and you can drop it (create a view
instead).

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2
Thanks David,
I cut-n-pasted your example but I am getting an error:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
The statement has been terminated.

Im unfamilar with coalesce, so,. Im not sure what to do.

Let me add more infomation, as my approach might not be ideal:
For any academic quarter (session) we have a count down to the last day
to add or drop a class. This day is known as "Day 0". Because each
session has a different starting date and add drop date, we use this
count down to day zero to compare session to session enrollments, via
Day0. Example, the fall session, the add/drop day might be 9/20/2000
while the next year is 9/23/2001.
I want to create a table that contains the session, a date within that
session and the day number (days from day0) for the given date. I
thought that I could use this table to update my student registrations
with the Day number, since I know the session and day they registered,
as example. I could then find out the number of students from different
sessions where the dayNo <= '0':
SELECT COUNT(*) FROM REGISTRATION WHERE SESSION_ID BETWEEN 200102 AND
200402 AND DAYNO <= 0 GROUP BY SESSION_ID
Thanks
Rob

Jul 23 '05 #3
I think the error message must be caused by duplicate/overlapping time
periods in your table. You didn't specify any keys but I assumed in my
UPDATE that each date would have a unique Session_id.

First, try changing the UPDATE to eliminate exact duplicates:

UPDATE Calendar
SET session_id =
COALESCE(
(SELECT DISTINCT F.session_id
FROM F___Example_Date AS F
WHERE Calendar.caldate BETWEEN F.start_dt AND F.end_dt
AND session_id IS NOT NULL)
,0)
WHERE EXISTS
(SELECT *
FROM F___Example_Date AS F
WHERE Calendar.caldate BETWEEN F.start_dt AND F.end_dt
AND session_id IS NOT NULL)

If you still get an error then you can find any overlapping date ranges
like this:

SELECT T1.*
FROM [F___Example_Date] AS T1,
[F___Example_Date] AS T2
WHERE T1.session_id <> T2.session_id
AND T1.start_dt <= T2.end_dt
AND T1.end_dt >= T2.start_dt

--
David Portas
SQL Server MVP
--

Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: robert | last post by:
running 8.1.7 server, 8.1.6 client. i *thought* inner join should not return nulls, but not only that, but i get way more rows than i'm expecting. assume: order table: order_number
1
by: jim | last post by:
I have two tables that are related by keys. For instance, Table employee { last_name char(40) not null, first_name char(40) not null, department_name char(40) not null, age int not null, ......
6
by: Brendan.Collins | last post by:
Hi I have a javascript problem that has been annoying me for two days now and thought that a javascript expert might have the magic solution. I am populating a table dynamically from the...
1
by: Andrew | last post by:
Hey all, I am very new to ASP.Net (and .Net in general), but that isn't stopping the boss from wanting to begin new projects in it. This latest project has me kinda stumped and after a couple...
11
by: jimstruckster | last post by:
I have a table with 10 rows, I want all rows except for the first to be hidden when the page first opens up. If the user puts a value in a text box in the first row then I want the second row to...
7
by: rcamarda | last post by:
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...
1
by: serge | last post by:
IF (SELECT OBJECT_ID('t1')) IS NOT NULL DROP TABLE t1 GO CREATE TABLE t1 (c1 INT, c2 INT) DECLARE @n INT SET @n = 1 WHILE @n <= 454 BEGIN INSERT INTO t1 VALUES (@n, @n)
17
by: pbd22 | last post by:
Hi. How does one return a range of rows. I know that "Top 5" will return rows 0 - 5 but, how do I get 6 - 10? thanks
0
by: ANGanley | last post by:
Can anyone help me this? I have a class. Public Class db_Vehicle Public bs_VehicleDetails As New BindingSource() Public da_VehicleDetails As New SqlDataAdapter() Public table...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.