Click here to Skip to main content
15,897,704 members
Search within:


How to handle null in below query.
Please Sign up or sign in to vote.
2.50/5 (2 votes)
See more:
SQL
SQL-Server-2014
How to handle null in below query.
SQL

select USERID,
case when SignDay = 01 then AttStatus end '01', -- CHECKIN EACH DAY
case when SignDay = 02 then AttStatus end '02',
case when SignDay = 03 then AttStatus end '03',
case when SignDay = 04 then AttStatus end '04',
case when SignDay = 05 then AttStatus end '05'
from vwAttInOut
where userid=11 and year(signdate)=2015 and month(signdate)=08

What I have tried:

I tried using CTE, Joins but still didn't find any way to handle null
Posted
Updated 26-Aug-16 1:05am
Add a Solution
Comments
Mehdi Gholam 26-Aug-16 1:08am    
Handle null in what?
Member 10626601 26-Aug-16 23:42pm    
SELECT t.USERID,t.[Date] SignDate,Day(t.[Date]) SignDay, CONVERT(VARCHAR(5),CHECKINOUT.CHECKTIME,108) Timein,
CONVERT(VARCHAR(5),CHECKINOUT.CHECKOUTTIME,108) 'TimeOut',CASE WHEN t.WorkDays = 0 THEN 'W' ELSE
CASE WHEN t.[Date] = Holiday.[Date] THEN 'H' ELSE
CASE WHEN c.LeaveTypeName is not null THEN c.LeaveTypeName ELSE
CASE WHEN CHECKINOUT.CHECKTIME IS NULL AND CHECKINOUT.CHECKOUTTIME IS NULL THEN 'A' ELSE
CASE WHEN CHECKINOUT.CHECKTIME IS NULL OR CHECKINOUT.CHECKOUTTIME IS NULL THEN '?' ELSE
CASE WHEN CAST(CHECKINOUT.CHECKTIME AS TIME) <= DATEADD(MINUTE,WorkHour.LateIn ,WorkHour.Timein) THEN
CASE WHEN CAST(CHECKINOUT.CHECKOUTTIME AS TIME) >= DATEADD(MINUTE,-WorkHour.EarlyOut ,WorkHour.TimOut) THEN 'P' ELSE '?'
END END END END END END END AttStatus
FROM
(SELECT USERINFO.USERID,TimeTable.[Date],TimeTable.WorkDays FROM USERINFO
CROSS JOIN TimeTable) t
LEFT JOIN CHECKINOUT ON t.[Date] = cast(CHECKINOUT.CHECKTIME as date) AND t.USERID = CHECKINOUT.USERID
LEFT OUTER JOIN WorkHour ON CHECKINOUT.WorkHourID = WorkHour.WorkHourID
LEFT OUTER JOIN Holiday on t.[Date] = Holiday.[Date]
OUTER APPLY(SELECT LeaveRequest.EmpID,zLeaveType.LeaveTypeName FROM LeaveRequest INNER JOIN zLeaveType ON LeaveRequest.LeaveTypeID = zLeaveType.LeaveTypeID
WHERE EmpID = t.USERID and t.[Date] BETWEEN FromDate AND ToDate) c
WHERE YEAR(t.[Date])=2015 and month(t.[Date])=08 and t.USERID=11
GROUP BY t.USERID,t.[Date],t.WorkDays,Holiday.[Date],c.LeaveTypeName,CHECKINOUT.CHECKTIME,CHECKINOUT.CHECKOUTTIME,WorkHour.LateIn,WorkHour.TimeIn,WorkHour.EarlyOut,
WorkHour.TimOut
---Result of the Query-----
USERID Date Day Timein Timeout AttStatus
11 2015-08-01 1 07:47 NULL ?
11 2015-08-02 2 NULL NULL A
11 2015-08-03 3 07:44 09:44 ?
11 2015-08-04 4 07:37 17:37 P
11 2015-08-05 5 07:48 17:58 P
11 2015-08-06 6 NULL NULL W
11 2015-08-07 7 NULL NULL W
11 2015-08-08 8 07:59 16:01 P
11 2015-08-09 9 07:59 16:01 P
11 2015-08-10 10 07:59 16:23 P
11 2015-08-11 11 07:04 17:20 Sick Leave
11 2015-08-12 12 06:51 18:02 Sick Leave
11 2015-08-13 13 NULL NULL W
11 2015-08-14 14 NULL NULL W
11 2015-08-15 15 07:59 16:01 Sick Leave
11 2015-08-16 16 07:00 17:44 Sick Leave
11 2015-08-17 17 07:11 18:19 Sick Leave
11 2015-08-18 18 07:08 18:26 Sick Leave
11 2015-08-19 19 NULL NULL Sick Leave
11 2015-08-20 20 NULL NULL W
11 2015-08-21 21 NULL NULL W
11 2015-08-22 22 06:29 17:55 Sick Leave
11 2015-08-23 23 07:08 17:47 H
11 2015-08-24 24 06:48 17:47 Sick Leave
11 2015-08-25 25 07:06 18:17 Sick Leave
11 2015-08-26 26 07:12 18:15 Sick Leave
11 2015-08-27 27 NULL NULL W
11 2015-08-28 28 NULL NULL W
11 2015-08-29 29 07:07 17:48 Sick Leave
11 2015-08-30 30 07:05 17:40 Sick Leave
11 2015-08-31 31 07:10 17:43 Sick Leave

