SQL to include all dates in range

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

SQL to include all dates in range

Author

Topic

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-08-21 : 11:47:53

I have the following script where I would have script to summarize superviors who have not approved timesheets. The script works fine, but I want it to include all Fridays (week_ending_date) from the previous month.

SELECT week_ending_date, supervisor_no, COUNT(*) AS not_valid_count
FROM GOVERNANCE_DATA
WHERE (week_ending_date BETWEEN '7/1/2009' AND '7/31/2009') AND (approved_state = 0)
GROUP BY week_ending_date, supervisor_no
ORDER BY supervisor_no, week_ending_date

The current output is as follows:

week_ending_date Supervisor_no not_valid_count
7/17/2009 11147 3
7/24/2009 11147 1
7/31/2009 11147 3

The output I would like to have is:

week_ending_date Supervisor_no not_valid_count
7/03/2009 11147 0
7/10/2009 11147 0
7/17/2009 11147 3
7/24/2009 11147 1
7/31/2009 11147 3

Thank you

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-08-21 : 11:56:22

outer join with this


DECLARE  @StartDate DATETIME, 
         @EndDate   DATETIME

SELECT @StartDate = '07/01/2009',
       @EndDate = '07/31/2009';

WITH DATE(date1) 
     AS (SELECT @startdate 
         UNION ALL 
         SELECT Dateadd(DAY,1,date1) 
         FROM   DATE 
         WHERE  date1 < @EndDate) 
SELECT   DATE1 AS ALL_FRIDAYS
FROM     DATE
WHERE DATEPART(DW, DATE1) = 6
ORDER BY date1

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-08-24 : 11:54:15

Hello:

I can't get the syntax down when I try using the outer join can you show me some additional detail on how to integrate together.

Thank you for your help.

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-08-24 : 13:54:52

I tried joining as follows but get the sytax error near WITH and WHERE:

DECLARE @StartDate DATETIME,
@EndDate DATETIME

SELECT @StartDate = '07/01/2009',
@EndDate = '07/31/2009';

SELECT week_ending_date, supervisor_no, COUNT(*) AS not_valid_count
FROM DCSC_GOVERNANCE_DATA

RIGHT OUTER JOIN WITH DATE(date1)
AS (SELECT @startdate
UNION ALL
SELECT Dateadd(DAY,1,date1)
FROM DATE
WHERE date1 < @EndDate)

SELECT DATE1 AS ALL_FRIDAYS
FROM DATE
WHERE DATEPART(DW, DATE1) = 6
ORDER BY date1

WHERE (week_ending_date BETWEEN @StartDate AND @EndDate) AND (approved_state = 0)
GROUP BY week_ending_date, supervisor_no

ORDER BY supervisor_no, week_ending_date

Anyone know what I am doing incorrectly?

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-08-24 : 15:09:20

please read about CTE
http://msdn.microsoft.com/en-us/library/ms190766.aspx

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-24 : 15:55:47

I may have missed the point but it looks to me like you just need to tweak your original query. Does this do it?


SELECT week_ending_date
       ,supervisor_no
       ,sum(case when approved_state = 0 then 1 else 0 end) AS not_valid_count
FROM     GOVERNANCE_DATA
WHERE    week_ending_date BETWEEN '7/1/2009' AND '7/31/2009' --AND (approved_state = 0)
GROUP BY week_ending_date, supervisor_no
ORDER BY supervisor_no, week_ending_date

Be One with the Optimizer
TG

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-08-25 : 10:51:01

TG:

Yes. This is what I was missing. Very simple solution.

Thank you

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-08-26 : 10:52:43

I found my problem using the script rohitkumar suggested. Everything works perfectly in 2005. The problem I have is that the server where I need to run the script is on 2000.

TG's script works, but it intcludes all records. I want to exclude users where all values are zero as noted in my original example.

What should I look at to make it work under 2000?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-26 : 11:02:35

Not sure what you mean by "All values" ? I think you mean this:


SELECT week_ending_date
       ,supervisor_no
       ,sum(case when approved_state = 0 then 1 else 0 end) AS not_valid_count
FROM     GOVERNANCE_DATA
WHERE    week_ending_date BETWEEN '7/1/2009' AND '7/31/2009' --AND (approved_state = 0)
GROUP BY week_ending_date, supervisor_no 
having sum(case when approved_state = 0 then 1 else 0 end) > 0
ORDER BY supervisor_no, week_ending_date

Be One with the Optimizer
TG

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-08-26 : 11:15:11

TG:

What I mean is that if a the supervisor_no is zero for all weeks, I do not want that supervisor to display.

I have my script down to the following two scripts:
---
This script displays which the count values for the users, but does not include the zero value weeks.

SELECT week_ending_date, supervisor_no, COUNT(*)
FROM GOVERNANCE_DATA
GROUP BY week_ending_date, supervisor_no
ORDER BY supervisor_no, week_ending_date

The current output is as follows:

week_ending_date Supervisor_no not_valid_count
7/17/2009 11147 3
7/24/2009 11147 1
7/31/2009 11147 3
---
This script displays al the weeks in the month:
SELECT Dateadd(DAY,0,week_ending_date)
FROM DCSC_GOVERNANCE_DATA
WHERE week_ending_date >= '07/01/2009' AND week_ending_date <= '07/31/2009'
GROUP BY week_ending_date
ORDER BY week_ending_date

The output is as follows:

7/3/2009
7/10/2009
7/17/2009
7/24/2009
7/31/2009
---

I want to combine both scripts so the output is as follows:

7/03/2009 11147 0
7/10/2009 11147 0
7/17/2009 11147 3
7/24/2009 11147 1
7/31/2009 11147 3

The original script is not display the 7/3 and 7/10 dates. I'd like to have JOIN, but I'm not sure of the syntax.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-26 : 13:01:37

Did you try my last suggestion?
If that doesn't work for you then post DDL (script to create table), DML (script to insert sample rows)
and expected output based on your sample data.

Be One with the Optimizer
TG

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-08-26 : 14:00:38

Here is the auto generated script. I can't generate the INSERTS from SQL 2000, but only three fields are used:

CREATE TABLE [GOVERNANCE_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) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

I tried creating the insert statement, but was unable to do so. I have the following fields that matter for a supervisor:

week_ending_date, employee_no, supervisor_no, approved_state
7/3/2009 651 11147 1
7/3/2009 200955 11147 1
7/3/2009 200932 11147 1
7/10/2009 200932 11147 1
7/10/2009 651 11147 1
7/10/2009 200955 11147 1
7/17/2009 200932 11147 0
7/17/2009 651 11147 0
7/17/2009 200955 11147 0
7/24/2009 200932 11147 1
7/24/2009 651 11147 3
7/24/2009 200955 11147 1
7/31/2009 200932 11147 0
7/31/2009 651 11147 0
7/31/2009 200955 11147 0

What I would really like to do in the end is have a table row as follows:

Supervisor_NO 7/03/09 7/10/09 7/17/09 7/24/09 7/31/09
11147 0 0 3 1 3

I could also use separate records as:

7/03/2009 11147 0
7/10/2009 11147 0
7/17/2009 11147 3
7/24/2009 11147 1
7/31/2009 11147 3

The previous output you provided does display the zeros, but it dispaly the zeros for all users. If a user has zeros values for all weeks, I do not want to include them in the final output. I only want to include user in the final output if even of the weeks contain a zero.

Hope this helps. I'm struggling to try to get this to work. I appreciate your support.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-26 : 14:09:04

>>I tried creating the insert statement, but was unable to do so

Here are your 1st two rows inserted. I think you can figure out how to add the rest


insert GOVERNANCE_DATA (week_ending_date, employee_no, supervisor_no, approved_state)
select '7/3/2009', 651, 11147, 1 union all
select '7/3/2009', 200955, 11147, 1

EDIT:
>>The previous output you provided does display the zeros, but it dispaly the zeros for all users
you mean the statement with the HAVING clause still didn't solve it?

Be One with the Optimizer
TG

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-08-26 : 14:34:59

Here's the full inser statement

insert GOVERNANCE_DATA (week_ending_date, employee_no, supervisor_no, approved_state)
select '7/3/2009', 651, 11147, 1
select '7/3/2009', 200955, 11147, 1
select '7/3/2009', 200932, 11147, 1
select '7/10/2009', 651, 11147, 1
select '7/10/2009', 200955, 11147, 1
select '7/10/2009', 200932, 11147, 1
select '7/17/2009', 651, 11147, 0
select '7/17/2009', 200955, 11147, 0
select '7/17/2009', 200932, 11147, 0
select '7/24/2009', 651, 11147, 1
select '7/24/2009', 200955, 11147, 3
select '7/24/2009', 200932, 11147, 1
select '7/31/2009', 651, 11147, 0
select '7/31/2009', 200955, 11147, 0
select '7/31/2009', 200932, 11147, 0

The results should be as follows:

7/03/2009 11147 0
7/10/2009 11147 0
7/17/2009 11147 3
7/24/2009 11147 1
7/31/2009 11147 3

I am only accounting valid records if approved_state <> 1

The HAVING clause did not correct the problem. The output came out as:

week_ending_date, supervisor_no, not_valid_count
7/17/2009 1147 3
7/31/2009 1147 3

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-26 : 14:44:13

Thanks (you neglected to include the "UNION ALL" part but I did that.

My original solution seems to get your desired output. If you have other data that you can include to illustrate your problem please add that to the DML.


use tempdb
go
CREATE TABLE [GOVERNANCE_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) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

insert GOVERNANCE_DATA (week_ending_date, employee_no, supervisor_no, approved_state)
select '7/3/2009', 651, 11147, 1 union all
select '7/3/2009', 200955, 11147, 1 union all
select '7/3/2009', 200932, 11147, 1 union all
select '7/10/2009', 651, 11147, 1 union all
select '7/10/2009', 200955, 11147, 1 union all
select '7/10/2009', 200932, 11147, 1 union all
select '7/17/2009', 651, 11147, 0 union all
select '7/17/2009', 200955, 11147, 0 union all
select '7/17/2009', 200932, 11147, 0 union all
select '7/24/2009', 651, 11147, 1 union all
select '7/24/2009', 200955, 11147, 3 union all
select '7/24/2009', 200932, 11147, 1 union all
select '7/31/2009', 651, 11147, 0 union all
select '7/31/2009', 200955, 11147, 0 union all
select '7/31/2009', 200932, 11147, 0 

SELECT week_ending_date
       ,supervisor_no
       ,sum(case when approved_state <> 1 then 1 else 0 end) AS not_valid_count
FROM     GOVERNANCE_DATA
WHERE    week_ending_date BETWEEN '7/1/2009' AND '7/31/2009' --AND (approved_state = 0)
GROUP BY week_ending_date, supervisor_no 
ORDER BY supervisor_no, week_ending_date


drop table GOVERNANCE_DATA

output:
week_ending_date        supervisor_no not_valid_count
----------------------- ------------- ---------------
2009-07-03 00:00:00.000 11147         0
2009-07-10 00:00:00.000 11147         0
2009-07-17 00:00:00.000 11147         3
2009-07-24 00:00:00.000 11147         1
2009-07-31 00:00:00.000 11147         3

EDIT:
don't forget to include your new expected output if you add sample data.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-26 : 15:06:14

>>What I mean is that if a the supervisor_no is zero for all weeks, I do not want that supervisor to display.
Ok I think I see what you're getting at. What you just said plus this:
If supervisor has any weeks at all (in the date range) with approved_state <> 1 then show all weeks for that guy.

Try this:


use tempdb
go
CREATE TABLE [GOVERNANCE_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) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

insert GOVERNANCE_DATA (week_ending_date, employee_no, supervisor_no, approved_state)
--original sample
select '7/3/2009', 651, 11147, 1 union all
select '7/3/2009', 200955, 11147, 1 union all
select '7/3/2009', 200932, 11147, 1 union all
select '7/10/2009', 651, 11147, 1 union all
select '7/10/2009', 200955, 11147, 1 union all
select '7/10/2009', 200932, 11147, 1 union all
select '7/17/2009', 651, 11147, 0 union all
select '7/17/2009', 200955, 11147, 0 union all
select '7/17/2009', 200932, 11147, 0 union all
select '7/24/2009', 651, 11147, 1 union all
select '7/24/2009', 200955, 11147, 3 union all
select '7/24/2009', 200932, 11147, 1 union all
select '7/31/2009', 651, 11147, 0 union all
select '7/31/2009', 200955, 11147, 0 union all
select '7/31/2009', 200932, 11147, 0  union all

--different supervisor but all zero weeks so don't show this guy
select '7/3/2009', 651, 1147, 1 union all
select '7/3/2009', 200955, 1147, 1 union all
select '7/3/2009', 200932, 1147, 1 union all
select '7/10/2009', 651, 1147, 1 union all
select '7/10/2009', 200955, 1147, 1 union all
select '7/10/2009', 200932, 1147, 1 union all
select '7/17/2009', 651, 1147, 1 union all
select '7/17/2009', 200955, 1147, 1 union all
select '7/17/2009', 200932, 1147, 1 union all
select '7/24/2009', 651, 11147, 1 union all
select '7/24/2009', 200955, 1147, 1 union all
select '7/24/2009', 200932, 1147, 1 union all
select '7/31/2009', 651, 1147, 1 union all
select '7/31/2009', 200955, 1147, 1 union all
select '7/31/2009', 200932, 1147, 1  union all

--third supervisor with 2 weeks in the range and one week with valid count
select '7/3/2009', 651, 147, 1 union all
select '7/3/2009', 200955, 147, 1 union all
select '7/3/2009', 200932, 147, 0 union all
select '7/10/2009', 651, 147, 1 union all
select '7/10/2009', 200955, 147, 1

SELECT week_ending_date
       ,supervisor_no
       ,sum(case when approved_state <> 1 then 1 else 0 end) AS not_valid_count
FROM     GOVERNANCE_DATA gd
WHERE    exists (
       select 'anything'
       from   GOVERNANCE_DATA
       where  supervisor_no = gd.supervisor_no
       and    week_ending_date BETWEEN '7/1/2009' AND '7/31/2009'
       and    approved_state <> 1
       )
GROUP BY week_ending_date, supervisor_no 
ORDER BY supervisor_no, week_ending_date


drop table GOVERNANCE_DATA

output:
week_ending_date        supervisor_no not_valid_count
----------------------- ------------- ---------------
2009-07-03 00:00:00.000 147           1
2009-07-10 00:00:00.000 147           0

2009-07-03 00:00:00.000 11147         0
2009-07-10 00:00:00.000 11147         0
2009-07-17 00:00:00.000 11147         3
2009-07-24 00:00:00.000 11147         1
2009-07-31 00:00:00.000 11147         3

EDIT:
added DML and OUTPUT

Be One with the Optimizer
TG

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-08-26 : 15:14:20

Yes. You are correct. The only issue I see is that for some reasone the between filter is not working. It is display all dates instead of 7/1/09 to 7/31/09. I'm working on trying to figure it out.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-26 : 15:34:18

see this is the reason for good sample data.

--add a valid status out of our date range
select '7/31/2010', 200932, 11147, 2  union all

I think you just need to include the date range criteria back in the WHERE clause as well as the EXISTS:


SELECT week_ending_date
       ,supervisor_no
       ,sum(case when approved_state <> 1 then 1 else 0 end) AS not_valid_count
FROM     GOVERNANCE_DATA gd
WHERE    exists (
       select 'anything'
       from   GOVERNANCE_DATA
       where  supervisor_no = gd.supervisor_no
       and    week_ending_date BETWEEN '7/1/2009' AND '7/31/2009'
       and    approved_state <> 1
       )
and    week_ending_date BETWEEN '7/1/2009' AND '7/31/2009'
GROUP BY week_ending_date, supervisor_no 
ORDER BY supervisor_no, week_ending_date

EDIT:
added reprimand for bad sample data

Be One with the Optimizer
TG

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-08-27 : 09:33:04

TG:

The script is now working exactly the way I wanted it. Now that I look at hte script, it seems straighforward, but overall it was a learning experience.

Your time is greatly appreciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-27 : 09:36:53

Great! I think that is the whole idea of this forum

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources