Selecting transactions for a set date

[SonOfZeus]

I've developed a stored procedure to retrieve the travel logs for vehicles on a specified date. The issue I'm hitting now is I want all vehicles, even if they didn't travel on that date to be returned, with the travel info simply blank. i.e.

Vehicle1 June 13 25Kilometers
Vehicle2 June 13 10Kilometers
Vehicle3
Vehicle4 June 13 10Kilometers

Other records may or may not exist for a vehicle on other days, thus the = june 13 or travellog.date isnull won't work.

Any ideas

 

[skuhlman]

Assuming you have two tables which look something like this, you can do something like this:

a.vid
a.vdescription

b.vid
b.traveldate
b.milestraveled

select a.vid,
a.vdescription,
b.traveldate,
sum(case when b.milestraveld is null then 0 else
b.milestraveled end) as totaldistance
from a left outer join b on a.vid = b.vid
where b.traveldate between @StartDate and @EndDate
group by b.traveldate, a.vdescription, a.vid
order by b.traveldate, a.vdescription, a.vid

 

[SQLSister]

How about creating a temp table with the fields you need. Then populate it with the unique vehicle names and then update it with the info for a specific date (other vehicles would stay null)and finally display the temp table results?

 

[SonOfZeus]

A temp table would work within the stored procedure. I have to refresh myself on temp tables as two people may be calling this procedure with different dates. Thanks

 

[sunila7]

Hi,

Maybe u can create a local temporary tables which are visible only in the current session. A create statement with a single # will do it some thing like this..

CREATE TABLE #temptale (fld1 INT).


But i think that it might be possible to get the data with as u want from a SQL. if u could put some sample data from tables and what output u r looking for. May be we can try to get the data with a SQL

Sunil