Include all dates in output - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Include all dates in output

Author

Topic

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-09-22 : 16:34:03

I have the following stored procedures:

If I give the parameters of

supervisor = 100 or 101
startDate = '9/1/2009'
endDate = '9/30/2009'

CREATE PROCEDURE sp_compliance_month (@supervisor int, @startDate dateTime, @endDate dateTime)

AS

SELECT td.week_ending_date, td.supervisor_no, s.SUP_NAME, SUM(CASE WHEN approved_state <> 1 THEN 1 ELSE 0 END) AS not_valid_count
FROM TIME_DATA td LEFT OUTER JOIN
DCSC_SUPERVISOR s ON s.SUP_NO = td.supervisor_no
WHERE (td.week_ending_date BETWEEN @startDate AND @endDate) AND (td.supervisor_no = @supervisor)
GROUP BY td.week_ending_date, td.supervisor_no, s.SUP_NAME
ORDER BY s.SUP_NAME, td.week_ending_date
GO

I retrieve the proper results for supervisor 100, however, I am missing the 9/4 record for supervisor 101 since they did not yet exist:

9/4/2009 100 John 0
9/11/2009 100 John 1
9/18/2009 100 John 0
9/11/2009 101 Mary L 0
9/18/2009 101 Mary L 7

How can I modify the procedure to include all dates so my output is as follows:

9/4/2009 100 John 0
9/11/2009 100 John 1
9/18/2009 100 John 0
9/4/2009 101 Mary L 0 (Additional record)
9/11/2009 101 Mary L 0
9/18/2009 101 Mary L 7

Thanks for any suggestions.

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-22 : 16:38:59

[code]

SELECT XXX.week_ending_date
, td.supervisor_no
, s.SUP_NAME
, SUM(CASE WHEN approved_state <> 1 THEN 1
ELSE 0
END) AS not_valid_count
FROM (SELECT DISTINCT week_ending_date FROM TIME_DATA) AS XXX
LEFT JOIN TIME_DATA td
ON XXX.week_ending_date = td.week_ending_date
LEFT JOIN DCSC_SUPERVISOR s
ON s.SUP_NO = td.supervisor_no
WHERE (td.week_ending_date BETWEEN @startDate AND @endDate) AND (td.supervisor_no = @supervisor)
GROUP BY td.week_ending_date, td.supervisor_no, s.SUP_NAME
ORDER BY s.SUP_NAME, td.week_ending_date
[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-09-22 : 16:50:09

Thanks for your reply. I tried the script and modified the group and order by clause, but still get the same results. Here's the updated script:

SELECT XXX.week_ending_date, td.supervisor_no, s.SUP_NAME, SUM(CASE WHEN approved_state <> 1 THEN 1 ELSE 0 END) AS not_valid_count
FROM (SELECT DISTINCT week_ending_date
FROM TIME_DATA) XXX LEFT OUTER JOIN
TIME_DATA td ON XXX.week_ending_date = td.week_ending_date LEFT OUTER JOIN
DCSC_SUPERVISOR s ON s.SUP_NO = td.supervisor_no
WHERE (td.week_ending_date BETWEEN @startDate AND @endDate)
GROUP BY XXX.week_ending_date, td.supervisor_no, s.SUP_NAME
ORDER BY s.SUP_NAME, XXX.week_ending_date

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-22 : 17:00:41

maybe if you post some sample data that's in the tables

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-09-23 : 08:37:52

Here's some sample data. You'll notice that supervisor 101 does not have a 9/4 week. In this case, I wan tto dispaly 0 or "N/A" since the employee did not supervise anyone during the week. Thanks for any dieas.

week_ending_date employee_no supervisor_no approved_state
9/18/2009 500 101 1
9/18/2009 501 101 1
9/18/2009 502 101 3
9/18/2009 503 101 3
9/18/2009 504 101 1
9/18/2009 505 101 0
9/18/2009 506 101 0
9/11/2009 500 101 1
9/11/2009 501 101 1
9/11/2009 502 101 1
9/11/2009 503 101 2
9/11/2009 504 101 1
9/11/2009 505 101 1
9/11/2009 506 101 1

9/18/2009 600 100 0
9/18/2009 601 100 1
9/18/2009 602 100 1
9/18/2009 603 100 1
9/18/2009 604 100 1
9/18/2009 605 100 0
9/18/2009 606 100 0
9/11/2009 600 100 1
9/11/2009 601 100 1
9/11/2009 602 100 1
9/11/2009 603 100 1
9/11/2009 604 100 1
9/11/2009 605 100 1
9/11/2009 606 100 1
9/4/2009 600 100 0
9/4/2009 601 100 1
9/4/2009 602 100 3
9/4/2009 603 100 1
9/4/2009 604 100 2
9/4/2009 605 100 1
9/4/2009 606 100 1

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-23 : 10:17:11

post it like



CREATE TABLE #myTable99(
	  week_ending_date	datetime 
	, employee_no		int
	, supervisor_no		int
	, approved_state	int
)
GO

