How to Extend the Query to Add 0 in the Cell When No Activity Is Performed

How to extend the query to add 0 in the cell when no activity is performed

you could try this also :- (this could only for one particular activity)

Set Nocount On;

Declare @MinDate Date
,@MaxDate Date

Declare @test Table
(
activity Varchar(100)
,date Date
,TimePerDay Decimal(5,2)
)

Declare @result Table
(
activity Varchar(100)
,date Date
,TimePerDay Decimal(5,2) Default 0
)

;WITH CTE AS
(
SELECT email
,last_update
,activity
,starttime
,endtime
,duration As Totaltime
From users With (Nolock)
WHERE activity ='cricket'
And email = 'abc'
GROUP BY email
,activity
,duration
,starttime
,endtime
,last_update
)

Insert Into @test(activity,date,TimePerDay)
Select activity
,Cast(starttime as date) As date
,SUM(datediff(second, starttime, endtime))/60.0 As TimePerDay
From cte With (Nolock)
where starttime >= dateadd(day, -15, last_update)
group by activity
,cast(starttime as date)

Select @MinDate = Min(Date)
,@MaxDate = Max(Date)
From @test

;With AllDates As
(
Select @MinDate As xDate
From @test As t1
Where t1.date = @MinDate

Union All

Select Dateadd(Day, 1, xDate)
From AllDates As ad
Where ad.xDate < @MaxDate
)

One way is :- (left join)

Select  'cricket' As activity
,ad.xDate
,Isnull(t.TimePerDay,0) As TimePerDay
From AllDates As ad With (Nolock)
Left Join @test As t On ad.xDate = t.date

another way is :- (insert with all dates and update)

    Insert Into @result(activity,date)
Select 'cricket'
,ad.xDate
From AllDates As ad With (Nolock)

Update t
Set t.TimePerDay = t1.TimePerDay
From @result As t
Join @test As t1 On t.date = t1.date

Select *
From @result As r

output

Sample Image

Update

Declare  @MinDate   Date
,@MaxDate Date

Select @MaxDate = Getdate()
,@MinDate = Dateadd(Day, -14, @MaxDate)

;With AllDates As
(
Select @MinDate As xDate

Union All

Select Dateadd(Day, 1, xDate)
From AllDates As ad
Where ad.xDate < @MaxDate
)

Select @activity As activity ---- @activity (your stored procedure parameter)
,ad.xDate
,Isnull(t.TimePerDay,0) As TimePerDay
From AllDates As ad With (Nolock)
Left Join @test As t On ad.xDate = t.date

Adding a leading zero to some values in column in MySQL

Change the field back to numeric and use ZEROFILL to keep the zeros

or

use LPAD()

SELECT LPAD('1234567', 8, '0');

Mysql query to increase counter or marker by one for every empty record..?

You can try to declare a variable @Val with CASE WHEN to make it.

using CASE WHEN to judgment number. if number is 0 or null do accumulate the @Val

Schema (MySQL v5.7)

CREATE TABLE `stack` (
`id` int(11) NOT NULL auto_increment,
`number` int(5) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

--
-- Dumping data for table `stack`
--

INSERT INTO `stack` (`id`, `number`) VALUES
(1, 52201),
(2, 53008),
(3, 55007),
(4, 75222),
(5, 0),
(6, 74992),
(7, 14553),
(8, 54582),
(9, 54581),
(10, 74991),
(11, 14554),
(12, 0),
(13, 11413),
(14, 72414),
(15, 31415),
(16, 71416),
(17, 0),
(18, 59823),
(19, 69824),
(20, 59821),
(21, 69825),
(22, 59826);

Query #1

SET @Val= 1;

There are no results to be displayed.


Query #2

SELECT id,  
(CASE WHEN coalesce(number,0) = 0 THEN @Val:=@Val+1 ELSE @Val END) counter,
number
FROM `stack` t1
order by id;

| id | number | counter |
| --- | ------ | ------- |
| 1 | 52201 | 1 |
| 2 | 53008 | 1 |
| 3 | 55007 | 1 |
| 4 | 75222 | 1 |
| 5 | 0 | 2 |
| 6 | 74992 | 2 |
| 7 | 14553 | 2 |
| 8 | 54582 | 2 |
| 9 | 54581 | 2 |
| 10 | 74991 | 2 |
| 11 | 14554 | 2 |
| 12 | 0 | 3 |
| 13 | 11413 | 3 |
| 14 | 72414 | 3 |
| 15 | 31415 | 3 |
| 16 | 71416 | 3 |
| 17 | 0 | 4 |
| 18 | 59823 | 4 |
| 19 | 69824 | 4 |
| 20 | 59821 | 4 |
| 21 | 69825 | 4 |
| 22 | 59826 | 4 |

View on DB Fiddle

Formatting Numbers by padding with leading zeros in SQL Server

Change the number 6 to whatever your total length needs to be:

SELECT REPLICATE('0',6-LEN(EmployeeId)) + EmployeeId

If the column is an INT, you can use RTRIM to implicitly convert it to a VARCHAR

SELECT REPLICATE('0',6-LEN(RTRIM(EmployeeId))) + RTRIM(EmployeeId)

And the code to remove these 0s and get back the 'real' number:

SELECT RIGHT(EmployeeId,(LEN(EmployeeId) - PATINDEX('%[^0]%',EmployeeId)) + 1)

SQL how to increase or decrease one for a int column in one command

To answer the first:

UPDATE Orders SET Quantity = Quantity + 1 WHERE ...

To answer the second:

There are several ways to do this. Since you did not specify a database, I will assume MySQL.

  1. INSERT INTO table SET x=1, y=2 ON DUPLICATE KEY UPDATE x=x+1, y=y+2
  2. REPLACE INTO table SET x=1, y=2

They both can handle your question. However, the first syntax allows for more flexibility to update the record rather than just replace it (as the second one does).

Keep in mind that for both to exist, there has to be a UNIQUE key defined...

SQL: How to fill empty cells with previous row value?

Faiz,

how about the following query, it does what you want as far as I understand it. The comments explain each step. Take a look at CTEs on Books Online. This example could even be changed to use the new MERGE command for SQL 2008.

/* Test Data & Table */
DECLARE @Customers TABLE
(Dates datetime,
Customer integer,
Value integer)

INSERT INTO @Customers
VALUES ('20100101', 1, 12),
('20100101', 2, NULL),
('20100101', 3, 32),
('20100101', 4, 42),
('20100101', 5, 15),
('20100102', 1, NULL),
('20100102', 2, NULL),
('20100102', 3, 39),
('20100102', 4, NULL),
('20100102', 5, 16),
('20100103', 1, 13),
('20100103', 2, 24),
('20100103', 3, NULL),
('20100103', 4, NULL),
('20100103', 5, 21),
('20100104', 1, 14),
('20100104', 2, NULL),
('20100104', 3, NULL),
('20100104', 4, 65),
('20100104', 5, 23) ;

/* CustCTE - This gives us a RowNum to allow us to build the recursive CTE CleanCust */
WITH CustCTE
AS (SELECT Customer,
Value,
Dates,
ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Dates) RowNum
FROM @Customers),

/* CleanCust - A recursive CTE. This runs down the list of values for each customer, checking the Value column, if it is null it gets the previous non NULL value.*/
CleanCust
AS (SELECT Customer,
ISNULL(Value, 0) Value, /* Ensure we start with no NULL values for each customer */
Dates,
RowNum
FROM CustCte cur
WHERE RowNum = 1
UNION ALL
SELECT Curr.Customer,
ISNULL(Curr.Value, prev.Value) Value,
Curr.Dates,
Curr.RowNum
FROM CustCte curr
INNER JOIN CleanCust prev ON curr.Customer = prev.Customer
AND curr.RowNum = prev.RowNum + 1)

/* Update the base table using the result set from the recursive CTE */
UPDATE trg
SET Value = src.Value
FROM @Customers trg
INNER JOIN CleanCust src ON trg.Customer = src.Customer
AND trg.Dates = src.Dates

/* Display the results */
SELECT * FROM @Customers

How to add leading zero when number is less than 10?

format(number,'00')

Version >= 2012

how to sum the time grouped by individual day in Sql-server

Can you try this

Select play, cast(starttime as date) as date,
SUM(datediff(MINUTE, endtime, starttime)) as TimePerDay
from cte
where starttime >= '2015-05-30 17:11:34.000'
group by play, cast(starttime as date)

union

SELECT 'hockey', DATEADD(DAY,number+1,(select min(starttime) from cte)) as date, 0 as TimePerDay
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY,number+1,(select min(starttime) from cte)) < (select max(starttime) from cte)
and CAST(DATEADD(DAY,number+1,(select min(starttime) from cte)) as date) not in (select cast(starttime as date) from cte)

For Express Version :

DECLARE @startDate date = (select min(starttime) from cte)
DECLARE @endDate date = (select max(starttime) from cte)

;WITH dates(Date) AS
(
SELECT @startdate as Date
UNION ALL
SELECT DATEADD(d,1,[Date])
FROM dates
WHERE DATE < @enddate
)
Select play, cast(starttime as date) as date,
SUM(datediff(MINUTE, endtime, starttime)) as TimePerDay
from cte
where starttime >= '2015-05-30 17:11:34.000'
group by play, cast(starttime as date)

union

SELECT 'hockey' as play, Date, 0 as TimePerDay
FROM dates
where Date not in (select cast(starttime as date) from cte)


Related Topics



Leave a reply



Submit