This is a good job.

Come to think of it, I've had a couple other odd ones:

About 20 years ago I went for an interview in Bristol (England) & the 'office' turned out to be...

+1

There must be more good stories out there..

No matter how the interview goes, I always try to get offered the job. At least I have the option to take it or leave it, & it's good...

Thank you.

One thing - using INNER JOINs assumes the ID exists in all tables. If it doesn't you will lose the whole record. If any table is missing...

How about this?

IF OBJECT_ID('dbo.RECORDS') IS NOT NULL

DROP TABLE dbo.RECORDS;

IF OBJECT_ID('dbo.NAMES') IS NOT NULL

DROP TABLE dbo.NAMES;

IF OBJECT_ID('dbo.DATES') IS NOT NULL

DROP TABLE dbo.DATES;

IF OBJECT_ID('dbo.PARENTS') IS NOT NULL

DROP TABLE dbo.PARENTS;

IF OBJECT_ID('dbo.BAPTISM') IS NOT...

Well - that'll be extremely slow.

Why don't you like the CTE solution?

You need to pass the parameter to the sql.

I think this will work:

ALTER PROCEDURE [dbo].[TestSPR]

@P1 int

AS

BEGIN

SET NOCOUNT ON;

DECLARE @MyExec VARCHAR(100)

SET @MyExec = 'Select * from MyTable...

Here's one I prepared earlier:

DECLARE @Lab_Time DateTime,

@StartDate_Yesterday DateTime,

@Two_AM_Yesterday DateTime,

@EndDate_Yesterday DateTime;

-- Set date values:

-- Use >= & < to check dates - this way you don't miss any & you don't...

Another point - it would be worth setting the date selection parameters outside the select statement like so:

DECLARE @DateParam DateTime;

select @DateParam = CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '02:00:01 AM');

-- Also: It might be tidier...

If I understand, you are getting 2 dates for some patients, & you only want one.

You want to choose which to display when there are 2.

You can join the 2...

The less full index pages are, the more of them must be read into memory to do a given search. The fuller they are, the less pages need to...

This is a good way using a CTE. Even though you delete from the CTE, the underlying table is modified:

IF OBJECT_ID('tempdb..#Contact') IS NOT NULL

DROP TABLE #Contact;

CREATE TABLE #Contact (FirstName...

Here are a couple of ideas:

This uses cross apply to get the data in a single query - but the 'distinct' is a bit clunky.

select distinct

P.productlineid,

OOS.NumberofProductsOutofStock

from

@Products P

cross apply (select...

This will create a table with total lines. It's not very set-based though, & you end up with the TotalLine column which you need to order it....

WITH Totals AS

(

SELECT...