Solved

Help with SQL

Posted on 2016-07-20
13
27 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 65

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 69

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
 
LVL 65

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 69

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 69

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:ScottPletcher
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 69

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

757 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now