CODEWITHSUNDEEP
sql-serverdatetimestored-proceduressum
Daniel_Ranjbar
Daniel_Ranjbar

Reputation: 120

how to sum up all time values on a given date?

I have a stopwatch project in C#. I have to start recording time and store it in my database in a Timespan column. I also have a Timestamp column which records the date that the time was recorded.

Now I want to have a store procedure(or anything) to pass the program a date and I want my program to search the database and sum up ALL values in every corresponding Timespan columns on the given date. In other words If I give it a date I want my program to look up every Timestamp column cells and if there is one or more timestamp equal to the given date I want the program to go ahead and check all of their related "Timespan" columns and sum them up.

My code only works if there is only 1 timestamp value equal to the given date and it yells at me if there is two or more. this is my problem. this is the error I get with:

Additional information: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. I know the reson of the following error I just dont know the solution

please dont just try to correct my code, suggest me a new way if you could. thank you.

My sum store procedure:

CREATE PROCEDURE SumOnDateReport
    @QueryDate nvarchar(50)
AS
    DECLARE @total_sec INT
    DECLARE @HourTime INT
    DECLARE @MinTime INT
    DECLARE @SecTime INT

    SET @HourTime = (SELECT CONVERT(INT, SUBSTRING(TimeStringColumn, 1, 2)) 
                     FROM InfoTable 
                     WHERE TimeStampColumn = @QueryDate)
    SET @MinTime = (SELECT CONVERT(INT, SUBSTRING(TimeStringColumn, 3, 2)) 
                    FROM InfoTable 
                    WHERE TimeStampColumn = @QueryDate)
    SET @SecTime = (SELECT CONVERT(INT, SUBSTRING(TimeStringColumn, 5, 2)) 
                    FROM InfoTable 
                    WHERE TimeStampColumn = @QueryDate)

    SET @total_sec = @HourTime * 3600 + @MinTime * 60 + @SecTime

    INSERT INTO InfoTable(TotalSecColumn) VALUES (null)

    UPDATE InfoTable 
    SET TotalSecColumn = @total_sec
    WHERE TimeStampColumn = @QueryDate

    SELECT TotalSecColumn 
    FROM InfoTable
    WHERE TimeStampColumn = @QueryDate

my table: 

CREATE TABLE [dbo].[InfoTable] (
    [Id]               INT           IDENTITY (1, 1) NOT NULL,
    [TimeSpanColumn]   NVARCHAR (50) NULL,
    [TimeStampColumn]  DATETIME      NULL,
    [TimeStringColumn] NVARCHAR (50) NULL,
    [TotalSecColumn]   NVARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Upvotes: 0

Views: 135

Answers (1)

CWeber
CWeber

Reputation: 26

Close. An integer variable can only accept a single value. To get the single value, you group and sum on each of the sub selects:

CREATE PROCEDURE SumOnDateReport @QueryDate nvarchar(50) AS DECLARE @total_sec INT DECLARE @HourTime INT DECLARE @MinTime INT DECLARE @SecTime INT

SET @HourTime = (SELECT SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 1, 2))) 
                 FROM InfoTable 
                 WHERE TimeStampColumn = @QueryDate
                 GROUP BY TimeStampColumn) * 3600
SET @MinTime = (SELECT SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 3, 2))) 
                FROM InfoTable 
                WHERE TimeStampColumn = @QueryDate
                GROUP BY TimeStampColumn) * 60
SET @SecTime = (SELECT SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 5, 2))) 
                FROM InfoTable 
                WHERE TimeStampColumn = @QueryDate
                GROUP BY TimeStampColumn)
SET @total_sec = @HourTime + @MinTime  + @SecTime

INSERT INTO InfoTable(TotalSecColumn) VALUES (null)

UPDATE InfoTable 
SET TotalSecColumn = @total_sec
WHERE TimeStampColumn = @QueryDate

SELECT TotalSecColumn 
FROM InfoTable
WHERE TimeStampColumn = @QueryDate

Upvotes: 1

Related Questions