[Webinar] Streamline your web hosting managementRegister Today

x
  • 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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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