SQL Query: compare current year to prior year

Posted on 2011-10-21
Last Modified: 2012-08-14
Can you help me with this?

I have database with list of jobs completed by date. (two columns: DateStamp, JobDesc).

I need a query that returns three columns. I have no problem with the third column, but I need help with the first two columns. I don't even know if it is possible to do this in SQL. The existing database table may or may not have jobs on every working date. So I need something to essentially create a table out of thin air. If this is not possible in SQL then I could do it in instead.

1.  The first column needs dates starting with today's date at the top and working backwards to the beginning of the year. It should include all dates except sundays even if there is no data in the database. The inner query should start from the EndDate so as to calculate the DaysRemaining. then the outer query strips the dates after today's date.

2. The second column needs the number of working days (Mon - Sat) until the specified deadline date.

3. The third column will have the number of jobs (any desc) on that date (using join)

DateStamp      DaysRemaining      JobsCompleted         
Tue 12/25/2012      0                          138         
Mon 12/24/2012      1                          128       
Sat 12/22/2012      2                          110         
Fri 12/21/2012      3                          94         
Thu 12/20/2012      4                          79         
Wed 12/19/2012      5                          65         
Tue 12/18/2012      6                          51         
Mon 12/17/2012      7                          41         
Sat 12/15/2012      8                          30         
Fri 12/14/2012      9                          12       

--Proposed solution:

DECLARE @StartDate date = '2011-01-01'
DECLARE @TodayDate date = getdate()
DECLARE @EndDate date = '2011-12-25'

	( SELECT ... need query returning dates and DaysRemaining from @StartDate until @EndDate...
	)  AS WorkingDates
WHERE DateStamp >= @StartDate AND DateStamp <= @TodayDate


SELECT JobDates.DateStamp, COUNT(JobDates.DateStamp) AS JobCount FROM
	(SELECT DATEADD(dd, DATEDIFF(dd, 0, DateStamp), 0) DateStamp from Tracking
	WHERE DateStamp >= @StartDate AND DateStamp <= @TodayDate
	) as JobDates
GROUP BY JobDates.DateStamp
ORDER BY JobDates.DateStamp	DESC

ON WorkingDates.DateStamp = JobDates.DateStamp

ORDER BY WorkingDates.DateStamp DESC

Open in new window

Question by:TadSter
    LVL 28

    Accepted Solution

    I don't think you can do this in a single query in the "traditional" sense. You will need to use a cursor that loops through every day of the year - DATEADD(...) function - and does the select for that date and inserts the count into the table.
    LVL 2

    Author Comment

    What about using a cursor?
    LVL 2

    Author Comment

    I'm not sure how to do the cursor thing. How do I insert dates into a cursor in a loop?
    DECLARE @Deadline date = '2011-12-25'
    	SELECT * FROM (VALUES ( DATEADD(dd, (DATEDIFF(dd, 0, @Deadline ) ), 0) )) 
    	AS ValueList (date)
    --INSERT INTO CURSOR DateList ('2011-12-24')
    CLOSE DateList

    Open in new window

    LVL 2

    Author Closing Comment

    Thanks for your help. I wasn't able to figure out the cursor, so I did a data table in That works.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now