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 4 years, 8 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
  ...

Just a thought ...

What about an exclusion table (or set of tables) that you can populate per customer.  Then modify your queries to exclude data based on the config table.

As...

Here is a page with a long list of known issues with...

On my SQL 2019 instance, the behaviour does not match your explanation

This should get you going in the right direction

;WITH cteBase AS (
  SELECT TABLE1.ID, TABLE2.Processingnumber
    , rn = ROW_NUMBER() OVER (PARTITION BY TABLE1.ID...

The following code will not divide the values exactly.  But since you have a large number of records, it uses the card number to split the records.  It will guarantee...

I believe that you are looking for fuzzy matches.  I have never had to do it before, but this looks like it might get you started

https://www.google.com/search?rlz=1C1GCEA_enZA912ZA912&sxsrf=ALeKk00uYiKdhg1PIAY7XR2lKfHio_Q_cg%3A1603725887965&ei=P-qWX-O5OsuM8gKosZMo&q=sql+fuzzy+match&oq=sql+fuzzy&gs_lcp=CgZwc3ktYWIQAxgBMgUIABDJAzICCAAyAggAMgIIADICCAAyAggAMgIIADIHCAAQFBCHAjICCAAyAggAOgcIABBHELADOgQIIxAnOggIABDJAxCRAjoFCAAQkQI6BAgAEEM6BwgAEMkDEENQnf8EWPCKBWCwsgVoAXAAeACAAccDiAHhDJIBBzItMi4yLjGYAQCgAQGqAQdnd3Mtd2l6yAEIwAEB&sclient=psy-ab