July 9, 2016 at 1:16 pm
Hello,
I am working on a database and would like to create a script that would auto-generate 1,2,3, or 5 years of data in one run. For example, say a parking lot contains 200 spaces and I would like to have inventory of each calendar day (365).
Is there a recommended way or sample as to how to best do this? Below is sample data (10 inventory records) And how would I be able to compare to a calendar to determine how many days are in the year (in case of a leap year).
-- SAMPLE CODE START
CREATE TABLE #ParkingInv(PIID int IDENTITY, CustID int,LotID int, RowNum int, SpaceID int, SpaceDesc varchar(25), WidthInFt int, StartDt datetime, EndDt datetime, Avail int)
INSERT INTO #ParkingInv VALUES (25,100,1,1,'A1',8,'07-09-2016 12:01AM', '07-09-2016 11:59 PM', 1)
INSERT INTO #ParkingInv VALUES (25,100,1,2,'A2',8,'07-09-2016 12:01AM', '07-09-2016 11:59 PM', 1)
INSERT INTO #ParkingInv VALUES (25,100,1,3,'A3',8,'07-09-2016 12:01AM', '07-09-2016 11:59 PM', 1)
INSERT INTO #ParkingInv VALUES (25,100,1,4,'A4',8,'07-09-2016 12:01AM', '07-09-2016 11:59 PM', 1)
INSERT INTO #ParkingInv VALUES (25,100,1,5,'A5',9,'07-09-2016 12:01AM', '07-09-2016 11:59 PM', 1)
INSERT INTO #ParkingInv VALUES (25,100,2,1,'B1',9,'07-09-2016 12:01AM', '07-09-2016 11:59 PM', 1)
INSERT INTO #ParkingInv VALUES (25,100,2,2,'B2',10,'07-09-2016 12:01AM', '07-09-2016 11:59 PM', 0)
INSERT INTO #ParkingInv VALUES (25,100,2,3,'B3',10,'07-09-2016 12:01AM', '07-09-2016 11:59 PM', 0)
INSERT INTO #ParkingInv VALUES (25,100,2,4,'B4',12,'07-09-2016 12:01AM', '07-09-2016 11:59 PM', 1)
INSERT INTO #ParkingInv VALUES (25,100,2,5,'B5',12,'07-09-2016 12:01AM', '07-09-2016 11:59 PM', 1)
SELECT * FROM #ParkingInv
-- SAMPLE CODE END
Basically, I would like take these 10 records and create each item (inventory) or each day of the year.
July 9, 2016 at 1:23 pm
rjjh78 (7/9/2016)
Hello,I am working on a database and would like to create a script that would auto-generate 1,2,3, or 5 years of data in one run.
in answer to your first question...one way (amongst many...)
DECLARE @sd DATE= '20140101'
DECLARE @ed DATE= '20181231'
SELECT DATEADD(dd, rn - 1, @sd) thedate
FROM
( SELECT rn = ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM sys.objects a CROSS JOIN sys.objects b
) x
WHERE x.rn <= DATEDIFF(dd, @sd, DATEADD(day, 1, @ed))
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 9, 2016 at 1:38 pm
Basically, I would like take these 10 records and create each item (inventory) or each day of the year.
thanks for the sample data...appreciated....but I think that you now need to fully explain what your expected results are.
In your sample data the startdt and enddt are all identical...as are the the custid and lotid columns.
??
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 9, 2016 at 1:50 pm
Thank you for your help!
So basically, what I am trying to do is turn each parking space into inventory by day. Or in 1 year, one parking space will have 365 individual inventory records. Perhaps thinking of hotel room would have been a better concept.
Using just the first row of my sample data, for an end result I would want to produce:
INSERT INTO #ParkingInv VALUES (25,100,1,1,'A1',8,'07-10-2016 12:01AM', '07-11-2016 11:59 PM', 1)
INSERT INTO #ParkingInv VALUES (25,100,1,1,'A1',8,'07-11-2016 12:01AM', '07-12-2016 11:59 PM', 1)
INSERT INTO #ParkingInv VALUES (25,100,1,1,'A1',8,'07-12-2016 12:01AM', '07-13-2016 11:59 PM', 1)
INSERT INTO #ParkingInv VALUES (25,100,1,1,'A1',8,'07-13-2016 12:01AM', '07-14-2016 11:59 PM', 1)
INSERT INTO #ParkingInv VALUES (25,100,1,1,'A1',8,'07-14-2016 12:01AM', '07-15-2016 11:59 PM', 1)
INSERT INTO #ParkingInv VALUES (25,100,1,1,'A1',8,'07-14-2016 12:01AM', '07-16-2016 11:59 PM', 1)
INSERT INTO #ParkingInv VALUES (25,100,1,1,'A1',8,'07-16-2016 12:01AM', '07-17-2016 11:59 PM', 1)
INSERT INTO #ParkingInv VALUES (25,100,1,1,'A1',8,'07-17-2016 12:01AM', '07-18-2016 11:59 PM', 1)
INSERT INTO #ParkingInv VALUES (25,100,1,1,'A1',8,'07-18-2016 12:01AM', '07-19-2016 11:59 PM', 1)
INSERT INTO #ParkingInv VALUES (25,100,1,1,'A1',8,'07-19-2016 12:01AM', '07-20-2016 11:59 PM', 1)
July 9, 2016 at 1:55 pm
I would do this with a (deliberate) cartesian product between Calendar and ParkingSpaces...
SELECT p.ParkingSpace, c.CalendarDate
FROM ParkingSpaces p CROSS JOIN Calendar c
WHERE c.CalendarDate BETWEEN '01-Jan-2016' AND '31-Dec-2016';
then just insert that result into your table.
July 9, 2016 at 6:20 pm
thank you guys, problem solved combining both suggestions!
July 9, 2016 at 7:44 pm
Hi, I maybe need to open a new string for this one, but if I want to query by a specific date range and only show the records when the space is avail for ALL the dates in the range, how can I do that?
Currently, my query shows all records that are available in a specific date range.
SELECT SpaceID, StartDt
WHERE CustID=25 AND Avail <>0 AND StartDT BETWEEN '07-09-2016' AND '07-15-2016'
In this case, I want to check to make sure the space is available for the entire date range, if not do not display the record.
July 9, 2016 at 8:21 pm
In this case, I want to check to make sure the space is available for the entire date range, if not do not display the record.
In that case, you would have to outer join your Calendar/dates table to your query and then check for nulls. If there are nulls then the entire range isn't a match
One way might be to use NOT EXISTS and a correlated subquery that checks for missing values (nulls). If no unmatched values exist, then the whole range is full/empty.
---
I've re-read this a few times... there are some tables that are missing.
The first one is the ParkingSpaces table. Something like
CREATE TABLE ParkingSpace (
ParkingSpaceID INT IDENTITY,
LotID INT,
SpaceID INT
CONSTRAINT pkParkingSpace PRIMARY KEY (LotID, SpaceID)
);
then there's the Calendar, which John Mitchell provided.
Then there's the intersection of the two
CREATE TABLE UsedSpace(
ParkingSpaceID INT,
AssignedToPersonID INT,
CalendarDate DATE
);
might be (ParkingSpaceID, PersonID, StartDate, EndDate).
Then you can find any spot that's not taken for the entire span... You'd just use NOT EXISTS and a correlated subquery to find if a space has any "reservations" against it in the given period of time.
Pieter
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply