TSQL cross apply?

cross apply probably isn't required

select
      CT.[clinicname]
    , DT.[datefield]
    , count( /* distinct */ VT.pk) as visit_count  -- may want distinct, same , not sure
from dateTable as DT
left join visitTable as VT on DT.[datefield = Vt.[datefield]
left join clinicTable as CT on VT.clinicFK = CT.PK
group by
      ct.[clinicname]

sorry, the note should have read like this:
-- may want distinct, if you have same person on same day?
and what you count here might be the patient_id perhaps (not the visit.PK)

also note the above does not account for 'time', if your visit data is dates with times other than 00:00:00 then the joining from the dates table to visit table would need to deal with that

{+edit}
darn, and the grouping would be...
group by
      ct.[clinicname]
    , DT.[datefield]

You will need dynamic SQL to use the dates from a table as column names.

If you are willing to use dynamic SQL (some places don't allow it all), let me know, and I can give you some sample code to generate and run the query.

quite right, apologies, for "Dates as columns" you almost certainly need a pivot - and as Scott points out that often leads to use of dynamic sql.

Here's an introductory article on dynamic pivots
https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/
but do note the advice in the comments to use QUOTENAME

and regarding dynamic sql: The Curse and Blessings of Dynamic SQL

I would suggest you provide us with your table definitions if you want more than generic information.

forgot these 2 good references on this topic by Mark Willis:
Dynamic Pivot Procedure for SQL Server

Dynamic Pivot Procedure without the Pivot function

Hello,

I am a bit surprised we are now talking about dynamic sql... but here are my table definitions.

I create the table of dates like this:

 

-- Create a table of dates we are interested in.
  with mycte as
  (
    select @startdate DateValue
    union all
    select DATEADD (day , 1 , DateValue ) from mycte where DATEADD (day , 1 , DateValue ) < @EndDAte

   )

  select DateValue into #ReportDates from mycte OPTION (MAXRECURSION 0) ;

Select allOpen in new window

Then my table definition for clinics is:

CREATE TABLE [dbo].[Units](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[ContactPerson] [nvarchar](50) NULL,
	[MedicalActivityCode] [nvarchar](50) NULL,
                  .......
 CONSTRAINT [PK_Units] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Select allOpen in new window

and the table of "visits" is:

CREATE TABLE [dbo].[Measurements](
	[msr_id] [int] IDENTITY(3,1) NOT NULL,
	[pat_id] [int] NOT NULL,
	[pat_Personnummer] [varchar](15) NOT NULL,
	[msr_Date] [datetime] NOT NULL,
                  ..............

 CONSTRAINT [PK_Measurements] PRIMARY KEY CLUSTERED 
(
	[msr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Select allOpen in new window

I have a "black hole" in my head when it comes to sql.  Not sure why but i just dont get it.  However with these three tables i should be able to create the cross tab table i need without to much trouble.

The generic reason for 'dynamic' is that the columns change each month

If you are content with a constant set of 31 columns (even for Feb) then the dynamic might be avoided (I say 'might' as that's an assumption - but feel its safe to say). Each such constant column would also have a constant heading e.g. just [1] through [31].

Does this explain the reason?

sorry, and to continue on the dynamic explanation ...

OR you don't have a cross tab
just [clinic] [date] [count]

OR you have columns for the clinics (which I assume could be static) e.g.
[date] [clinic1] clinic2] [clinic3]
and this cross tab would need amendment for any new clinic (or reduction of clinics)

and a question now.

will his field
[Measurements].[msr_Date]

hold times other than 00:00:00+000 ?
(my guess is it would)

The field [Measurements].[msr_Date] holds the visit date - so it should have a valid date value.

With regards to the fact that column names change each month would it be easier to have a cross tab that has the days in my table of dates #ReportDates as the columns?

I may have misled you about that.  I just need the period in the #ReportDates table.

if the columns headings change (at all) it leads to dynamic solutions - does not matter if they come from cte or temp table  or permanent table.

consider you desired output for June of this year
then consider it for August of this year

do the headings change?
do the number of columns change (30 to 31)?

" I just need the period in the #ReportDates table."
does this mean ONE value per clinic?
(not per day values)

perhaps you could provide an expected result layout?

ok perphaps i should have explained that this code will be in a stored procedure.

So i will "dynamically" create the temp table #ReportDates.

After that it should be possible to create the table i mean.  Dates as columns, clinics as rows, and the total number of visits for each date and clinic in the cells.

"Dates as columns" leads to a need for dynamic sql e.g.

NAME       2013/06/01 2013/06/02 2013/06/03 ...
deVilliers     1          1          1      ...
McGrath        0          0          1      ...
Waugh          1          1          0      ...

Select allOpen in new window

Produced by the code below.
Notes:
I have used a date string format YYYY/MM/DD (format style 111), this can be changed
Each date heading is concatenated into a 8000 varchar, so this forms a limit of number of dates that can be evaluated.

DECLARE @startdate datetime
DECLARE @EndDAte datetime

SET @startdate = '20130601'
SET @EndDAte = '20130701'


-- Create a table of dates we are interested in.
;WITH mycte AS
  (
    SELECT @startdate DateValue
    UNION ALL
    SELECT DATEADD (DAY , 1 , DateValue ) FROM mycte WHERE DATEADD (DAY , 1 , DateValue ) < @EndDAte

   )

SELECT DateValue INTO #ReportDates FROM mycte OPTION (MAXRECURSION 0) ;

DECLARE @Columns varchar(8000)
DECLARE @SQL nvarchar(max)

SET @Columns = substring(
                         (
                          SELECT ',' + QUOTENAME(convert(varchar, DateValue ,111))
                          FROM #ReportDates
                          FOR XML path('')
                         )
                        , 2, 8000)


SET @SQL = N'SELECT * FROM'
          + ' (SELECT'
          + '        U.name'
          + '      , convert(varchar, D.DateValue ,111) AS DateValue'
          + '      , m.pat_id'
          + ' FROM #ReportDates AS D'
          + ' INNER JOIN [dbo].[Measurements] AS M ON D.DateValue = M.msr_date'
          + ' INNER JOIN [dbo].[Units] AS U ON M.msr_id = U.id'
          + ' ) AS sourcedata'
          + ' PIVOT'
          + '  (count(pat_id) for DateValue in (' + @Columns + ')) pivottable'

--select @sql

EXEC(@sql)
;

Select allOpen in new window

a trial of this available to run at: http://sqlfiddle.com/#!3/593b8/2

Ok thanks for that.  I tested it and works but the limitation is the number of dates that can be used.

I think that in the report the values will be grouped by weeks or months so maybe i can use that already when creating the columns.

While the experts have been working i have been working on my own solution but without success.  Maybe you can look at it and say why.

I have the following code:
 

 -- Create a table of dates we are interested in.
  with mycte as
  (
    select @startdate DateValue
    union all
    select DATEADD (day , 1 , DateValue ) from mycte where DATEADD (day , 1 , DateValue ) < @EndDAte

   )

  select DateValue into #ReportDates from mycte OPTION (MAXRECURSION 0) ;

Select allOpen in new window

Then i do the following the create a table of the selected dates and all clinics.  I create an extra column called MeasurementCount that i will update next.

select rd.DateValue as ProductionDate, u.id, 0 as MeasurementCount into #Cells from #ReportDates rd
cross apply pharosBPSD.dbo.units U

Select allOpen in new window

Now i try to update the cells with the MeasurementCounts for each date and clinic using:

update #cells  set MeasurementCount = (select Count(msr_id) from Measurements M where m.kli_Kliniknr = #cells.id  and m.msr_Date = #Cells.ProductionDate ) ;

Select allOpen in new window

But i Always get a zero count!

However when i use the following (remove the date parts) i get a result:

update #cells  set MeasurementCount = (select Count(msr_id) from Measurements M where m.kli_Kliniknr = #cells.id) ;

Select allOpen in new window

The counts i get back are the total number of records but they are correct (if i wanted total records)

What is wrong with the where condition that makes it fail when adding:

 and m.msr_Date = #Cells.ProductionDate 

Select allOpen in new window

Is it a conversion probelm?  I think ProductionDate could be a varchar?

the "limitation" is approx 571 dates using "[YYYY/MM/DD] ," as the heading string
how many columns were you aiming at?

>>report the values will be grouped by weeks or months
then your cte should increment accordingly, and the dynamic code adjusted for the needed groupings.

>>I think ProductionDate could be a varchar?
no, that would not help.

try "cross JOIN" instead of "cross apply"

so far I don't see how that solution moves dates into columns

how many columns were you aiming at?
i think that i will need at least a year of date (365), maybe two years, and there is always some idiot who will want to look at 10 years of data.

report the values will be grouped by weeks or months
then your cte should increment accordingly, and the dynamic code adjusted for the needed groupings.
that is what i plan to to


try "cross JOIN" instead of "cross apply"
I already have produced a table that has a row for every date/clinic combination.  Its not the cross apply or cross join that is the issue.

When i try to complete the MeasurementCount column using the following i get zero as the count for all rows.

update #cells  set MeasurementCount = (select Count(msr_id) from Measurements M where m.kli_Kliniknr = #cells.id and m.msr_Date = #cells.ProductionDate  ) ;

Select allOpen in new window

when i remove the date part of the query (see below) i get MeasurementCounts.

update #cells  set MeasurementCount = (select Count(msr_id) from Measurements M where m.kli_Kliniknr = #cells.id ) ;

Select allOpen in new window

but ofcouse it is the clinics total counts in the database and not for the particular day.

Do you understand the issue?



so far I don't see how that solution moves dates into columns
My reporting tool could do that for me.

yes this give the dates and counts.  However i realise now that is need the values of all the clinics on each date.

I am using Crystal Reports an will group the data in counties.  (An extra column in the Units table).

So i can show the same x-axis for each county I need to have a full set of dates for each clinic even if the count is zero.

I guess a left outer join on the measurements and units table may do it?

I think this is why i did a cross apply with the units table.

>>need the values of all the clinics on each date.
group by
      U.[Name]          ---<<<<     Is This the Clinic Name?
    , D.[DateValue]

I cannot see what you see, and I'm guessing as to the data as there is no sample data. But so far as I can tell the group by above does include the clinic. If that's not correct please identify how the clinic is related to the measurements table.

>>I think this is why i did a cross apply with the units table.
are you saying there is NO relationship between measurements and units?

If you look back at the tables definitions you shared, there isn't anything I can seee that is clearly related to a clinic which is why I adopted

 M.kli_Kliniknr = U.ID

which comes from your code

Is it a correct field?