How to query on a date range, display matching records but also display companies with no matchi

OK, hopefully someone can help me on this one. I have a report that allows a user to select a date range and returns the applicable data. Hypothetically, this returns to the user 3 rows of data for 3 different companies\sites. The problem is that the client wants to see the other 7 companies\sites that don't have data for the date range specified by the user, in the same report.

I am having issues with this as my query returns the results that exist within the date range. If now rows of data exist for the date range, how can I possibly display the other 7 companies? I looked at embedding a report inside of another report, but I can't think of a query I could write that says, if these companies are not in the result set generated by the user, display them anyway. I also looked at using a different dataset then the one I included below.

Maybe I am missing something, I don't know. I am pretty knew to SQL but don't know how to go about capturing this data. I included my query for your review. It relies on views and whatnot, but maybe someone has an idea I could look into.

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)
GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site

Jambi,

you can basically write an Union Sql query

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)

UNION ALL

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site

WHERE some 7 other company exist

GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site

The union statement will return your date range and the other 7 company data if they exist.

Ham

How to query on a date range, display matching records but also display companies with no ma

OK, hopefully someone can help me on this one. I have a report that allows a user to select a date range and returns the applicable data. Hypothetically, this returns to the user 3 rows of data for 3 different companies\sites. The problem is that the client wants to see the other 7 companies\sites that don't have data for the date range specified by the user, in the same report.

I am having issues with this as my query returns the results that exist within the date range. If now rows of data exist for the date range, how can I possibly display the other 7 companies? I looked at embedding a report inside of another report, but I can't think of a query I could write that says, if these companies are not in the result set generated by the user, display them anyway. I also looked at using a different dataset then the one I included below.

Maybe I am missing something, I don't know. I am pretty knew to SQL but don't know how to go about capturing this data. I included my query for your review. It relies on views and whatnot, but maybe someone has an idea I could look into.

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)
GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site

Jambi,

you can basically write an Union Sql query

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)

UNION ALL

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site

WHERE some 7 other company exist

GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site

The union statement will return your date range and the other 7 company data if they exist.

Ham

How to query data through scheduled tasks

Hello,
We are currently using an inventory control system that is a little lacking.
It allows us to store expiration dates for our materials but lacks any way t
o
use the information. The system is built on an MSSQL 2000 database.
What I would like to do is write a program to place into the Windows
scheduled tasks to run once a day. The app needs to compare the exp. date
field in the table to the current date. If the date is within two weeks or
less of the current date I want to generate an e-mail notification or pop up
message to send to a selected group of internal users. Everyone has an
external e-mail account so I can send directly with an smtp engine.
Has anyone else tried or purchased something that could do this? If not then
what language would be recommended that is capable of doing this on a
schedule basis? If this can be done in SQL then it is simply beyond my
abilities so I would be looking for a contractor to program it.
Our network is running on Server 2000. I run Merak mail server and Goldmine
CRM/e-mail client. All local machines are windows with IE.
Thanks for your assistance.
LWhite> What I would like to do is write a program to place into the Windows
> scheduled tasks to run once a day. The app needs to compare the exp. date
> field in the table to the current date. If the date is within two weeks or
> less of the current date I want to generate an e-mail notification or pop
up
> message to send to a selected group of internal users. Everyone has an
> external e-mail account so I can send directly with an smtp engine.
Sounds like a pretty simple thing to do with SQL Server Agent (keeping the
rules and logic inside the database) and a simple mailing product like
xp_smtp_sendmail. You won't need Windows Scheduled Tasks... let us know if
you have any questions after reading http://www.aspfaq.com/2403
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||"LWhite" wrote:

> Hello,
> We are currently using an inventory control system that is a little lackin
g.
> It allows us to store expiration dates for our materials but lacks any way
to
> use the information. The system is built on an MSSQL 2000 database.
> What I would like to do is write a program to place into the Windows
> scheduled tasks to run once a day. The app needs to compare the exp. date
> field in the table to the current date. If the date is within two weeks or
> less of the current date I want to generate an e-mail notification or pop
up
> message to send to a selected group of internal users. Everyone has an
> external e-mail account so I can send directly with an smtp engine.
> Has anyone else tried or purchased something that could do this? If not th
en
> what language would be recommended that is capable of doing this on a
> schedule basis? If this can be done in SQL then it is simply beyond my
> abilities so I would be looking for a contractor to program it.
> Our network is running on Server 2000. I run Merak mail server and Goldmin
e
> CRM/e-mail client. All local machines are windows with IE.
> Thanks for your assistance.
> LWhite
I would use a SQL Server to schedule a job to kick off a task which executes
a sproc like the one below...assuming you can have SQL Mail installed:
create procedure spNotifyProductExpiration
as
begin
declare @.vcSQL varchar(8000)
select @.vcSQL = 'select productname,
abs(datediff(dd,expirationdate,getdate()
)) as ExpireDays from stuff where
abs(datediff(dd,expirationdate,getdate()
)) <= 15'
exec master..xp_sendmail @.recipient='manager@.yourcompany.com',
@.subject='Expirations',
@.query = @.vcSQL, @.message='These products will
be expiring soon!'
end

How to query data through scheduled tasks

Hello,
We are currently using an inventory control system that is a little lacking.
It allows us to store expiration dates for our materials but lacks any way to
use the information. The system is built on an MSSQL 2000 database.
What I would like to do is write a program to place into the Windows
scheduled tasks to run once a day. The app needs to compare the exp. date
field in the table to the current date. If the date is within two weeks or
less of the current date I want to generate an e-mail notification or pop up
message to send to a selected group of internal users. Everyone has an
external e-mail account so I can send directly with an smtp engine.
Has anyone else tried or purchased something that could do this? If not then
what language would be recommended that is capable of doing this on a
schedule basis? If this can be done in SQL then it is simply beyond my
abilities so I would be looking for a contractor to program it.
Our network is running on Server 2000. I run Merak mail server and Goldmine
CRM/e-mail client. All local machines are windows with IE.
Thanks for your assistance.
LWhite
> What I would like to do is write a program to place into the Windows
> scheduled tasks to run once a day. The app needs to compare the exp. date
> field in the table to the current date. If the date is within two weeks or
> less of the current date I want to generate an e-mail notification or pop
up
> message to send to a selected group of internal users. Everyone has an
> external e-mail account so I can send directly with an smtp engine.
Sounds like a pretty simple thing to do with SQL Server Agent (keeping the
rules and logic inside the database) and a simple mailing product like
xp_smtp_sendmail. You won't need Windows Scheduled Tasks... let us know if
you have any questions after reading http://www.aspfaq.com/2403
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||"LWhite" wrote:

> Hello,
> We are currently using an inventory control system that is a little lacking.
> It allows us to store expiration dates for our materials but lacks any way to
> use the information. The system is built on an MSSQL 2000 database.
> What I would like to do is write a program to place into the Windows
> scheduled tasks to run once a day. The app needs to compare the exp. date
> field in the table to the current date. If the date is within two weeks or
> less of the current date I want to generate an e-mail notification or pop up
> message to send to a selected group of internal users. Everyone has an
> external e-mail account so I can send directly with an smtp engine.
> Has anyone else tried or purchased something that could do this? If not then
> what language would be recommended that is capable of doing this on a
> schedule basis? If this can be done in SQL then it is simply beyond my
> abilities so I would be looking for a contractor to program it.
> Our network is running on Server 2000. I run Merak mail server and Goldmine
> CRM/e-mail client. All local machines are windows with IE.
> Thanks for your assistance.
> LWhite
I would use a SQL Server to schedule a job to kick off a task which executes
a sproc like the one below...assuming you can have SQL Mail installed:
create procedure spNotifyProductExpiration
as
begin
declare @.vcSQL varchar(8000)
select @.vcSQL = 'select productname,
abs(datediff(dd,expirationdate,getdate())) as ExpireDays from stuff where
abs(datediff(dd,expirationdate,getdate())) <= 15'
exec master..xp_sendmail @.recipient='manager@.yourcompany.com',
@.subject='Expirations',
@.query = @.vcSQL, @.message='These products will
be expiring soon!'
end

How to query data through scheduled tasks

Hello,
We are currently using an inventory control system that is a little lacking.
It allows us to store expiration dates for our materials but lacks any way to
use the information. The system is built on an MSSQL 2000 database.
What I would like to do is write a program to place into the Windows
scheduled tasks to run once a day. The app needs to compare the exp. date
field in the table to the current date. If the date is within two weeks or
less of the current date I want to generate an e-mail notification or pop up
message to send to a selected group of internal users. Everyone has an
external e-mail account so I can send directly with an smtp engine.
Has anyone else tried or purchased something that could do this? If not then
what language would be recommended that is capable of doing this on a
schedule basis? If this can be done in SQL then it is simply beyond my
abilities so I would be looking for a contractor to program it.
Our network is running on Server 2000. I run Merak mail server and Goldmine
CRM/e-mail client. All local machines are windows with IE.
Thanks for your assistance.
LWhite> What I would like to do is write a program to place into the Windows
> scheduled tasks to run once a day. The app needs to compare the exp. date
> field in the table to the current date. If the date is within two weeks or
> less of the current date I want to generate an e-mail notification or pop
up
> message to send to a selected group of internal users. Everyone has an
> external e-mail account so I can send directly with an smtp engine.
Sounds like a pretty simple thing to do with SQL Server Agent (keeping the
rules and logic inside the database) and a simple mailing product like
xp_smtp_sendmail. You won't need Windows Scheduled Tasks... let us know if
you have any questions after reading http://www.aspfaq.com/2403
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||"LWhite" wrote:
> Hello,
> We are currently using an inventory control system that is a little lacking.
> It allows us to store expiration dates for our materials but lacks any way to
> use the information. The system is built on an MSSQL 2000 database.
> What I would like to do is write a program to place into the Windows
> scheduled tasks to run once a day. The app needs to compare the exp. date
> field in the table to the current date. If the date is within two weeks or
> less of the current date I want to generate an e-mail notification or pop up
> message to send to a selected group of internal users. Everyone has an
> external e-mail account so I can send directly with an smtp engine.
> Has anyone else tried or purchased something that could do this? If not then
> what language would be recommended that is capable of doing this on a
> schedule basis? If this can be done in SQL then it is simply beyond my
> abilities so I would be looking for a contractor to program it.
> Our network is running on Server 2000. I run Merak mail server and Goldmine
> CRM/e-mail client. All local machines are windows with IE.
> Thanks for your assistance.
> LWhite
I would use a SQL Server to schedule a job to kick off a task which executes
a sproc like the one below...assuming you can have SQL Mail installed:
create procedure spNotifyProductExpiration
as
begin
declare @.vcSQL varchar(8000)
select @.vcSQL = 'select productname,
abs(datediff(dd,expirationdate,getdate())) as ExpireDays from stuff where
abs(datediff(dd,expirationdate,getdate())) <= 15'
exec master..xp_sendmail @.recipient='manager@.yourcompany.com',
@.subject='Expirations',
@.query = @.vcSQL, @.message='These products will
be expiring soon!'
end