Returning Dates not in table

[Khanson82]

If I have this below. How can I have it return dates where no activity happened so it will return zero values and I can pivot it and have all dates on top with counts below it? Right now it pulls all the dates for what I'm asking. The tables do not have all date stamps in them, so this would be some sort of statement to define that..



select a.ClientName,d.clientid,e.agency,a.clerk, a.dttime, a.appserial, b.appt_datetime
from vwhistory_acct0 a with(nolock)
inner join vwacc d
on a.clientname = d.clientname
left outer join vw0 e
on a.appserial = e.serial
and e.clientid = d.clientid
left join hhapp b with(nolock) on a.appserial = b.appserial and b.COID = a.ClientName
and b.serial in
(select max(serial) from hhapp with(nolock)
where appt_status in ('Appointment Complete','Appointment Pending')
group by appserial,coid)
where a.serial in ( select min(serial) from vwhistory_acct0 with(nolock) where
[Status] = 'scheduled'
and ClientName = a.ClientName
group by appserial,ClientName)

 

[kjv1611]

I'm not sure what you're saying - selecting something that isn't there... and expecting to get something. There has to be some specific value you're querying. Are you looking for NULL values, then? Or if you're just looking for records that do not match a certain set of dates, then you ought to have that set of dates in a particular table. If you have to, create a temp dates table, something like:

CREATE #Dates(
  [MyDate] [datetime] NOT NULL
)

INSERT INTO #Dates
SELECT '1/1/2010'
UNION
SELECT '1/5/2010'
UNION
SELECT '1/12/2010'

Then compare against that table... it'll be a lot faster that way anyway. I'm no expert by far, but it looks to me like you're going to put a hurting on any server with a load on it with the total query you're trying to run..

 

[Khanson82]

Basically, With the above sql, if I put the result in excel/crystal and pivot, it will give me the date on top but leave out days due to no records having anything changed and require a datestamp in the table. My ideal result would be to have all dates across the top even if nothing happened those days..but if nothing happened on those days the date wouldn't even be in the tables im pulling from..It does sound a little off..but just wondering if you could someone tell it to populate a date if it didnt exist, type statement..

 

[simian336]

I think you might be help by a Calendar table.
Then you could join the 2 and return missing values.

See....

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

Simi

 

[kjv1611]

Yeah, that sounds like a real winner, simian336.

Khanson82, you could import a calendar table like simian336 mentioned, and then you could LEFT or RIGHT join that table in, so that you include ALL the dates from the calendar, regardless of whether there's a match in your other table or not...

So something like this:

SELECT a.Name ,a.MyDate
FROM MyTable a
          RIGHT JOIN
     CalendarTable c
          ON a.MyDate = c.CalDate

So, doing this would then set things up in such a way that you could pivot the way you're talking about.

Make sense?

 

[Khanson82]

That makes total sense..Off to requesting for deployments now..We'll see what the dba thinks abt it.. Thanks again!