Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Return Single Day of Week/Date for an annual query

Posted on 2013-01-03
9
Medium Priority
?
571 Views
Last Modified: 2013-01-13
I am creating a query to return the total of checks by week Saturday-Friday.
Checks may have been written on several dates during the week.
I only want to return the date for Thursday each week.
This did not work but should illustrate what I’m trying to do.


set DateFirst 5 -- Friday

select
    datepart( ww, p.[CHECKDATE]) [Week]
  , sum(p.[INSURANCE]) [Insurance]
  , (select convert( varchar(10), p.[CHECKDATE], 1) where datepart( ww, p.[CHECKDATE]) = 4 ) [CheckDate]
from [PAYROLL] p
where p.[CHECKDATE] >= '01/01/2012'  
and p.[CHECKDATE] < '01/01/2013'
and p.[ INSURANCE] <> 0.00  
group by datepart( ww, p.[CHECKDATE]), p.[CHECKDATE]

set DateFirst 7 -- Always be sure to set it back
0
Comment
Question by:Greg Rowland
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 23

Expert Comment

by:Steve Wales
ID: 38741430
Based on this stackoverflow article:

http://stackoverflow.com/questions/3839923/get-date-of-all-saturdays-in-a-given-year-sql-server

The following query would return you all the Thursdays in 2012:

declare @d datetime
declare @d = '20120101'

select dateadd(dd,number,@d)
from master..spt_values
where type = 'P'
and year (dateadd(dd,number,@d) = year(@d)
and datepart(dw,dateadd(dd,number,@d)) = 5

Open in new window


So maybe you could have your base query for your checks and add a final part to it of something like

and CHECKDATE in (the select above)

You'd need to do a little date manipulation to truncate the dates to midnight of the dates returned only or convert to char with just the dates (which is horrible on indexing), but hopefully that gives you a good starting point.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 38741575
Not sure you need to add in a dates table in this instance as I believe you just want the dates on Thursday within your data. If I am correct, try the following.

set DateFirst 5 -- Friday

select
    DATEPART(ww, p.[CHECKDATE]) [Week]
  , SUM(p.[INSURANCE]) [Insurance]
  , p.[CHECKDATE] [CheckDate]
from [PAYROLL] p
where p.[CHECKDATE] >= '01/01/2012'  
and p.[CHECKDATE] < '01/01/2013'
and p.[ INSURANCE] <> 0.00
and DATEPART(ww, p.[CHECKDATE]) = 4
group by p.[CHECKDATE]

set DateFirst 7 -- Always be sure to set it back

Open in new window


The performance will suffer because of the function on the p.[CHECKDATE] column, but the above should do what you desire. You could try with the table of dates LEFT OUTER JOIN [PAYROLL] ON p.[CHECKDATE] = [Thursday Date] to see if it performs better as it probably will even though you have to introduce more code.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 38741633
To circumvent the timestamp issue as mentioned by the expert above, I would typically do something like:

select dateadd(dd, number, '2012') as StartDT
     , dateadd(dd, number+1, '2012') as EndDT
from master..spt_values
where type = 'P'
and dateadd(dd, number, '2012') < '2013'
and datename(dw, dateadd(dd, number, '2012')) = 'Thursday'

Open in new window


Then in the JOIN, I would use:
ON p.[CHECKDATE] >= d.StartDT and p.[CHECKDATE] < d.EndDT

You could then group on d.StartDT to ensure you got every record for that day regardless of timestamp.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38741650
It looks to me like your weeks really start on Saturday not Friday, so I guess don't fully understand in which week to add checks for each day.

If a check is written on Friday, does it count in the NEXT Thu's total or the PREVIOUS Thu's?

For example, should a check written on Fri, Dec 21 be included in Dec 20's total or Dec 27's?

If a check is written on Saturday, does it count in the NEXT Thu's total or the PREVIOUS Thu's?

For example, should a check written on Sat, Dec 22 be included in Dec 20's total or Dec 27's?
0
 
LVL 4

Author Comment

by:Greg Rowland
ID: 38742524
Hi Scott,

Our payroll period ends / journals on Thursday at midnight.
These other suggestions would drop the insurance amounts on checks not printed on Thursday.
I’m looking to accumulate all checks insurance for the journal week, only returning the journal date.
Typically things like this I would create a temp table with the weeks then, accumulate the totals on a second pass.
Just looking for a silver bullet. Nice to hear from you, thanx:) Greg
PS this is Obama care making work for us, at least it pays well:)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 38742740
Ah, that makes more sense. Then you can generate your dates like this:

select dateadd(dd, number-7, '2012') as StartDT
     , dateadd(dd, number, '2012') as EndDT
from master..spt_values
where type = 'P'
and dateadd(dd, number, '2012') < '2013'
and datename(dw, dateadd(dd, number, '2012')) = 'Friday'

Open in new window


This gives you the date for Friday at midnight as well as the previous week's Friday at midnight. You JOIN to your data as:
ON p.[CHECKDATE] >= d.StartDT and p.[CHECKDATE] < d.EndDT

This will give you all the insurance checks written after last Friday at midnight up to (but not including) this Friday at midnight.
0
 
LVL 4

Accepted Solution

by:
Greg Rowland earned 0 total points
ID: 38753609
No silver bullet, just good old  fashioned code.

use myDataBase
go

/****** Object:  StoredProcedure [dbo].[sp_pr_insurance_journal_amounts]    Script Date: 01/07/2013 23:34:43 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_pr_insurance_journal_amounts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_pr_insurance_journal_amounts]
go

/*
  sp_pr_insurance_journal_amounts.sql

  11:22 PM 1/7/2013gr

*/


create proc [dbo].[sp_pr_insurance_journal_amounts] @dStartDate  as DateTime, @dEndDate as DateTime
as
begin

  declare @InsuranceWithHolding table
    (
        [Rec#] [int] IDENTITY (1, 1)
      , [Journal Date] datetime
      , [Insurance Total] decimal(10,2)

    )


  while ( @dStartDate <= @dEndDate) begin  

      if (datename(dw, @dStartDate ) = 'Thursday') begin

        insert into @InsuranceWithHolding (
            [Journal Date]
          , [Insurance Total])
   
          select
            convert (varchar( 12), @dStartDate, 1) -- [Journal Date]
            , sum([SECTION135CAFETERIAAMOUNT])     -- [Insurance]
          from [PAYROLL]
          where [CHECKDATE] >= @dStartDate - 6
          and [CHECKDATE] <= @dStartDate

      end


      set @dStartDate = @dStartDate + 1

  end

  select
      [Rec#]
    , convert( varchar(12), [Journal Date], 1) [Journal Date]  
    , [Insurance Total]
  from @InsuranceWithHolding

end
go

grant  execute  on [dbo].[sp_pr_insurance_journal_amounts] to [BlaBlaBla]
go
0
 
LVL 4

Author Comment

by:Greg Rowland
ID: 38753610
DECLARE @RC int
DECLARE @dStartDate datetime
DECLARE @dEndDate datetime

-- TODO: Set parameter values here.
set @dStartDate = '01/01/2012'
set @dEndDate = '12/31/2012'

EXECUTE @RC = [myDataBase].[dbo].[sp_pr_insurance_journal_amounts]
   @dStartDate
  ,@dEndDate
0
 
LVL 4

Author Closing Comment

by:Greg Rowland
ID: 38771592
It works
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question