Replace Null and Zeros only with Empty string in SQL Server

PRA
 
on Oct 30, 2020 11:34 PM
888 Views

Hi!

I used below script, but its count wrong when quantity is 10 its show 1. 

DECLARE @KARTA AS TABLE(
    [Kod] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nchar](20) NULL,
    [Birthday] [datetime] NULL,
    [Job] [nchar](20) NULL
)
 
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Rustam', CAST(N'1987-12-22' AS Date), N'1')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Muhammad', CAST(N'1994-12-08' AS Date), N'1')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Firuz', CAST(N'1998-04-19' AS Date), N'2')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Safar', CAST(N'2008-01-01' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Nurullo', CAST(N'1987-12-22' AS Date), N'1') 
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Safo', CAST(N'2008-01-29' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Samar', CAST(N'2008-01-25' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Samir', CAST(N'2008-01-09' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sodiq', CAST(N'2008-01-06' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sadaf', CAST(N'2008-01-08' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sobir', CAST(N'2008-01-20' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sitora', CAST(N'2008-01-22' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sabur', CAST(N'2008-01-04' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Saido', CAST(N'2008-01-07' AS Date), N'3')   
 
SELECT CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25) AS Age,
    REPLACE(COUNT(CASE K.JOB WHEN 1 THEN 1 END),0,'') worker,
    REPLACE(COUNT(CASE K.JOB WHEN 2 THEN 1 END),0,'') student,
    REPLACE(COUNT(CASE K.JOB WHEN 3 THEN 1 END),0,'') puple
FROM @KARTA K
GROUP BY CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25)
ORDER BY CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25)

Result must be:

Age

worker

student

puple

12

 

 

10

22

 

1

 

25

1

 

 

32

2

 

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Oct 31, 2020 07:33 AM

Hi PRA,

Refer below query.

SQL

DECLARE @KARTA AS TABLE(
    [Kod] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nchar](20) NULL,
    [Birthday] [datetime] NULL,
    [Job] [nchar](20) NULL
)
  
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Rustam', CAST(N'1987-12-22' AS Date), N'1')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Muhammad', CAST(N'1994-12-08' AS Date), N'1')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Firuz', CAST(N'1998-04-19' AS Date), N'2')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Safar', CAST(N'2008-01-01' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Nurullo', CAST(N'1987-12-22' AS Date), N'1')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Safo', CAST(N'2008-01-29' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Samar', CAST(N'2008-01-25' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Samir', CAST(N'2008-01-09' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sodiq', CAST(N'2008-01-06' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sadaf', CAST(N'2008-01-08' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sobir', CAST(N'2008-01-20' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sitora', CAST(N'2008-01-22' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sabur', CAST(N'2008-01-04' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Saido', CAST(N'2008-01-07' AS Date), N'3') 

SELECT CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25) AS Age,
    ISNULL(CAST(NULLIF(COUNT(CASE K.JOB WHEN 1 THEN 1 END),0) AS VARCHAR), '') worker,
    ISNULL(CAST(NULLIF(COUNT(CASE K.JOB WHEN 2 THEN 1 END),0) AS VARCHAR), '') student,
    ISNULL(CAST(NULLIF(COUNT(CASE K.JOB WHEN 3 THEN 1 END),0) AS VARCHAR), '') puple
FROM @KARTA K
GROUP BY CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25)
ORDER BY CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25)

Output

Age worker student puple
12     10
22   1  
25 1    
32 2