RIAS
asked on
SQL Query
Hello,
I have query which specifies 'ON Job' based ob the collection date
Need to change the ' on job' criteria if the A.StartDate = 23-May-2017 and A.EndDate =26-May-2017 then it OnJob ; Check Start Date and EndDate . If EndDate is 23-may-2017 then Available
Regards
I have query which specifies 'ON Job' based ob the collection date
SELECT DISTINCT B.NAME, B.mobileTelephoneNumber, A.Allocation,
CASE
WHEN A.CollectionDate = CAST(GETDATE() AS date) THEN NULL
ELSE'YES'
END as Available,
CASE
WHEN A.CollectionDate = CAST(GETDATE() AS date) THEN 'ON JOB'
ELSE NULL
END as NotAvailable
FROM (SELECT DriverID, CAST(CollectionDateTime AS date) as CollectionDate, Allocation
FROM Comp_JOB
GROUP BY DriverID,Allocation, CAST(CollectionDateTime AS date)) A
INNER JOIN FieldResource B ON A.DriverID = B.ID order by NotAvailable desc
Need to change the ' on job' criteria if the A.StartDate = 23-May-2017 and A.EndDate =26-May-2017 then it OnJob ; Check Start Date and EndDate . If EndDate is 23-may-2017 then Available
Regards
Where do you have the Start and End dates in your query?
ASKER
StartDate and EndDate are new columns or fields added to thetable Comp_JOB
So if you add them to the subquery it may impact the GROUP BY and consequently the results.
You should check if this works for you:
You should check if this works for you:
SELECT DISTINCT B.NAME, B.mobileTelephoneNumber, A.Allocation,
CASE
WHEN A.CollectionDate = CAST(GETDATE() AS date) THEN NULL
ELSE'YES'
END as Available,
CASE
WHEN A.StartDate = '20170523' AND A.EndDate ='20170526' THEN 'ON JOB'
ELSE NULL
END as NotAvailable
FROM (SELECT DriverID, CAST(CollectionDateTime AS date) CollectionDate, Allocation, CAST(A.StartDate AS date) StartDate, CAST(A.EndDate AS date) EndDate
FROM Comp_JOB
GROUP BY DriverID,Allocation, CAST(CollectionDateTime AS date), CAST(A.StartDate AS date), CAST(A.EndDate AS date)) A
INNER JOIN FieldResource B ON A.DriverID = B.ID
ORDER BY NotAvailable desc
ASKER
Will check brb , thanks!
ASKER
Vitor,
Tried this but gt an error :
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "A.AllocStDate" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "A.AllocEndDate" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "A.AllocStDate" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "A.AllocEndDate" could not be bound.
Tried this but gt an error :
SELECT DISTINCT B.NAME, B.mobileTelephoneNumber, A.Allocation,A.AllocStDate,
CASE
WHEN A.CollectionDate = CAST(GETDATE() AS date) THEN NULL
ELSE'YES'
END as Available,
CASE
WHEN A.AllocStDate < = CAST(GETDATE() AS date) AND A.AllocEndDate < = CAST(GETDATE() AS date) THEN 'ON JOB'
ELSE NULL
END as NotAvailable
FROM (SELECT DriverID, CAST(CollectionDateTime AS date) CollectionDate, Allocation, CAST(A.AllocStDate AS date) AllocStDate, CAST(A.AllocEndDate AS date) AllocEndDate
FROM comp_JOB
GROUP BY DriverID,Allocation, CAST(CollectionDateTime AS date), CAST(A.AllocStDate AS date), CAST(A.AllocEndDate AS date)) A
INNER JOIN FieldResource B ON A.DriverID = B.ID
ORDER BY NotAvailable desc
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "A.AllocStDate" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "A.AllocEndDate" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "A.AllocStDate" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "A.AllocEndDate" could not be bound.
That's because you're using the alias 'A' inside the subquery and you shouldn't. Just remove the 'A.' inside the subquery:
SELECT DISTINCT B.NAME, B.mobileTelephoneNumber, A.Allocation,A.AllocStDate,
CASE
WHEN A.CollectionDate = CAST(GETDATE() AS date) THEN NULL
ELSE'YES'
END as Available,
CASE
WHEN A.AllocStDate < = CAST(GETDATE() AS date) AND A.AllocEndDate < = CAST(GETDATE() AS date) THEN 'ON JOB'
ELSE NULL
END as NotAvailable
FROM (SELECT DriverID, CAST(CollectionDateTime AS date) CollectionDate, Allocation, CAST(AllocStDate AS date) AllocStDate, CAST(AllocEndDate AS date) AllocEndDate
FROM comp_JOB
GROUP BY DriverID,Allocation, CAST(CollectionDateTime AS date), CAST(AllocStDate AS date), CAST(AllocEndDate AS date)) A
INNER JOIN FieldResource B ON A.DriverID = B.ID
ORDER BY NotAvailable desc
ASKER
Vitor,
It does not give any one as ON job
It does not give any one as ON job
I warned you that this might have impact on the results. Without sample data I can't help much.
What the subquery alone returns?
What the subquery alone returns?
SELECT DriverID, CAST(CollectionDateTime AS date) CollectionDate, Allocation, CAST(AllocStDate AS date) AllocStDate, CAST(AllocEndDate AS date) AllocEndDate
FROM comp_JOB
GROUP BY DriverID,Allocation, CAST(CollectionDateTime AS date), CAST(AllocStDate AS date), CAST(AllocEndDate AS date)
ASKER
Vitor,
It does give correct results but, displays 'Null' instead of 'On Job'
It does give correct results but, displays 'Null' instead of 'On Job'
Please post the sample data (better, the real data with sensitivity fields masked).
ASKER
ok Sir
ASKER
NAME mobileTelephoneNumber Allocation AllocStDate AllocEndDate Available NotAvailable
kjikj StartDate: 24 May 2017 ; EndDate: 26 May 2017 ; Notes: test 24/05/2017 26/05/2017 NULL NULL
oioi StartDate: 24 May 2017 ; EndDate: 26 May 2017 ; Notes: 24/05/2017 26/05/2017 NULL NULL
jyui 97 NULL NULL YES NULL
jipjpijp 199 NULL NULL YES NULL
kljuh 77212 NULL NULL YES NULL
ASKER
Vitor ,
I am struggling with formatting
I am struggling with formatting
ASKER
NAME mobileTelephoneNumber Allocation AllocStDate AllocEndDate Available NotAvailable
kjikj Notes: test 24/05/2017 26/05/2017 NULL NULL
oioi 24/05/2017 26/05/2017 NULL NULL
jyui 97 NULL NULL YES NULL
jipjpijp 199 NULL NULL YES NULL
kljuh 77212 NULL NULL YES NULL
kjikj Notes: test 24/05/2017 26/05/2017 NULL NULL
oioi 24/05/2017 26/05/2017 NULL NULL
jyui 97 NULL NULL YES NULL
jipjpijp 199 NULL NULL YES NULL
kljuh 77212 NULL NULL YES NULL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Please see the sample data
Sample.txt
Sample.txt
ASKER
Worked Brilliantly mate!
Thank you Sir as always
Thank you Sir as always
Yes, I understood by your 2nd attempt of posting the sample data.
Don't forget this solution has the date coded so isn't dynamically. For example if the StartDate is always before TODAY and EndDate before 2 days from TODAY, then the following should be used:
Don't forget this solution has the date coded so isn't dynamically. For example if the StartDate is always before TODAY and EndDate before 2 days from TODAY, then the following should be used:
SELECT DISTINCT B.NAME, B.mobileTelephoneNumber, A.Allocation,A.AllocStDate,
CASE
WHEN A.CollectionDate = CAST(GETDATE() AS date) THEN NULL
ELSE'YES'
END as Available,
CASE
WHEN A.AllocStDate < = CAST(GETDATE() AS date) AND A.AllocEndDate < = CAST(GETDATE()+2 AS date) THEN 'ON JOB'
ELSE NULL
END as NotAvailable
FROM (SELECT DriverID, CAST(CollectionDateTime AS date) CollectionDate, Allocation, CAST(AllocStDate AS date) AllocStDate, CAST(AllocEndDate AS date) AllocEndDate
FROM comp_JOB
GROUP BY DriverID,Allocation, CAST(CollectionDateTime AS date), CAST(AllocStDate AS date), CAST(AllocEndDate AS date)) A
INNER JOIN FieldResource B ON A.DriverID = B.ID
ORDER BY NotAvailable desc
ASKER
Vitor,
End Date should be after today , do i still need to add
Thanks
End Date should be after today , do i still need to add
CAST(GETDATE()+2 AS date)
2 or nothing?Thanks
That's the number of days after today.
I just added 2 because in your example you explicitly said 26-05 and that's after tomorrow.
If you want to work with tomorrow (25-05) then add only 1 day instead of 2 days.
I just added 2 because in your example you explicitly said 26-05 and that's after tomorrow.
If you want to work with tomorrow (25-05) then add only 1 day instead of 2 days.
ASKER
Vitor,
There is a small issue with the query and cannot address it .Will you help ?
There is a small issue with the query and cannot address it .Will you help ?
ASKER