You have nothing that binds the 3 records together and nothing that orders the records.  So there is no way to consistently achieve what you are trying to do.

I'm too lazy to create tables to test.  But I believe that there are 2 ways that you could achieve this.

SELECT    Table1.EMPID
FROM  ...

SQL is not Excel.  By default, it cannot see the value in the previous or next row.

However, Window Functions are going to be your new best friend for this task,...

Deadlocks are not my strong point.  That said, deadlocks occur when 2 or more processes are waiting on each other to access the same resource.  The deadlocks can occur at...

This may be what you are looking for .

SELECT  *
FROM    COMPANY
WHERE   PRODUCTS IN ( 'Product1', 'Product2', 'Product3', 'Product4' );

If you are only interested in specific employees, then put the filter on the Employee table

WITH cteHours AS (
...
)
SELECT e.identifier
, AllHours = ISNULL(SUM(h.[HOURS]),0)/60.00
FROM cms.dbo.Employee AS e
LEFT JOIN...

I cannot see your tables or the data in them, so I cannot test the scripts.

The query that you have is not the same as the one that I posted. ...

@DesNorton - yes it works now but if the employee ID is there and he or she don't swipe then nothing is captured as it would appear as null....

If you have an unknown list of [Type], dynamic cross-tab queries are dealt with expertly and in great detail in these two Jeff Moden articles:

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%E2%80%93-converting-rows-to-columns-1

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

There is no need for the sub-query

SELECT    sc.ID
        , sc.[Name]
       ...

Perhaps Trigram searches would help in this case

Trigram Wildcard String Search in SQL Server

I don't like these massive procs with code branches.  I would create a separate proc for each bucket.  Then in this proc, EXEC the relevant new proc within each IF...

• This reply was modified 5 years, 3 months ago by DesNorton.

What you need is a PIVOT or a Cross-Tab.  The following SQL will work for your provided sample data

SELECT      m.zplid
  ...

Hi Des

thanks for the answer it works well except for values which are zero then it doesn’t list them. how to list the zero values?

For example if I include...

Why are you using a cursor?  A simple sum with group by will do the trick

WITH cteHours AS (
  SELECT    emp_id
  ...