question

abhijk1990 avatar image
abhijk1990 asked

Fetching records based on date range in another table

I am having issue getting the correct data out of this query.

I am trying to fetch ClientID by comparision between 2 table based on Date range


Scenario:


Table A service data

Table B outcome data

Report Requirement – List of the Clients and the total number of clients that have a date entry in Table A but missing entry for that Client in Table B in the date range within the previous six months or in the next 45 days of that date.


Two main field for comparison in the table are ClientID & Date and I am using the below query to get those client IDs from Table A


SELECT DISTINCT B.ClientID,B.Date

FROM Table_B AS B

LEFT JOIN Table_A AS A

ON B.ClientID = A.ClientID

WHERE CAST(B.Date AS date) NOT BETWEEN DATEADD(DAY, -180, CAST(A.Date AS date))

AND DATEADD(DAY, 45, CAST(A.Date AS date))


P.S. I am using all the cast as the dates are in Varchar format as this table is created and populated from Azure.


Could be something really simple but is not striking,

Thanks heaps.




@anthony.green @SaiVijaya @user_edit @harbingergroup @Rickwaldorf
@madhusasinair @sheela @Usman Butt @sanei05

joinssql-azurecross-tab
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Jeff Moden avatar image
Jeff Moden answered

@abhijk1990 ,

Heh... Although I have no love for Azure, you can't blame everything on it. There is no reason why date data ultimately needs to be stored as character based formatted data. That should only reserved for display time and almost never stored.

Shifting gears to you're problem, the following code works just fine with dates that are stored as character based data or the correct temporal datatype. The implicit conversions will slow it down if character base dates are present but it'll still work.

 WITH cteA AS
(
 SELECT  DISTINCT
         ClientID
        ,[Date]  = ca.[Date]
        ,Date180 = DATEADD(dd,-180,ca.[Date])
        ,Date45  = DATEADD(dd,  45,ca.[Date])
   FROM dbo.Table_A
  CROSS APPLY (SELECT TRY_CONVERT(DATE,[Date]))ca([Date])
)
 SELECT a.*
   FROM cteA a
  WHERE NOT EXISTS (SELECT * 
                      FROM dbo.Table_B b 
                     WHERE b.ClientID = a.ClientID
                       AND b.[Date]  >= a.Date180
                       AND b.[Date]  <= a.Date45)
;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.