January 10, 2015 at 2:45 pm
Thanks to this site, my DB project (SQL Server back end with MS Access Front End) has been transformed.
I have written close to 80 function now. I need help on how to complete this one.
This is a function where 4 queries return a date for 4 business conditions.
The return value should be zero if none of the 4 return a date. Or the Business Rule number that has the most recent date.
What date is the latest and return the Number of the latest date.
example: I can understand SELECT CASE WHEN @Value1 > @Value2 THEN @Value1 ELSE @Value2 END AS MaxColumn
But, I can't find an example of 4 values with the option of returning 0 if all of the 4 date variables are null.
/****** Object: UserDefinedFunction [dbo].[RE_2190] Script Date: 1/10/2015 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ST APD Submit
-- Can not have Approved, or WithDrawn
-- It is giving date time - get date only - or it returns Null
-- Get date for each Denied, Expired, Withdrawn - then use logic to determine what was last - add this for State / Fed to each of the 3 St/ Fed functions
CREATE FUNCTION [dbo].[RE_2190](@ID_Wells int)
RETURNS datetime
AS
BEGIN;
DECLARE @LatestWithDrawn_DT as datetime; --1
DECLARE @LatestDenied_DT as datetime; --2
DECLARE @LatestApproved_DT as datetime; --3
DECLARE @LatestSubmitted_DT as datetime; --4
DECLARE @intReturnValue as int; -- return null or 0 through 4 (0 None of these had any dates)
-- This first one will trump the other three if it's date is the latest of the possible 4 dates
-- 1. any type can be withdrawn - get the latest date for Withdrawn_sub in WithDrawn Dt column (this trumps Dt_APD_Sub any status.)
Set @LatestWithDrawn_DT =(SELECT TOP 1 Dt_APD_WithDrawn_Sub
FROM tblAPD_Fed_ST_CO
WHERE (ID_Wells =@ID_Wells) AND (txtFedStCo = 'St') AND
(NOT (Dt_APD_Sub IS NULL)) AND not (Dt_APD_WithDrawn_Sub IS NULL) AND
(lngID_APD_Status IN (1, 2, 3, 4))
ORDER BY ID_Wells, Dt_APD_WithDrawn_Sub DESC
)
-- 2. Denied
Set @LatestDenied_DT =(SELECT TOP 1 Dt_APD_Apv
FROM tblAPD_Fed_ST_CO
WHERE (ID_Wells =@ID_Wells) AND (txtFedStCo = 'St') AND (txt_APD_Apv_Status = 'Denied') AND
(NOT (Dt_APD_Sub IS NULL)) AND (Dt_APD_WithDrawn_Sub IS NULL) AND
(lngID_APD_Status IN (1, 2, 3, 4))
ORDER BY ID_Wells, Dt_APD_Apv DESC
)
-- 3. If approved it could be current or expired. Did a Withdrawn or Denied take place after this date? then it trumped it.
Set @LatestApproved_DT=(SELECT TOP 1 Dt_APD_Apv
FROM tblAPD_Fed_ST_CO
WHERE (ID_Wells =@ID_Wells) AND (txtFedStCo = 'St') AND (txt_APD_Apv_Status = 'Approved') AND
(NOT (Dt_APD_Sub IS NULL)) AND (Dt_APD_WithDrawn_Sub IS NULL) AND
(lngID_APD_Status IN (1, 2, 3, 4))
ORDER BY ID_Wells, Dt_APD_Apv DESC
)
-- 4. If Submitted it could be current or expired. Did a Withdrawn or Denied take place after this date? then it trumped it.
Set @LatestSubmitted_DT=(SELECT TOP 1 Dt_APD_Apv
FROM tblAPD_Fed_ST_CO
WHERE (ID_Wells =@ID_Wells) AND (txtFedStCo = 'St') AND (txt_APD_Apv_Status = 'Submitted') AND
(NOT (Dt_APD_Sub IS NULL)) AND (Dt_APD_WithDrawn_Sub IS NULL) AND
(lngID_APD_Status IN (1, 2, 3, 4))
ORDER BY ID_Wells, Dt_APD_Apv DESC
)
-- Checked, in the few cases of 2 dates for one @ID_Wells, the latest date is returned for each of the 4.
-- Objective - The possibilities are 4 Nulls for the 4 Date variables (return 0)
-- Which one of the 4 above have the most recent date? AND Return the numbers 0 through 4.
-- NEED HELP WITH FORMULA HERE compare 4 dates (each date variable can have Null or a date) Need to know what category is the most recent date.
return @intReturnValue-- value returned by function it will return either 0 through 4
END;
GO
Each of these sql queries use the PK @ID_Wells to return zero to (max of 3 records) so the TOP 1 returns the latest record for each query.
The maximum table size in 4 years will remain well under 100,000 records.
January 10, 2015 at 3:54 pm
Need three things from you.
1) The schema for the table used in the function - you can limit it to the columns used in the function and any indexed columns and the index definitions
2) Sample data for this table - enough to test each possible result
3) expected results based on the sample data for each possible result
January 11, 2015 at 9:03 am
If you insert the results into a table variable rather than individual variables, your final act could be to select the MAX date from that table variable.
Step 2 would be to find a way to consolidate at least the last 3 queries in that function so that you don't have to hit the tables a total 0f 4 times. I don't know if such consolidation would help here because I don't know how often your going to hit the function.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 11, 2015 at 9:28 am
Quick suggestion based on Jeff's post
😎
/****** Object: UserDefinedFunction [dbo].[RE_2190] Script Date: 1/10/2015 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ST APD Submit
-- Can not have Approved, or WithDrawn
-- It is giving date time - get date only - or it returns Null
-- Get date for each Denied, Expired, Withdrawn - then use logic to determine what was last - add this for State / Fed to each of the 3 St/ Fed functions
CREATE FUNCTION [dbo].[RE_2190](@ID_Wells int)
RETURNS datetime
AS
BEGIN;
/*
Table variable to hold the intermediate results
*/
DECLARE @RESULTS TABLE
(
R_FLAG INT NOT NULL
,R_DATE DATETIME NULL
)
-- This first one will trump the other three if it's date is the latest of the possible 4 dates
-- 1. any type can be withdrawn - get the latest date for Withdrawn_sub in WithDrawn Dt column (this trumps Dt_APD_Sub any status.)
INSERT INTO @RESULTS(R_FLAG,R_DATE)
SELECT 1 AS R_FLAG
,(SELECT TOP 1 Dt_APD_WithDrawn_Sub
FROM tblAPD_Fed_ST_CO
WHERE (ID_Wells =@ID_Wells) AND (txtFedStCo = 'St') AND
(NOT (Dt_APD_Sub IS NULL)) AND not (Dt_APD_WithDrawn_Sub IS NULL) AND
(lngID_APD_Status IN (1, 2, 3, 4))
ORDER BY ID_Wells, Dt_APD_WithDrawn_Sub DESC
) AS R_DATE
-- 2. Denied
INSERT INTO @RESULTS(R_FLAG,R_DATE)
SELECT 2 AS R_FLAG
,(SELECT TOP 1 Dt_APD_Apv
FROM tblAPD_Fed_ST_CO
WHERE (ID_Wells =@ID_Wells) AND (txtFedStCo = 'St') AND (txt_APD_Apv_Status = 'Denied') AND
(NOT (Dt_APD_Sub IS NULL)) AND (Dt_APD_WithDrawn_Sub IS NULL) AND
(lngID_APD_Status IN (1, 2, 3, 4))
ORDER BY ID_Wells, Dt_APD_Apv DESC
) AS R_DATE
-- 3. If approved it could be current or expired. Did a Withdrawn or Denied take place after this date? then it trumped it.
INSERT INTO @RESULTS(R_FLAG,R_DATE)
SELECT 4 AS R_FLAG
,(SELECT TOP 1 Dt_APD_Apv
FROM tblAPD_Fed_ST_CO
WHERE (ID_Wells =@ID_Wells) AND (txtFedStCo = 'St') AND (txt_APD_Apv_Status = 'Approved') AND
(NOT (Dt_APD_Sub IS NULL)) AND (Dt_APD_WithDrawn_Sub IS NULL) AND
(lngID_APD_Status IN (1, 2, 3, 4))
ORDER BY ID_Wells, Dt_APD_Apv DESC
) AS R_DATE
-- 4. If Submitted it could be current or expired. Did a Withdrawn or Denied take place after this date? then it trumped it.
INSERT INTO @RESULTS(R_FLAG,R_DATE)
SELECT 4 AS R_FLAG
,(SELECT TOP 1 Dt_APD_Apv
FROM tblAPD_Fed_ST_CO
WHERE (ID_Wells =@ID_Wells) AND (txtFedStCo = 'St') AND (txt_APD_Apv_Status = 'Submitted') AND
(NOT (Dt_APD_Sub IS NULL)) AND (Dt_APD_WithDrawn_Sub IS NULL) AND
(lngID_APD_Status IN (1, 2, 3, 4))
ORDER BY ID_Wells, Dt_APD_Apv DESC
) AS R_DATE
-- Checked, in the few cases of 2 dates for one @ID_Wells, the latest date is returned for each of the 4.
-- Objective - The possibilities are 4 Nulls for the 4 Date variables (return 0)
-- Which one of the 4 above have the most recent date? AND Return the numbers 0 through 4.
-- NEED HELP WITH FORMULA HERE compare 4 dates (each date variable can have Null or a date) Need to know what category is the most recent date.
/*
NOTE:
The function will only return one scalar value and the @intReturnValue does not match the declared data type
*/
RETURN (
SELECT
MAX(R.R_DATE)
FROM @RESULTS R
)
-- return @intReturnValue-- value returned by function it will return either 0 through 4
END;
GO
January 11, 2015 at 10:01 am
May even be easier than that. I am just waiting for the information I requested.
January 11, 2015 at 11:39 am
Thanks! Got the reply Saturday. Came to work downtown Sunday to provide the example data with intended results.
There is a big football game starting soon.
I should have that data up soon.
The code supplied for a single max date response: The max date won't supply the answer.
This is a sub-step of one Rule of a Rule Engine - A rule engine must supply only a True or False.
The number 0 to 4 from this step is used to call a specific final Rule Engine. The date is deterministic for the Rule Engine single answer.
How often:
The current application log shows a single record (ID_Well) is called on average once every 32 minutes by the entire user community.
Then a QA report used during application off-time called all 14,000 ID_Well once a day.
The Rule Engine is in the general category of Quality Assurance instead of Data Processing.
Let me get a Record example up now.
Thank you for all the valued comments and suggestions.
-- 20 minutes later updated
One ID_Well can have many Permits (APD). These are filtered to State Permits (ST)
An APD can be Submitted (Dt APD Sub). Once the Submit becomes approved the Dt_APD_Apv is completed (with an permit expire date).
- Denied status means that either Submitted or Approved was "denied" If that was the latest event - it remains Denied.
- If a Dt_APD_WithDrawn_Sub (Date this record's permit was withdrawn, that is important to know it was Withdrawn)
If after a permit expired, if after a permit was Denied, if after a permit was Withdrawn... A new permit could be Submitted.
This is why each function has the Top 1 Condition (latest date) for each of the above.
Everything is driven by the most recent submit date. Plus the logic of the fields.
If all records are Null - there is No permit resulting in a 0
If the very last date was the Dt_APD_Withdrawn - then it is a 1 (last permit was withdrawn, better get busy on the next one)
If the last recent date status was Submitted, we don't care about previous records.
If the last recent date status was Approved, then we are good and don't care about history.
Just in case anyone is wondering about the last Approval Expired Date. That is done later. These permits are a "preliminary permit" step.
So, it is OK if it is expired (Dt_APD_Exp), this is covered somewhere else after this step.
ID_Wells Dt_APD_Apv txtFedStCo lngID_APD_Status Dt_APD_Sub txt_APD_Apv_Status Dt_APD_Exp Dt_APD_WithDrawn_Sub
----------- ---------------------- ---------- ---------------- ---------------------- ------------------ ---------------------- ----------------------
12 2007-11-20 00:00:00 St 1 2007-11-05 00:00:00 Approved 2008-11-20 00:00:00 NULL
390 NULL St 1 2006-02-14 00:00:00 Submitted NULL NULL
971 2007-04-24 00:00:00 St 1 2007-03-16 00:00:00 Approved 2008-04-24 00:00:00 NULL
971 NULL St 2 2008-04-11 00:00:00 Submitted NULL NULL
1619 2008-04-30 00:00:00 St 1 2008-04-18 00:00:00 Approved 2009-05-30 00:00:00 NULL
1619 2009-04-30 00:00:00 St 2 2009-04-15 00:00:00 Approved 2010-04-30 00:00:00 NULL
1619 2010-04-27 00:00:00 St 2 2010-04-08 00:00:00 Approved 2011-04-27 00:00:00 NULL
1619 2011-07-20 00:00:00 St 2 2011-05-10 00:00:00 Withdrawn NULL NULL
1619 2011-08-08 00:00:00 St 3 2011-07-20 00:00:00 Approved 2012-08-07 00:00:00 NULL
1619 2012-08-22 00:00:00 St 2 2012-08-02 00:00:00 Approved 2013-08-22 00:00:00 NULL
1637 NULL St 1 2008-06-30 00:00:00 Submitted NULL NULL
1637 2008-09-08 00:00:00 St 3 2008-08-19 00:00:00 Approved 2009-09-08 00:00:00 NULL
1637 2009-09-17 00:00:00 St 2 2009-08-31 00:00:00 Approved 2010-09-17 00:00:00 NULL
1637 2010-10-05 00:00:00 St 2 2010-09-07 00:00:00 Approved 2011-10-05 00:00:00 NULL
1637 2011-11-01 00:00:00 St 2 2011-09-29 00:00:00 Approved 2012-11-01 00:00:00 NULL
1637 2012-11-05 00:00:00 St 2 2012-10-31 00:00:00 Denied NULL NULL
3381 2010-12-06 00:00:00 St 1 2010-06-29 00:00:00 Withdrawn 2011-12-06 00:00:00 2011-01-18 00:00:00
4295 2011-01-25 00:00:00 St 1 2010-11-24 00:00:00 Returned NULL NULL
4295 2012-02-29 00:00:00 St 3 2011-11-30 00:00:00 Approved 2013-02-28 00:00:00 NULL
6530 2013-08-02 00:00:00 St 1 2013-01-17 00:00:00 Denied NULL NULL
6530 2013-11-06 00:00:00 St 3 2013-08-30 00:00:00 Denied NULL NULL
6530 2014-09-25 00:00:00 St 3 2014-07-08 00:00:00 Approved 2015-09-25 00:00:00 NULL
6540 2013-03-05 00:00:00 St 1 2013-01-21 00:00:00 Approved 2014-03-05 00:00:00 NULL
6540 2014-05-09 00:00:00 St 2 2014-03-19 00:00:00 Approved 2015-05-09 00:00:00 NULL
6541 2013-02-14 00:00:00 St 1 2012-11-27 00:00:00 Approved 2014-02-14 00:00:00 NULL
9627 2014-12-01 00:00:00 St 1 2014-10-22 00:00:00 Denied NULL NULL
CREATE TABLE [dbo].[tblAPD_Fed_ST_CO](
[intID_APD_FedStCo] [int] IDENTITY(1,1) NOT NULL,
[ID_Wells] [int] NULL,
[txtFedStCo] [nvarchar](9) NULL,
[lngID_APD_Status] [int] NULL,
[lngID_Reg_Office] [int] NULL,
[Dt_APD_Sub] [datetime2](0) NULL,
[Dt_APD_Apv] [datetime2](0) NULL,
[txt_APD_Apv_Status] [nvarchar](12) NULL,
[WithdrawalType] [nvarchar](12) NULL,
[Dt_APD_WithDrawn_Sub] [datetime2](0) NULL,
[Dt_APD_WithDrawn_APProved] [datetime2](0) NULL,
CONSTRAINT [tblAPD_Fed_ST_CO$PrimaryKey] PRIMARY KEY CLUSTERED
January 11, 2015 at 2:08 pm
Mile Higher Than Sea Level (1/11/2015)
Thanks! Got the reply Saturday. Came to work downtown Sunday to provide the example data with intended results.There is a big football game starting soon.
I should have that data up soon.
The code supplied for a single max date response: The max date won't supply the answer.
This is a sub-step of one Rule of a Rule Engine - A rule engine must supply only a True or False.
The number 0 to 4 from this step is used to call a specific final Rule Engine. The date is deterministic for the Rule Engine single answer.
How often:
The current application log shows a single record (ID_Well) is called on average once every 32 minutes by the entire user community.
Then a QA report used during application off-time called all 14,000 ID_Well once a day.
The Rule Engine is in the general category of Quality Assurance instead of Data Processing.
Let me get a Record example up now.
Thank you for all the valued comments and suggestions.
-- 20 minutes later updated
One ID_Well can have many Permits (APD). These are filtered to State Permits (ST)
An APD can be Submitted (Dt APD Sub). Once the Submit becomes approved the Dt_APD_Apv is completed (with an permit expire date).
- Denied status means that either Submitted or Approved was "denied" If that was the latest event - it remains Denied.
- If a Dt_APD_WithDrawn_Sub (Date this record's permit was withdrawn, that is important to know it was Withdrawn)
If after a permit expired, if after a permit was Denied, if after a permit was Withdrawn... A new permit could be Submitted.
This is why each function has the Top 1 Condition (latest date) for each of the above.
Everything is driven by the most recent submit date. Plus the logic of the fields.
If all records are Null - there is No permit resulting in a 0
If the very last date was the Dt_APD_Withdrawn - then it is a 1 (last permit was withdrawn, better get busy on the next one)
If the last recent date status was Submitted, we don't care about previous records.
If the last recent date status was Approved, then we are good and don't care about history.
Just in case anyone is wondering about the last Approval Expired Date. That is done later. These permits are a "preliminary permit" step.
So, it is OK if it is expired (Dt_APD_Exp), this is covered somewhere else after this step.
ID_Wells Dt_APD_Apv txtFedStCo lngID_APD_Status Dt_APD_Sub txt_APD_Apv_Status Dt_APD_Exp Dt_APD_WithDrawn_Sub
----------- ---------------------- ---------- ---------------- ---------------------- ------------------ ---------------------- ----------------------
12 2007-11-20 00:00:00 St 1 2007-11-05 00:00:00 Approved 2008-11-20 00:00:00 NULL
390 NULL St 1 2006-02-14 00:00:00 Submitted NULL NULL
971 2007-04-24 00:00:00 St 1 2007-03-16 00:00:00 Approved 2008-04-24 00:00:00 NULL
971 NULL St 2 2008-04-11 00:00:00 Submitted NULL NULL
1619 2008-04-30 00:00:00 St 1 2008-04-18 00:00:00 Approved 2009-05-30 00:00:00 NULL
1619 2009-04-30 00:00:00 St 2 2009-04-15 00:00:00 Approved 2010-04-30 00:00:00 NULL
1619 2010-04-27 00:00:00 St 2 2010-04-08 00:00:00 Approved 2011-04-27 00:00:00 NULL
1619 2011-07-20 00:00:00 St 2 2011-05-10 00:00:00 Withdrawn NULL NULL
1619 2011-08-08 00:00:00 St 3 2011-07-20 00:00:00 Approved 2012-08-07 00:00:00 NULL
1619 2012-08-22 00:00:00 St 2 2012-08-02 00:00:00 Approved 2013-08-22 00:00:00 NULL
1637 NULL St 1 2008-06-30 00:00:00 Submitted NULL NULL
1637 2008-09-08 00:00:00 St 3 2008-08-19 00:00:00 Approved 2009-09-08 00:00:00 NULL
1637 2009-09-17 00:00:00 St 2 2009-08-31 00:00:00 Approved 2010-09-17 00:00:00 NULL
1637 2010-10-05 00:00:00 St 2 2010-09-07 00:00:00 Approved 2011-10-05 00:00:00 NULL
1637 2011-11-01 00:00:00 St 2 2011-09-29 00:00:00 Approved 2012-11-01 00:00:00 NULL
1637 2012-11-05 00:00:00 St 2 2012-10-31 00:00:00 Denied NULL NULL
3381 2010-12-06 00:00:00 St 1 2010-06-29 00:00:00 Withdrawn 2011-12-06 00:00:00 2011-01-18 00:00:00
4295 2011-01-25 00:00:00 St 1 2010-11-24 00:00:00 Returned NULL NULL
4295 2012-02-29 00:00:00 St 3 2011-11-30 00:00:00 Approved 2013-02-28 00:00:00 NULL
6530 2013-08-02 00:00:00 St 1 2013-01-17 00:00:00 Denied NULL NULL
6530 2013-11-06 00:00:00 St 3 2013-08-30 00:00:00 Denied NULL NULL
6530 2014-09-25 00:00:00 St 3 2014-07-08 00:00:00 Approved 2015-09-25 00:00:00 NULL
6540 2013-03-05 00:00:00 St 1 2013-01-21 00:00:00 Approved 2014-03-05 00:00:00 NULL
6540 2014-05-09 00:00:00 St 2 2014-03-19 00:00:00 Approved 2015-05-09 00:00:00 NULL
6541 2013-02-14 00:00:00 St 1 2012-11-27 00:00:00 Approved 2014-02-14 00:00:00 NULL
9627 2014-12-01 00:00:00 St 1 2014-10-22 00:00:00 Denied NULL NULL
CREATE TABLE [dbo].[tblAPD_Fed_ST_CO]([intID_APD_FedStCo] [int] IDENTITY(1,1) NOT NULL,
[ID_Wells] [int] NULL,
[txtFedStCo] [nvarchar](9) NULL,
[lngID_APD_Status] [int] NULL,
[lngID_Reg_Office] [int] NULL,
[Dt_APD_Sub] [datetime2](0) NULL,
[Dt_APD_Apv] [datetime2](0) NULL,
[txt_APD_Apv_Status] [nvarchar](12) NULL,
[WithdrawalType] [nvarchar](12) NULL,
[Dt_APD_WithDrawn_Sub] [datetime2](0) NULL,
[Dt_APD_WithDrawn_APProved] [datetime2](0) NULL,
CONSTRAINT [tblAPD_Fed_ST_CO$PrimaryKey] PRIMARY KEY CLUSTERED
Few of things.
One, the CREATE TABLE statement is incomplete. The constraint doesn't work. What column is the primary key?
Two, your sample data does not match the table definition.
Three, no expected results based on different inputs to the function.
Also, this is how you should post your sample data for SQL Server 2008 or newer:
insert into dbo.tblAPD_Fed_ST_CO(ID_Wells,Dt_APD_Apv,txtFedStCo,lngID_APD_Status,Dt_APD_Sub,txt_APD_Apv_Status,Dt_APD_Exp,Dt_APD_WithDrawn_Sub)
select * from
(values
(12,'2007-11-20 00:00:00','St',1,'2007-11-05 00:00:00','Approved','2008-11-20 00:00:00',NULL),
(390,NULL,'St',1,'2006-02-14 00:00:00','Submitted',NULL,NULL),
(971,'2007-04-24 00:00:00','St',1,'2007-03-16 00:00:00','Approved','2008-04-24 00:00:00',NULL),
(971,NULL,'St',2,'2008-04-11 00:00:00','Submitted',NULL,NULL),
(1619,'2008-04-30 00:00:00','St',1,'2008-04-18 00:00:00','Approved','2009-05-30 00:00:00',NULL),
(1619,'2009-04-30 00:00:00','St',2,'2009-04-15 00:00:00','Approved','2010-04-30 00:00:00',NULL),
(1619,'2010-04-27 00:00:00','St',2,'2010-04-08 00:00:00','Approved','2011-04-27 00:00:00',NULL),
(1619,'2011-07-20 00:00:00','St',2,'2011-05-10 00:00:00','Withdrawn',NULL,NULL),
(1619,'2011-08-08 00:00:00','St',3,'2011-07-20 00:00:00','Approved','2012-08-07 00:00:00',NULL),
(1619,'2012-08-22 00:00:00','St',2,'2012-08-02 00:00:00','Approved','2013-08-22 00:00:00',NULL),
(1637,NULL,'St',1,'2008-06-30 00:00:00','Submitted',NULL,NULL),
(1637,'2008-09-08 00:00:00','St',3,'2008-08-19 00:00:00','Approved','2009-09-08 00:00:00',NULL),
(1637,'2009-09-17 00:00:00','St',2,'2009-08-31 00:00:00','Approved','2010-09-17 00:00:00',NULL),
(1637,'2010-10-05 00:00:00','St',2,'2010-09-07 00:00:00','Approved','2011-10-05 00:00:00',NULL),
(1637,'2011-11-01 00:00:00','St',2,'2011-09-29 00:00:00','Approved','2012-11-01 00:00:00',NULL),
(1637,'2012-11-05 00:00:00','St',2,'2012-10-31 00:00:00','Denied',NULL,NULL),
(3381,'2010-12-06 00:00:00','St',1,'2010-06-29 00:00:00','Withdrawn','2011-12-06 00:00:00','2011-01-18 00:00:00'),
(4295,'2011-01-25 00:00:00','St',1,'2010-11-24 00:00:00','Returned',NULL,NULL),
(4295,'2012-02-29 00:00:00','St',3,'2011-11-30 00:00:00','Approved','2013-02-28 00:00:00',NULL),
(6530,'2013-08-02 00:00:00','St',1,'2013-01-17 00:00:00','Denied',NULL,NULL),
(6530,'2013-11-06 00:00:00','St',3,'2013-08-30 00:00:00','Denied',NULL,NULL),
(6530,'2014-09-25 00:00:00','St',3,'2014-07-08 00:00:00','Approved','2015-09-25 00:00:00',NULL),
(6540,'2013-03-05 00:00:00','St',1,'2013-01-21 00:00:00','Approved','2014-03-05 00:00:00',NULL),
(6540,'2014-05-09 00:00:00','St',2,'2014-03-19 00:00:00','Approved','2015-05-09 00:00:00',NULL),
(6541,'2013-02-14 00:00:00','St',1,'2012-11-27 00:00:00','Approved','2014-02-14 00:00:00',NULL),
(9627,'2014-12-01 00:00:00','St',1,'2014-10-22 00:00:00','Denied',NULL,NULL)
)dt(ID_Wells,Dt_APD_Apv,txtFedStCo,lngID_APD_Status,Dt_APD_Sub,txt_APD_Apv_Status,Dt_APD_Exp,Dt_APD_WithDrawn_Sub);
Best thing to do when posting DDL and sample data, run your code in an empty database to be sure it all runs correctly.
January 11, 2015 at 2:11 pm
Of course, the insert INTO, sorry about that.
January 11, 2015 at 2:17 pm
Mile Higher Than Sea Level (1/11/2015)
Of course, the insert INTO, sorry about that.
But also look at the columns for the sample data and table definition, they don't match.
January 11, 2015 at 2:27 pm
I had to look up how to do this. The format is from insert into a new table.
But, it left off the Primary Key intID_APD_FedStCo of INT. This is just an autocounter
Sorry to be such a pain. Was trying to leave out all of the other columns that didn't affect the outcome.
Now I see there is no actual data, will be right back
INSERT INTO [RegulatoryDB].[dbo].[ATempTable]
([ID_Wells]
,[txtFedStCo]
,[lngID_APD_Status]
,[lngID_Reg_Office]
,[Dt_APD_Sub]
,[Dt_APD_Apv]
,[txt_APD_Apv_Status]
,[blnApprovalCancled]
,[Dt_ApprovalCancled]
,[Dt_APD_Exp]
,[blnComm_Req]
,[Dt_Comm_Approved]
,[blnROW_Req]
,[Dt_DsgnSub]
,[Dt_DsgnApv]
,[Dt_NOS_Submitted]
,[txtNOS_eSubNo]
,[Dt_LocXcpt_Sub]
,[Dt_LocXcpt_apv]
,[Dt_LocDirHorz_sub]
,[Dt_LocDirHorz_Apv]
,[txtElectSubNo]
,[blnConsultation]
,[txtConsultationType]
,[blnPhotoSubmitted]
,[Dt_PhotoSubmitted]
,[txtUpdatedBy]
,[Dt_Transaction]
,[txtComment]
,[txtRecordStatus]
,[intCountyPermit]
,[txtHighwayNo]
,[dtPreOnsite]
,[dtOnsite]
,[blnTribalConnurance]
,[DtDrillingPlanRequested]
,[blnConsultationRequired]
,[IDConsultation]
,[dtConsultationConducted]
,[dtRefPhotosSubmitted]
,[txtMarkForDelete]
,[HighwayNo]
,[PermitNo]
,[SubmittedBy]
,[Match]
,[WithdrawalType]
,[Dt_APD_WithDrawn_Sub]
,[Dt_APD_WithDrawn_APProved]
,[Dt_APD_WithDrawn_LastUpdated]
,[Dt_APD_WithDrawn_Created]
,[SubmittedBy_Withdrawn]
,[CountyAddress])
VALUES
(<ID_Wells, int,>
,<txtFedStCo, nvarchar(9),>
,<lngID_APD_Status, int,>
,<lngID_Reg_Office, int,>
,<Dt_APD_Sub, datetime2(0),>
,<Dt_APD_Apv, datetime2(0),>
,<txt_APD_Apv_Status, nvarchar(12),>
,<blnApprovalCancled, bit,>
,<Dt_ApprovalCancled, datetime2(0),>
,<Dt_APD_Exp, datetime2(0),>
,<blnComm_Req, bit,>
,<Dt_Comm_Approved, bit,>
,<blnROW_Req, bit,>
,<Dt_DsgnSub, datetime2(0),>
,<Dt_DsgnApv, datetime2(0),>
,<Dt_NOS_Submitted, datetime2(0),>
,<txtNOS_eSubNo, nvarchar(50),>
,<Dt_LocXcpt_Sub, datetime2(0),>
,<Dt_LocXcpt_apv, datetime2(0),>
,<Dt_LocDirHorz_sub, datetime2(0),>
,<Dt_LocDirHorz_Apv, datetime2(0),>
,<txtElectSubNo, nvarchar(50),>
,<blnConsultation, bit,>
,<txtConsultationType, int,>
,<blnPhotoSubmitted, bit,>
,<Dt_PhotoSubmitted, datetime2(0),>
,<txtUpdatedBy, nvarchar(15),>
,<Dt_Transaction, datetime2(0),>
,<txtComment, nvarchar(50),>
,<txtRecordStatus, nvarchar(1),>
,<intCountyPermit, int,>
,<txtHighwayNo, nvarchar(25),>
,<dtPreOnsite, datetime2(0),>
,<dtOnsite, datetime2(0),>
,<blnTribalConnurance, bit,>
,<DtDrillingPlanRequested, datetime2(0),>
,<blnConsultationRequired, bit,>
,<IDConsultation, int,>
,<dtConsultationConducted, datetime2(0),>
,<dtRefPhotosSubmitted, datetime2(0),>
,<txtMarkForDelete, nvarchar(1),>
,<HighwayNo, nvarchar(25),>
,<PermitNo, nvarchar(25),>
,<SubmittedBy, nvarchar(30),>
,<Match, nvarchar(1),>
,<WithdrawalType, nvarchar(12),>
,<Dt_APD_WithDrawn_Sub, datetime2(0),>
,<Dt_APD_WithDrawn_APProved, datetime2(0),>
,<Dt_APD_WithDrawn_LastUpdated, datetime2(0),>
,<Dt_APD_WithDrawn_Created, datetime2(0),>
,<SubmittedBy_Withdrawn, nvarchar(30),>
,<CountyAddress, nvarchar(50),>)
GO
January 11, 2015 at 2:32 pm
Be sure to post the complete DDL for the table as well.
January 11, 2015 at 2:53 pm
This is the entire table
CREATE TABLE [dbo].[tblAPD_Fed_ST_CO](
[intID_APD_FedStCo] [int] IDENTITY(1,1) NOT NULL,
[ID_Wells] [int] NULL,
[txtFedStCo] [nvarchar](9) NULL,
[lngID_APD_Status] [int] NULL,
[lngID_Reg_Office] [int] NULL,
[Dt_APD_Sub] [datetime2](0) NULL,
[Dt_APD_Apv] [datetime2](0) NULL,
[txt_APD_Apv_Status] [nvarchar](12) NULL,
[blnApprovalCancled] [bit] NULL,
[Dt_ApprovalCancled] [datetime2](0) NULL,
[Dt_APD_Exp] [datetime2](0) NULL,
[blnComm_Req] [bit] NULL,
[Dt_Comm_Approved] [bit] NULL,
[blnROW_Req] [bit] NULL,
[Dt_DsgnSub] [datetime2](0) NULL,
[Dt_DsgnApv] [datetime2](0) NULL,
[Dt_NOS_Submitted] [datetime2](0) NULL,
[txtNOS_eSubNo] [nvarchar](50) NULL,
[Dt_LocXcpt_Sub] [datetime2](0) NULL,
[Dt_LocXcpt_apv] [datetime2](0) NULL,
[Dt_LocDirHorz_sub] [datetime2](0) NULL,
[Dt_LocDirHorz_Apv] [datetime2](0) NULL,
[txtElectSubNo] [nvarchar](50) NULL,
[blnConsultation] [bit] NULL,
[txtConsultationType] [int] NULL,
[blnPhotoSubmitted] [bit] NULL,
[Dt_PhotoSubmitted] [datetime2](0) NULL,
[txtUpdatedBy] [nvarchar](15) NULL,
[Dt_Transaction] [datetime2](0) NULL,
[txtComment] [nvarchar](50) NULL,
[txtRecordStatus] [nvarchar](1) NULL,
[intCountyPermit] [int] NULL,
[txtHighwayNo] [nvarchar](25) NULL,
[dtPreOnsite] [datetime2](0) NULL,
[dtOnsite] [datetime2](0) NULL,
[blnTribalConnurance] [bit] NULL,
[DtDrillingPlanRequested] [datetime2](0) NULL,
[blnConsultationRequired] [bit] NULL,
[IDConsultation] [int] NULL,
[dtConsultationConducted] [datetime2](0) NULL,
[dtRefPhotosSubmitted] [datetime2](0) NULL,
[txtMarkForDelete] [nvarchar](1) NULL,
[HighwayNo] [nvarchar](25) NULL,
[PermitNo] [nvarchar](25) NULL,
[SubmittedBy] [nvarchar](30) NULL,
[Match] [nvarchar](1) NULL,
[WithdrawalType] [nvarchar](12) NULL,
[Dt_APD_WithDrawn_Sub] [datetime2](0) NULL,
[Dt_APD_WithDrawn_APProved] [datetime2](0) NULL,
[Dt_APD_WithDrawn_LastUpdated] [datetime2](0) NULL,
[Dt_APD_WithDrawn_Created] [datetime2](0) NULL,
[SubmittedBy_Withdrawn] [nvarchar](30) NULL,
[CountyAddress] [nvarchar](50) NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL,
CONSTRAINT [tblAPD_Fed_ST_CO$PrimaryKey] PRIMARY KEY CLUSTERED
(
[intID_APD_FedStCo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- added ( ....) On Primary
January 11, 2015 at 2:57 pm
How to I get the insert text values as you asked from my statement:
SELECT * into [ATempTable2]
FROM tblAPD_Fed_ST_CO
WHERE (ID_Wells IN (1637, 12, 42, 390, 971, 9627, 6530, 6541, 6540, 1619, 3381, 4295)) AND (txtFedStCo = 'St') AND (lngID_APD_Status IN (1, 2, 3, 4))
With the sql text Insert into -- with the associated values so you can create the table? I just can't seem to figure out the process.
January 11, 2015 at 2:58 pm
Mile Higher Than Sea Level (1/11/2015)
This is the entire table
CREATE TABLE [dbo].[tblAPD_Fed_ST_CO](
[intID_APD_FedStCo] [int] IDENTITY(1,1) NOT NULL,
[ID_Wells] [int] NULL,
[txtFedStCo] [nvarchar](9) NULL,
[lngID_APD_Status] [int] NULL,
[lngID_Reg_Office] [int] NULL,
[Dt_APD_Sub] [datetime2](0) NULL,
[Dt_APD_Apv] [datetime2](0) NULL,
[txt_APD_Apv_Status] [nvarchar](12) NULL,
[blnApprovalCancled] [bit] NULL,
[Dt_ApprovalCancled] [datetime2](0) NULL,
[Dt_APD_Exp] [datetime2](0) NULL,
[blnComm_Req] [bit] NULL,
[Dt_Comm_Approved] [bit] NULL,
[blnROW_Req] [bit] NULL,
[Dt_DsgnSub] [datetime2](0) NULL,
[Dt_DsgnApv] [datetime2](0) NULL,
[Dt_NOS_Submitted] [datetime2](0) NULL,
[txtNOS_eSubNo] [nvarchar](50) NULL,
[Dt_LocXcpt_Sub] [datetime2](0) NULL,
[Dt_LocXcpt_apv] [datetime2](0) NULL,
[Dt_LocDirHorz_sub] [datetime2](0) NULL,
[Dt_LocDirHorz_Apv] [datetime2](0) NULL,
[txtElectSubNo] [nvarchar](50) NULL,
[blnConsultation] [bit] NULL,
[txtConsultationType] [int] NULL,
[blnPhotoSubmitted] [bit] NULL,
[Dt_PhotoSubmitted] [datetime2](0) NULL,
[txtUpdatedBy] [nvarchar](15) NULL,
[Dt_Transaction] [datetime2](0) NULL,
[txtComment] [nvarchar](50) NULL,
[txtRecordStatus] [nvarchar](1) NULL,
[intCountyPermit] [int] NULL,
[txtHighwayNo] [nvarchar](25) NULL,
[dtPreOnsite] [datetime2](0) NULL,
[dtOnsite] [datetime2](0) NULL,
[blnTribalConnurance] [bit] NULL,
[DtDrillingPlanRequested] [datetime2](0) NULL,
[blnConsultationRequired] [bit] NULL,
[IDConsultation] [int] NULL,
[dtConsultationConducted] [datetime2](0) NULL,
[dtRefPhotosSubmitted] [datetime2](0) NULL,
[txtMarkForDelete] [nvarchar](1) NULL,
[HighwayNo] [nvarchar](25) NULL,
[PermitNo] [nvarchar](25) NULL,
[SubmittedBy] [nvarchar](30) NULL,
[Match] [nvarchar](1) NULL,
[WithdrawalType] [nvarchar](12) NULL,
[Dt_APD_WithDrawn_Sub] [datetime2](0) NULL,
[Dt_APD_WithDrawn_APProved] [datetime2](0) NULL,
[Dt_APD_WithDrawn_LastUpdated] [datetime2](0) NULL,
[Dt_APD_WithDrawn_Created] [datetime2](0) NULL,
[SubmittedBy_Withdrawn] [nvarchar](30) NULL,
[CountyAddress] [nvarchar](50) NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL,
CONSTRAINT [tblAPD_Fed_ST_CO$PrimaryKey] PRIMARY KEY CLUSTERED
Doesn't work, get the following error:
Msg 102, Level 15, State 1, Line 56
Incorrect syntax near 'CLUSTERED'.
This does not look like SQL Server.
January 11, 2015 at 3:00 pm
Mile Higher Than Sea Level (1/11/2015)
How to I get the insert text values as you asked from my statement:SELECT * into [ATempTable2]
FROM tblAPD_Fed_ST_CO
WHERE (ID_Wells IN (1637, 12, 42, 390, 971, 9627, 6530, 6541, 6540, 1619, 3381, 4295)) AND (txtFedStCo = 'St') AND (lngID_APD_Status IN (1, 2, 3, 4))
With the sql text Insert into -- with the associated values so you can create the table? I just can't seem to figure out the process.
I manually formatted the data you originally posted. Time consuming. Take a read of the first article I reference below in my signature block regarding asking for help.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply