none
get the query with calendar day

    Question

  • a table has a column which contain amount with another date column so I want to get a result of query with calendar days from system even not focus if there is not any record with calendar day in the table. That's why I want to specify of lack days which does not include sales' amount. How to do that?
     By the way, I think this query if I figure out thanks to someone then It needs to specify a between values from starting to end. It means there must be a initialize.
    • Edited by Dynamo70 Wednesday, September 11, 2013 3:41 AM
    Wednesday, September 11, 2013 3:38 AM

Answers

All replies

  • >>query with calendar days from system even not focus if there is not any record with calendar day in the table

    Cant really understand the question? could you please explain?

    A sample data and expected result would be easy to understand ( with an explanation of logic)


    Satheesh

    Wednesday, September 11, 2013 3:44 AM
  • The query runs independently from table's date which contains sales invoice date and total amount. If calendar day matches with table's sales date then the query sum the total amount as calendar day if it does not match then the row must be null because there is not any matching in table.
    Wednesday, September 11, 2013 3:54 AM
  • Hi,

    your question is not clear , if you need calendar tables , try below links :

    http://blog.jontav.com/post/9380766884/calendar-tables-are-incredibly-useful-in-sql

    http://blog.sqlauthority.com/2008/02/20/sql-server-udf-to-return-a-calendar-for-any-date-for-any-year/


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Wednesday, September 11, 2013 3:55 AM
    Moderator
  • Take a look at my recent article http://social.technet.microsoft.com/wiki/contents/articles/19443.t-sql-applying-apply-operator.aspx

    It provides a link to an article about Calendar table and also shows a script creating a quick 'Calendar' table.

    Once you created the Calendar table, you LEFT JOIN from it into your Sales table.


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


    My blog


    My TechNet articles

    Wednesday, September 11, 2013 4:04 AM
    Moderator
  • Here is the table rows and possible result. I'm looking for this, hope this gives an clear idea.
    Wednesday, September 11, 2013 4:35 AM
  • Here is some good calendar tales/functions explained. You could use them to join your table

    http://stackoverflow.com/questions/5635594/how-to-create-a-calender-table-for-100-years-in-sql

    and decide how many days from the calendar you want to show. here is a sample code

    IF EXISTS (SELECT * FROM information_schema.tables WHERE Table_Name = 'Calendar' AND Table_Type = 'BASE TABLE')
    BEGIN
    DROP TABLE [Calendar]
    END
    
    CREATE TABLE [Calendar]
    (
        [CalendarDate] DATETIME
    )
    
    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME
    SET @StartDate = '20130101'
    SET @EndDate = DATEADD(d, 365, @StartDate)
    
    WHILE @StartDate <= @EndDate
    BEGIN
     INSERT INTO [Calendar]
     (
           CalendarDate
     )
     SELECT @StartDate
    SET @StartDate = DATEADD(dd, 1, @StartDate)
    END
    
    
    GO
    DECLARE @CUST table(ID INT IDENTITY (1,1),
    DATE DATETIME,AMOUNT int)
    
    set dateformat ymd;
    insert into @cust
    values('20130901',1500);
    insert into @cust
    values('20130902',2500);
    insert into @cust
    values('20130905',5500);
    insert into @cust
    values('20130908',8500);
    
    select * from Calendar c left join @cust c2 on c.[CalendarDate]=c2.date
    where month(CalendarDate)=month(getdate())


    Satheesh

    Wednesday, September 11, 2013 5:11 AM
  • Try

    Select Cal.The_Date, Sum.[Sum Amount] From Calendar Cal LEFT JOIN (select [Date],

    SUM(Amount) as [Sum Amount] from CustomerSales GROUP BY [Date]) Sum ON Cal.The_Date = Sum.[Date] WHERE Cal.The_Date between @StartDate and @EndDate


    where Calendar table you create based on the article I sent you a reference in my prior message.


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


    My blog


    My TechNet articles

    Wednesday, September 11, 2013 11:03 AM
    Moderator
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    >> a table has a column which contains an amount with another date column so I want to get a result of query with calendar days from system even not focus if there is not any record [sic] with calendar day in the table. That's why I want to specify of lack days which does not include sales amount. How to do that? <<

    Your English is bad. This might be:

    SELECT .. COALESCE (S.sale_amt, 0.00) AS daily_sale_amt
      FROM Calendar AS C 
           LEFT OUTER JOIN 
           Sales AS S
           ON S.sale_date = C.cal_date; 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, September 11, 2013 6:44 PM