T-SQL query to compare date and find if column entry exists for a month

Question

I have a two columns named "Statement Date" and "Account Number". For some account numbers statements are uploaded monthly, for some quarterly and for some annually and the date on which these statements are uploaded is saved in "statement date"

I want to check if for a particular a statement is uploaded or not. For e.g- "account number"-2345426576 "statement date"-5/30/2016, I have to check if there is entry for previous month(4/30/2016) and next month(6/30/2016). If entry is absent then it should return missing for that month.

If dates are to be compared of different tables and then find if a statement is missing or not.i.e if t1.date>t2.date then check t1.date is how many month greater. Same goes if t1.date

Check this out. http://stackoverflow.com/questions/639854/check-if-a-row-exists-otherwise-insert — Danieboy, Jul 27 '16 at 08:23

score 0 · Answer 1 · answered Jul 27 '16 at 08:24

Create a function for this:

CREATE FUNCTION [dbo].[IsUploaded]
(
       @AccountNo int,
       @Date datetime
)
RETURNS bit
AS
BEGIN
    DECLARE @MonthStart datetime
    DECLARE @NextMonthStart datetime


    -- current month 
   SET @MonthStart = DATEADD(month, DATEDIFF(month, 0, @Date), 0)
   SET @NextMonthStart = DATEADD(month, 1, @MonthStart)

   IF EXISTS (SELECT 1 From UploadedStatememtsTable WHERE UploadDate >= @MonthStart AND UploadDate < @NextMonthStart AND AccountNo = @AccountNo) 
      RETURN 1

   -- next month
   SET @MonthStart = @NextMonthStart
   SET @NextMonthStart = DATEADD(month, 1, @MonthStart)

   IF EXISTS (SELECT 1 From UploadedStatememtsTable WHERE UploadDate >= @MonthStart AND UploadDate < @NextMonthStart AND AccountNo = @AccountNo) 
      RETURN 1

   -- previous month
   SET @MonthStart = DATEADD(month, -2, @MonthStart)
   SET @NextMonthStart = DATEADD(month, 1, @MonthStart)

   IF EXISTS (SELECT 1 From UploadedStatememtsTable WHERE UploadDate >= @MonthStart AND UploadDate < @NextMonthStart AND AccountNo = @AccountNo) 
      RETURN 1

   RETURN 0

END

When executing the function, error comes up saying "function has too many arguments specified" — Akanksha, Jul 27 '16 at 09:32

neer · Answer 2 · 2016-07-27T08:53:06.133

Try this

SELECT
    T.accountnumber,
    T.statementdate,    
    CASE
      WHEN 
        EXISTS
        (
            SELECT TOP 1 1 FROM Tbl tmp
            WHERE
                tmp.accountnumber = T.accountnumber AND
                tmp.statementdate =  DATEADD(mm, 1, T.statementdate) -- Next month
        ) AND
        EXISTS
        (
            SELECT TOP 1 1 FROM Tbl tmp
            WHERE
                tmp.accountnumber = T.accountnumber AND                 
                tmp.statementdate =  DATEADD(mm, -1, T.statementdate)-- Priv month
        )   
      THEN 'There is entry'
      ELSE 'Missing' END 
FROM
    Tbl T

T-SQL query to compare date and find if column entry exists for a month

2 Answers2