[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

SQL Query: compare current year to prior year

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 VB.net 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 * FROM 
	( SELECT ... need query returning dates and DaysRemaining from @StartDate until @EndDate...
	)  AS WorkingDates
WHERE DateStamp >= @StartDate AND DateStamp <= @TodayDate
ORDER BY DateStamp DESC

LEFT OUTER JOIN

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

0
TadSter
Asked:
TadSter
  • 3
1 Solution
 
strickddCommented:
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.
0
 
TadSterAuthor Commented:
What about using a cursor?
0
 
TadSterAuthor Commented:
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'

DECLARE DateList CURSOR FOR
	SELECT * FROM (VALUES ( DATEADD(dd, (DATEDIFF(dd, 0, @Deadline ) ), 0) )) 
	AS ValueList (date)
	
OPEN DATELIST

FETCH NEXT FROM DateList
--INSERT INTO CURSOR DateList ('2011-12-24')

CLOSE DateList
DEALLOCATE DateList

Open in new window

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now