ringovski (8/18/2013)

ChrisM@Work (8/16/2013)

Stefan_G (8/16/2013)

This is a good idea - scan the large tables a single time.

To improve performance even more you should include a WHERE condition for GNLFSMID as...

Yes - have a read of this MS article.

An UPDATE then.

update mytable set date = NULL where name...

delete from mytable where name = somename and date = somedate.

pwalter83 (8/16/2013)

ChrisM@Work (7/24/2013)

pwalter83 (7/24/2013)

Hi,

The reason is -...

I think it should look more like this:

SELECT

w.eID, w.name,

al.[status],

COALESCE(al.CheckTime, c.[Date]) AS [Date]

,CASE WHEN al.CheckTime IS NULL THEN 'A' ELSE 'P' END AS Attendance

FROM dbo.employee AS w

CROSS JOIN...

You're doing just fine - you've learned a huge amount in the time you've been posting...

My mistake, coding before thinking. Try this:

SELECT *

FROM Calendar c

CROSS JOIN employee e

LEFT JOIN attendlog a

ON a.eid = e.eid

AND a.CheckTIme = c.Date

WHERE c.[Date] >= CAST('20130801' AS DATETIME)

AND...

If an employee has no attendance record for the selected time period then they will be excluded...

As Stefan has pointed out, your spec is full of errors and is very difficult to understand. What I suggest you do is to show with examples what you mean...

I'd use a CROSS APPLY cascade. This article [/url]has at least one example which is very similar to your requirements.

Stefan_G (8/16/2013)

This is a good idea - scan the large tables a single time.

To improve performance even more you should include a WHERE condition for GNLFSMID as well:

WHERE ...

I'd guess that's way too complicated for the optimiser to deal with and you've got a plan which is far from ideal. Thava has the right idea - you should...

The performance would be appalling. Consider Luis' approach, or even APPLY():

SELECT SalesPersonID, FirstName, LastName,

x.Address1,

x.Address2,

x.City,

...etc....

FROM dbo.SalesPersons

OUTER APPLY (

SELECT Address1, Address2, City

FROM dbo.Addresses

WHERE (LookUpID = 1)

AND (RecordTypeID = 3)

)...

;WITH src AS

(

SELECT

database_id, db_buffer_pages = COUNT_BIG(*)

FROM sys.dm_os_buffer_descriptors

--WHERE database_id BETWEEN 5 AND 32766

GROUP BY database_id

)

INSERT INTO MyNewTable ([db_name], db_buffer_pages, db_buffer_MB, db_buffer_percent)

SELECT

[db_name] = CASE [database_id] WHEN 32767

THEN 'Resource DB'

ELSE...