CODEWITHSUNDEEP
sqlsql-serversql-server-2012dynamic-sqldatediff
atroul
atroul

Reputation: 219

SQL Server 2012: DateDiff excluding weekends in where clause

I have a dynamic SQL query which counts documents in a specific office which are signed more than 1 working day of their created date.

Here is the query:

set @strsql = '(select @cnt = COUNT(*) from '+@TableNameDocs+' D
                inner join dbo.Signatures S on D.id = S.TableId
                where S.cityid = '+str(@Cityid)+' and S.OfficeId = '+str(@Officeid)+' and S.isValid = 1 and D.cityid = '+str(@Cityid)+' and D.OfficeId = '+str(@Officeid)+' and DATEDIFF(day,D.CreatedDate,COALESCE(S.SignedDate, GETDATE())) > 1)'

But I want to alter it so it counts only working dates with a check if the office is in a country where the weekend is Saturday and Sunday or in a country where the weekend is Friday and Saturday. I can get the country of the office from the Offices table. The offices can be in Lebanon (Sat-Sun weekend) or in Saudi Arabia (Fri-Sat weekend)

Upvotes: 1

Views: 69

Answers (1)

aggicd
aggicd

Reputation: 737

Maybe you can try something like this : First find the Country of the @Officeid from Offices tables:

-- GET OFFICE'S COUNTRY
    SELECT @country = [Country] from dbo.Offices
    where officeid = @Officeid

Then depending on the country :

IF @country = 'SAUDI ARABIA' --Weekend in Saudi Arabia is Fri-Sat
    BEGIN
        set @strsql = '(select @cnt=COUNT(*) from '+@TableNameDocs+' D
                        inner join dbo.Signatures S on D.id = S.TableId
                        where S.cityid = '+str(@Cityid)+' and S.OfficeId = '+str(@Officeid)+' and S.isValid = 1 and D.cityid = '+str(@Cityid)+' and D.OfficeId = '+str(@Officeid)+' and 
                        ((DATEDIFF(dd, D.CreatedDate, COALESCE(S.SignedDate, GETDATE())))
                        -(DATEDIFF(wk, D.CreatedDate, COALESCE(S.SignedDate, GETDATE())) * 2)
                        -(CASE WHEN DATENAME(dw, D.CreatedDate) = ''Saturday'' THEN 1 ELSE 0 END)
                        -(CASE WHEN DATENAME(dw, COALESCE(S.SignedDate, GETDATE())) = ''Friday'' THEN 1 ELSE 0 END))>1)'

    END
    else
    BEGIN
        set @strsql = '(select @cnt=COUNT(*) from '+@TableNameDocs+' D
                        inner join dbo.Signatures S on D.id = S.TableId
                        where S.cityid = '+str(@Cityid)+' and S.OfficeId = '+str(@Officeid)+' and S.isValid = 1 and D.cityid = '+str(@Cityid)+' and D.OfficeId = '+str(@Officeid)+' and 
                            ((DATEDIFF(dd, D.CreatedDate, COALESCE(S.SignedDate, GETDATE())))
                            -(DATEDIFF(wk, D.CreatedDate, COALESCE(S.SignedDate, GETDATE())) * 2)
                            -(CASE WHEN DATENAME(dw, D.CreatedDate) = ''Sunday'' THEN 1 ELSE 0 END)
                            -(CASE WHEN DATENAME(dw, COALESCE(S.SignedDate, GETDATE())) = ''Saturday'' THEN 1 ELSE 0 END))>1)'
    END

credits Jeff Moden

Upvotes: 1

Related Questions