Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Substract End Dates Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 4:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 9, 2014 7:21 AM
Points: 98, Visits: 251
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
Post #1443175
Posted Wednesday, April 17, 2013 4:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 5,306, Visits: 9,686
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
Post #1443177
Posted Wednesday, April 17, 2013 4:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 9, 2014 7:21 AM
Points: 98, Visits: 251
not getting proper result Sir.
I have tried.
Post #1443184
Posted Wednesday, April 17, 2013 4:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 7, 2014 3:30 AM
Points: 2,631, Visits: 4,722
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/
Post #1443185
Posted Wednesday, April 17, 2013 5:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 9, 2014 7:21 AM
Points: 98, Visits: 251
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
Post #1443186
Posted Wednesday, April 17, 2013 5:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 7, 2014 3:30 AM
Points: 2,631, Visits: 4,722
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/
Post #1443198
Posted Wednesday, April 17, 2013 6:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 9, 2014 7:21 AM
Points: 98, Visits: 251
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
Post #1443210
Posted Wednesday, April 17, 2013 6:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 7, 2014 3:30 AM
Points: 2,631, Visits: 4,722
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/
Post #1443219
Posted Wednesday, April 17, 2013 6:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:09 PM
Points: 23,218, Visits: 31,905
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.




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)
Post #1443221
Posted Wednesday, April 17, 2013 6:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:00 AM
Points: 2,433, Visits: 7,501
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1443226
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse