Combine sql queries

Old Pedant · June 17th, 2008, 01:23 PM

Okay, "IF" it will be, then.

Have to go to a couple of meeetings. Back later.

Clue to get started:

IF only one record
SELECT INTO temp# one record with jobid and quantity
ELSE
SELECT INTO temp# all records for given dates with jobid's and quantities

Now simply JOIN your main query to the temp# table, regardless of which temp# table it is.

Make sense?

snufse · June 17th, 2008, 01:36 PM

If its easier and more clean with 2 sps, that is fine, I do not want to complicate the issue (since you mentioned that option) ..... I appreciate you help ...

Old Pedant · June 17th, 2008, 02:06 PM

I feel like I've missed something, because it occurs to me that in the case when a single JobID is used, there is still no guarantee that we get only a single record for the final result.

That's because there might be many records with that same JobID and with different ChangeDate values. Or maybe not. Maybe your data isn't orgainized that way??

ALSO... As written, this will retrieve all records for *ANY* changeDate in the case of a single JobID. Again, not sure of the data.

Anyway, here's my hack at it. Don't be surprised if I've goofed a couple of places, as it's all off the top of my head.

Code:

CREATE PROCEDURE sp_Vecellio_Test1
   @JobNumber double,
   @DateFrom datetime,
   @DateTo datetime,
   @TypeId varchar(20)

AS

SET NOCOUNT ON

-- we allow passing either NULL or 0 for job number to mean "use dates instead"
--
IF ISNULL(@JobNumber,0.0) <= 0.0 
BEGIN
    SELECT @JobNumber, SUM(dbo.ProductionEvent.Quantity) AS total
    INTO #tempJobQty
    FROM dbo.Batch INNER JOIN
         dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
         dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
         dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid  INNER JOIN
         dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
         dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid 
    WHERE dbo.job.CompanyJobId = @JobNumbr and dbo.SourceType.CompanySourceTypeId = @TypeId
    -- no GROUP BY needed, since this is SUM of *all* qualifying records

    -- for purposes of the final query, we need dummy datefrom, dateto values
    -- that will get all records
    SET @DateFrom = '1/1/1900'
    SET @DateTo   = '1/1/2100'
END
ELSE
BEGIN
    SELECT dbo.job.CompanyJobId, SUM(dbo.ProductionEvent.Quantity) AS total
    INTO #tempJobQty
    FROM dbo.Batch INNER JOIN
         dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
         dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
         dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid  INNER JOIN
         dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
         dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid 
    WHERE dbo.SourceType.CompanySourceTypeId = @TypeId
      AND dbo.Job.ChangeDate >= @DateFrom 
      AND dbo.Job.ChangeDate < ( @DateTo + 1 )
    GROUP BY dbo.job.CompanyJobId
END

SELECT NOCOUNT OFF

-- Now we join that temp table to the other query to get full results:
--
SELECT J.CompanyJobId, J.Name, dbo.Region.CompanyRegionID, J.Active, J.ChangeDate, temp.total AS Ttl_Qty
FROM dbo.Job AS J
     INNER JOIN #tempJobQty AS temp ON J.CompanyJobId = temp.CompanyJobID
     LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid 
     LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 
WHERE     dbo.job.CompanyJobId = @JobNumber
  AND dbo.Job.ChangeDate >= @DateFrom 
  AND dbo.Job.ChangeDate < ( @DateTo + 1 )
ORDER BY  dbo.Job.CompanyJobId

The reason I use
AND dbo.Job.ChangeDate < ( @DateTo + 1 )
instead of just
AND dbo.Job.ChangeDate <= @DateTo
(or instead of BETWEEN) is just "insurance." If the value of ChangeDate is actually a date *AND* time, then you can miss records that are on the @DateTo date but are after midnight. This way works with values that are pure dates *OR* values that are dates and times.

Old Pedant · June 17th, 2008, 02:08 PM

OOPS!!!!

At the very bottom of the code, this part

WHERE dbo.job.CompanyJobId = @JobNumber
  AND dbo.Job.ChangeDate >= @DateFrom
  AND dbo.Job.ChangeDate < ( @DateTo + 1 )

*SHOULD* be just

WHERE dbo.Job.ChangeDate >= @DateFrom
  AND dbo.Job.ChangeDate < ( @DateTo + 1 )

The selection for JobId has already been done, thanks to the JOIN to the #tempJobQty table.

snufse · June 17th, 2008, 02:57 PM

Wow!! I wil try to make this run through my sp. So far thank you.

Question, why could we not select the job or the jobs from the job table (into temp table) and then use the temp table to get the total quantities we need (then we know all the job numbers that are applicable)? I started on something like this:

Code:

CREATE PROCEDURE sp_Vecellio_Test2

   @JobNumber int,
   @DateFrom datetime,
   @DateTo datetime,
   @TypeProd varchar (20)

AS

DECLARE @totalproduction decimal


-- Get single job (single job parm)
IF @JobNumber <> 0

BEGIN

SELECT         dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Region.CompanyRegionID, dbo.Job.Active, 
                      dbo.Job.ChangeDate into #myTempJobs
FROM            dbo.Job 
                      LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 
WHERE        dbo.job.CompanyJobId = @JobNumber 
ORDER BY   dbo.Job.CompanyJobId

END

-- Get multiple jobs by date range (from/to date parm)
ELSE
    BEGIN

SELECT        dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Region.CompanyRegionID, dbo.Job.Active, 
                      dbo.Job.ChangeDate into #myTempJobNs
FROM            dbo.Job 
                      LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 
WHERE        dbo.Job.ChangeDate >= @DateFrom and dbo.Job.ChangeDate <= @DateTo
ORDER BY   dbo.Job.CompanyJobId

END

-- Get "Production Material type = PR)
SELECT          @totalmaterial = SUM(dbo.ProductionEvent.Quantity)

FROM             dbo.Batch INNER JOIN
                       dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
                       dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
                       dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid  INNER JOIN
                       dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
                       dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid left outer join
             .myTempJobs on myTempJobs.CompanyJobId = dbo.Job.CompanyJobId
WHERE         dbo.SourceType.CompanySourceTypeId  = @TypeProd

Old Pedant · June 17th, 2008, 03:19 PM

You know your data a lot better than I do. (Understatement.)

Simple answer: If it works, use it!

I just saw your original post with the three LEFT JOINs and thought you really needed all of them. If not, go for it!

snufse · June 17th, 2008, 03:47 PM

I don't really know the db, been thrown into this and experimenting with omitting joins to see if it still renders correct data.

I will use your code.

I know the main table is the job table and content of the extract from the job table dictates what needs to be added up. That is why I was experimenting ....