CODEWITHSUNDEEP
sqlsql-server
gbade_
gbade_

Reputation: 349

Trying To Use Case In A SQL-SERVER Query

I have a table with these columnsenter image description here I wrote two queries below to report when transactions are either sent or pending -

SELECT [unityTimeStamp], datediff(hour, unityTimeStamp, getdate())difference_by_hours, datediff(minute, unityTimeStamp, getdate())difference_by_minutes  
FROM [VansoAlertDB].[dbo].[Vanso] WHERE [unityTimeStamp]>dateadd(hour,-10,getdate()) and deliverystatus = 'P'

SELECT [sent_at], datediff(hour, sent_at, getdate())difference_by_hours, datediff(minute, sent_at, getdate())difference_by_minutes  
FROM [VansoAlertDB].[dbo].[Vanso] WHERE [unityTimeStamp]>dateadd(hour,-10,getdate()) and deliverystatus = 'S'

No I have tried writing a query that would as check, if all transactions have been made within an hour or more than, then a value of 1 should be assign to a new column S1hr else 0. Likewise, it'd check if any transactions were sent successfully within 30 minutes and assign a value of 1 else 0.

For the pending transactions, it'd check the unityTimeStamp column as against current timeStamp and assign 1 if P1hr>= 1hour else 0, and 1 if P30min<=30min else 0

I tried this -

SELECT  
case when  max( deliverystatus)='S' and     datediff(hour,getdate(),max(sent_at))>=1 then 1 else 0 end S1hr,
case when max( deliverystatus)='S' and datediff(minute,getdate(),max(sent_at))>=30 then 1 else 0 end S30min, 
case when  max( deliverystatus)='P' and datediff(hour,getdate(),max(unityTimeStamp))>=1 then 1 else 0 end P1hr,
case when  max( deliverystatus)='P' and  datediff(minute,getdate(),max(unityTimeStamp))>=30 then 1 else 0 end P30min
FROM [VansoAlertDB].[dbo].[Vanso] WHERE [unityTimeStamp]>dateadd(hour,-10,getdate())

But I keep getting - 

S1hr, S30min, P1hr, P30min

0   , 1     , 0   , 0

How do I use the CASE statement to get the desired result?

The Sent Query

Sent/Processed transactions from the query mentioned above

sent_at               destinationAddress    difference_by_hours difference_by_minutes
2017-03-16 08:15:00   08060293904           7   440
2017-03-16 08:15:00   08165777415           7   440
2017-03-16 08:15:00   08035001717           7   440
2017-03-16 08:16:00   08185200110           7   439
2017-03-16 08:15:00   08092717339           7   440
2017-03-16 08:15:00   2347055686321         7   440

The Pending Query

Pending transactions from the query mentioned above

unityTimeStamp  destinationAddress  difference_by_hours difference_by_minutes
2017-03-16 09:14:00 08062313735       6                  405
2017-03-16 09:14:00 2348036736566     6                  405
2017-03-16 09:14:00 08022621333       6                  405
2017-03-16 09:14:00 08034859672       6                  405
2017-03-16 09:14:00 2347013038026     6                  405
2017-03-16 09:14:00 2348060472208     6                  405

Upvotes: 0

Views: 55

Answers (1)

Gordon Bell
Gordon Bell

Reputation: 13633

If I understand correctly, you are expecting a single row back from the query to show the number of orders in each category? Something like this?

select  
sum(case when deliverystatus='S' and datediff(hour,getdate(),sent_at)>=1 then 1 else 0 end) S1hr,
sum(case when deliverystatus='S' and datediff(minute,getdate(),sent_at)>=30 then 1 else 0 end) S30min, 
sum(case when deliverystatus='P' and datediff(hour,unityTimeStamp,getdate())>=1 then 1 else 0 end) P1hr,
sum(case when deliverystatus='P' and datediff(minute,unityTimeStamp,getdate())>=30 then 1 else 0 end) P30min
from [VansoAlertDB].[dbo].[Vanso] where [unityTimeStamp]>dateadd(hour,-10,getdate())

Upvotes: 1

Related Questions