Now I want to transform the date column into rows
Magic Wonder 26-Aug-16 1:12am    
Your question is not clearly indicating that in which field you are getting NULL in output. Kindly improve it clearly so that we can help you.
Member 10626601 26-Aug-16 23:42pm    
SELECT t.USERID,t.[Date] SignDate,Day(t.[Date]) SignDay, CONVERT(VARCHAR(5),CHECKINOUT.CHECKTIME,108) Timein,
CONVERT(VARCHAR(5),CHECKINOUT.CHECKOUTTIME,108) 'TimeOut',CASE WHEN t.WorkDays = 0 THEN 'W' ELSE
CASE WHEN t.[Date] = Holiday.[Date] THEN 'H' ELSE
CASE WHEN c.LeaveTypeName is not null THEN c.LeaveTypeName ELSE
CASE WHEN CHECKINOUT.CHECKTIME IS NULL AND CHECKINOUT.CHECKOUTTIME IS NULL THEN 'A' ELSE
CASE WHEN CHECKINOUT.CHECKTIME IS NULL OR CHECKINOUT.CHECKOUTTIME IS NULL THEN '?' ELSE
CASE WHEN CAST(CHECKINOUT.CHECKTIME AS TIME) <= DATEADD(MINUTE,WorkHour.LateIn ,WorkHour.Timein) THEN
CASE WHEN CAST(CHECKINOUT.CHECKOUTTIME AS TIME) >= DATEADD(MINUTE,-WorkHour.EarlyOut ,WorkHour.TimOut) THEN 'P' ELSE '?'
END END END END END END END AttStatus
FROM
(SELECT USERINFO.USERID,TimeTable.[Date],TimeTable.WorkDays FROM USERINFO
CROSS JOIN TimeTable) t
LEFT JOIN CHECKINOUT ON t.[Date] = cast(CHECKINOUT.CHECKTIME as date) AND t.USERID = CHECKINOUT.USERID
LEFT OUTER JOIN WorkHour ON CHECKINOUT.WorkHourID = WorkHour.WorkHourID
LEFT OUTER JOIN Holiday on t.[Date] = Holiday.[Date]
OUTER APPLY(SELECT LeaveRequest.EmpID,zLeaveType.LeaveTypeName FROM LeaveRequest INNER JOIN zLeaveType ON LeaveRequest.LeaveTypeID = zLeaveType.LeaveTypeID
WHERE EmpID = t.USERID and t.[Date] BETWEEN FromDate AND ToDate) c
WHERE YEAR(t.[Date])=2015 and month(t.[Date])=08 and t.USERID=11
GROUP BY t.USERID,t.[Date],t.WorkDays,Holiday.[Date],c.LeaveTypeName,CHECKINOUT.CHECKTIME,CHECKINOUT.CHECKOUTTIME,WorkHour.LateIn,WorkHour.TimeIn,WorkHour.EarlyOut,
WorkHour.TimOut
---Result of the Query-----
USERID Date Day Timein Timeout AttStatus
11 2015-08-01 1 07:47 NULL ?
11 2015-08-02 2 NULL NULL A
11 2015-08-03 3 07:44 09:44 ?
11 2015-08-04 4 07:37 17:37 P
11 2015-08-05 5 07:48 17:58 P
11 2015-08-06 6 NULL NULL W
11 2015-08-07 7 NULL NULL W
11 2015-08-08 8 07:59 16:01 P
11 2015-08-09 9 07:59 16:01 P
11 2015-08-10 10 07:59 16:23 P
11 2015-08-11 11 07:04 17:20 Sick Leave
11 2015-08-12 12 06:51 18:02 Sick Leave
11 2015-08-13 13 NULL NULL W
11 2015-08-14 14 NULL NULL W
11 2015-08-15 15 07:59 16:01 Sick Leave
11 2015-08-16 16 07:00 17:44 Sick Leave
11 2015-08-17 17 07:11 18:19 Sick Leave
11 2015-08-18 18 07:08 18:26 Sick Leave
11 2015-08-19 19 NULL NULL Sick Leave
11 2015-08-20 20 NULL NULL W
11 2015-08-21 21 NULL NULL W
11 2015-08-22 22 06:29 17:55 Sick Leave
11 2015-08-23 23 07:08 17:47 H
11 2015-08-24 24 06:48 17:47 Sick Leave
11 2015-08-25 25 07:06 18:17 Sick Leave
11 2015-08-26 26 07:12 18:15 Sick Leave
11 2015-08-27 27 NULL NULL W
11 2015-08-28 28 NULL NULL W
11 2015-08-29 29 07:07 17:48 Sick Leave
11 2015-08-30 30 07:05 17:40 Sick Leave
11 2015-08-31 31 07:10 17:43 Sick Leave


I want to transform the date column into rows
Maciej Los 26-Aug-16 2:07am    
Based on above piece of query, we can't help you. We can't see your data and we can't read in your mind. Please provide sample data.

2 solutions

SQL
select USERID,
CASE ISNULL(USERID, 'NULLVALUE')
case when SignDay = 01 then AttStatus end '01', -- CHECKIN EACH DAY
case when SignDay = 02 then AttStatus end '02',
case when SignDay = 03 then AttStatus end '03',
case when SignDay = 04 then AttStatus end '04',
case when SignDay = 05 then AttStatus end '05'
when 'NULLVALUE' then 'USERID'
else 'USERID'
end
from vwAttInOut
where userid=11 and year(signdate)=2015 and month(signdate)=08


Hope this will solves your problem.
 
Share this answer
 
Posted
Hi,

Below solution is help full for you, any further clarification please reply here.


SQL
select USERID,
 case when SignDay = 01 then AttStatus end '01', -- CHECKIN EACH DAY
 case when SignDay = 02 then AttStatus end '02',
 case when SignDay = 03 then AttStatus end '03',
 case when SignDay = 04 then AttStatus end '04',
 case when SignDay = 05 then AttStatus end '05',
 case when SignDay IS NULL then AttStatus end 'No Day',
 case when SignDay = '' then AttStatus end 'No Day'
 from vwAttInOut
 where userid=11 and year(signdate)=2015 and month(signdate)=08
 
Share this answer
 
Posted
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
Getting null response for below LDAP query in C#?
How to optimize below SQL query
How to handle null values in dynamic query in SQL server?
How to generate below output query
handle Null in sql server
What is wrong in the below SQL query
Handle Nulls in foreach statement
How to write query on below requirement
Please solve below Query
Element.closest null handling in JS/TS

Advertise
Privacy
Cookies
Terms of Use
Last Updated 26 Aug 2016
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web04 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900