Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Create Temp Table with Days between Dates?

Status
Not open for further replies.
tqeonline

tqeonline

MIS
Oct 5, 2009
304
US
I have a query that returns results like this:

Date Opened_Count Closed_Count Total
2010-09-09 00:00:00.000 4 NULL 4
2010-09-10 00:00:00.000 2 NULL 2 2010-09-12 00:00:00.000 3 NULL 3
Click to expand...

All the total column is, is Open - Closed. in this example they are all null but there are numbers in Closed_Count down the table.

Is there a way to insert the missing date? (2010-09-11) in the table.

Like if i say date range 9-9 - 9-12. display 9-9 9-10 9-11 9-12 even if 9-10 is empty.

I'd want it to display like this:
Date Opened_Count Closed_Count Total
2010-09-10 00:00:00.000 0 0 0
Click to expand...

Just for information... the original query is this:

Code: 
SELECT 
            isnull(cast([Opened_Date] as datetime), cast([Closed_Date] as datetime)) as 'Date',
            [Opened_Count], 
            [Closed_Count],
            isnull([Opened_Count],0)-isnull([Closed_Count],0) as Total

            FROM
	            (SELECT convert(varchar,BG_DETECTION_DATE,101) as 'Opened_Date', COUNT(BG_DETECTION_DATE) as Opened_Count
	            FROM BUG
	            WHERE BG_DETECTED_IN_RCYC = 1118
		            AND BG_STATUS <> 'Invalid'
		            AND BG_STATUS <> 'New'
	            GROUP BY BG_DETECTION_DATE) AS OPENED

            FULL OUTER JOIN

	            (SELECT Closed_Date, COUNT(Closed_Date) AS Closed_Count
	            FROM
		            (SELECT AU_ENTITY_ID ,convert(varchar,max(AU_TIME),101) AS Closed_Date
		            FROM BUG,AUDIT_LOG, AUDIT_PROPERTIES WITH (NOLOCK)
		            WHERE AU_ENTITY_TYPE = 'BUG'
			            AND AP_FIELD_NAME = 'BG_STATUS'
			            AND (BG_STATUS = 'Deferred' or BG_STATUS = 'Closed')
			            AND AU_ACTION_ID = AP_ACTION_ID
			            AND BG_BUG_ID = AU_ENTITY_ID
			            AND (AP_NEW_VALUE = 'Deferred' or AP_NEW_VALUE = 'Closed')
			            AND BG_DETECTED_IN_RCYC = 1118
		            GROUP BY AU_ENTITY_ID) AS AUDIT_CLOSED
	            GROUP BY AUDIT_CLOSED.CLOSED_DATE) AS CLOSED
            ON OPENED.Opened_Date = CLOSED.Closed_Date
            ORDER BY 'Date'

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008
 
Sort by date Sort by votes
How would i create a temp table then left join my current information on it.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008
 
Upvote 0 Downvote
first, create a numbers table

CREATE TABLE numbers ( n INTEGER NOT NULL P_RIMARY KEY );
INSERT INTO numbers VALUES (0);
INSERT INTO numbers VALUES (1);
INSERT INTO numbers VALUES (2);
INSERT INTO numbers VALUES (3);
INSERT INTO numbers VALUES ...

you can then generate the dates with DATEADD

SELECT DATEADD(DAY,n,'2010-09-09') AS thedate
FROM numbers
WHERE DATEADD(DAY,n,'2010-09-09') <= '2010-09-19'

and then use this in a left outer join to your data

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Upvote 0 Downvote
like this?
Code: 
DECLARE @COUNT DECIMAL (6,1)
DECLARE @START_DATE DATETIME
DECLARE @END_DATE DATETIME
DECLARE @DURATION_DD DECIMAL (6,1)
Declare @Cycle INT 
SET @Cycle = 1118
SET ANSI_WARNINGS OFF

CREATE TABLE #Days_Between --Prepare Temp Table
(
  CYCLEDATE Datetime,
)

SET @COUNT = 0

--Retrieve and set Start Date
SELECT @START_DATE = RCYC_START_DATE 
FROM RELEASE_CYCLES WITH (NOLOCK)
WHERE RCYC_ID = @Cycle

--Retrieve and set End Date
SET @END_DATE = (case when (SELECT RCYC_END_DATE
	FROM RELEASE_CYCLES WITH (NOLOCK)
	WHERE RCYC_ID = @Cycle
	AND RCYC_END_DATE >= GETDATE()) is null 
THEN GETDATE() ELSE
	(SELECT RCYC_END_DATE
	FROM RELEASE_CYCLES WITH (NOLOCK)
	WHERE RCYC_ID = @Cycle
	AND RCYC_END_DATE >= GETDATE())
END)
	
--Calculate project Duration in hours based on start date and end date
SET @DURATION_DD = DATEDIFF(DD, @START_DATE, @END_DATE)

--Build a row for each hour in Cycle
WHILE (@COUNT) < @DURATION_DD + 1
BEGIN                  

      --Insert the values into the temp table
      INSERT INTO #Days_Between (CYCLEDATE)
      VALUES (DATEADD(DD,@COUNT,@START_DATE))

      Set @COUNT = @COUNT + 1
CONTINUE
END

SELECT CYCLEDATE as "Date", isnull(Opened_Count,0) as "Opened_Count", isnull(Closed_Count,0) as "Closed_Count", isnull(Total,0) as "Total"

FROM #Days_Between
		LEFT OUTER JOIN
		(SELECT 
        isnull(cast([Opened_Date] as datetime), cast([Closed_Date] as datetime)) as 'Date',
        [Opened_Count], 
        [Closed_Count],
        isnull([Opened_Count],0)-isnull([Closed_Count],0) as Total

        FROM
            (SELECT convert(varchar,BG_DETECTION_DATE,101) as 'Opened_Date', COUNT(BG_DETECTION_DATE) as Opened_Count
            FROM BUG
            WHERE BG_DETECTED_IN_RCYC = @Cycle
	            AND BG_STATUS <> 'Invalid'
	            AND BG_STATUS <> 'New'
            GROUP BY BG_DETECTION_DATE) AS OPENED

        FULL OUTER JOIN

            (SELECT Closed_Date, COUNT(Closed_Date) AS Closed_Count
            FROM
	            (SELECT AU_ENTITY_ID ,convert(varchar,max(AU_TIME),101) AS Closed_Date
	            FROM BUG,AUDIT_LOG, AUDIT_PROPERTIES WITH (NOLOCK)
	            WHERE AU_ENTITY_TYPE = 'BUG'
		            AND AP_FIELD_NAME = 'BG_STATUS'
		            AND (BG_STATUS = 'Deferred' or BG_STATUS = 'Closed')
		            AND AU_ACTION_ID = AP_ACTION_ID
		            AND BG_BUG_ID = AU_ENTITY_ID
		            AND (AP_NEW_VALUE = 'Deferred' or AP_NEW_VALUE = 'Closed')
		            AND BG_DETECTED_IN_RCYC = @Cycle
	            GROUP BY AU_ENTITY_ID) AS AUDIT_CLOSED
            GROUP BY AUDIT_CLOSED.CLOSED_DATE) AS CLOSED
        ON OPENED.Opened_Date = CLOSED.Closed_Date)as Data
        ON Data.[Date] = #Days_Between.CycleDate

DROP TABLE #Days_Between

(This works by the way.. I just finished it)

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008
 
Upvote 0 Downvote
Code: 
[COLOR=blue]DECLARE[/color] @dBegin [COLOR=#FF00FF]datetime[/color], @dEnd [COLOR=#FF00FF]datetime[/color]
[COLOR=blue]SET[/color] @dBegin = [COLOR=red]'20100901'[/color] [COLOR=green]-- Sep, 01 2010
[/color][COLOR=blue]SET[/color] @dEnd   = [COLOR=red]'20101231'[/color] [COLOR=green]-- Dec, 31 2010  
[/color]
[COLOR=green]-- Single select statement here
[/color][COLOR=blue]select[/color] r.range_date
[COLOR=blue]from[/color]  ([COLOR=blue]select[/color] @dBegin + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 range_date
              [COLOR=blue]from[/color] ([COLOR=blue]select[/color] 0 b1  union [COLOR=blue]select[/color] 1    b1)  t1
       [COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b2  union [COLOR=blue]select[/color] 2    b2)  t2
       [COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b3  union [COLOR=blue]select[/color] 4    b3)  t3
       [COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b4  union [COLOR=blue]select[/color] 8    b4)  t4
       [COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b5  union [COLOR=blue]select[/color] 16   b5)  t5
       [COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b6  union [COLOR=blue]select[/color] 32   b6)  t6
       [COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b7  union [COLOR=blue]select[/color] 64   b7)  t7
       [COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b8  union [COLOR=blue]select[/color] 128  b8)  t8
       [COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b9  union [COLOR=blue]select[/color] 256  b9)  t9
       [COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b10 union [COLOR=blue]select[/color] 512  b10) t10
       [COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b11 union [COLOR=blue]select[/color] 1024 b11) t11
       [COLOR=blue]cross[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] 0 b12 union [COLOR=blue]select[/color] 2048 b12) t12
       [COLOR=blue]where[/color] @dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @dEnd) r
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] r.range_date

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
Working Days giving NULL values
Replies
8
Views
305
Cpreston
Cpreston
grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
772
mikrom
mikrom
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
341
Mike Lewis
Mike Lewis
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
544
BobSuruncle
BobSuruncle
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
1K
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top