CODEWITHSUNDEEP
sqlsql-servert-sqlinsert-into
hmahdavi
hmahdavi

Reputation: 2354

Incorrect syntax near the keyword SELECT

I try to group by my purchases by hour .In this case I lave number hours that I have not any purchase in it. I want check select query and if once of hours (0 1 2 .... 22 23 ) not exist into select query : add a record with default value into @Table .I get this error :

Incorrect syntax near the keyword SELECT

How can i fix this?

    DECLARE @Table AS TABLE (Price DECIMAL, NumberOfPhurchase INT, Hour INT);
    DECLARE @i AS INT = 0;
    DECLARE @j AS INT = 0;

    INSERT INTO @Table (Price,NumberOfPhurchase,Hour)
    VALUES
      (
        SELECT SUM(p.Price)    ,
           COUNT(p.Price)  ,
           DATEPART(HOUR, p.IssueDate)
    FROM   dbo.Payments    AS p
    WHERE  p.[state] = 6
           AND p.Transactionsuccess = 1
           AND (p.ReserveType = @ReserveType OR @ReserveType = 0)
           AND p.IssueDate >= @StartDate
           AND p.IssueDate <= @EndDate
    GROUP BY
           DATEPART(HOUR, p.IssueDate)

      )


    WHILE @i <= 23
    BEGIN
        SET @j =  
        SELECT COUNT(*) FROM @Table 
        WHERE  @Table.Hour = @i;
        IF @j = 0
        BEGIN
            INSERT INTO @Table
              (
                Price,
                NumberOfPhurchase,
                Hour
              )
            VALUES
              (
                0,
                0,
                @j
              )
        END
        SET @i=@i+1;
    END
    SELECT
        @Table.Price,
        @Table.NumberOfPhurchase,
        @Table.Hour
    FROM
        @Table

Upvotes: 1

Views: 2652

Answers (4)

Nick.Mc
Nick.Mc

Reputation: 19184

For completeness sake here is a non procedural solution. If you are going to use databases you should at least understand what I'm doing here:

INSERT INTO @Table (Price,NumberOfPurchase,Hour)
SELECT ISNULL(SUM(p.Price),0)    ,
       ISNULL(COUNT(p.Price),0)  ,
       H.HourNumber
FROM   
(
VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23)
) H (HourNumber)
LEFT OUTER JOIN dbo.Payments    AS p
ON H.HourNumber = DATEPART(HOUR, p.IssueDate)
AND p.[state] = 6
AND p.Transactionsuccess = 1
AND (p.ReserveType = @ReserveType OR @ReserveType = 0)
AND p.IssueDate >= @StartDate
AND p.IssueDate <= @EndDate
GROUP BY
DATEPART(HOUR, p.IssueDate)

Upvotes: 2

SQL Hacks
SQL Hacks

Reputation: 1332

Just drop the word VALUES

INSERT INTO @Table (Price,NumberOfPhurchase,Hour)
    SELECT SUM(p.Price)    ,
       COUNT(p.Price)  ,
       DATEPART(HOUR, p.IssueDate)
    FROM   dbo.Payments    AS p
    WHERE  p.[state] = 6
       AND p.Transactionsuccess = 1
       AND (p.ReserveType = @ReserveType OR @ReserveType = 0)
       AND p.IssueDate >= @StartDate
       AND p.IssueDate <= @EndDate
    GROUP BY
       DATEPART(HOUR, p.IssueDate)

You can fill in the zero values with another INSERT .. SELECT statement:

  WHILE @i <= 23
  BEGIN
       INSERT INTO @Table(Price,NumberOfPhurchase,Hour)
       SELECT  0,0,@i
       WHERE @i NOT IN (SELECT Hour FROM @Table)
       SET @i=@i+1
  END

Upvotes: 3

R. Richards
R. Richards

Reputation: 25141

I was able to get this working with the code below (SQL Server 2016): 

    DECLARE @Table AS TABLE (Price DECIMAL, NumberOfPhurchase INT, Hour INT);
    DECLARE @i AS INT = 0;
    DECLARE @j AS INT = 0; 

    INSERT INTO @Table (Price,NumberOfPhurchase,Hour)
    SELECT SUM(p.Price)    ,
           COUNT(p.Price)  ,
           DATEPART(HOUR, p.IssueDate)
    FROM   dbo.Payments    AS p
    WHERE  p.[state] = 6
           AND p.Transactionsuccess = 1
           AND (p.ReserveType = @ReserveType OR @ReserveType = 0)
           AND p.IssueDate >= @StartDate
           AND p.IssueDate <= @EndDate
    GROUP BY
           DATEPART(HOUR, p.IssueDate)

    WHILE @i <= 23
    BEGIN
        SELECT @j =  
         COUNT(*) FROM @Table 
        WHERE  Hour = @i;
        IF @j = 0
        BEGIN
            INSERT INTO @Table
              (
                Price,
                NumberOfPhurchase,
                Hour
              )
            VALUES
              (
                0,
                0,
                @j
              )
        END
        SET @i=@i+1;
    END
    SELECT
       Price,
       NumberOfPhurchase,
       Hour
    FROM
        @Table

Upvotes: 0

neo mouse
neo mouse

Reputation: 51

If this is TSQL, then instead of 

SET @j =  
SELECT COUNT(*) FROM @Table

try:

SELECT @j = COUNT(*) FROM @Table

Upvotes: 1

Related Questions