Hope this is what you are looking for:

SELECT code, transdate,SumTransLast7days = ISNULL(c.sumoftran, 0) FROM #Test a

CROSS APPLY

(SELECT sumoftran = SUM(nooftran) FROM #Test b

WHERE b.Code = a.code

AND (b.transdate > DATEADD(dd,-7,a.TransDate) and...

Below is a basic example for Group By

CREATE TABLE #Studentcourseschedule(

Department int,

Courseid int,

studentid int)

go

insert into #Studentcourseschedule(Department, Courseid,studentid)

values(1,1,1)

,(1,2,1)

,(1,3,1)

,(1,1,5)

,(1,3,6)

,(1,1,7)

,(2,2,1)

,(2,1,2)

,(2,3,2)

go

select department, COUNT(studentid) Studentcount

from #Studentcourseschedule

group by department

order by Department

go

select department, courseid, COUNT(studentid) Studentcount

from #Studentcourseschedule

group by...

What is the problem you are facing when you create the UDF?

Hi Craig,

Thank you for the explanation. I understood (The dt* are alias name for the derived queries and the q* and n are the alias name for the columns)...

Hi Lynn,

Just to understand, Could you please explain how the query works? Especially the part

(SELECT 1) dt1(n)

CROSS APPLY (SELECT COUNT(*) FROM dbo.Customers) dt2(q1)

...

Hi,

From what I understand from your query is that you want to compare the C.dCreated field for currentdate and fetch data. Hope below should solve the problem.

Begin

--declare @d_today datetime

--set @d_today=(select...

Is this what you are expecting?

SELECT a.customer, a.status, DATEDIFF(dd, a.mlastupdt, getdate()) AS NumDays

FROM (SELECT Customer, max(LastUpdateDate) AS mlastupdt, [Status], createddate

...

begin

declare @COLOR VARCHAR(20);

set @COLOR = NULL

select id,ISNULL(color,'NULL') color from TBLCOLOR where color in

(select color from TBLCOLOR where color = COALESCE(@COLOR,color))

or isnull(color, 0) = isnull(@COLOR,0)

end

OUTPUT:

idcolor

1RED

2BLUE

3NULL

4GREEN

5BLACK

6NULL

Is this what you are looking for?

---Set paramter to NULL

begin

declare @COLOR VARCHAR(20);

set @COLOR = NULL

select id,color from TBLCOLOR where color in

(select color from TBLCOLOR where color = COALESCE(@COLOR,color))

or...