October 26, 2016 at 6:52 am
Looking for a procedure that insert values (24 hour) from a primary/main table to the secondary/sub table.
My main has the following columns that comes from a csv file.
TableName:PrimaryTable
1.DealNumber (Primary Key)
2.ProductName
3.StartDate
4.EndDate
5.MW
6.Price
Assume that the csv file as 10 rows where deal number is unique, Whenever the user uploads the data from csv file to SQL db table, if the start date and end dates are same then the secondary table should automatically fills in data (1-24) on the hourly column for that particular dealnumber along with data for MW, Price. Is there a way to do this. I dont want to store the secondary data in a temp table as i nee this data to show it in infopath form.
TableName:SecondaryTable
1.DealNumber
2.Hourly (1-24)
3.MW
4.Price
October 26, 2016 at 7:08 am
It isn't clear to me what you actually want. Can you provide some sample data (as INSERT statements, preferrably with an initial CREATE TABLE statement) and how this sample data must look like in the results?
October 26, 2016 at 7:23 am
Hanshi, I am sorry for not being clear. I have a SQL table called Primary that gets the data from a csv file. This primary table has following columns (DealNumber,StartDate,Enddate,MW,Price). I have created a secondary in that i need to show the deal number, MW, Price from the first table along with i need to show hours (1 to 24) if the start and end date are same.
I dont know how to insert deal numbers, MW, Price automatically to the secondary table with hours column if the start and end date same.
Say for example my primary data have the following data:
PrimaryTable:
Deal Number startdate enddate MW Price
10001 10/25/2016 10/25/2016 250 23
10002 10/25/2016 10/31/2016 250 20
Secondary Table should show the vaules only for the deal that has the same start and end date:
DealNumber Hourly MW Price
10001 1 250 23
10001 2 250 23
10001 3 250 23
10001 4 250 23
10001 5 250 23
10001 6 250 23
10001 7 250 23
10001 8 250 23
10001 9 250 23
10001 10 250 23
10001 11 250 23
10001 12 250 23
10001 13 250 23
10001 14 250 23
10001 15 250 23
10001 16 250 23
10001 17 250 23
10001 18 250 23
10001 19 250 23
10001 20 250 23
10001 21 250 23
10001 22 250 23
10001 23 250 23
10001 24 250 23
October 26, 2016 at 7:32 am
When posting, you should really provide the INSERT statements, as a copy paste of data can be awful to read. I've done this for you:
CREATE TABLE #Primary (DealNumber INT,
StartDate DATE,
EndDate DATE,
MV INT,
Price INT);
INSERT INTO #Primary
VALUES (10001, '2016-10-25', '2016-10-25', 250, 23), --You should always provide dates in a globally accepted format, to me I would read '10/25/2016' as the 10th day of the 25th month. That doen't make sense.
(10002, '2016-10-25', '2016-10-31', 250, 20);
DROP TABLE #Primary
It can also mean that we make incorrect assumptions of your data types (for all I know MV might not always be an INT, and Price could be a DECIMAL).
Edit: Fixed spelling
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 26, 2016 at 7:35 am
Why do you want to store the data 24 times?
You can achieve the result without having all that data stored. It's just a waste of storage when a query will return the same result.
CREATE TABLE #PrimaryTable(
DealNumber int,
startdate date,
enddate date,
MW int,
Price int
);
INSERT INTO #PrimaryTable
VALUES
(10001, '10/25/2016', '10/25/2016', 250, 23),
(10002, '10/25/2016', '10/31/2016', 250, 20);
GO
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
cteTally(n) AS(
SELECT TOP (24) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E2
)
SELECT p.DealNumber,
t.n AS Hourly,
P.MW,
P.Price
FROM cteTally t
CROSS JOIN #PrimaryTable p
WHERE P.DealNumber = 10001
GO
DROP TABLE #PrimaryTable
October 26, 2016 at 7:39 am
Thank you Tom. Appreciate it.
October 26, 2016 at 7:42 am
Luis Cazares (10/26/2016)
Why do you want to store the data 24 times?You can achieve the result without having all that data stored. It's just a waste of storage when a query will return the same result.
CREATE TABLE #PrimaryTable(
DealNumber int,
startdate date,
enddate date,
MW int,
Price int
);
INSERT INTO #PrimaryTable
VALUES
(10001, '10/25/2016', '10/25/2016', 250, 23),
(10002, '10/25/2016', '10/31/2016', 250, 20);
GO
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
cteTally(n) AS(
SELECT TOP (24) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E2
)
SELECT p.DealNumber,
t.n AS Hourly,
P.MW,
P.Price
FROM cteTally t
CROSS JOIN #PrimaryTable p
WHERE P.DealNumber = 10001
GO
DROP TABLE #PrimaryTable
This looks like an excellent solution to the OP's question. Only one minor change in the WHERE clause, as the OP want's oly results for the Delanumber where the dates are the same.
So change
WHERE P.DealNumber = 10001
to
WHERE P.startdate = P.enddate
October 26, 2016 at 7:43 am
Luis, Thank you for the code and the suggestion. Really Appreciate it. We need to show hours in the secondary table as in the front end GUI we got to show the end user the hours and their corresponding MW and price. An end user can can edit the MW & Price for any hours they want.
October 26, 2016 at 7:49 am
Deries (10/26/2016)
Luis, Thank you for the code and the suggestion. Really Appreciate it. We need to show hours in the secondary table as in the front end GUI we got to show the end user the hours and their corresponding MW and price. An end user can can edit the MW & Price for any hours they want.
In that case, you can create a trigger to do the insert for you
CREATE TRIGGER dbo.InsertSecondary
ON dbo.PrimaryTable
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.SecondaryTable ( DealNumber, Hourly, MW, Price )
SELECT i.DealNumber, Hrs.Hr, i.MW, i.Price
FROM INSERTED AS i
CROSS APPLY (VALUES ( 1), ( 2), ( 3), ( 4), ( 5), ( 6)
, ( 7), ( 8), ( 9), (10), (11), (12)
, (13), (14), (15), (16), (17), (18)
, (19), (20), (21), (22), (23), (24)
) AS Hrs(Hr)
WHERE i.StartDate = i.EndDate
END;
October 26, 2016 at 7:55 am
Deries (10/26/2016)
Luis, Thank you for the code and the suggestion. Really Appreciate it. We need to show hours in the secondary table as in the front end GUI we got to show the end user the hours and their corresponding MW and price. An end user can can edit the MW & Price for any hours they want.
In that case, I would only store the edited mw & prices. Then just add them to the query. No need to be storing repeated data.
October 26, 2016 at 8:07 am
I am bit confused, sorry about that. If i use the above code will that create a table Primary and insert the data? I dont want to create any table as they are already and I only wanted to insert data from primary to secondary. Luis use your code and the suggestion to implement?
October 26, 2016 at 10:01 am
My sincere thanks to each one of you who helped me here and gave their suggestions. It helped me a lot to figure out the best practice to use for my development. Appreciate it.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply