Comparing dates in SQL Server - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Comparing dates in SQL Server

Author

Topic

amitranjan
Starting Member

45 Posts

Posted - 2008-12-17 : 05:06:46

Hi, i have a small issue, please help me out. Issue is that , I have two fields in my table, insurance start date and insurance end date. Insurance start date is mandatory at UI while end date is not mandatory. One can skip it. I am saving both fields as sql server datetime. For empty fields itwill save 01/01/1900 12:00:00 AM to tables.
One more condition here is , i have various insurance types, like primary , secondary, tertiary. one can add any insurace to any date but with a condition , that is no new primary insurance exists in between any existing primary insurances start and end date. Till this every thing is fine and i did it too..but now i recieved one more condition, that is , if end user do not fills any end date and insurance type is primary, then he/she cannot add any new primary after that date..
That is if i added 02/12/2008 as start date and left the end date field blank..then i will not be able to add any primary isurances on or after 2nd december. I can add before that day but not after that day.

I want it to achieve thru sql queries.

Select * from insurance where status = 1 and filing_id = 2300 and patient_id = @PatientID and Insurance_Start_date <= NewEffectiveDate and Insurance_End_date >= @NewEffectiveDate.
It returns row and from there, i used to put validations.

This is what i have done earlier. Now i want to put a check for above issue .

amit Ranjan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 05:17:54

add a INSTEAD OF iNSERT trigger to cehck this condition


CREATE TRIGGER checkdata
ON YourTable
INSTEAD OF INSERT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM Table t 
               INNER JOIN INSERTED i 
               ON i.insurance_type=t.insurance_type
               WHERE t.insurance_type='Primary'
               AND i.insurance_start_date > t.insurance_start_date
               AND DATEDIFF(dd,0,t.insurance_end_date)=0)
INSERT INTO Table
SELECT * FROM INSERTED
END

amitranjan
Starting Member

45 Posts

Posted - 2008-12-17 : 06:50:49

Hi will you please explain me how this query work. I dont want to insert data. I just want to check here. Where condition satisfies or not?

amit Ranjan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 06:56:50

what you told in first post was to check the condition and insert only if it satisfies it.

amitranjan
Starting Member

45 Posts

Posted - 2008-12-17 : 07:38:08

quote:
Originally posted by visakh16

what you told in first post was to check the condition and insert only if it satisfies it.

Let me explain you clearly...first look at my exisitng query
Select *
from m_patient_insurance_map
where
status = 1 and fk_filing_id = 2300 and FK_patient_id = @PatientID
and
Insurance_Start_date <= @NewEffectiveDate and
Insurance_End_date >= @NewEffectiveDate
.......Actually i am developing a Web App in ASP.Net 2008. So this is a sp which return a row. I capture this in a function and returns it as a datatable to frontend. From frontend i checks if it has any number of rows, if it has..then generates a javascript alert to the user, else allow user to proceed further with insert statement which is another sp. The above sp is only select sp, that returns me no of rows and i need only number of rows.

Now let me tell you the exact condition again.
There are two fields Insurance_Start_date and Insurance_End_Date in table. Also for the same I have in UI. But in UI end date is not mandatory, user can leave it blank. I am having three types of insurances, Primary, Secondary and tertiary.
We are considering here only primary insurace whose filing order is 2301. So lets take an example where, i had added insurance to my database, which is of type primary and its start date is 12/02/2008[mm/dd/yyyy] and end date is 12/22/2008[mm/dd/yyy]that means insurance's validity is 2nd dec 2008 to 22nd dec 2008.

in my first case, i want to add another primary insurance then its start date must not lie in between 2nd dec to 22 dec. valid dates are before 2nd dec or after 26th january.

in my second case, i had added insurance of type primary and supplied only its start date that is 2nd jan 2009 and left the end date field blank. And inserted it to db. Please remember end date is not mandatory from UI and a user can leave it blank. So my default value "01/01/1900 12:00:00 AM"will get added in place of null value.

in 3rd case, i am again inserting a new primary insurance, and i had opted start date that is 3rd April 2009. Now here i want to throw an alert to user that is why because if you look at previous case[2nd one ]i m having a primary insurance whose start date is mentioned but end date is absent. That is new date is 3rd of April and previously i have mentioned 2nd jan 2009. So now i want to throw an alert to user that he/she cannot add any new insurance after 2nd jan 2009.

for throwing error , I need such a query which return any number of row, so that from UI i can perform if rows exists then throw alert else let user to proceed with inserting new record.
Hpoing , my issue will be now clear to all of you..

amit Ranjan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 07:57:30

i still think what you need is this


CREATE TRIGGER checkdata
ON YourTable
INSTEAD OF INSERT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM Table t 
               INNER JOIN INSERTED i 
               ON i.insurance_type=t.insurance_type
               WHERE t.insurance_type='Primary'
               AND i.filling_order=2300
               AND i.insurance_start_date > t.insurance_start_date
               AND (DATEDIFF(dd,0,t.insurance_end_date)=0 OR i.insurance_start_date <t.insurance_end_date))
BEGIN
INSERT INTO Table
SELECT * FROM INSERTED
END
ELSE
BEGIN
RAISERROR 'Entered date values overlap with existing record',10,1
END
END

this trigger will be fired everytime you try to insert record and it will check if there exists any records in table of type primary & filling order 2300 with dates which happens to be in range of current entered dates or any record which has start date before this and default end. if none exists, it inserts else it raises error to user. this can be captured and shown in front end app to user.

amitranjan
Starting Member

45 Posts

Posted - 2008-12-17 : 08:15:03

When i try to run this query at my sql server, it gives error..
Msg 156, Level 15, State 1, Procedure checkdata, Line 6
Incorrect syntax near the keyword 'Table'.
Msg 156, Level 15, State 1, Procedure checkdata, Line 14
Incorrect syntax near the keyword 'Table'.
Msg 156, Level 15, State 1, Procedure checkdata, Line 17
Incorrect syntax near the keyword 'ELSE'.

I modified your query to this

CREATE TRIGGER checkdata
ON M_PATIENT_INSURANCE_MAP
INSTEAD OF INSERT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM Table t
INNER JOIN INSERTED i
ON i.insurance_type=t.insurance_type
WHERE t.insurance_type='Primary'
AND i.filling_order=2300
AND i.insurance_start_date > t.insurance_start_date
AND (DATEDIFF(dd,0,t.insurance_end_date)=0 OR i.insurance_start_date <t.insurance_end_date))
BEGIN
INSERT INTO Table
SELECT * FROM INSERTED
END
ELSE
BEGIN
RAISERROR 'Entered date values overlap with existing record',10,1
END
END

amit Ranjan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 08:17:49

i gave table for illustration you should be replacing it with your table name

amitranjan
Starting Member

45 Posts

Posted - 2008-12-17 : 08:27:26

Now i am getting this......
Msg 102, Level 15, State 1, Procedure checkdata, Line 19
Incorrect syntax near 'Entered date values overlap with existing record'.

amit Ranjan

amitranjan
Starting Member

45 Posts

Posted - 2008-12-17 : 09:18:40

Please help me out

amit Ranjan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 10:40:50

[code]
CREATE TRIGGER checkdata
ON M_PATIENT_INSURANCE_MAP
INSTEAD OF INSERT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM Table t
INNER JOIN INSERTED i
ON i.insurance_type=t.insurance_type
WHERE t.insurance_type='Primary'
AND i.filling_order=2300
AND i.insurance_start_date > t.insurance_start_date
AND (DATEDIFF(dd,0,t.insurance_end_date)=0 OR i.insurance_start_date <t.insurance_end_date))
BEGIN
INSERT INTO Table
SELECT * FROM INSERTED
END
ELSE
BEGIN
RAISERROR ('Entered date values overlap with existing record',10,1)
END
END
[/code]

amitranjan
Starting Member

45 Posts

Posted - 2008-12-18 : 04:53:27

Sorry bro, this is not working..if possible please suggest me some alternative sql queries. Can't I use simple if else condition or switch case statement for checking that in sql. If yes, please supply me with that....It will be highly appreciated.

amit Ranjan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources