Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help with SQL

Posted on 2016-07-20
13
Medium Priority
?
35 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
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 70

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 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

824 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