Inserting new rows - 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

Inserting new rows

Author

Topic

Mae
Starting Member

4 Posts

Posted - 2011-07-04 : 18:25:13

Hello,

I was hoping someone could help resolve my task. I have an existing table which is just like a tracking table for the other table counts.

Now my problem is, if the source table has no records added for this day, it will not appear in the tracking table that I've created. How will I be able to still insert rows for the source tables with no records into the tracking table automatically - daily?

Please see my illustration below to see clearly what I was trying to achieve with this task. (Those with 0 counts are the source tables with no records but are still added into the tracking table).

Thanks in advance for your assistance.

REPORT TYPES:
SONE
OMAP
HLMK
AEIS
AAAE

This is what my current tracking table looks like:
RUN_DATE REPORT_TABLE TOTAL_COUNTS COUNTS_SUPPRESSED
6/29/2011 SONE 28,017 28,017
6/30/2011 OMAP 2 2
6/30/2011 HLMK 2 2
6/30/2011 SONE 29,426 29,425
7/1/2011 HLMK 3 3
7/1/2011 SONE 26,284 26,284
7/2/2011 HLMK 11 11
7/2/2011 SONE 48,073 48,073
7/3/2011 SONE 31,230 9,232
7/3/2011 HLMK 3 3
7/4/2011 OMAP 1 1
7/4/2011 SONE 41,619 41,619
7/4/2011 HLMK 4 4

This is what I'm trying to achieve:

RUN_DATE REPORT_TABLE TOTAL_COUNTS COUNTS_SUPPRESSED
6/29/2011 SONE 28,017 28,017
6/29/2011 OMAP 0 0
6/29/2011 HLMK 0 0
6/29/2011 AEIS 0 0
6/29/2011 AAAE 0 0
6/30/2011 OMAP 2 2
6/30/2011 HLMK 2 2
6/30/2011 SONE 29,426 29,425
6/30/2011 AEIS 0 0
6/30/2011 AAAE 0 0
7/1/2011 HLMK 3 3
7/1/2011 SONE 26,284 26,284
7/1/2011 OMAP 0 0
7/1/2011 AEIS 0 0
7/1/2011 AAAE 0 0
7/2/2011 HLMK 11 11
7/2/2011 SONE 48,073 48,073
7/2/2011 OMAP 0 0
7/2/2011 AEIS 0 0
7/2/2011 AAAE 0 0
7/3/2011 SONE 31,230 9,232
7/3/2011 HLMK 3 3
7/3/2011 OMAP 0 0
7/3/2011 AEIS 0 0
7/3/2011 AAAE 0 0
7/4/2011 OMAP 1 1
7/4/2011 SONE 41,619 41,619
7/4/2011 HLMK 4 4
7/4/2011 AEIS 0 0
7/4/2011 AAAE 0 0

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-04 : 18:27:32

Probably just left join to a date table.
Depends on how you are getting the counts - can you post the query it's probably easy to amend.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Mae
Starting Member

4 Posts

Posted - 2011-07-04 : 18:37:21

This is what I currently have to pull those counts:

To add the records pulled from the source tables to the tracking table:
INSERT INTO TRACKING (RUN_DATE, REPORT_TABLE, TOTAL_COUNTS)
SELECT CURRENT_DATE
,REPORT_TABLE
,count(trans_id)
FROM FULF_FILES_BASE

GROUP BY CURRENT_DATE, REPORT_TABLE;

To suppress the records:
DELETE FROM FULF_FILES_BASE WHERE REPORT_TABLE||''||NEW_MBR_ID||''||AWD_ID_NBR||''||XACT_DT_KEY IN
(SELECT REPORT_TABLE||''||NEW_MBR_ID||''||AWD_ID_NBR||''||XACT_DT_KEY FROM FULF_FILES_BASE_PREV);

To update the rest of the fields:
UPDATE TRACKING
FROM (SELECT REPORT_TABLE
,COUNT(TRANS_ID) AS RECORDS_BASE
FROM FULF_FILES_BASE
GROUP BY REPORT_TABLE
) BS
SET RECORDS_BASE = BS.RECORDS_BASE
,COUNTS_SUPPRESSED = TOTAL_COUNTS - BS.RECORDS_BASE
WHERE RUN_DATE = CURRENT_DATE
AND REPORT_TABLE = BS.REPORT_TABLE
;

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-04 : 19:04:38

one way
select d.CURRENT_DATE, n.REPORT_TABLE, count(d.CURRENT_DATE)
from
(select distinct CURRENT_DATE from FULF_FILES_BASE) d
cross join
(select distinct REPORT_TABLE from FULF_FILES_BASE) n
left join REPORT_TABLE t
on t.CURRENT_DATE = d.CURRENT_DATE
and t.REPORT_TABLE = n.REPORT_TABLE
group by d.CURRENT_DATE, n.REPORT_TABLE

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Mae
Starting Member

4 Posts

Posted - 2011-07-06 : 09:23:55

thank you sooo much for your help nigelrivett!

only made a few adjustments but this is it!

thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources