August 5, 2016 at 10:12 am
Hi,
-- IF from #Table1 , enddate is in the past , then max(Cumulative)
-- IF from #Table1 , startdate is in the future , then 0 as Cumulative
-- IF from #Table1 , getdate() between startdate and enddate , then use (Cumulative) value where TimeOfDay = Getdate()
CREATE TABLE #Table1
(
ID1 UNIQUEIDENTIFIER,
ID2 UNIQUEIDENTIFIER,
TimeOfDay DATETIME,
Cumulative DECIMAL (38,6)
)
INSERT INTO #Table1 (ID1,ID2,TimeOfDay,Cumulative )
SELECT '14F10A98-0CBE-E511-A5EF-B00594F948C7','4E0B8F0C-0CBE-E511-A5EF-B00594F948C7','2016-05-06 00:00:00.000',32.263052 UNION
SELECT '240B8F0C-0CBE-E511-A5EF-B00594F948C7','4E0B8F0C-0CBE-E511-A5EF-B00594F948C7','2016-05-07 00:00:00.000',38.263052 UNION
SELECT '240B8F0C-0CBE-E511-A5EF-B00594F948C7','4E0B8F0C-0CBE-E511-A5EF-B00594F948C7','2016-05-08 00:00:00.000',39.263052 UNION
SELECT '240B8F0C-0CBE-E511-A5EF-B00594F948C7','4E0B8F0C-0CBE-E511-A5EF-B00594F948C7','2016-05-09 00:00:00.000',40.328815 UNION
SELECT '240B8F0C-0CBE-E511-A5EF-B00594F948C7','4E0B8F0C-0CBE-E511-A5EF-B00594F948C7','2016-08-08 00:00:00.000',44.328815 UNION
SELECT '200B9F0C-0CBE-E511-A5EF-B00594F948C7','17E1C3C6-7E39-E611-8860-80000B9B5293','2016-09-13 00:00:00.000',22.400000 UNION
SELECT '200B9F0C-0CBE-E511-A5EF-B00594F948C7','17E1C3C6-7E39-E611-8860-80000B9B5293','2016-09-14 00:00:00.000',28.400000 UNION
SELECT '200B9F0C-0CBE-E511-A5EF-B00594F948C7','17E1C3C6-7E39-E611-8860-80000B9B5293','2016-08-08 00:00:00.000',18.400000 UNION
SELECT '700B9F0C-0CBE-E511-A5EF-B00594F948C7','AD556826-4444-E611-8426-80000B9AEB87','2016-08-03 00:00:00.000',28.400000 UNION
SELECT '710B9F0C-0CBE-E511-A5EF-B00594F948C7','AD556826-4444-E611-8426-80000B9AEB87','2016-08-04 00:00:00.000',29.400000 UNION
SELECT '711B9F0C-0CBE-E511-A5EF-B00594F948C7','AD556826-4444-E611-8426-80000B9AEB87','2016-08-05 00:00:00.000',30.400000 UNION
SELECT '712B9F0C-0CBE-E511-A5EF-B00594F948C7','AD556826-4444-E611-8426-80000B9AEB87','2016-08-06 00:00:00.000',27.400000 UNION
SELECT '713B9F0C-0CBE-E511-A5EF-B00594F948C7','AD556826-4444-E611-8426-80000B9AEB87','2016-08-07 00:00:00.000',42.400000 UNION
SELECT '714B9F0C-0CBE-E511-A5EF-B00594F948C7','AD556826-4444-E611-8426-80000B9AEB87','2016-08-08 00:00:00.000',66.400000 UNION
SELECT '714B9F0C-0CBE-E511-A5EF-B00594F948C7','AD556826-4444-E611-8426-80000B9AEB87','2016-08-09 00:00:00.000',76.400000
CREATE TABLE #Table2
(
ID1 UNIQUEIDENTIFIER,
StartDate DATETIME,
EndDate DATETIME,
)
INSERT INTO #Table2 ( ID1, StartDate, EndDate )
SELECT '4E0B8F0C-0CBE-E511-A5EF-B00594F948C7','2016-05-03 08:00:00.000','2016-05-10 17:00:00.000' UNION
SELECT '17E1C3C6-7E39-E611-8860-80000B9B5293','2016-09-13 08:00:00.000','2016-09-14 17:00:00.000' UNION
SELECT 'AD556826-4444-E611-8426-80000B9AEB87','2016-08-03 08:00:00.000','2016-08-09 17:00:00.000'
SELECT * FROM #Table1
SELECT * FROM #Table2
--Worked on the query, but was not able to get result for the last condition
SELECT
T.ID1,CAST(T.[StartDate] AS DATE) TaskStartDate,--CAST([EndDate] AS DATE) TaskEndDate,CAST(GETDATE() AS DATE) Today, CAST(GETDATE() AS DATE),
CASE WHEN DATEDIFF(DAY, getdate(), T.[StartDate]) > 0 THEN 0
WHEN DATEDIFF(DAY, getdate(), T.[EndDate])< 0 THEN MAX(ROUND(A.Cumulative ,3))
WHEN CAST(GETDATE() AS DATE) BETWEEN CAST([T].[StartDate] AS DATE) AND CAST(T.[EndDate] AS DATE) THEN (SELECT A.Cumulative FROM #Table1 A WHERE CAST(A.TimeOfDay AS DATE) = CAST(GETDATE() AS DATE) )
END AS Cumulative_Original
FROM #Table1 A JOIN #Table2 T ON A.ID2 = T.ID1
GROUP BY T.ID1,T.[StartDate],[EndDate]
DROP TABLE #Table1
DROP TABLE #Table2
Please help.
Thanks,
PSB
August 5, 2016 at 10:22 am
PSB (8/5/2016)
--Please help.
Thanks,
PSB
What is your question?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 5, 2016 at 10:48 am
Need help when getdate() between startdate and enddate , then the query should use (Cumulative) value where TimeOfDay = Getdate()
Thanks,
PSB
August 5, 2016 at 10:56 am
It should pick 30.400000 for TimeOfDay 2016-08-05 00:00:00.000 .
August 5, 2016 at 11:41 am
deleted
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 5, 2016 at 11:45 am
Can anyone help me with the last condition ?
IF from #Table1 , getdate() between startdate and enddate , then use (Cumulative) value where TimeOfDay = Getdate()
August 5, 2016 at 11:51 am
PSB (8/5/2016)
Can anyone help me with the last condition ?IF from #Table1 , getdate() between startdate and enddate , then use (Cumulative) value where TimeOfDay = Getdate()
TimeOfDay looks like a date. GetDate() is a datetime. They will therefore rarely be equal.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 5, 2016 at 12:15 pm
I have used cast as date in both
August 6, 2016 at 8:26 am
Could you please at least give an example of what the result should be?
Don't explain it in words, draw us a picture.
Create a result table and populate it with the results you want to see from the input data in your example.
It would be a big help. Thank you .
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 7, 2016 at 3:57 pm
Desired result:
SELECT 'AD556826-4444-E611-8426-80000B9AEB87','2016-08-03' AS TaskStartDate,76.400000 AS Cumulative_Original
SELECT '17E1C3C6-7E39-E611-8860-80000B9B5293','2016-09-13' AS TaskStartDate,0.000000 AS Cumulative_Original
SELECT '4E0B8F0C-0CBE-E511-A5EF-B00594F948C7','2016-05-03' AS TaskStartDate,44.329000 AS Cumulative_Original
Thanks,
PSB
August 7, 2016 at 7:54 pm
Here's how the line in question should read.
WHEN CAST(GETDATE() AS DATE) BETWEEN CAST([StartDate] AS DATE) AND CAST([EndDate] AS DATE)
The key is that [StartDate] comes before [EndDate].
BETWEEN requires that the earlier data come first.
BETWEEN [EndDate] and [Startdate] can only be true when both dates are the same.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 8, 2016 at 2:58 am
Try this for table1 and table2 :
Select
t2.ID1,
t2.startdate as taskstartdate,
case
when t2.enddate < getdate() then max(t1.Cumulative)
when t2.StartDate > getdate() then 0
when getdate() < t2.enddate and getdate() > t2.StartDate
then (Select t3.Cumulative from Table1 as t3
where cast(t3.TimeOfDay as date) =cast(getdate() as date))
End AS cumulative_original
from table1 as t1
inner join Table2 as t2
on t1.ID2=t2.ID1
group by t2.ID1,
t2.StartDate,
t2.endDate
August 8, 2016 at 5:42 am
Getting a "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." using
WHEN CAST(GETDATE() AS DATE) BETWEEN CAST([T].[StartDate] AS DATE) AND CAST(T.[EndDate] AS DATE) THEN (SELECT A.Cumulative FROM #Table1 A WHERE CAST(A.TimeOfDay AS DATE) = CAST(GETDATE() AS DATE) )
Please help
Thanks,
PSB
August 8, 2016 at 5:46 am
Are you trying to sum up the amount of all rows between a range? If so, you need to use the SUM() function in your subquery.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 8, 2016 at 5:55 am
Subquery value should be based on Id2 value from #Table1 or Id1 from #Table2
IF from #Table1 , getdate() between startdate and enddate , then use (Cumulative) value where TimeOfDay = Getdate() where A.ID2 = T.ID1
When getdate() is in between start and end dates , then for that particular T.Id2 from #Table2 ,I need to get the Cumulative value where GEtDate() and TimeOfDay are equal . Other Id2 from #Table1 will also have TimeOfDay = GetDAte() or greater , but there should be only one value per ID2 level.
--Desired RESULT
SELECT 'AD556826-4444-E611-8426-80000B9AEB87','2016-08-03' AS TaskStartDate,76.400000 AS Cumulative_Original
SELECT '17E1C3C6-7E39-E611-8860-80000B9B5293','2016-09-13' AS TaskStartDate,0.000000 AS Cumulative_Original
SELECT '4E0B8F0C-0CBE-E511-A5EF-B00594F948C7','2016-05-03' AS TaskStartDate,44.329000 AS Cumulative_Original
Thanks
PSB
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply