Sql Query with a condition

Hello ,
Please find the query which needs a condition to be added :
;WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     Table_JOB J
     GROUP BY J.DriverID ) ,
     A
AS ( SELECT j.ID ,
            j.ClientName ,
            j.DriverID ,
            CAST(j.CollectionDateTime AS DATE) AS AllocStDate ,
            j.Allocation ,
            CAST(j.AllocEndDate AS DATE) AS AllocEndDate
     FROM  Table_JOB j
            INNER JOIN Drivers d ON j.driverId = d.driverId
                                    AND j.CollectionDatetime = d.LastAllocStDate ) ,
     T
AS ( SELECT B.NAME ,
            A.Allocation ,
            A.AllocStDate ,
            A.AllocEndDate ,
            A.ClientName ,
            A.ID ,
            IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob
     FROM   A
            INNER JOIN FieldResource B ON A.DriverID = B.ID ) ,
     Z
AS ( SELECT DISTINCT T.Name ,
            T.Allocation ,
            T.AllocStDate ,
            T.AllocEndDate ,
            IIF(T.OnJob IS NULL, NULL, T.ClientName) AS ClientName ,
            IIF(T.OnJob IS NULL, NULL, T.ID) AS ID ,
            IIF(T.OnJob IS NULL, 'YES', NULL) AS NotAllocated ,
            T.OnJob AS Allocated
     FROM   T )
,CTE1 AS
(
      SELECT   
                   Z.Name ,
                   Z.Allocation, 
                    Z.Allocated ,
                   Z.AllocStDate ,
                   Z.AllocEndDate ,
                   Z.ClientName ,
                   Z.ID ,
                   Z.NotAllocated ,                                               
                   IIF( 1=1  
                   AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
                   , 'Allocated', '') AS IsAllocatedByTransferTab
      FROM     Z
                   
)
,CTE2 AS
(
      SELECT u.Name,u.Allocation,u.AllocStDate,u.AllocEndDate      , u.ClientName ,u.ID,u.NotAllocated,
      CASE WHEN u.NotAllocated IS NULL THEN 'Yes' ELSE u.Allocated END Allocated
      ,u.IsAllocatedByTransferTab
      ,TrDate,Comments
      FROM 
      (
            SELECT Z.Name,      Z.Allocation ,Z.allocated       ,  
            CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END AllocStDate
             ,      Z.AllocEndDate      , Z.ClientName ,      Z.ID , 
             CASE WHEN TT.Comments = 'Allocated' THEN NULL ELSE Z.NotAllocated END      NotAllocated 
            , CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.Comments
            ELSE Z.IsAllocatedByTransferTab END IsAllocatedByTransferTab
            ,COUNT(*) OVER (PARTITION BY Z.Name ORDER BY CASE 
            WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END DESC) rnk
                  ,TT.TrDate,TT.Comments 
            FROM CTE1 Z
            LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name 
      )u WHERE u.rnk = 1 
)
,CTE3 AS
(
      SELECT Name      ,Allocation,      AllocStDate,      AllocEndDate      ,ClientName      
      ,ID
      ,IIF (  AllocEndDate < GETDATE() , 'Yes' , NotAllocated  ) NotAllocated
      ,IIF ( AllocEndDate < GETDATE()  , NULL  , Allocated  ) Allocated
      ,IIF ( AllocEndDate < GETDATE() , NULL , IsAllocatedByTransferTab ) IsAllocatedByTransferTab
      ,TrDate,Comments
      FROM CTE2
)
SELECT Name      ,Allocation,      AllocStDate,      AllocEndDate      ,ClientName      
,ID 
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , NULL , NotAllocated  ) NotAllocated
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , 'Yes'  , Allocated  ) Allocated
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , 'Allocated' , IsAllocatedByTransferTab ) IsAllocatedByTransferTab
FROM CTE3
order by notallocated asc

Open in new window


The expected Result is the excel sheet please find it attached
ExpectedResult6.xlsx
RIASAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello RIAS

Please make an attempt to changed code below

;WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     Table_JOB J
     GROUP BY J.DriverID ) ,
     A
AS ( SELECT j.ID ,
            j.ClientName ,
            j.DriverID ,
            CAST(j.CollectionDateTime AS DATE) AS AllocStDate ,
            j.Allocation ,
            CAST(j.AllocEndDate AS DATE) AS AllocEndDate
     FROM  Table_JOB j
            INNER JOIN Drivers d ON j.driverId = d.driverId
                                    AND j.CollectionDatetime = d.LastAllocStDate ) ,
     T
AS ( SELECT B.NAME ,
            A.Allocation ,
            A.AllocStDate ,
            A.AllocEndDate ,
            A.ClientName ,
            A.ID ,
            IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob
     FROM   A
            INNER JOIN FieldResource B ON A.DriverID = B.ID ) ,
     Z
AS ( SELECT DISTINCT T.Name ,
            T.Allocation ,
            T.AllocStDate ,
            T.AllocEndDate ,
            IIF(T.OnJob IS NULL, NULL, T.ClientName) AS ClientName ,
            IIF(T.OnJob IS NULL, NULL, T.ID) AS ID ,
            IIF(T.OnJob IS NULL, 'YES', NULL) AS NotAllocated ,
            T.OnJob AS Allocated
     FROM   T )
,CTE1 AS
(
      SELECT  
                   Z.Name ,
                   Z.Allocation,
                    Z.Allocated ,
                   Z.AllocStDate ,
                   Z.AllocEndDate ,
                   Z.ClientName ,
                   Z.ID ,
                   Z.NotAllocated ,                                              
                   IIF( 1=1  
                   AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
                   , 'Allocated', '') AS IsAllocatedByTransferTab
      FROM     Z
                   
)
,CTE2 AS
(
      SELECT u.Name,u.Allocation,u.AllocStDate,u.AllocEndDate      , u.ClientName ,u.ID,u.NotAllocated,
      CASE WHEN u.NotAllocated IS NULL THEN 'Yes' ELSE u.Allocated END Allocated
      ,u.IsAllocatedByTransferTab
      ,TrDate,Comments
      FROM
      (
            SELECT Z.Name,      Z.Allocation ,Z.allocated       ,  
            CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END AllocStDate
             ,      Z.AllocEndDate      , Z.ClientName ,      Z.ID ,
             CASE WHEN TT.Comments = 'Allocated' THEN NULL ELSE Z.NotAllocated END      NotAllocated
            , CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.Comments
            ELSE Z.IsAllocatedByTransferTab END IsAllocatedByTransferTab
            ,COUNT(*) OVER (PARTITION BY Z.Name ORDER BY CASE
            WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END DESC) rnk
                  ,TT.TrDate,TT.Comments ,TT.IsNotActive
            FROM CTE1 Z
            LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name
                  
      )u WHERE u.rnk = 1 AND u.IsNotActive <> 'True'
)
,CTE3 AS
(
      SELECT Name      ,Allocation,      AllocStDate,      AllocEndDate      ,ClientName      
      ,ID
      ,IIF (  AllocEndDate < GETDATE() , 'Yes' , NotAllocated  ) NotAllocated
      ,IIF ( AllocEndDate < GETDATE()  , NULL  , Allocated  ) Allocated
      ,IIF ( AllocEndDate < GETDATE() , NULL , IsAllocatedByTransferTab ) IsAllocatedByTransferTab
      ,TrDate,Comments
      FROM CTE2
)
SELECT Name      ,Allocation,      AllocStDate,      AllocEndDate      ,ClientName      
,ID
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , NULL , NotAllocated  ) NotAllocated
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , 'Yes'  , Allocated  ) Allocated
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , 'Allocated' , IsAllocatedByTransferTab ) IsAllocatedByTransferTab
FROM CTE3
order by notallocated asc

Vaibhav
1
RIASAuthor Commented:
Thanks Trying...
1
RIASAuthor Commented:
Vaibhav,

IsNotActive is a bit field.
Currently your query is not giving any results.

Thanks
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Vaibhav GoelMSBI , SQL ConsultantCommented:
;WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     Table_JOB J
     GROUP BY J.DriverID ) ,
     A
AS ( SELECT j.ID ,
            j.ClientName ,
            j.DriverID ,
            CAST(j.CollectionDateTime AS DATE) AS AllocStDate ,
            j.Allocation ,
            CAST(j.AllocEndDate AS DATE) AS AllocEndDate
     FROM  Table_JOB j
            INNER JOIN Drivers d ON j.driverId = d.driverId
                                    AND j.CollectionDatetime = d.LastAllocStDate ) ,
     T
AS ( SELECT B.NAME ,
            A.Allocation ,
            A.AllocStDate ,
            A.AllocEndDate ,
            A.ClientName ,
            A.ID ,
            IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob
     FROM   A
            INNER JOIN FieldResource B ON A.DriverID = B.ID ) ,
     Z
AS ( SELECT DISTINCT T.Name ,
            T.Allocation ,
            T.AllocStDate ,
            T.AllocEndDate ,
            IIF(T.OnJob IS NULL, NULL, T.ClientName) AS ClientName ,
            IIF(T.OnJob IS NULL, NULL, T.ID) AS ID ,
            IIF(T.OnJob IS NULL, 'YES', NULL) AS NotAllocated ,
            T.OnJob AS Allocated
     FROM   T )
,CTE1 AS
(
      SELECT  
                   Z.Name ,
                   Z.Allocation,
                    Z.Allocated ,
                   Z.AllocStDate ,
                   Z.AllocEndDate ,
                   Z.ClientName ,
                   Z.ID ,
                   Z.NotAllocated ,                                              
                   IIF( 1=1  
                   AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
                   , 'Allocated', '') AS IsAllocatedByTransferTab
      FROM     Z
                   
)
,CTE2 AS
(
      SELECT u.Name,u.Allocation,u.AllocStDate,u.AllocEndDate      , u.ClientName ,u.ID,u.NotAllocated,
      CASE WHEN u.NotAllocated IS NULL THEN 'Yes' ELSE u.Allocated END Allocated
      ,u.IsAllocatedByTransferTab
      ,TrDate,Comments
      FROM
      (
            SELECT Z.Name,      Z.Allocation ,Z.allocated       ,  
            CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END AllocStDate
             ,      Z.AllocEndDate      , Z.ClientName ,      Z.ID ,
             CASE WHEN TT.Comments = 'Allocated' THEN NULL ELSE Z.NotAllocated END      NotAllocated
            , CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.Comments
            ELSE Z.IsAllocatedByTransferTab END IsAllocatedByTransferTab
            ,COUNT(*) OVER (PARTITION BY Z.Name ORDER BY CASE
            WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END DESC) rnk
                  ,TT.TrDate,TT.Comments ,TT.IsNotActive
            FROM CTE1 Z
            LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name
                  
      )u WHERE u.rnk = 1 AND u.IsNotActive <> 1
)
,CTE3 AS
(
      SELECT Name      ,Allocation,      AllocStDate,      AllocEndDate      ,ClientName      
      ,ID
      ,IIF (  AllocEndDate < GETDATE() , 'Yes' , NotAllocated  ) NotAllocated
      ,IIF ( AllocEndDate < GETDATE()  , NULL  , Allocated  ) Allocated
      ,IIF ( AllocEndDate < GETDATE() , NULL , IsAllocatedByTransferTab ) IsAllocatedByTransferTab
      ,TrDate,Comments
      FROM CTE2
)
SELECT Name      ,Allocation,      AllocStDate,      AllocEndDate      ,ClientName      
,ID
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , NULL , NotAllocated  ) NotAllocated
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , 'Yes'  , Allocated  ) Allocated
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , 'Allocated' , IsAllocatedByTransferTab ) IsAllocatedByTransferTab
FROM CTE3
order by notallocated asc
0
RIASAuthor Commented:
Thanks
1
RIASAuthor Commented:
Vaibhav,
Still no results .
When I remove this bit
AND u.IsNotActive = 1
There are results
Thanks
0
Vaibhav GoelMSBI , SQL ConsultantCommented:
Very Impressive RIAS.

Updated.

;WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     Table_JOB J
     GROUP BY J.DriverID ) ,
     A
