Results 1 to 7 of 7
  1. #1
    Join Date
    May 2012
    Posts
    14

    Unanswered: Quering DateTime Field and Summarize just using date

    I have 2 tables that I would like to summarize a couple of columns for a full day of production(12:00:00 AM to 11:59:59 PM) based on passed variables. Here are my Tables:

    Order_Details_tbl
    PlantID – IngredientID – AmountBatched – DateTime
    1 – 8 – 1000 – 4/30/2012 1:23:12 PM
    1 – 8 – 1000 – 4/30/2012 4:23:12 PM
    1 – 8 – 1000 – 5/1/2012 1:23:12 PM
    1 – 8 – 1000 – 5/1/2012 10:23:12 PM
    1 – 8 – 4500 – 5/3/2012 1:23:12 PM
    1 – 8 – 11000 – 5/7/2012 1:23:12 PM
    1 – 8 – 1000 – 5/7/2012 10:23:12 AM
    1 – 8 – 1000 – 5/7/2012 1:23:12 PM
    1 – 8 – 1000 – 5/7/2012 1:23:12 PM
    1 – 8 – 1000 – 5/8/2012 9:23:12 AM
    1 – 8 – 1000 – 5/8/2012 4:23:12 PM
    1 – 8 – 1000 – 5/8/2012 2:23:12 PM

    Order_Details_Details_tbl
    PlantID – IngredientID – AmountBatched – DateTime
    1 – 8 – 100 – 4/30/2012 1:23:12 PM
    1 – 8 – 11000 – 5/4/2012 11:23:12 PM
    1 – 8 – 11000 – 5/7/2012 11:23:12 PM
    1 – 8 – 1000 – 5/8/2012 11:23:12 AM
    1 – 8 – 1000 – 5/8/2012 1:23:12 AM
    1 – 8 – 1000 – 5/8/2012 11:23:12 PM
    1 – 8 – 1000 – 5/8/2012 5:23:12 PM
    1 – 8 – 1000 – 5/8/2012 2:23:12 PM

    I will pass in the @PlantID int, @IngredientID int, and @Days int. I want to sum the AmountBatched from both tables and display the total for each given day. The @Days will indicate the number of days to query off of previous to the current date. I would also like to eliminate weekends from the results. For example when stored procedure is run passing the following values @PlantID = 1, @IngredientID = 8, and @Days = 14. If date procedure is run is 5/9/2012, would like to summarize for 4/25/2012 to 5/8/2012 excluding weekends if possible.

    Results
    Date – AmountBatched
    4/25/2012 – 0
    4/26/2012 – 0
    4/27/2012 -- 0
    4/30/2012 -- 2100
    5/1/2012 -- 2000
    5/2/2012 -- 0
    5/3/2012 -- 4500
    5/4/2012 -- 11000
    5/7/2012 -- 25000
    5/8/2012 – 8000

    Notice 4/28, 4/29, 5/5, and 5/6 are eliminated from the results, which are weekends. Is this possible in a sql stored procedure? I am writing an app in vb .net and am hoping to get the results I need in a single call to sql server and not have to make several calls back. I have not worked with advanced datetime methods in sql server before. Any help would be appreciated.

    Thanks in advance,

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    with CTE as
    (
        select CAST(DateTimeCol as Date) as DateCol, AmountBatched 
        from Order_Details_tbl
        where (PlantID = @PlantID) and
              (IngredientID = @IngredientID) and
              (DATEDIFF(day, DateTimeCol, GETDATE()) between 1 and @Days) and
              (DATEPART(weekday, DateTimeCol) between 2 and 6)
            
        union all
           
        select CAST(DateTimeCol as Date) as DateCol, AmountBatched 
        from Order_Details_Details_tbl
        where (PlantID = @PlantID) and
              (IngredientID = @IngredientID) and
              (DATEDIFF(day, DateTimeCol, GETDATE()) between 1 and @Days) and
              (DATEPART(weekday, DateTimeCol) between 2 and 6)
    )
        
    select DateCol, SUM(AmountBatched) as AmonutBatched
    from CTE
    group by DateCol
    Hope this helps.

  3. #3
    Join Date
    May 2012
    Posts
    14
    This is very close. How can I get it to show the date and an AmountBatched of 0 if there is no Data for a given day. Right now, the results do not show any weekdays, that do not have any data. So in essence, everytime @Days = 14, there should always be 14 results. Does this may sense?

    Thanks again,

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    with 
        CTE_Details as
        (
            select CAST(DateTimeCol as Date) as DateCol, AmountBatched 
            from Order_Details_tbl
            where (PlantID = @PlantID) and
                  (IngredientID = @IngredientID) and
                  (DATEDIFF(day, DateTimeCol, GETDATE()) between 1 and @Days) and
                  (DATEPART(weekday, DateTimeCol) between 2 and 6)
            
            union all
           
            select CAST(DateTimeCol as Date) as DateCol, AmountBatched 
            from Order_Details_Details_tbl
            where (PlantID = @PlantID) and
                  (IngredientID = @IngredientID) and
                  (DATEDIFF(day, DateTimeCol, GETDATE()) between 1 and @Days) and
                  (DATEPART(weekday, DateTimeCol) between 2 and 6)
        ),
    
        CTE_Tots as
        (
            select DateCol, SUM(AmountBatched) as AmonutBatched
            from CTE_Details
            group by DateCol
        ),
    
        CTE_Dates as
        (
            select CAST(DATEADD(DAY, -1, GETDATE()) AS Date) as DateCol, 1 as DayNo
            
            union all
            
            select DATEADD(day, -1, DateCol) as DateCol, (DayNo + 1) as DayNo 
            from CTE_Dates
            where DayNo < 14
        )
        
    select d.DateCol, COALESCE(t.AmonutBatched, 0) as AmountBatched
    from CTE_Dates as d
    left join CTE_Tots as t on t.DateCol = d.DateCol
    order by d.DateCol
    Hope this helps.

  5. #5
    Join Date
    May 2012
    Posts
    14
    This is right on, but I mistold you in my last post....I apologize. I said if I pass @Days = 14 I want 14 results, but I forgot I want to filter out the weekends, so if I pass @Days = 14 I should get 10 results. I tried using the DatePart that you used in the CTE_Details in the CTE_Dates but my syntax is all hosed up

    Thanks,

  6. #6
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    select d.DateCol, COALESCE(t.AmonutBatched, 0) as AmountBatched
    from CTE_Dates as d
    left join CTE_Tots as t on t.DateCol = d.DateCol
    where DATEPART(weekday, d.DateCol) between 2 and 6
    order by d.DateCol
    Hope this helps.

  7. #7
    Join Date
    May 2012
    Posts
    14
    Perfect. Thanks so much for all your help!!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •