SQL Server query runs - but no data is returned

Question

Although the following query runs, no data is being returned. The query splits columns into JudgmentMonth, Consumer and Commercial. My knowledge of SQL Server is not great but I'm guessing no date parameters are given hence no data is being returned. I'm not sure where date is to be manually entered after >= and <=

WITH dset AS
(
    SELECT 
        COUNT(category) AS Volumes, 
        MONTH(creation_date) AS JudgmentMonth, 
        transaction_type, 
        REPLACE(record_type, 3, 2) AS RecordType
    FROM 
        table 
    WHERE 
        transaction_type = 'jg' 
        AND category = 'CCJ' 
        AND CAST(creation_date AS DATE) >=  CONVERT(VARCHAR(12), GETDATE(), 101) 
        AND CAST(creation_date AS DATE) <=  CONVERT(VARCHAR(12), GETDATE(), 101)
    GROUP BY 
        MONTH(creation_date), transaction_type, REPLACE(record_type, 3, 2)
)
SELECT    
    x.JudgmentMonth,
    MAX(CASE x.RecordType WHEN '1' THEN x.Volumes END) CONSUMER,
    MAX(CASE x.RecordType WHEN '2' THEN x.Volumes END) COMMERCIAL
FROM    
    (SELECT    
         r.JudgmentMonth, r.RecordType, r.Volumes
     FROM    
         dset r) x
GROUP BY 
    x.JudgmentMonth

Why are you converting your dates (GETDATE()) to varchar? That's not going to work. Just use convert(date, getdate()) instead? And if it's just one date, then use = — Veijo
– Veijo, Commented Jun 11, 2018 at 15:52

simon at rcl · Accepted Answer · 2018-06-11 15:54:56Z

1

You are giving it dates: you give it getdate() twice!

creation_date >= getdate and creation_date <= getdate

is the same as

creation_date = getdate()

So unless you have rows with a creation_date exactly matching the current date/time, you won't get much at all.

answered Jun 11, 2018 at 15:54

simon at rcl

7,3691 gold badge21 silver badges25 bronze badges

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

Comments

Thom A · Accepted Answer · 2018-06-11 16:06:01Z

1

Converting your column creation_date to a date is going to make your query non-SARGable. Assuming that what you want is data for tthe current date, I would suggest:

...
WHERE ...
  AND creation_date >= CONVERT(date, GETDATE())
  AND creation_date < CONVERT(date, DATEADD(DAY, 1, GETDATE()))
GROUP BY...

This will create a SARGable query, and thus perform far faster than converting both sides of the expression.

answered Jun 11, 2018 at 16:06

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Collectives™ on Stack Overflow

SQL Server query runs - but no data is returned

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related