INSERT INTO #myTable99 (
	week_ending_date, employee_no, supervisor_no, approved_state)

SELECT '9/18/2009',  500 , 101 , 1 UNION ALL
SELECT '9/18/2009',  501 , 101 , 1 UNION ALL
SELECT '9/18/2009',  502 , 101 , 3 UNION ALL
SELECT '9/18/2009',  503 , 101 , 3 UNION ALL
SELECT '9/18/2009',  504 , 101 , 1 UNION ALL
SELECT '9/18/2009',  505 , 101 , 0 UNION ALL
SELECT '9/18/2009',  506 , 101 , 0 UNION ALL
SELECT '9/11/2009',  500 , 101 , 1 UNION ALL
SELECT '9/11/2009',  501 , 101 , 1 UNION ALL
SELECT '9/11/2009',  502 , 101 , 1 UNION ALL
SELECT '9/11/2009',  503 , 101 , 2 UNION ALL
SELECT '9/11/2009',  504 , 101 , 1 UNION ALL
SELECT '9/11/2009',  505 , 101 , 1 UNION ALL
SELECT '9/11/2009',  506 , 101 , 1 UNION ALL

SELECT '9/18/2009',  600 , 100 , 0 UNION ALL
SELECT '9/18/2009',  601 , 100 , 1 UNION ALL
SELECT '9/18/2009',  602 , 100 , 1 UNION ALL
SELECT '9/18/2009',  603 , 100 , 1 UNION ALL
SELECT '9/18/2009',  604 , 100 , 1 UNION ALL
SELECT '9/18/2009',  605 , 100 , 0 UNION ALL
SELECT '9/18/2009',  606 , 100 , 0 UNION ALL
SELECT '9/11/2009',  600 , 100 , 1 UNION ALL
SELECT '9/11/2009',  601 , 100 , 1 UNION ALL
SELECT '9/11/2009',  602 , 100 , 1 UNION ALL
SELECT '9/11/2009',  603 , 100 , 1 UNION ALL
SELECT '9/11/2009',  604 , 100 , 1 UNION ALL
SELECT '9/11/2009',  605 , 100 , 1 UNION ALL
SELECT '9/11/2009',  606 , 100 , 1 UNION ALL
SELECT '9/4/2009',   600 , 100 , 0 UNION ALL
SELECT '9/4/2009',   601 , 100 , 1 UNION ALL
SELECT '9/4/2009',   602 , 100 , 3 UNION ALL
SELECT '9/4/2009',   603 , 100 , 1 UNION ALL
SELECT '9/4/2009',   604 , 100 , 2 UNION ALL
SELECT '9/4/2009',   605 , 100 , 1 UNION ALL
SELECT '9/4/2009',   606 , 100 , 1

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-09-23 : 11:55:15

OK. Here's the structure with some sample data to demonsrate the situation above:

CREATE TABLE [dbo].[TIME_DATA](
[timecardID] [int] IDENTITY(1,1) NOT NULL,
[week_ending_date] [datetime] NULL,
[timecard_submitted] [datetime] NULL,
[employee_no] [int] NULL,
[supervisor_no] [int] NULL,
[supervisor_submission_date] [datetime] NULL,
[supervisor_submission_weekday] [int] NULL,
[approved_state] [int] NULL,
[supervisor_comments] [nvarchar](100) NULL
)

INSERT INTO [Time_Data] VALUES(14882,'Sep 4 2009 12:00:00:000AM',NULL,200540,100,'Sep 7 2009 12:00:00:000AM',3,1,'')
INSERT INTO [Time_Data] VALUES(15095,'Sep 4 2009 12:00:00:000AM',NULL,8114,100,'Sep 10 2009 12:00:00:000AM',6,1,'')
INSERT INTO [Time_Data] VALUES(15324,'Sep 4 2009 12:00:00:000AM',NULL,200552,100,'Sep 10 2009 12:00:00:000AM',6,1,'')
INSERT INTO [Time_Data] VALUES(15356,'Sep 4 2009 12:00:00:000AM',NULL,3101,100,'Sep 10 2009 12:00:00:000AM',6,1,'')
INSERT INTO [Time_Data] VALUES(15421,'Sep 4 2009 12:00:00:000AM',NULL,1678,100,'Sep 10 2009 12:00:00:000AM',6,1,'')
INSERT INTO [Time_Data] VALUES(15493,'Sep 11 2009 12:00:00:000AM',NULL,1574,101,'Sep 18 2009 12:00:00:000AM',7,1,'')
INSERT INTO [Time_Data] VALUES(15506,'Sep 11 2009 12:00:00:000AM',NULL,200540,100,'Sep 13 2009 12:00:00:000AM',2,1,'')
INSERT INTO [Time_Data] VALUES(15619,'Sep 11 2009 12:00:00:000AM',NULL,1811,101,'Sep 18 2009 12:00:00:000AM',7,1,'')
INSERT INTO [Time_Data] VALUES(15655,'Sep 11 2009 12:00:00:000AM',NULL,15241,101,'Sep 18 2009 12:00:00:000AM',7,1,'')
INSERT INTO [Time_Data] VALUES(15710,'Sep 11 2009 12:00:00:000AM',NULL,201493,101,'Sep 18 2009 12:00:00:000AM',7,1,'')
INSERT INTO [Time_Data] VALUES(15719,'Sep 11 2009 12:00:00:000AM',NULL,8114,100,'Sep 13 2009 12:00:00:000AM',2,1,'')
INSERT INTO [Time_Data] VALUES(15814,'Sep 11 2009 12:00:00:000AM',NULL,2081,101,'Sep 18 2009 12:00:00:000AM',7,1,'')
INSERT INTO [Time_Data] VALUES(15947,'Sep 11 2009 12:00:00:000AM',NULL,200552,100,'Sep 13 2009 12:00:00:000AM',2,1,'')
INSERT INTO [Time_Data] VALUES(15967,'Sep 11 2009 12:00:00:000AM',NULL,16481,101,'Sep 18 2009 12:00:00:000AM',7,1,'')
INSERT INTO [Time_Data] VALUES(15978,'Sep 11 2009 12:00:00:000AM',NULL,3101,100,'Sep 13 2009 12:00:00:000AM',2,1,'')
INSERT INTO [Time_Data] VALUES(15989,'Sep 11 2009 12:00:00:000AM',NULL,202101,101,'Sep 18 2009 12:00:00:000AM',7,1,'')
INSERT INTO [Time_Data] VALUES(16043,'Sep 11 2009 12:00:00:000AM',NULL,1678,100,'Sep 13 2009 12:00:00:000AM',2,1,'')
INSERT INTO [Time_Data] VALUES(16114,'Sep 18 2009 12:00:00:000AM',NULL,1574,101,'Sep 23 2009 12:00:00:000AM',5,1,'')
INSERT INTO [Time_Data] VALUES(16127,'Sep 18 2009 12:00:00:000AM',NULL,200540,100,'Sep 21 2009 12:00:00:000AM',3,1,'')
INSERT INTO [Time_Data] VALUES(16241,'Sep 18 2009 12:00:00:000AM',NULL,1811,101,'Sep 23 2009 12:00:00:000AM',5,1,'')
INSERT INTO [Time_Data] VALUES(16277,'Sep 18 2009 12:00:00:000AM',NULL,15241,101,'Sep 23 2009 12:00:00:000AM',5,1,'')
INSERT INTO [Time_Data] VALUES(16332,'Sep 18 2009 12:00:00:000AM',NULL,201493,101,'Sep 23 2009 12:00:00:000AM',5,1,'')
INSERT INTO [Time_Data] VALUES(16341,'Sep 18 2009 12:00:00:000AM',NULL,8114,100,'Sep 21 2009 12:00:00:000AM',3,1,'')
INSERT INTO [Time_Data] VALUES(16436,'Sep 18 2009 12:00:00:000AM',NULL,2081,101,'Sep 23 2009 12:00:00:000AM',5,1,'')
INSERT INTO [Time_Data] VALUES(16570,'Sep 18 2009 12:00:00:000AM',NULL,200552,100,'Sep 21 2009 12:00:00:000AM',3,1,'')
INSERT INTO [Time_Data] VALUES(16590,'Sep 18 2009 12:00:00:000AM',NULL,16481,101,'Sep 23 2009 12:00:00:000AM',5,1,'')
INSERT INTO [Time_Data] VALUES(16601,'Sep 18 2009 12:00:00:000AM',NULL,3101,100,'Sep 21 2009 12:00:00:000AM',3,1,'')
INSERT INTO [Time_Data] VALUES(16612,'Sep 18 2009 12:00:00:000AM',NULL,202101,101,'Sep 23 2009 12:00:00:000AM',5,1,'')
INSERT INTO [Time_Data] VALUES(16666,'Sep 18 2009 12:00:00:000AM',NULL,1678,100,'Sep 21 2009 12:00:00:000AM',3,1,'')

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources