April 17, 2013 at 4:11 am
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
April 17, 2013 at 4:21 am
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
April 17, 2013 at 4:53 am
not getting proper result Sir.
I have tried.
April 17, 2013 at 4:55 am
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.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 17, 2013 at 5:03 am
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
April 17, 2013 at 5:24 am
kiran.rajenimbalkar (4/17/2013)
if end date column would be null then pls treat as 20790606please 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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 17, 2013 at 6:10 am
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
April 17, 2013 at 6:22 am
Not sure but I hope this is what you need
SELECTA1.*, COALESCE( A1.fg_eff_dt_key, 20790606 ) - COALESCE( A2.fg_eff_dt_key, 20790606 ) AS DiffDays
FROMabc AS A1
LEFT OUTER JOINabc AS A2 ON A1.fg_seq_nbr = A2.fg_seq_nbr - 1
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 17, 2013 at 6:33 am
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.
April 17, 2013 at 6:42 am
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
SELECTA1.*, COALESCE( A1.fg_eff_dt_key, 20790606 ) - COALESCE( A2.fg_eff_dt_key, 20790606 ) AS DiffDaysFROMabc AS A1
LEFT OUTER JOINabc 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.
April 17, 2013 at 6:46 am
I had a copy/paste issue, here is updated code:
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_end_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_end_dt_key as varchar) as date) end) DayDiff
from abc;
drop table abc;
April 17, 2013 at 6:49 am
Cadavre (4/17/2013)
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 NULLBEGIN
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
SELECTA1.*, COALESCE( A1.fg_eff_dt_key, 20790606 ) - COALESCE( A2.fg_eff_dt_key, 20790606 ) AS DiffDaysFROMabc AS A1
LEFT OUTER JOINabc 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.
Two things. One, why the cross applies here. I don't think they are really needed. Two, the OP did say to treat NULL using getdate() (even though he said it like this: gatedate).
April 17, 2013 at 7:00 am
Yes its very help full for me..
Thanks all of you Sir....for helping me out in this solution
April 17, 2013 at 7:10 am
Yes your solution was very helpful for me
Thank you Sir for helping me out in this situation
April 17, 2013 at 7:26 am
Lynn Pettis (4/17/2013)
Two things. One, why the cross applies here. I don't think they are really needed. Two, the OP did say to treat NULL using getdate() (even though he said it like this: gatedate).
Replying to your first point, I find it easier to read.
Replying to your second point, I missed him say anything about getdate but I did spot this: -
kiran.rajenimbalkar (4/17/2013)
if end date column would be null then pls treat as 20790606
I assumed that he'd want the same for a start date, but getdate does make more sense.
Just for completeness, we'd fix what I posted like this: -
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 ISNULL(CAST(CAST(fg_eff_dt_key AS VARCHAR(9)) AS DATE), GETDATE()) 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;
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply