SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Substract End Dates


Substract End Dates

Author
Message
kiran.rajenimbalkar
kiran.rajenimbalkar
SSC-Addicted
SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)

Group: General Forum Members
Points: 429 Visits: 331
Hi Professional
I need to substract the end date as like below
2-1,3-2,4-3, and showing in new column like datediif_days

if there would be null we have to treat as gatedate.

CREATE TABLE  abc
(gg_key int,
fg_sta_key int,
fg_eff_dt_key int,
fg_end_dt_key int,
fg_seq_nbr int)



insert into abc values(19925,2,20130128,20130128,1)
insert into abc values (19925,8,20130128,20130128,2)
insert into abc values(19925,1,20130129,20130416,3)
insert into abc values (19925,5,20130417,NULL,4)



Regards,
KiranR
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35185 Visits: 16670
Kiran

You need to add a computed column to the table. Use the DATEDIFF function to set its value.

Incidentally, why are you storing date values as int in your table?

John
kiran.rajenimbalkar
kiran.rajenimbalkar
SSC-Addicted
SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)

Group: General Forum Members
Points: 429 Visits: 331
not getting proper result Sir.
I have tried.
Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6080 Visits: 5280
kiran.rajenimbalkar (4/17/2013)
not getting proper result Sir.
I have tried.


Can you also post the expected result based on your sample data?
That will help people understand what you need exactly.


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
kiran.rajenimbalkar
kiran.rajenimbalkar
SSC-Addicted
SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)

Group: General Forum Members
Points: 429 Visits: 331
if end date column would be null then pls treat as 20790606

please check only fg_end_dt_key column

(20790606-20130416) ---660190
(20130416-20130128) --288
(20130128-20130128) --0
Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6080 Visits: 5280
kiran.rajenimbalkar (4/17/2013)
if end date column would be null then pls treat as 20790606

please check only fg_end_dt_key column

(20790606-20130416) ---660190
(20130416-20130128) --288
(20130128-20130128) --0


This doesn't seem to be related to the sample data provided
Your sample data has 4 rows inserted
We would like to know what value would you like the additional column to hold for these 4 rows


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
kiran.rajenimbalkar
kiran.rajenimbalkar
SSC-Addicted
SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)

Group: General Forum Members
Points: 429 Visits: 331
Hi Sir,

I would like to show data in additional column by substrating end date column

(20790606-20130128) ---660478 days
(20130416-20130128) --288 days
(20130128-20130128) --1 days
Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6080 Visits: 5280
Not sure but I hope this is what you need

SELECT   A1.*, COALESCE( A1.fg_eff_dt_key, 20790606 ) - COALESCE( A2.fg_eff_dt_key, 20790606 ) AS DiffDays
FROM abc AS A1
LEFT OUTER JOIN abc AS A2 ON A1.fg_seq_nbr = A2.fg_seq_nbr - 1




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96373 Visits: 38981
Here is your sample data and some code. You tell us what the problem is:



CREATE TABLE abc
(gg_key int,
fg_sta_key int,
fg_eff_dt_key int,
fg_end_dt_key int,
fg_seq_nbr int)

insert into abc values(19925,2,20130128,20130128,1)
insert into abc values (19925,8,20130128,20130128,2)
insert into abc values(19925,1,20130129,20130416,3)
insert into abc values (19925,5,20130417,NULL,4);

select
gg_key,
fg_sta_key,
fg_eff_dt_key,
fg_end_dt_key,
fg_seq_nbr,
cast(cast(fg_eff_dt_key as varchar) as date) as fg_eff_dt,
case when fg_end_dt_key is null then getdate() else cast(cast(fg_eff_dt_key as varchar) as date) end as fg_end_dt,
datediff(day,cast(cast(fg_eff_dt_key as varchar) as date),case when fg_end_dt_key is null then getdate() else cast(cast(fg_eff_dt_key as varchar) as date) end) DayDiff
from abc;

drop table abc;




Questions:

1) Why all the "key" fields (fg_sta_key, fg_eff_dt_key, fg_end_dt_key)
2) Why are date values being stored as integers, these should be stored using date or date/time data types.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9196 Visits: 8492
Why are you storing dates as INT? To get the number of days difference, you're going to need to convert to DATE.

Try something like this: -
IF OBJECT_ID('abc') IS NOT NULL
BEGIN
DROP TABLE abc;
END
CREATE TABLE abc (gg_key INT, fg_sta_key INT, fg_eff_dt_key INT, fg_end_dt_key INT, fg_seq_nbr INT);

INSERT INTO abc
VALUES (19925, 2, 20130128, 20130128, 1);
INSERT INTO abc
VALUES (19925, 8, 20130128, 20130128, 2);
INSERT INTO abc
VALUES (19925, 1, 20130129, 20130416, 3);
INSERT INTO abc
VALUES (19925, 5, 20130417, NULL, 4);


SELECT gg_key, fg_sta_key, fg_eff_dt_key, fg_end_dt_key, fg_seq_nbr,
DATEDIFF(d,DATE_fg_eff_dt_key,DATE_fg_end_dt_key) AS diffDays
FROM abc
CROSS APPLY (SELECT CAST(CAST(ISNULL(fg_eff_dt_key,20790606) AS VARCHAR(9)) AS DATE) AS DATE_fg_eff_dt_key,
CAST(CAST(ISNULL(fg_end_dt_key,20790606) AS VARCHAR(9)) AS DATE) AS DATE_fg_end_dt_key) ca;



That results in: -
gg_key      fg_sta_key  fg_eff_dt_key fg_end_dt_key fg_seq_nbr  diffDays
----------- ----------- ------------- ------------- ----------- -----------
19925 2 20130128 20130128 1 0
19925 8 20130128 20130128 2 0
19925 1 20130129 20130416 3 77
19925 5 20130417 NULL 4 24156


Kingston Dhasian (4/17/2013)
Not sure but I hope this is what you need

SELECT   A1.*, COALESCE( A1.fg_eff_dt_key, 20790606 ) - COALESCE( A2.fg_eff_dt_key, 20790606 ) AS DiffDays
FROM abc AS A1
LEFT OUTER JOIN abc AS A2 ON A1.fg_seq_nbr = A2.fg_seq_nbr - 1



That isn't using those stupidly stored dates as dates. e.g. If you use "20130301" and "20130401", your code will tell us that there are -660,305 "DiffDays". Whereas from a human perspective, we can see that there is 1 month so we know that since there are 31 days in March that the answer is 31. Obviously I'm assuming that the OP is storing dates as INT, but with column names that are called "dt_key" and data that looks so much like a date, I think that's a safe assumption.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search