locked
SQL Query to Get ID counts in a weekly basis? RRS feed

  • Question

  • User1483017757 posted

    I have a SQL table containing data as below -

    IN_ID,  Open_Datetime, Resolve_Datetime

    Can I get a SQL query to view data like below -

    Total_IN_ID, Resolve_Week

    Total_IN_ID should show the IN_ID count corresponding to Resolve_Datetime column.

    The Resolve_Week should show up the date range with Monday as start of the week and Sunday as end, in that month or week number,

    I am trying to build a SSRS report and I want to show up the line chart with this requirement.

    I was going through the blog and found the thread  SQL Query to Get Last 10 Weeks of Data? which appears to have similar logic but, it is not helping me.

    Tuesday, August 21, 2018 11:58 AM

All replies

  • User77042963 posted
    Select Count(*) as Total_IN_ID
    , datepart(week,Resolve_Datetime) Resolve_Week
    Group by datepart(week,Resolve_Datetime ), year(Resolve_Datetime )

    Tuesday, August 21, 2018 2:19 PM
  • User1483017757 posted

    It helped!! Thank you very much, Limno.

    What if I want to show the Resolve_Week as date range -- like -- 2018-06-04 to 2018-06-10?

    Thursday, August 23, 2018 4:11 AM
  • User77042963 posted

    Post some sample data and we will continue from there. Thanks.

    Thursday, August 23, 2018 1:43 PM
  • User77042963 posted

    You need to post your table DDL, sample date and expected result on this forum.

    You will get help quicker from people on this forum.

    Thursday, August 23, 2018 8:34 PM
  • User1483017757 posted

    Pardon me, Limno. I forgot to paste it here. Please find my data sample.

    Total_IN_ID Resolve_Week
    219                      21
    162                      22
    667                      23
    465                      24

    Here, the above result is from the query you gave me.

    Now, I am actually looking for the values in the Resolve_Week in the dates Range like May 27th, 2018 - June 2nd, 2018, instead of the numbers (21, 22, 23 and so on).

    Friday, August 24, 2018 4:53 AM
  • User77042963 posted

    If you post your question in the form of create table insert script, I will try to help to write a query. Thanks.

    An example:

     (you need to modified a little)

    Select Count(*) as Total_IN_ID
    , DATEADD(week, DATEDIFF(week,0,Resolve_Datetime),-1) Beginning_Resolve_Week
    From yourtable
    Group by DATEADD(week, DATEDIFF(week,0,Resolve_Datetime),-1)

    Friday, August 24, 2018 2:32 PM