May 21, 2010 at 12:20 pm
Hello the below query will give is giving me users who are blocked in the past 2 days, I want to check what was their recent blocks and unblocks and the time difference between the Block and Unblock. Similarly I want to calculate for 1 week, 15 days and 30 days. Can somebody please help me here...
Query Below
SELECT t.CAN, Count(t.Block) BlockCount, Count(t.UnBLock) UnblockCount
FROM(
SELECT CAN, 1 As Block, 0 As UnBlock
FROM VideoCreditBlocks (NOLOCK)
WHERE RecordType IN ('B') AND ProcessedResult = 'D000000'
AND ProcessedDatetime >= '05/17/2010' AND ProcessedDatetime <= '05/19/2010'
UNION
SELECT CAN, 0 As Block, 1 As UnBlock
FROM VideoCreditBlocks (NOLOCK)
WHERE RecordType IN ('U') AND ProcessedResult = 'D000000'
AND ProcessedDatetime >='05/17/2010' AND ProcessedDatetime <= '05/19/2010'
) t
GROUP BY t.CAN
Thanks in advance
May 21, 2010 at 1:57 pm
Replace your hard coded Date values by the respective DATEADD..
For 1 Week Use
ProcessedDatetime > DATEADD(DD,-7,GETDATE()) and ProcessedDatetime < GETDATE()
For 15 Days Use
SELECT DATEADD(DD,-15,GETDATE()) and ProcessedDatetime < GETDATE()
For 30 Days Use
SELECT DATEADD(DD,-30,GETDATE()) and ProcessedDatetime < GETDATE()
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
May 21, 2010 at 3:27 pm
Thanks for replying
I actully need the difference between one block to one unblock, within the specific range if date.
Ex: if customer is blocked on Monday, when did e got unblock. the time taken to unblock each blocked customer.
May 21, 2010 at 8:21 pm
Sorry, I could not follow your question. I do not have any data to test what you are asking and I cannot generate data on my side either. May I suggest you to follow the link in signature to look for the article how to post question and sample data accordingly to get help better and quicker.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
May 22, 2010 at 3:43 am
Hello the below query will give is giving me users who are blocked in the past 2 days, I want to check what was their recent blocks and unblocks and the time difference between the Block and Unblock. Similarly I want to calculate for 1 week, 15 days and 30 days. Can somebody please help me here...
Query Below
May 22, 2010 at 10:08 am
afrinmou420 (5/22/2010)
Hello the below query will give is giving me users who are blocked in the past 2 days, I want to check what was their recent blocks and unblocks and the time difference between the Block and Unblock. Similarly I want to calculate for 1 week, 15 days and 30 days. Can somebody please help me here...Query Below
No spamming please...
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply