locked
Select statement compiling but not showing data RRS feed

  • Question

  • USE MyStore
    GO


    SELECT
    d.First_Of_Month "Sales month",
    i.Item_Name,
    i.Item_Section,
    Count(s.Sales_ID) Sales,
    Sum(i.Item_Price) Revenue

    FROM dbo.Items i
    inner join dbo.Sales s
    on i.Item_ID = s.Item_ID
    inner join DIM_Date d
    on s.Sale_Date = d.Date_Key

    Group by
    d.First_Of_Month ,
    i.Item_Name,
    i.Item_Section

    GO

    The above statement compiles both on SSMS and Visual Studio but does not show the numbers.If i do the select statement per table it works so i think the problem could be with my statement.Please help

    Friday, February 10, 2017 8:43 PM

Answers

  • That looks good. Perhaps the problem is with your data.

    Does this return data?

    SELECT Count(*)
    FROM dbo.Items i
    inner join dbo.Sales s on i.Item_ID = s.Item_ID

    Does this return data?

    select count(*) from dbo.Sales s

    inner join DIM_Date d
    on s.Sale_Date = d.Date_Key

    Friday, February 10, 2017 8:58 PM

All replies

  • That looks good. Perhaps the problem is with your data.

    Does this return data?

    SELECT Count(*)
    FROM dbo.Items i
    inner join dbo.Sales s on i.Item_ID = s.Item_ID

    Does this return data?

    select count(*) from dbo.Sales s

    inner join DIM_Date d
    on s.Sale_Date = d.Date_Key

    Friday, February 10, 2017 8:58 PM
  • What are types of Sale_Date and Date_Key columns? Could it be that Sale_Date is datetime (or datetime2) with time portion and so your join with DIM_Date doesn't return data?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, February 10, 2017 9:18 PM
  • Good day SIBONGIM,

    Those type of questions are very simple to answer and solve, if you provides us the tools to reproduce the issue, and what we need is very simple to provide.

    Please post:

    1) Queries to CREATE your tables (including indexes if we want to discuss performance as well)
    2) Queries  to INSERT sample data.
    3) The desired result given the sample, as text or image of excel for example.
    4) A short description of the business rules, and how you got 1-2 of the results
    5) Which version of SQL Server you are using (this will help to fit the query to your version).

    Regards,


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    • Edited by pituachMVP Wednesday, March 22, 2017 8:42 AM
    Wednesday, March 22, 2017 8:41 AM