• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 36
  • Last Modified:

Help with SQL

I have job and task tables.  Multiple tasks per job - one to many.    Each task has a date.  if any task date is the current date, I want a query that will return all data in a job record, with an additional column that indicates that there is a task scheduled for the current day and a column that returns that task date.

How can I do this?
0
HLRosenberger
Asked:
HLRosenberger
  • 6
  • 4
  • 2
  • +1
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<total air code>
SELECT j.*
FROM Job j
   JOIN (SELECT DISTINCT JobID FROM JobType WHERE TaskDate = CAST(GETDATE() as date) jt ON j.ID = jt.JobID

Open in new window


Not following the extra columns, as if a row is returned then there is a task scheduled for today.
0
 
Éric MoreauSenior .Net ConsultantCommented:
not sure where you want to start! would something like this help?
select *
, (select count(*) from job where jobid = T.Jobid)
from task as T
where date = '2016-07-20'

Open in new window

0
 
HLRosenbergerAuthor Commented:
want to start?  what do you mean?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>want to start?  what do you mean?
Your question is very generic, without a table schema or sample data to work with, so we gave generic answers.  If you want a more detailed answer then you'll need to write a more detailed question.

Keep in mind experts here cannot connect to your data source(s) and run queries, so we are entirely dependent on what you tell us.
0
 
HLRosenbergerAuthor Commented:
ok sorry.  

say job table has ID, name and location, and task table has ID, name, date, and jobID (reference to job table) .  I need a query that returns job.id, job.name, job.location, PLUS a column (count, Boolean, does not matter) that indicates for each job whether it has an associated task for a specific date.  

I can't work out the SQL...
0
 
Éric MoreauSenior .Net ConsultantCommented:
select * 
, (select count(*) from tasks where jobID = J.ID and date = '2017-07-20')
from Jobs as J

Open in new window

0
 
HLRosenbergerAuthor Commented:
Eric - I got that to work, but now the issue is I need to pass in the date, like on a where clause or a stored proc.  A proc would have  fixed set of columns.  I'd really like to use a view, but then I would need the date as part of a WHERE clause using the view.    How could you query be change so that the date is part of a WHERE clause?
0
 
Éric MoreauSenior .Net ConsultantCommented:
you can't really do that with a view.

Can you use a SP or a UDF instead to which you can pass arguments?
0
 
Scott PletcherSenior DBACommented:
DECLARE @specific_date datetime
SET @specific_date = GETDATE() --or set to other specific day/date

SELECT j.*, CASE WHEN t.jobID IS NULL THEN 0 ELSE 1 END AS has_task_on_date, t.date AS min_task_date
FROM job j
OUTER APPLY (
    SELECT TOP (1) *
    FROM task t
    WHERE t.jobID = j.jobID AND
        t.date >= DATEADD(DAY, DATEDIFF(DAY, 0, @specific_date), 0) AND
        t.date < DATEADD(DAY, DATEDIFF(DAY, 0, @specific_date) + 1, 0)
    ORDER BY t.date
) AS oa1
0
 
HLRosenbergerAuthor Commented:
A proc will work, as I already created one.   But the results need to be part of a UNION and I need to allow and ORDER BY clause.    But not ORDER BY in the proc, ORDER BY on the entire SQl which can have a number of UNIONs.
0
 
Éric MoreauSenior .Net ConsultantCommented:
create a table-value UDF. You will be able to pass arguments to your functions and use the return as if it was a table.
0
 
HLRosenbergerAuthor Commented:
Never used  UDF.  I'll research it.  

How about this -     Just dealing with the tasks table - if I create a view that has columns for the primary key and the date, that would do the trick.  I could then join on the jobs table.  So, the view would need to pick off either no records or a single record for each job, where in the case of a single record, the date of that record would be the current date.   Now multiple task records could exist for any date.
0
 
HLRosenbergerAuthor Commented:
thanks for all your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now