date filter condition - 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

date filter condition

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2011-02-10 : 13:59:24

Hi
I need to have a condition in the which gives available hours , considering the tester whose decommision date is not greater than the day_date.
I have 4 tables :
3 dimension tables and 1 fact table
dim_date : has all possible dates in all formats
dim_tester : has the all the tester values , and its inception ( when the tester was bought) and decommision(when the tester was decommsioned or removed)
dim_time : which has hour seg prime values , and the available hours for a day
Fct_tester_utilization : this table has the keys to link all the dimension tables and the logged hours (used hours) details.
i need to calculate available hours based on the inception date and decommision date.
A tester is considered to be online if its between inception and decommsion date, and available hours has to be calculated for all the testers online, if the tester is decommisioned then it has the date value of when its decommsioned if not it has the far value like 12/31/9999.
Here is the ddl and some sample data



CREATE TABLE EDW_DIM.DIM_DATE 
(
  DATE_KEY NUMBER(*, 0) 
, DAY_DATE DATE 
, SUN_WEEK_START_DATE DATE )

Sample Data:
DATE_KEY DAY_DATE   SUN_WEEK_START_DATE
3260	05-DEC-10	05-DEC-10
3261	06-DEC-10	05-DEC-10
3262	07-DEC-10	05-DEC-10
3263	08-DEC-10	05-DEC-10
3264	09-DEC-10	05-DEC-10
3265	10-DEC-10	05-DEC-10
3266	11-DEC-10	05-DEC-10
3267	12-DEC-10	12-DEC-10
3268	13-DEC-10	12-DEC-10
3269	14-DEC-10	12-DEC-10
3270	15-DEC-10	12-DEC-10
3271	16-DEC-10	12-DEC-10
3272	17-DEC-10	12-DEC-10
3273	18-DEC-10	12-DEC-10
3274	19-DEC-10	19-DEC-10
3275	20-DEC-10	19-DEC-10
3276	21-DEC-10	19-DEC-10
3277	22-DEC-10	19-DEC-10
3278	23-DEC-10	19-DEC-10
3279	24-DEC-10	19-DEC-10
3280	25-DEC-10	19-DEC-10
3281	26-DEC-10	26-DEC-10
3282	27-DEC-10	26-DEC-10
3283	28-DEC-10	26-DEC-10

DIM_TESTER:

CREATE TABLE EDW_DIM.DIM_TESTER 
(
  TESTER_KEY NUMBER(10, 0) NOT NULL 
, TESTER_NAME VARCHAR2(25 BYTE) 
, TESTER_CATEGORY VARCHAR2(25 BYTE) 
, TESTER_INCEPTION_DATE DATE 
, TESTER_DECOMMISSION_DATE DATE 
)

Sample data:
TESTER_KEY TESTER_NAME TESTER_CATEGORY  TESTER_INCEPTION_DATE TESTER_DECOMMISSION_DATE 
401	RFR6155-2	Bench	02-NOV-06	31-DEC-99
402	RFR6250-2	Bench	02-NOV-06	25-SEP-09
403	RFR6250-3	Bench	02-NOV-06	31-DEC-99
404	RFR6250-7	Bench	02-NOV-06	31-DEC-99
405	RFR6275-2	Bench	02-NOV-06	27-MAY-08
406	RFR6275-3	Bench	02-NOV-06	31-DEC-99
407	SMOKY	        Bench	02-NOV-06	31-DEC-99
408	SUGAR	        Bench	02-NOV-06	31-DEC-99
409	ZIF-02	        Bench	02-NOV-06	31-DEC-99
410	ZIF-03	        Bench	02-NOV-06	25-SEP-09
411	ZIF-04	        Bench	02-NOV-06	25-SEP-09
412	ZIF-18	        Bench	02-NOV-06	31-DEC-99
413	ACDC	        ATE	05-NOV-03	09-DEC-06
414	AEROSMITH	ATE	05-FEB-05	15-OCT-09
416	BEATLES	        ATE	10-APR-06	02-OCT-07
417	BLAINE	     SIMULATOR	14-MAY-03	31-DEC-09
418	BURNS	     SIMULATOR	02-SEP-03	31-DEC-09
419	CAPOTE	        ATE	16-JUN-05	31-DEC-99
420	CHICO	        ATE	03-JUN-03	31-DEC-99
421	CLASH	        ATE	01-APR-04	09-NOV-10
422	DAPHNE	        ATE	29-DEC-04	31-DEC-99
424	EXACTA	        ATE	24-MAR-03	31-DEC-99
425	FOGHAT	        ATE	16-JUN-05	31-DEC-99
427	GRATEFULDEAD	ATE	05-MAR-03	05-NOV-07
428	GRENACHE	ATE	06-JAN-06	31-DEC-99
431	IDITAROD	ATE	24-MAR-03	31-DEC-99
432	IRONMAIDEN	ATE	20-JUL-03	30-JAN-07
434	LEDZEPPELIN	ATE	01-FEB-05	07-NOV-10
532	UFLEX1	        ATE	18-SEP-07	14-OCT-08

DIM_TIME

CREATE TABLE EDW_DIM.DIM_TIME 
(
  TIME_KEY NUMBER 
, HOUR_SEG_PRIME VARCHAR2(50 BYTE) 
) 

Sample Data:

TIME_KEY HOUR_SEG_PRIME
1	AM-OS:12 AM - 7 AM
61	AM-OS:12 AM - 7 AM
121	AM-OS:12 AM - 7 AM
181	AM-OS:12 AM - 7 AM
241	AM-OS:12 AM - 7 AM
301	AM-OS:12 AM - 7 AM
361	AM-OS:12 AM - 7 AM
421	AM-Eng:7 AM - 9 AM
481	AM-Eng:7 AM - 9 AM
541	Prime Time:9 AM - 7 PM
601	Prime Time:9 AM - 7 PM
661	Prime Time:9 AM - 7 PM
721	Prime Time:9 AM - 7 PM
781	Prime Time:9 AM - 7 PM
841	Prime Time:9 AM - 7 PM
901	Prime Time:9 AM - 7 PM
961	Prime Time:9 AM - 7 PM
1021	Prime Time:9 AM - 7 PM
1081	Prime Time:9 AM - 7 PM
1141	PM-Eng:7 PM - 10 PM
1201	PM-Eng:7 PM - 10 PM
1261	PM-Eng:7 PM - 10 PM
1321	PM-OS:10 PM - 12 AM
1381	PM-OS:10 PM - 12 AM

FCT_TESTER_UTILIZATION:

CREATE TABLE EDW_FACT.FCT_TESTER_UTILIZATION 
(
  TESTER_KEY NUMBER(10, 0) 
, DIM_DATE_KEY NUMBER(10, 0) 
, DIM_TIME_KEY NUMBER(10, 0) 
, LOGGED_TIME NUMBER(10, 0) 
)

Sample Data:

DIM_DATE_KEY	DIM_TIME_KEY	TESTER_KEY	LOGGED_TIME
3260	1	345	60
3260	1	347	60
3261	1	707	60
3261	1	708	60
3261	1	709	60
3261	1	710	60
3261	1	711	60
3261	1	713	60
3261	1	715	60
3261	1	722	60
3261	1	723	60
3261	1	724	60
3261	1	726	60
3261	1	728	60
3261	1	730	60
3261	1	734	60
3261	1	739	60
3261	1	742	60
3261	1	745	60
3261	1	749	60
3261	1	753	60
3261	1	754	60
3261	1	759	60
3261	1	760	60
3261	1	766	60
3261	1	773	60
3261	1	775	60
3261	1	776	60
3261	1	780	60
3261	61	345	60
3261	61	355	60

I have calculated available hours :

Here is my query :



SQL SELECT 
B.HOUR_SEG_PRIME,
t.tester_name,
TRUNC(c.DAY_DATE + 1,'IW')-1 AS Week,
c.DAY_DATE,
t.tester_inception_date,
t.tester_decommission_date,
 (
			CASE
				WHEN B.HOUR_SEG_PRIME = 'AM-OS:12 AM - 7 AM' THEN 0.292
				WHEN B.HOUR_SEG_PRIME = 'AM-Eng:7 AM - 9 AM' THEN 0.083
				WHEN B.HOUR_SEG_PRIME = 'Prime Time:9 AM - 7 PM' THEN 0.417
				WHEN B.HOUR_SEG_PRIME = 'PM-Eng:7 PM - 10 PM' THEN 0.125
				WHEN B.HOUR_SEG_PRIME = 'PM-OS:10 PM - 12 AM' THEN 0.083
			END
		)* 24 AS "Available_Hours"
FROM dim_time B join fct_tester_utilization f 
on  f.dim_time_key = B.time_key 
join dim_date c
on f.dim_date_key = c.date_key
join dim_tester t
on t.tester_key = f.tester_key

I have not written a where condition in the above query , could any one please assist me in writing query considering the above mentioned conditions for inception and decommision dates?

Output with the above query:




WEEK TESTER_NAME DAY_DATE INCEPTION_DATE DECOMMISION_DATEAVAILABLE_HOURS
12/5/2010	BROWN	12/6/2010	11/2/2006	12/31/9999	10.01
12/5/2010	CHICO	12/11/2010	6/3/2003	12/31/9999	3.00
12/5/2010	MEGATRACE	12/8/2010	8/20/2009	8/27/2010	10.01
12/5/2010	MEGATRACE	12/9/2010	8/20/2009	8/27/2010	10.01
12/5/2010	MEGATRACE	12/10/2010	8/20/2009	8/27/2010	10.01
12/5/2010	MERLOT	12/5/2010	3/24/2003	12/31/9999	7.01
12/5/2010	MEZCAL	12/5/2010	6/2/2006	12/31/9999	7.01
12/5/2010	OUZO	12/11/2010	4/23/2008	12/31/9999	1.99
12/5/2010	RFALAB-080	12/9/2010	1/17/2009	12/31/9999	10.01
12/5/2010	RFR6275-2	12/8/2010	11/2/2006	5/27/2008	10.01
12/12/2010	MEGATRACE	12/13/2010	8/20/2009	8/27/2010	10.01
12/12/2010	MEGATRACE	12/16/2010	8/20/2009	8/27/2010	10.01
12/12/2010	MEZCAL	12/18/2010	6/2/2006	12/31/9999	7.01
12/19/2010	MARCO	12/25/2010	3/24/2003	12/31/9999	7.01
12/19/2010	MEGATRACE	12/21/2010	8/20/2009	8/27/2010	10.01
12/19/2010	MEGATRACE	12/22/2010	8/20/2009	8/27/2010	10.01
12/19/2010	MERLOT	12/22/2010	3/24/2003	12/31/9999	10.01
12/26/2010	MALBEC	12/27/2010	4/14/2006	12/31/9999	10.01
12/26/2010	MARCO	1/1/2011	3/24/2003	12/31/9999	7.01
12/26/2010	MEGATRACE	12/28/2010	8/20/2009	8/27/2010	10.01

In the above output the tester MEGATRACE has already been decommsioned on 8/27/2010 but still there is a day_date , i should not calculate available hours for the testers which have already been decommisoned.

Expected Output :



WEEK	TESTER_NAME	DAY_DATE	TESTER_INCEPTION_DATE	TESTER_DECOMMISION_DATE	AVAILABLE_HOURS
12/5/2010	BROWN	12/6/2010	11/2/2006	12/31/9999	10.01
12/5/2010	CHICO	12/11/2010	6/3/2003	12/31/9999	3.00
12/5/2010	MERLOT	12/5/2010	3/24/2003	12/31/9999	7.01
12/5/2010	MEZCAL	12/5/2010	6/2/2006	12/31/9999	7.01
12/5/2010	OUZO	12/11/2010	4/23/2008	12/31/9999	1.99
12/5/2010   RFALAB-080    12/9/2010      1/17/2009	12/31/9999	10.01
12/12/2010	MEZCAL	12/18/2010	6/2/2006	12/31/9999	7.01
12/19/2010	MARCO	12/25/2010	3/24/2003	12/31/9999	7.01
12/19/2010	MERLOT	12/22/2010	3/24/2003	12/31/9999	10.01
12/26/2010	MALBEC	12/27/2010	4/14/2006	12/31/9999	10.01
12/26/2010	MARCO	1/1/2011	3/24/2003	12/31/9999	7.01
12/26/2010    MEGATRACE	8/26/2010      8/20/2009    8/27/2010	10.01

The last day_date for Megatrace should not be greater than the decommsionned date.

Please Help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-10 : 18:36:44

You should make the sample data usable.
Edit your query to make the sample data look like

INSERT ...
SELECT ... UNION ALL
SELECT ... UNION ALL
SELECT ...

So that we can copy and paste the sample data to test.

N 56°04'39.26"
E 12°55'05.63"

rds207
Posting Yak Master

198 Posts

Posted - 2011-02-11 : 02:14:48

quote:
Originally posted by Peso

You should make the sample data usable.
Edit your query to make the sample data look like

INSERT ...
SELECT ... UNION ALL
SELECT ... UNION ALL
SELECT ...

So that we can copy and paste the sample data to test.

N 56°04'39.26"
E 12°55'05.63"

Here is the sample data ...

[CODE]

CREATE TABLE [dbo].[Available_Hours](
[WEEK] [date] NULL,
[TESTER] [varchar](20) NULL,
[DAY_DATE] [date] NULL,
[INCEPTION_DATE] [date] NULL,
[DECOMMISION_DATE] [date] NULL,
[TESTER_CATEGORY] [varchar](20) NULL,
[AVAILABLE_HOURS] [varchar](20) NULL
) ON [PRIMARY]

GO

INSERT INTO [Available_Hours]
([WEEK]
,[TESTER]
,[DAY_DATE]
,[INCEPTION_DATE]
,[DECOMMISION_DATE]
,[TESTER_CATEGORY]
,[AVAILABLE_HOURS])
select '12/5/2010','BROWN','12/6/2010','11/2/2006','12/31/9999','Bench','10.008' union
select '12/5/2010','CHAR-BENCH','12/10/2010','6/9/2010','12/31/9999','Bench', '10.008'union
select '12/5/2010' ,'CHAR-BENCH','12/11/2010','6/9/2010','12/31/9999','Bench', '10.008'union
select '12/5/2010' ,'MEGATRACE','12/7/2010','8/20/2009','8/27/2010','ATE','10.008'union
select '12/5/2010' ,'MEGATRACE','12/8/2010','8/20/2009','8/27/2010','ATE','10.008'union
select '12/5/2010' ,'MEGATRACE','12/9/2010','8/20/2009','8/27/2010','ATE', '10.008'union
select '12/5/2010' ,'MEGATRACE','12/10/2010','8/20/2009','8/27/2010','ATE', '10.008'union
select '12/5/2010' ,'MERLOT','12/5/2010','3/24/2003','12/31/9999', 'ATE', '7.008'union
select'12/5/2010' ,'MEZCAL', '12/5/2010','6/2/2006', '12/31/9999', 'ATE', '7.008'union
select'12/5/2010' ,'OUZO', '12/11/2010', '4/23/2008','12/31/9999', 'ATE','1.992'union
select'12/5/2010' ,'RFR6275-2','12/8/2010','11/2/2006','5/27/2008','Bench','10.008'union
select'12/5/2010' ,'SALUD','12/10/2010', '5/6/2004','12/31/9999', 'ATE', '10.008'union
select'12/5/2010' ,'SALUD', '12/11/2010', '5/6/2004' ,'12/31/9999','ATE', '10.008'union
select'12/5/2010' ,'SMOKY', '12/11/2010', '11/2/2006' ,'12/31/9999', 'Bench', '10.008'union
select'12/5/2010' ,'ST-SAN01-01', '12/6/2010', '5/6/2008' ,'12/31/9999', 'SIMULATOR', '7.008'union
select'12/12/2010', 'MEGATRACE', '12/13/2010', '8/20/2009' ,'8/27/2010', 'ATE', '10.008'union
select'12/12/2010', 'MEGATRACE', '12/16/2010', '8/20/2009', '8/27/2010' ,'ATE', '10.008'union
select'12/19/2010', 'EXACTA', '12/22/2010', '3/24/2003' ,'12/31/9999' ,'ATE', '10.008'union
select'12/19/2010', 'EXACTA', '12/24/2010', '3/24/2003' ,'12/31/9999' ,'ATE', '7.008'union
select'12/19/2010', 'FOGHAT', '12/24/2010', '6/16/2005' ,'12/31/9999' ,'ATE', '7.008'union
select'12/19/2010', 'FOZZIE', '12/25/2010', '11/2/2006', '12/31/9999' ,'Bench', '10.008'union
select'12/19/2010', 'IFLEX1A-SIN',' 12/21/2010', '8/15/2009' ,'12/31/9999', 'SIMULATOR', '10.008'union
select'12/19/2010', 'IFLEX1A-SIN',' 12/22/2010' ,'8/15/2009', '12/31/9999', 'SIMULATOR', '10.008'union
select'12/19/2010', 'MEGATRACE','12/21/2010', '8/20/2009' ,'8/27/2010' ,'ATE', '10.008'union
select'12/19/2010', 'MEGATRACE','12/22/2010', '8/20/2009' ,'8/27/2010' ,'ATE', '10.008'union
select'12/26/2010', 'MEGATRACE','12/28/2010', '8/20/2009', '8/27/2010' ,'ATE', '10.008'

GO
[/CODE]

Please let me know the query to get available hours only for the testers online that means testers having day_date between inception and decommision dates.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-11 : 02:55:36

[code]SELECT *
FROM Available_Hours
WHERE Day_Date BETWEEN Inception_Date AND Decommision_Date[/code]

N 56°04'39.26"
E 12°55'05.63"

rds207
Posting Yak Master

198 Posts

Posted - 2011-02-11 : 03:11:30

quote:
Originally posted by Peso

SELECT *
FROM Available_Hours
WHERE Day_Date BETWEEN Inception_Date AND Decommision_Date

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources