SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-15 : 16:03:38

Bye.

Kristen
Test

22859 Posts

Posted - 2007-10-15 : 22:34:01

"The current date starts with the first day of the previous month.
Example today is October 10, 2007 I need September 01, 2007 for this query."

You can get that with

DATEADD(Month, DATEDIFF(Month, 0, '20071010')-1, 0)

I've had a look at your code but it seems to contain a whole load of debugging stuff (outputting data for various scenarios by the looks of it), the first cursor isn't used, and so on that its hard to see the wood for the trees.

Do you really need a cursor? I would have thought you could just do a set based:

INSERT INTO #TP_TIES_OVerlaping_Dates
SELECT ...
FROM ...

It might help if you explain what this Sproc needs to do pls

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-15 : 22:48:58

Additional thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91023

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-16 : 10:21:35

Good Morning Kristen:

Thank You, You are the first person, that has given me a clear answer. I am going to explain in plain English What this SP is suppose to do.

I have to find any station_nbr that has more than one group_code and that the current date = (DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0) The current date starts with the first day of the previous month.
Example today is October 10, 2007 I need September 01, 2007 for this query.
To insert the record it needs to be active meaning that the current date is in between Beg_eff_date and end_eff_date, and the end_eff_date is = ‘12/31/3000’

I need to write SP(cursor) that reads line by line this table and that insert the answer: INSERT INTO #TP_TIES_OVerlaping_Dates.

Example:
Current_date: 09/01/2007

Group_Code Station_nbr beg_eff_date end_eff_date
0000003 00371101 03/12/2006 12/31/3000
0000016 00371101 07/08/2007 12/31/3000

The above explanation is for the first type of Error.

The second Type of Error:
If the Group Code ends with "W" and the End_effective_date = '12/31/3000' and the W_Beg_date is Part of the Temp Table(I to create the W_Beg_Date Column) -- is GREATER than the End_eff_date. I want to Insert this Record into the Table. I need to set sometype of Record Control to make the different between one error to another one.

I thank you so much for your help.....I hope this is clear to understand for everybody.

End

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 11:28:19

May if you can provide proper and accurate sample data together with expected output we can help you.
There are many routines available here SQLTeam that may fit your purposes.

Are you only inserting in one table selecting from another and just substract one month from date?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 11:33:25

Is this something similar to what you want?

DECLARE	@Sample TABLE (FromDate DATETIME, ToDate DATETIME)

INSERT	@Sample
SELECT	'20070401', '20070701' UNION ALL
SELECT	'20070101', '20070301' UNION ALL
SELECT	'20070501', '20070901'

/*
1 2 3 4 5 6 7 8 9
*****************
      |-----|
|---|          
        |-------|
*****************
*/

SELECT		s1.FromDate,
		s1.ToDate,
		s2.FromDate,
		s2.ToDate
FROM		@Sample AS s1
CROSS JOIN	@Sample AS s2
WHERE		s1.ToDate >= s2.FromDate
		AND s1.FromDate <= s2.ToDate
		AND s1.FromDate < s2.FromDate

E 12°55'05.25"
N 56°04'39.16"

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-16 : 11:48:27

<code> Group_Code ***** Station_nbr ******* Beg_eff_date***** End_Eff_date
00000005 00251 08/03/2007 12/31/3000
00000015 00251 02/01/2007 12/31/3000
00000015N 00251 02/01/2007 12/31/3000

Current_date = 09/01/20007 (already have the correct SQL syntax)

All of them coming from TIES_Temp.dbo.station_group----**** In the first path I don't need the record that ends with "N" to eliminate that record, I need to Select *
from TIES_Temp.dbo.group_detail
Where group_type = 'NPN'. I need the two first Records both with the same station_nbr but different group_code
00000005 00251 08/03/2007 12/31/3000
00000015 00251 02/01/2007 12/31/3000 </code>

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 23:29:42

Original posts:

10/15/2007 : 16:03:38

Hi:

Once, I have the selection criteria working for the first cursor. I need to write the Stored Procedure for the rest of cursors. UUUUFFFff, this is amazingly difficult for me. Here is what I have done so far.

TIES_Temp.dbo.station_group

Error Report:
Criteria for Error 1:

I have to find any station_nbr that has more than one group_code and that the current date = (Month-1, getdate) I am not sure if this is the right way to write the previous month from the current date. The current date starts with the first day of the previous month.
Example today is October 10, 2007 I need September 01, 2007 for this query.
To insert the record it needs to be active meaning that the current date is in between Beg_eff_date and end_eff_date, and the end_eff_date is = ‘12/31/3000’

I need to write SP(cursor) that reads line by line this table and that insert the answer: INSERT INTO #TP_TIES_OVerlaping_Dates.

Example:
Current_date: 09/01/2007

Group_Code Station_nbr beg_eff_date end_eff_date
0000003 00371101 03/12/2006 12/31/3000
0000016 00371101 07/08/2007 12/31/3000

This records needs to be in the report. There are other 2 Errors but if you give me a hand with this one, I can figure it out the other two.

Here is the Query that I have created to get all the data that all the Selection Criteria.
TIES_Temp.dbo.station_group and dbo.group_detail

The first Query is the Selection Criteria for all station_nbr records with more than one group_code within the Station_Group Table (active records).

The Second query is coming from Group Detail Table where group type = ‘NPN’. This group type filter out all the group_code that ends with N or W. Reflecting only the Primary Code the group_code without any letters at the end. I need to find a way to combine both queries into one, so my end result will be all station_nbr records with more than one group_code, all active, within the Station_Group Table.

1. My question is:
How can I write the syntax to put the two queries together?

________________________________________________________________________

/* Declare Overlaping Dates 1 cursor*/
DECLARE Overlaping_Duplicate_cursor INSENSITIVE CURSOR FOR
Select * From
(SELECT group_code,station_nbr, beg_eff_date, end_eff_date
From TIES_Temp.dbo.station_group
where(dateadd(month,-1,getdate()) Between beg_eff_date and end_eff_date))
a,
(Select station_nbr, count(station_nbr) as ErrorDuplicate
From TIES_Temp.dbo.station_group
GROUP BY station_nbr
HAVING (COUNT(station_nbr) > 1 )) b
Where a.station_nbr = b.station_nbr
FOR READ ONLY

INSERT INTO #TP_TIES_OVerlaping_Dates(group_code,station_nbr, beg_eff_date, end_eff_date,
W_beg_date, W_End_date,N_Beg_date,N_End_Date,Debug)
values (@groupcode,@stationnumber,@begdate, @endeffdate,@W_beg_date,@W_End_Date,@N_Beg_Date,@N_End_Date,@TEST)

select
group_code, count(*)As ErrorDuplicate
from TIES_Temp.dbo.station_group
group by group_code, station_nbr, beg_eff_date, end_eff_date
having count(*)> 1
Question 2

I don’t understand what do I need this part inside the SP? What exactly this selection does? At the end I need to enter in the report only the records that have station_nbr actives for more than one group_code

Here is the Error that I am getting when I try to join both queries to give the selection criteria for the Cursor.

Here is the entire Stored Procedure

CREATE PROC dbo.swn_sp_TIES_Overlap_Dates

AS
/ overlaping dates.
*
*
*********************************************************************
*
* Modification History:
*
*********************************************************************

--*************************************************************
--SET SQL STMT BASED ON SQL TYPE PASSED IN
--*************************************************************/

/* Create a Temp Table */

Create Table #TP_TIES_OVerlaping_Dates(
group_code char (12),
station_nbr char (20),
beg_eff_date datetime,
end_eff_date datetime,
C02_pct float,
Current_Month datetime,
W_Beg_date datetime,
W_End_date datetime,
N_beg_date datetime,
N_End_date datetime,
debug varchar(255))

--------------------------------First Temp Table Selection-------------------------------------------------

DECLARE @groupcode char (12),
@stationnumber char(20),
@Error Varchar(2),
@begdate datetime,
@endeffdate datetime,
@record_found_flag char(1),
@Records int,
@current_date datetime,
@Count varchar(10),
@TEST varchar(10)

/* Set the record_found_flag */
SELECT @record_found_flag = 'N'

/* Declare Overlaping Dates 1 cursor*/
DECLARE Overlaping_Duplicate_cursor INSENSITIVE CURSOR FOR
Select * From
(SELECT group_code,station_nbr, beg_eff_date, end_eff_date
From TIES_Temp.dbo.station_group
where(dateadd(month,-1,getdate()) Between beg_eff_date and end_eff_date))
a,
(Select station_nbr, count(station_nbr) as ErrorDuplicate
From TIES_Temp.dbo.station_group
GROUP BY station_nbr
HAVING (COUNT(station_nbr) > 1 )) b
Where a.station_nbr = b.station_nbr
FOR READ ONLY

INSERT INTO #TP_TIES_OVerlaping_Dates(group_code,station_nbr, beg_eff_date, end_eff_date)
values (@groupcode,@stationnumber,@begdate, @endeffdate)

select
group_code, count(*)As ErrorDuplicat
from TIES_Temp.dbo.station_group
group by group_code
having count(*)> 1

DEALLOCATE Overlaping_Duplicate_cursor
--------------------------------2nd Compare Temp Table against Station Group Selection-------------------------------------------------

DECLARE @groupcode2 char (12),
@stationnumber2 char(20),
@Error2 Varchar(2),
@begdate2 datetime,
@endeffdate2 datetime,
@current_C02 float,
@record_found_flag2 char(1),
@N_Beg_date datetime,
@N_End_date datetime,
@W_Beg_date datetime,
@W_End_date datetime,
@Records2 int,
@current_date2 datetime,
@Count2 varchar(10),
@TEST2 varchar(10)

/* Declare Overlaping Dates 2 cursor to read from the Temp Table */
DECLARE OverlapingDates_Report_cursor INSENSITIVE CURSOR FOR
SELECT
a.group_code, a.station_nbr,a.beg_eff_date,a.end_eff_date
from #TP_TIES_OVerlaping_Dates a, TIES_Temp.dbo.station_group b
where(dateadd(month,-1,getdate()) Between b.beg_eff_date and b.end_eff_date)
and (a.group_code = b.group_code)
FOR READ ONLY

INSERT INTO #TP_TIES_OVerlaping_Dates(group_code,station_nbr, beg_eff_date, end_eff_date,
W_beg_date, W_End_date,N_Beg_date,N_End_Date,Debug)
values (@groupcode,@stationnumber,@begdate, @endeffdate,@W_beg_date,@W_End_Date,@N_Beg_Date,@N_End_Date,@TEST)

/* Procedure Division*/
OPEN OverlapingDates_Report_cursor
FETCH NEXT FROM OverlapingDates_Report_cursor INTO @groupcode, @stationnumber, @begdate, @endeffdate

WHILE @@FETCH_STATUS = 0
BEGIN
-- Begin cursor and set the @record_found_flag
SELECT @record_found_flag = 'N'
Set @N_Beg_date = ''
Set @N_End_date = ''
Set @W_Beg_date = ''
Set @W_End_date = ''
---- Set @groupcode = ''
Set @current_date = dateadd(month,-1,getdate())

---------Check for 'W' Records'---------
If @groupcode like '%W'
BEGIN
Select @W_Beg_date = Beg_eff_date, @W_End_date= End_eff_date
From TIES_Temp.dbo.station_group
Where (station_nbr = @stationnumber)
and RIGHT(rtrim(group_code),1) = 'W'
AND (@current_date between Beg_eff_date and end_eff_date)
END

---------Check for 'N' Records'---------
If @groupcode like '%N'
BEGIN
Select @N_Beg_date = Beg_eff_date, @N_End_date= End_eff_date
From TIES_Temp.dbo.station_group
Where (station_nbr = @stationnumber)
and RIGHT(rtrim(@groupcode),1) = 'N'
AND (@current_date between Beg_eff_date and end_eff_date)
END

If @W_End_date = '12/31/3000'
Begin
Select *
From TIES_Temp.dbo.station_group
where @W_beg_date > end_eff_date
End

If @N_End_date = '12/31/3000'
Begin
Select *
From TIES_Temp.dbo.station_group
Where @N_Beg_date > beg_eff_date
End

INSERT INTO #TP_TIES_OVerlaping_Dates(group_code,station_nbr, beg_eff_date, end_eff_date,
W_beg_date, W_End_date,N_Beg_date,N_End_Date,Debug)
values (@groupcode,@stationnumber,@begdate, @endeffdate,@W_beg_date,@W_End_Date,@N_Beg_Date,@N_End_Date,@TEST)

FETCH NEXT FROM OverlapingDates_Report_cursor INTO @groupcode,@stationnumber, @begdate, @endeffdate

-- End Overlap Report--------
END

Select *
From #TP_TIES_OVerlaping_Dates

CLOSE OverlapingDates_Report_cursor
---Close Overlaping_Duplicate_cursor

/* Remove cursor structures (Deallocate) */

DEALLOCATE OverlapingDates_Report_cursor
GO

Let me know if you need anything or if you have any problems.
Osiris

10/16/2007 : 10:21:35

Good Morning Kristen:

Thank You, You are the first person, that has given me a clear answer. I am going to explain in plain English What this SP is suppose to do.

I have to find any station_nbr that has more than one group_code and that the current date = (DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0) The current date starts with the first day of the previous month.
Example today is October 10, 2007 I need September 01, 2007 for this query.
To insert the record it needs to be active meaning that the current date is in between Beg_eff_date and end_eff_date, and the end_eff_date is = ‘12/31/3000’

I need to write SP(cursor) that reads line by line this table and that insert the answer: INSERT INTO #TP_TIES_OVerlaping_Dates.

Example:
Current_date: 09/01/2007

Group_Code Station_nbr beg_eff_date end_eff_date
0000003 00371101 03/12/2006 12/31/3000
0000016 00371101 07/08/2007 12/31/3000

The above explanation is for the first type of Error.

The second Type of Error:
If the Group Code ends with "W" and the End_effective_date = '12/31/3000' and the W_Beg_date is Part of the Temp Table(I to create the W_Beg_Date Column) -- is GREATER than the End_eff_date. I want to Insert this Record into the Table. I need to set sometype of Record Control to make the different between one error to another one.

I thank you so much for your help.....I hope this is clear to understand for everybody.

End

10/16/2007 : 11:48:27

<code> Group_Code ***** Station_nbr ******* Beg_eff_date***** End_Eff_date
00000005 00251 08/03/2007 12/31/3000
00000015 00251 02/01/2007 12/31/3000
00000015N 00251 02/01/2007 12/31/3000

Current_date = 09/01/20007 (already have the correct SQL syntax)

All of them coming from TIES_Temp.dbo.station_group----**** In the first path I don't need the record that ends with "N" to eliminate that record, I need to Select *
from TIES_Temp.dbo.group_detail
Where group_type = 'NPN'. I need the two first Records both with the same station_nbr but different group_code
00000005 00251 08/03/2007 12/31/3000
00000015 00251 02/01/2007 12/31/3000 </code>

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 01:32:23

What happened?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 02:00:29

OP deleted original post

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 02:27:49