AS ( SELECT j.ID ,
            j.ClientName ,
            j.DriverID ,
            CAST(j.CollectionDateTime AS DATE) AS AllocStDate ,
            j.Allocation ,
            CAST(j.AllocEndDate AS DATE) AS AllocEndDate
     FROM  Table_JOB j
            INNER JOIN Drivers d ON j.driverId = d.driverId
                                    AND j.CollectionDatetime = d.LastAllocStDate ) ,
     T
AS ( SELECT B.NAME ,
            A.Allocation ,
            A.AllocStDate ,
            A.AllocEndDate ,
            A.ClientName ,
            A.ID ,
            IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob
     FROM   A
            INNER JOIN FieldResource B ON A.DriverID = B.ID ) ,
     Z
AS ( SELECT DISTINCT T.Name ,
            T.Allocation ,
            T.AllocStDate ,
            T.AllocEndDate ,
            IIF(T.OnJob IS NULL, NULL, T.ClientName) AS ClientName ,
            IIF(T.OnJob IS NULL, NULL, T.ID) AS ID ,
            IIF(T.OnJob IS NULL, 'YES', NULL) AS NotAllocated ,
            T.OnJob AS Allocated
     FROM   T )
,CTE1 AS
(
      SELECT  
                   Z.Name ,
                   Z.Allocation,
                    Z.Allocated ,
                   Z.AllocStDate ,
                   Z.AllocEndDate ,
                   Z.ClientName ,
                   Z.ID ,
                   Z.NotAllocated ,                                              
                   IIF( 1=1  
                   AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
                   , 'Allocated', '') AS IsAllocatedByTransferTab
      FROM     Z
                   
)
,CTE2 AS
(
      SELECT u.Name,u.Allocation,u.AllocStDate,u.AllocEndDate      , u.ClientName ,u.ID,u.NotAllocated,
      CASE WHEN u.NotAllocated IS NULL THEN 'Yes' ELSE u.Allocated END Allocated
      ,u.IsAllocatedByTransferTab
      ,TrDate,Comments
      FROM
      (
            SELECT Z.Name,      Z.Allocation ,Z.allocated       ,  
            CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END AllocStDate
             ,      Z.AllocEndDate      , Z.ClientName ,      Z.ID ,
             CASE WHEN TT.Comments = 'Allocated' THEN NULL ELSE Z.NotAllocated END      NotAllocated
            , CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.Comments
            ELSE Z.IsAllocatedByTransferTab END IsAllocatedByTransferTab
            ,COUNT(*) OVER (PARTITION BY Z.Name ORDER BY CASE
            WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END DESC) rnk
                  ,TT.TrDate,TT.Comments ,TT.IsNotActive
            FROM CTE1 Z
            LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name
                  
      )u WHERE u.rnk = 1 AND u.IsNotActive = 1
)
,CTE3 AS
(
      SELECT Name      ,Allocation,      AllocStDate,      AllocEndDate      ,ClientName      
      ,ID
      ,IIF (  AllocEndDate < GETDATE() , 'Yes' , NotAllocated  ) NotAllocated
      ,IIF ( AllocEndDate < GETDATE()  , NULL  , Allocated  ) Allocated
      ,IIF ( AllocEndDate < GETDATE() , NULL , IsAllocatedByTransferTab ) IsAllocatedByTransferTab
      ,TrDate,Comments
      FROM CTE2
)
SELECT Name      ,Allocation,      AllocStDate,      AllocEndDate      ,ClientName      
,ID
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , NULL , NotAllocated  ) NotAllocated
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , 'Yes'  , Allocated  ) Allocated
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , 'Allocated' , IsAllocatedByTransferTab ) IsAllocatedByTransferTab
FROM CTE3
order by notallocated asc
1
RIASAuthor Commented:
Nope Vaibhav,
Still no results displayed.
0
Vaibhav GoelMSBI , SQL ConsultantCommented:
Provide of output of this please

;WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     Table_JOB J
     GROUP BY J.DriverID ) ,
     A
AS ( SELECT j.ID ,
            j.ClientName ,
            j.DriverID ,
            CAST(j.CollectionDateTime AS DATE) AS AllocStDate ,
            j.Allocation ,
            CAST(j.AllocEndDate AS DATE) AS AllocEndDate
     FROM  Table_JOB j
            INNER JOIN Drivers d ON j.driverId = d.driverId
                                    AND j.CollectionDatetime = d.LastAllocStDate ) ,
     T
AS ( SELECT B.NAME ,
            A.Allocation ,
            A.AllocStDate ,
            A.AllocEndDate ,
            A.ClientName ,
            A.ID ,
            IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob
     FROM   A
            INNER JOIN FieldResource B ON A.DriverID = B.ID ) ,
     Z
AS ( SELECT DISTINCT T.Name ,
            T.Allocation ,
            T.AllocStDate ,
            T.AllocEndDate ,
            IIF(T.OnJob IS NULL, NULL, T.ClientName) AS ClientName ,
            IIF(T.OnJob IS NULL, NULL, T.ID) AS ID ,
            IIF(T.OnJob IS NULL, 'YES', NULL) AS NotAllocated ,
            T.OnJob AS Allocated
     FROM   T )
,CTE1 AS
(
      SELECT  
                   Z.Name ,
                   Z.Allocation,
                    Z.Allocated ,
                   Z.AllocStDate ,
                   Z.AllocEndDate ,
                   Z.ClientName ,
                   Z.ID ,
                   Z.NotAllocated ,                                              
                   IIF( 1=1  
                   AND EXISTS ( SELECT * FROM Transfer TT WHERE TT.Driver = Z.Name AND TT.Comments = 'Allocated' )
                   , 'Allocated', '') AS IsAllocatedByTransferTab
      FROM     Z
                   
)
,CTE2 AS
(
      SELECT u.Name,u.Allocation,u.AllocStDate,u.AllocEndDate      , u.ClientName ,u.ID,u.NotAllocated,
      CASE WHEN u.NotAllocated IS NULL THEN 'Yes' ELSE u.Allocated END Allocated
      ,u.IsAllocatedByTransferTab
      ,TrDate,Comments,IsNotActive
      FROM
      (
            SELECT Z.Name,      Z.Allocation ,Z.allocated       ,  
            CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END AllocStDate
             ,      Z.AllocEndDate      , Z.ClientName ,      Z.ID ,
             CASE WHEN TT.Comments = 'Allocated' THEN NULL ELSE Z.NotAllocated END      NotAllocated
            , CASE WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.Comments
            ELSE Z.IsAllocatedByTransferTab END IsAllocatedByTransferTab
            ,COUNT(*) OVER (PARTITION BY Z.Name ORDER BY CASE
            WHEN  TT.Comments = 'Transfer'  or  TT.Comments = 'Allocated' THEN TT.TrDate ELSE Z.AllocStDate END DESC) rnk
                  ,TT.TrDate,TT.Comments ,TT.IsNotActive
            FROM CTE1 Z
            LEFT JOIN [Transfer] TT ON TT.Driver = Z.Name
                  
      )u WHERE u.rnk = 1
)
,CTE3 AS
(
      SELECT IsNotActive, Name      ,Allocation,      AllocStDate,      AllocEndDate      ,ClientName      
      ,ID
      ,IIF (  AllocEndDate < GETDATE() , 'Yes' , NotAllocated  ) NotAllocated
      ,IIF ( AllocEndDate < GETDATE()  , NULL  , Allocated  ) Allocated
      ,IIF ( AllocEndDate < GETDATE() , NULL , IsAllocatedByTransferTab ) IsAllocatedByTransferTab
      ,TrDate,Comments
      FROM CTE2
)
SELECT Name  ,IsNotActive    ,Allocation,      AllocStDate,      AllocEndDate      ,ClientName      
,ID
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , NULL , NotAllocated  ) NotAllocated
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , 'Yes'  , Allocated  ) Allocated
,IIF ( TrDate > GETDATE() AND Comments = 'Allocated' , 'Allocated' , IsAllocatedByTransferTab ) IsAllocatedByTransferTab
FROM CTE3
order by notallocated asc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RIASAuthor Commented:
Vaibhav ,
its looking good...let me test it even more..
Thanks
0
Vaibhav GoelMSBI , SQL ConsultantCommented:
Impressive. I will wait for you post

Vaibhav
0
RIASAuthor Commented:
Thanks! Worked like a charm
0
Vaibhav GoelMSBI , SQL ConsultantCommented:
Much obliged.

Vaibhav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.