SQL Question

[9944pdx]

select avg(datediff(d,coalesce(datevalue1,getdate()),coalesce(datevalue2,getdate())))as DaysDiffer from Tablename ; how do I change this statement for data for only this current year.

Thanks folks.

[June7]

With a WHERE clause, like:

WHERE Year(datevalue1)=Year(GetDate())

Is coalesce() a custom VBA function?

[orange]

from Google:
The COALESCE function in SQL returns the first non-NULL expression among its arguments. The syntax for COALESCE is as follows: COALESCE ("expression 1", "expressions 2", ...) It is the same as the following CASE statement: SELECT CASE ("column_name")

Neither Coalesce nor CASE are supported with Access SQL.


What is your GetDate() function?
What do you want to do in plain English?

[pbaldy]

I don't understand the question either, but the syntax is SQL Server. GetDate() is the equivalent of Now().

[9944pdx]

I need an Average Count of two date columns for 2018.

Column 1 NAME = DATE_START
Column 2 Name = Date_Received
Table Name = REC_REC

[pbaldy]

Presuming the rest works, and presuming you only care about the start date:

select avg(datediff(d,coalesce(datevalue1,getdate()),coalesce(datevalue2,getdate())))as DaysDiffer
from Tablename
WHERE Date_Start Between '1/1/2018' AND '12/31/2018'

[9944pdx]

Hello,
the answer you provided worked.

I used the coalesce function in case there were NULL values for DateValue1, and DateValue2.

Recap: I am being asked to provide an AVG of record count per Day between two date columns for 2018.

Thanks.

[pbaldy]

Happy to help.

[ArviLaanemets]

I need an Average Count of two date columns for 2018.

Column 1 NAME = DATE_START
Column 2 Name = Date_Received
Table Name = REC_REC

And how do you count days, when DATE_START is e.g. December 1st of 2017, and Date_Received is February 1st of 2018? I think you have to check for year of start date, and when it is less than your query year, you have to replace the start date with January 1st of query year. And in case the query year can be any year from past (not only current year), then you have to make the year check for date received too (Date_Recieved, or end of query year, or GetDate())

[9944pdx]

Can you provide what this would look like?

[ArviLaanemets]

On fly
(I based the query on pbaldy's example)
(and I haven't tested expressions to calculate first and last date of year, got them from https://dwaincsql.com/2014/04/04/man...imes-in-t-sql/)
someting like:

SELECT
avg(
      datediff(
           d,
           (CASE 
                WHEN datevalue1 < CAST(1+DATEADD(month, 1-1,DATEADD(year,QueryYear-1900,0))-1 AS DATE) 
                                    THEN CAST(1+DATEADD(month, 1-1,DATEADD(year,QueryYear-1900,0))-1 AS DATE)
                                    ELSE datevalue1 
                          END),
           (CASE 
               WHEN QueryYear = Year(GetDate()) 
               THEN COALESCE(datevalue2, GetDate()) 
               ELSE 
                    (CASE 
                         WHEN YEAR(datevalue2) = YEAR(QueryYear) 
                         THEN  datevalue2
                         ELSE CAST(1-1+DATEADD(month, 1-1,DATEADD(year,QueryYear+1-1900,0))-1 AS DATE) 
                                             END) 
          END),
          )
     )
)
from Tablename
WHERE datevalue1 <= CAST(1-1+DATEADD(month, 1-1,DATEADD(year,QueryYear+1-1900,0))-1 AS DATE) AND (datevalue2 Is Null OR datevalue2 >= CAST(1+DATEADD(month, 1-1,DATEADD(year,QueryYear-1900,0))-1 AS DATE))

[9944pdx]

Thanks, I will work with that and come back to you if I have questions.

[Minty]

These are a the most common date functions I use - from here http://www.sqlservercentral.com/blog...date-routines/

declare @ThisDate datetime;
set @ThisDate = getdate();


select dateadd(dd, datediff(dd, 0, @ThisDate), 0)     -- Beginning of this day
select dateadd(dd, datediff(dd, 0, @ThisDate) + 1, 0) -- Beginning of next day
select dateadd(dd, datediff(dd, 0, @ThisDate) - 1, 0) -- Beginning of previous day
select dateadd(wk, datediff(wk, 0, @ThisDate), 0)     -- Beginning of this week (Monday)
select dateadd(wk, datediff(wk, 0, @ThisDate) + 1, 0) -- Beginning of next week (Monday)
select dateadd(wk, datediff(wk, 0, @ThisDate) - 1, 0) -- Beginning of previous week (Monday)
select dateadd(mm, datediff(mm, 0, @ThisDate), 0)     -- Beginning of this month
select dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0) -- Beginning of next month
select dateadd(mm, datediff(mm, 0, @ThisDate) - 1, 0) -- Beginning of previous month
select dateadd(qq, datediff(qq, 0, @ThisDate), 0)     -- Beginning of this quarter (Calendar)
select dateadd(qq, datediff(qq, 0, @ThisDate) + 1, 0) -- Beginning of next quarter (Calendar)
select dateadd(qq, datediff(qq, 0, @ThisDate) - 1, 0) -- Beginning of previous quarter (Calendar)
select dateadd(yy, datediff(yy, 0, @ThisDate), 0)     -- Beginning of this year
select dateadd(yy, datediff(yy, 0, @ThisDate) + 1, 0) -- Beginning of next year
select dateadd(yy, datediff(yy, 0, @ThisDate) - 1, 0) -- Beginning of previous year

[9944pdx]

all is well, thanks for all your help folks.