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
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.
INSERT INTO table SET x=1, y=2 ON DUPLICATE KEY UPDATE x=x+1, y=y+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
Logging SQL Statements of Entity Framework 5 for Database-First Aproach
Is This Normalization Correct? (Two Many-To-Manys Connected by a Many-To-One)
How to Compare If Two Strings Contain the Same Words in T-SQL for SQL Server 2008
SQL - Converting 24-Hour ("Military") Time (2145) to "Am/Pm Time" (9:45 Pm)
How to Select Only the Latest Entry in a Table
Select Columns from One Table Based on the Column Names from Another Table
Delete Records Within Instead of Delete Trigger
Restore SQL Server Database - Failed: 38(Reached the End of the File.)
Update Multiple Columns in Merge Statement Oracle
Is There a Function That Takes a Year, Month and Day to Create a Date in Postgresql
Can SQL Server Pivot Without Knowing the Resulting Column Names
SQL Server 2008 Password Ending in a Semicolon
SQL Server Compact Edition Isnull(Sth, ' ') Returns a Boolean Value
How to Return a New Identity Column Value from an SQLserver Select Statement
Cannot Have a Qualifier in the Select List While Performing a Join W/ Using Keyword
Sql- Union All a Large Number of Tables
Split String Oracle into a Single Column and Insert into a Table