Solved

Help with SQL

Posted on 2016-07-20
13
33 Views
Last Modified: 2016-07-22
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
Comment
Question by:HLRosenberger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41721232
<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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41721235
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
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41721319
want to start?  what do you mean?
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 66

Expert Comment

by:Jim Horn
ID: 41721326
>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
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41721365
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41721527
select * 
, (select count(*) from tasks where jobID = J.ID and date = '2017-07-20')
from Jobs as J

Open in new window

0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41721609
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41721616
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41721648
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
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41721670
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
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 41721691
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
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41723400
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
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 41725219
thanks for all your help.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question