Link to home
Start Free TrialLog in
Avatar of RIAS
RIASFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL Query

Hello,

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

Open in new window


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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Where do you have the Start and End dates in your query?
Avatar of RIAS

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:
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

Open in new window

Avatar of RIAS

ASKER

Will check brb , thanks!
Avatar of RIAS

ASKER

Vitor,
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

Open in new window




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

Open in new window

Avatar of RIAS

ASKER

Vitor,
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?
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)

Open in new window

Avatar of RIAS

ASKER

Vitor,
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).
Avatar of RIAS

ASKER

ok Sir
Avatar of RIAS

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

Open in new window

Avatar of RIAS

ASKER

Vitor ,
I am struggling with formatting
Avatar of RIAS

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
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RIAS

ASKER

Please see the sample data
Sample.txt
Avatar of RIAS

ASKER

Worked Brilliantly mate!
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:
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

Open in new window

Avatar of RIAS

ASKER

Vitor,
End Date should be after today , do i still need to add
CAST(GETDATE()+2 AS date)

Open in new window

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.
Avatar of RIAS

ASKER

Vitor,
There is a small issue with the query and cannot address it .Will you help ?