locked
Simple SQL Date Question RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Guys

    OK, so pretty basic with this but hope this makes sence;

    I have a few columns (each row is by day). "Persons Name" "Hours in Day" "Cost for Day" & "Date"

    So this is like a simple employees report.

    Can I, write something which uses the date, and gives me a value Year To Date? (YTD)

    Sunday, April 7, 2013 2:42 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote 
    SELECT SUM(CostForDay) FROM myTable
WHERE YEAR([DATE]) = YEAR(GETDATE())

    • Proposed as answer by Alex.Friedman Sunday, April 7, 2013 2:50 PM
    • Unproposed as answer by Naomi N Sunday, April 7, 2013 2:53 PM
    • Proposed as answer by Kapil.Kumawat Monday, April 8, 2013 1:22 PM
    • Unproposed as answer by Kapil.Kumawat Monday, April 8, 2013 1:22 PM
    • Proposed as answer by Kapil.Kumawat Monday, April 8, 2013 1:24 PM
    • Marked as answer by Allen Li - MSFT Monday, April 15, 2013 7:58 AM
    Sunday, April 7, 2013 2:48 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    May be

    SELECT [Persons Name], SUM([Hours in Day]) as [Total Hours], SUM([Cost for Day]) as [Total Cost]

FROM EmployeeHours

WHERE [Date] >=dateadd(year, datediff(year, '19000101', CURRENT_TIMESTAMP), '19000101') -- beginning of the year

AND [Date] < dateadd(year, 1+datediff(year, '19000101', CURRENT_TIMESTAMP), '19000101') -- start of next year

GROUP BY [Persons Name]


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


    My blog

    Sunday, April 7, 2013 2:53 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote 
    SELECT SUM(CostForDay) FROM myTable
WHERE YEAR([DATE]) = YEAR(GETDATE())

    • Proposed as answer by Alex.Friedman Sunday, April 7, 2013 2:50 PM
    • Unproposed as answer by Naomi N Sunday, April 7, 2013 2:53 PM
    • Proposed as answer by Kapil.Kumawat Monday, April 8, 2013 1:22 PM
    • Unproposed as answer by Kapil.Kumawat Monday, April 8, 2013 1:22 PM
    • Proposed as answer by Kapil.Kumawat Monday, April 8, 2013 1:24 PM
    • Marked as answer by Allen Li - MSFT Monday, April 15, 2013 7:58 AM
    Sunday, April 7, 2013 2:48 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I'm not sure exactly what you want.  For this type of question it is helpful if you give us some sample data, the result you would want from that sample data and the release of SQL Server you are using.

    Tom

    Sunday, April 7, 2013 2:50 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    May be

    SELECT [Persons Name], SUM([Hours in Day]) as [Total Hours], SUM([Cost for Day]) as [Total Cost]

FROM EmployeeHours

WHERE [Date] >=dateadd(year, datediff(year, '19000101', CURRENT_TIMESTAMP), '19000101') -- beginning of the year

AND [Date] < dateadd(year, 1+datediff(year, '19000101', CURRENT_TIMESTAMP), '19000101') -- start of next year

GROUP BY [Persons Name]


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


    My blog

    Sunday, April 7, 2013 2:53 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    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. If you have to work from specs this vague, update your resume.  

    --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

    Sunday, April 7, 2013 4:50 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    You unproposed an answer that was effectively the same as yours - and both are incorrect.  YTD generally means Jan 1 to the current date.  Without further knowledge of the data to be queried, it would be inappropriate to suggest a query that includes everything with a date that falls in the current year.  There are many valid reasons to post a transaction that falls in the future - even if only a few days in the future (we do it all the time).
    Monday, April 8, 2013 1:06 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    The reason I unproposed that answer was because it would have been non-sargable and bad performing. But I decided that perhaps the idea was OK and that's what the OP wanted. Without more input from OP we can only guess.

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


    My blog

    Monday, April 8, 2013 1:09 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    But I guess your other point is valid - we wanted the upper limit to be current date, not the beginning of the next year. It is easy to fix the query I posted by stripping the date portion of the current date.

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


    My blog

    Monday, April 8, 2013 1:11 PM