Incorrect syntax near the keyword SELECT

Question

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

You can do that in one step. Create a 'times' table that has 24 records numbered from 0 to 23 in it and just outer join to it. If you are interested in this method post back. — Nick.Mc
– Nick.Mc, Commented Jan 1, 2017 at 12:29

SQL Hacks · Accepted Answer · 2017-01-01 12:37:05Z

3

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

edited Jan 1, 2017 at 12:37

answered Jan 1, 2017 at 12:24

SQL Hacks

1,3321 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hmahdavi Over a year ago

this is right but Now I have a same error on the SET @j = SELECT COUNT(*) FROM @Table statment

Nick.Mc · Accepted Answer · 2017-01-01 12:48:38Z

2

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)

answered Jan 1, 2017 at 12:48

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Comments

neo mouse · Accepted Answer · 2017-01-01 12:28:29Z

1

If this is TSQL, then instead of

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

try:

SELECT @j = COUNT(*) FROM @Table

answered Jan 1, 2017 at 12:28

neo mouse

513 bronze badges

Comments

R. Richards · Accepted Answer · 2017-01-01 12:29:43Z

0

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

answered Jan 1, 2017 at 12:29

R. Richards

25.2k10 gold badges67 silver badges65 bronze badges

3 Comments

Nick.Mc Over a year ago

I suggest you learn to do this in one simple step using a tally table and outer join

R. Richards Over a year ago

@Nick.McDermaid Are you giving me coding tips? :)

Nick.Mc Over a year ago

Sorry I thought you were the OP.. anyway this is a programmers procedural approach to what is normally achieved using a tally table and an outer join.

Collectives™ on Stack Overflow

Incorrect syntax near the keyword SELECT

4 Answers 4

1 Comment

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related