July 18, 2014 at 1:03 am
Hi experts,
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)
/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,
[assgn_dtm] [datetime] NOT NULL,
[complet_dtm] [datetime] NULL,
[days_of_diff] [int] NULL
) ON [PRIMARY]
GO
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(1,1000090,1000031,'6/4/2012 7:41:59 PM','6/8/2012 11:59:38 PM',4)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(2,1000090,1000031,'6/4/2012 7:41:59 PM','6/8/2012 11:59:42 PM',4)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(3,1000090,1000031,'6/8/2012 11:59:48 PM','6/11/2012 11:59:29 PM',3)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(4,1000090,1000031,'6/8/2012 11:59:48 PM','6/11/2012 11:59:28 PM',3)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(5,1000090,1000031,'6/11/2012 11:59:31 PM','6/12/2012 11:59:09 PM',1)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(6,1000090,1000031,'6/11/2012 11:59:31 PM','6/12/2012 11:59:09 PM',1)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(7,1000090,1000031,'6/12/2012 11:59:11 PM','6/14/2012 11:59:57 PM',2)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(8,1000090,1000031,'6/12/2012 11:59:11 PM','6/14/2012 11:59:56 PM',2)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(9,1000090,1000031,'6/15/2012 11:59:19 PM','6/18/2012 11:59:14 PM',3)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(10,1000090,1000031,'6/15/2012 11:59:19 PM','6/18/2012 11:59:14 PM',3)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(11,1000090,1000031,'6/18/2012 11:59:16 PM','6/19/2012 11:59:10 PM',1)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(12,1000090,1000031,'6/18/2012 11:59:16 PM','6/19/2012 11:59:09 PM',1)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(13,1000090,1000031,'6/19/2012 11:59:13 PM','6/20/2012 11:59:17 PM',1)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(14,1000090,1000031,'6/19/2012 11:59:13 PM','6/20/2012 11:59:18 PM',1)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(15,1000090,1000031,'6/20/2012 11:59:21 PM','6/21/2012 11:59:09 PM',1)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(16,1000090,1000031,'6/20/2012 11:59:21 PM','6/21/2012 11:59:09 PM',1)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(17,1000090,1000031,'6/21/2012 11:59:11 PM','6/22/2012 11:59:46 PM',1)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(18,1000090,1000031,'6/21/2012 11:59:11 PM','6/22/2012 11:59:48 PM',1)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(19,1000090,1000031,'6/22/2012 11:59:55 PM','6/25/2012 11:59:30 PM',3)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(20,1000090,1000031,'6/22/2012 11:59:55 PM','6/25/2012 11:59:29 PM',3)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(21,1000090,1000031,'6/25/2012 11:59:34 PM','6/27/2012 12:00:27 AM',2)
Insert into [temp_tbl] ([tbl_id],[cs_id],[USERID],[assgn_dtm],[complet_dtm],[days_of_diff]) Values(22,1000090,1000031,'6/25/2012 11:59:34 PM','6/27/2012 12:00:29 AM',2)
GO
-----------------------------------------------------------------
select * from temp_tbl
------------------------------------------------------------------------------------
I got the logic for sum of days diff and also to calculate the count of assgn_dtm if this appearing in complet_dtm, but I am not happy with the solution
1st query
--------------
select [cs_id],userid
--,[assgn_dtm],[complet_dtm]
--,COUNT(diff_of_days)
,SUM(diff_of_days)
from
(
select [cs_id],USERID,[assgn_dtm],[complet_dtm],DATEDIFF(dd,[assgn_dtm],[complet_dtm]) diff_of_days,
ROW_NUMBER () over(partition by convert(varchar,[assgn_dtm],101),convert(varchar,[complet_dtm],101)order by [assgn_dtm]) rownum
from temp_tbl where [cs_id]=1000090 and userid =1000031
) aa where rownum=1
group by [cs_id],userid
order by 3
2nd query
--------------
select * from
(
select [assgn_dtm],[cs_id],userid ,
ROW_NUMBER () over(partition by convert(varchar,[assgn_dtm],101),convert(varchar,[complet_dtm],101)order by [assgn_dtm]) rownum
from temp_tbl wb
where [cs_id]=1000090 and userid =1000031
and exists
(select [complet_dtm] from temp_tbl wb1 where wb.[tbl_id]>wb1.[tbl_id] and [cs_id]=1000090 and userid =1000031)
) aa where rownum =1
order by 1
i need to get 1st query SUM(diff_of_days) - 2nd query count
your help is appreciated, please let me know if you need any further info.
Regards
July 18, 2014 at 3:09 am
No help Yet !!!!! 🙁
July 18, 2014 at 3:22 am
based on your sample data...what should the result look like?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 18, 2014 at 3:32 am
select cs_id,USERID, SUM(days_of_diff)-(COUNT(dates) --if the same date is appearing both in assgn_dtm and complet_dtm)
from temp_tbl
like
select 1000090 'cs_id',1000031 'USERID', 22-11 total_pending_days
July 18, 2014 at 3:41 am
Can you share the desire output in a result set its still not clear.
July 18, 2014 at 4:03 am
twin,
we have two date columns like assgn_dtm and complet_dtm.
Need to calculate the difference of these two dates
for a given cs_id and userid
cs_id and userid can have multiple rows for assgn_dtm and complet_dtm, so when computing the sum of date diff i need to exclude those days which are occuring both in assgn_dtm and complet_dtm
(eg., as the sample below,
in the second row complet_dtm is 2012-06-08, same is appearing the 3rd row for assign_dtm
case_iduserid assign_dtm complet_dtm
100009010000312011-12-29 15:49:09.0002011-12-29 15:50:43.780
100009010000312012-06-04 19:41:59.0002012-06-08 23:59:38.000
100009010000312012-06-08 23:59:48.0002012-06-11 23:59:29.000
100009010000312012-06-11 23:59:31.0002012-06-12 23:59:09.000
100009010000312012-06-12 23:59:11.0002012-06-14 23:59:57.000
100009010000312012-06-15 00:00:00.0002012-06-15 23:59:16.000
100009010000312012-06-15 23:59:19.0002012-06-18 23:59:14.000
100009010000312012-06-18 23:59:16.0002012-06-19 23:59:09.000
100009010000312012-06-19 23:59:13.0002012-06-20 23:59:18.000
100009010000312012-06-20 23:59:21.0002012-06-21 23:59:09.000
100009010000312012-06-21 23:59:11.0002012-06-22 23:59:48.000
100009010000312012-06-22 23:59:55.0002012-06-25 23:59:29.000
100009010000312012-06-25 23:59:34.0002012-06-27 00:00:29.000
because I have already considered the date 08th in the second row, I should eliminate this date for further occurances
Hope this is clear
July 18, 2014 at 4:44 am
pmadhavapeddi22 (7/18/2014)
twin,we have two date columns like assgn_dtm and complet_dtm.
Need to calculate the difference of these two dates
for a given cs_id and userid
cs_id and userid can have multiple rows for assgn_dtm and complet_dtm, so when computing the sum of date diff i need to exclude those days which are occuring both in assgn_dtm and complet_dtm
(eg., as the sample below,
in the second row complet_dtm is 2012-06-08, same is appearing the 3rd row for assign_dtm
case_iduserid assign_dtm complet_dtm
100009010000312011-12-29 15:49:09.0002011-12-29 15:50:43.780
100009010000312012-06-04 19:41:59.0002012-06-08 23:59:38.000
100009010000312012-06-08 23:59:48.0002012-06-11 23:59:29.000
100009010000312012-06-11 23:59:31.0002012-06-12 23:59:09.000
100009010000312012-06-12 23:59:11.0002012-06-14 23:59:57.000
100009010000312012-06-15 00:00:00.0002012-06-15 23:59:16.000
100009010000312012-06-15 23:59:19.0002012-06-18 23:59:14.000
100009010000312012-06-18 23:59:16.0002012-06-19 23:59:09.000
100009010000312012-06-19 23:59:13.0002012-06-20 23:59:18.000
100009010000312012-06-20 23:59:21.0002012-06-21 23:59:09.000
100009010000312012-06-21 23:59:11.0002012-06-22 23:59:48.000
100009010000312012-06-22 23:59:55.0002012-06-25 23:59:29.000
100009010000312012-06-25 23:59:34.0002012-06-27 00:00:29.000
because I have already considered the date 08th in the second row, I should eliminate this date for further occurances
Hope this is clear
based on this dataset (which looks different from your first post)can you please provide us with your expected result....not just the explanation...this will help us confirm your explanation.
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 18, 2014 at 4:49 am
select [cs_id],userid, SUM(DATEDIFF(dd,[assgn_dtm],[complet_dtm])) - count(*)
from (
select distinct [cs_id],userid,cast([assgn_dtm] as date) as [assgn_dtm], cast([complet_dtm] as date) as [complet_dtm]
from temp_tbl
) Z
group by [cs_id],userid
does it make any sense ?
July 18, 2014 at 4:57 am
For that only I need logic to get the required output, but i am truly stuck what to provide now
select cs_id,USERID, SUM(datediff(dd, assign_dtm, complet_dtm))-(COUNT(repetitive_dates))
from temp_tbl
--if the same date is appearing both in assgn_dtm and complet_dtm
I dont know how do I get this repetitive_days count
like
select 1000090 'cs_id',1000031 'USERID', 22-11 total_pending_days
In the given set of data 11 dates are existing both in assign_dtm and complet_dtm
so i need to eliminate those
July 18, 2014 at 5:45 am
This is a truly wild guess but seems to fit the sketchy description:
;WITH DedupedData AS (
SELECT tbl_id, cs_id, USERID, assgn_dtm, complet_dtm,
rn = ROW_NUMBER () OVER (PARTITION BY cs_id, USERID, CAST(assgn_dtm AS DATE), CAST(complet_dtm AS DATE) ORDER BY assgn_dtm)
FROM #temp_tbl
)
SELECT
cs_id, USERID,
[days] = SUM(DATEDIFF(dd, assgn_dtm, complet_dtm)),
cnt = COUNT(*)
FROM DedupedData
WHERE rn = 1
GROUP BY cs_id, USERID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 18, 2014 at 6:23 am
twin.devil (7/18/2014)
select [cs_id],userid, SUM(DATEDIFF(dd,[assgn_dtm],[complet_dtm])) - count(*)
from (
select distinct [cs_id],userid,cast([assgn_dtm] as date) as [assgn_dtm], cast([complet_dtm] as date) as [complet_dtm]
from temp_tbl
) Z
group by [cs_id],userid
does it make any sense ?
twin,
for count(*) -- the logic is we need to consider if the complete date for tbl_id =2 is repeating for assign_dtm in tbl_id=3
like (the common scenario : end_date of one row is start_date for the next row) we need to count those repetitive days
I am really sorry to bother you all
July 18, 2014 at 7:27 am
WITH cte (cs_id,USERID,assgn_dtm,complet_dtm,diff_of_days,rownum) AS (
SELECTcs_id,USERID,assgn_dtm,complet_dtm,DATEDIFF(dd,assgn_dtm,complet_dtm),
ROW_NUMBER () OVER(PARTITION BY CAST(assgn_dtm as date),CAST(complet_dtm as date) ORDER BY assgn_dtm)
FROM#temp_tbl
WHEREcs_id = 1000090
ANDuserid = 1000031
)
SELECTcs_id,USERID,
SUM(CASE WHEN rownum = 1 THEN diff_of_days ELSE 0 END),
SUM(2-rownum)
FROMcte
GROUPBY cs_id,USERID
Far away is close at hand in the images of elsewhere.
Anon.
July 18, 2014 at 7:30 am
this is just for your "repetitive days"
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,
[assgn_dtm] [datetime] NOT NULL,
[complet_dtm] [datetime] NULL,
[days_of_diff] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO [temp_tbl] VALUES(1,1000090,1000031,'2011-12-29 15:49:09.000','2011-12-29 15:50:43.780',NULL)
INSERT INTO [temp_tbl] VALUES(2,1000090,1000031,'2012-06-04 19:41:59.000','2012-06-08 23:59:38.000',NULL)
INSERT INTO [temp_tbl] VALUES(3,1000090,1000031,'2012-06-08 23:59:48.000','2012-06-11 23:59:29.000',NULL)
INSERT INTO [temp_tbl] VALUES(4,1000090,1000031,'2012-06-11 23:59:31.000','2012-06-12 23:59:09.000',NULL)
INSERT INTO [temp_tbl] VALUES(5,1000090,1000031,'2012-06-12 23:59:11.000','2012-06-14 23:59:57.000',NULL)
INSERT INTO [temp_tbl] VALUES(6,1000090,1000031,'2012-06-15 00:00:00.000','2012-06-15 23:59:16.000',NULL)
INSERT INTO [temp_tbl] VALUES(7,1000090,1000031,'2012-06-15 23:59:19.000','2012-06-18 23:59:14.000',NULL)
INSERT INTO [temp_tbl] VALUES(8,1000090,1000031,'2012-06-18 23:59:16.000','2012-06-19 23:59:09.000',NULL)
INSERT INTO [temp_tbl] VALUES(9,1000090,1000031,'2012-06-19 23:59:13.000','2012-06-20 23:59:18.000',NULL)
INSERT INTO [temp_tbl] VALUES(10,1000090,1000031,'2012-06-20 23:59:21.000','2012-06-21 23:59:09.000',NULL)
INSERT INTO [temp_tbl] VALUES(11,1000090,1000031,'2012-06-21 23:59:11.000','2012-06-22 23:59:48.000',NULL)
INSERT INTO [temp_tbl] VALUES(12,1000090,1000031,'2012-06-22 23:59:55.000','2012-06-25 23:59:29.000',NULL)
INSERT INTO [temp_tbl] VALUES(13,1000090,1000031,'2012-06-25 23:59:34.000','2012-06-27 00:00:29.000',NULL)
;
WITH a as (
SELECT DISTINCT cs_id, USERID, CAST(assgn_dtm AS DATE) d
FROM temp_tbl)
,b as (
SELECT DISTINCT cs_id, USERID, CAST(complet_dtm AS DATE) d
FROM temp_tbl)
SELECT COUNT(*) AS cnt
FROM a INNER JOIN
b ON a.cs_id = b.cs_id AND a.USERID = b.USERID AND a.d = b.d
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 18, 2014 at 8:20 am
Thank you J Livingston and David Burrows.
I appreciate your help and time
July 18, 2014 at 8:41 am
pmadhavapeddi22 (7/18/2014)
Thank you J Livingston and David Burrows.I appreciate your help and time
<<cough>>
----------------------------------------------------------------------------
JLS
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 8 ms.
Table 'temp_tbl'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
-------------------------------------------------------------------------------
David
SQL Server parse and compile time:
CPU time = 2 ms, elapsed time = 2 ms.
Table 'temp_tbl'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
-------------------------------------------------------------------------------
ChrisM
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'temp_tbl'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
-------------------------------------------------------------------------------
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply