August 8, 2016 at 6:11 am
I don't get that error message with the sample data you supplied. If your test data has more than one date within the range, you have to do some sort of aggregation like SUM() or MAX().
Think about what you are doing for a moment. You have a GROUP BY query, and one of the values you are returning from your case statement is a MAX().
__________________________________________________
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 6:28 am
Sample data :
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',76.400000 UNION
SELECT '714B9F0C-0CBE-E511-A5EF-B00594F948C7','AD556826-4444-E611-8426-80000B9AEB87','2016-08-09 00:00:00.000',86.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 ORDER BY ID2,Cumulative
SELECT * FROM #Table2
SELECT CAST(GETDATE() AS DATE)
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
-- WHERE T.ID1 = 'AD556826-4444-E611-8426-80000B9AEB87'
GROUP BY T.ID1,T.[StartDate],[EndDate]
DROP TABLE #Table1
DROP TABLE #Table2
-- 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()
--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
August 8, 2016 at 6:33 am
PSB: you've been here long enough to be aware of the code="sql" tags. Is there a reason why you choose not to use them, to help others read your posts?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 8, 2016 at 6:43 am
Try this:
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 max(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 6:58 am
SELECT T.ID1,
CAST(T.[StartDate] AS DATE) TaskStartDate,
MAX(CASE WHEN DATEDIFF(DAY, getdate(), T.[StartDate]) > 0 THEN ROUND(0 ,3)
WHEN DATEDIFF(DAY, getdate(), T.[EndDate])< 0 THEN ROUND(A.Cumulative ,3)
WHEN CAST(GETDATE() AS DATE) BETWEEN CAST([T].[StartDate] AS DATE)
AND CAST(T.[EndDate] AS DATE)
AND CAST(A.TimeOfDay AS DATE) = CAST(GETDATE() AS DATE) THEN A.Cumulative
END) AS Cumulative_Original
FROM #Table1 A
JOIN #Table2 T ON A.ID2 = T.ID1
GROUP BY T.ID1,T.[StartDate],[EndDate]
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply