and NOT... - 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

SQL Server 2000 Forums

Transact-SQL (2000)

and NOT...

Author

Topic

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-12-06 : 18:38:24

Hi guys,
I need to ammend this query to exclude a certain source system:

select right(convert(varchar,load_date,103),10) as Record_Date,
source_system,
sum(TOTAL_NUMBER_OF_RECORDS_IN_FILE) as Records_File,
sum(TOTAL_NUMBER_OF_RECORDS_PROCESSED) as Records_Processed,
sum(TOTAL_NUMBER_OF_RECORDS_LOADED) as Records_Loaded,
sum(TOTAL_NUMBER_OF_RECORDS_NOT_LOADED) as Records_Not_Loaded,
sum(TOTAL_NUMBER_OF_RECORDS_IN_FILE)-sum(TOTAL_NUMBER_OF_RECORDS_PROCESSED) as Compare_File_Processed
from load_report
group by right(convert(varchar,load_date,103),10), source_system
order by Record_date

Here is a snippet of the above results:

LOAD DATE SOURCE SYSTEM RECORDS FILE RECORDS PROCESSED RECORDS LOADED RECORDS NOT LOADED COMPARE FILE PROCESSED
1/11/2006 aa 25839 25839 3021 22818 0
1/11/2006 bb 7452 7452 7452 0 0
1/11/2006 cc 5021763 5021763 1119 5020644 0
1/11/2006 dd -3 459972 444726 15246 -459975
2/11/2006 aa 24174 24174 2898 21276 0
2/11/2006 bb 6810 6810 6810 0 0
2/11/2006 cc 5022729 5022729 966 5021763 0
2/11/2006 dd -3 457842 447471 10371 -457845

I would like my query to exclude calculation of 'compare file processed' only for records where the 'source system' is dd. that is put an error message on column 'compare file processed' saying "dd calc is ignored". However is still want the records for the other columns of records dd still selected. do i use a case statement or if/else statement on the sum(TOTAL_NUMBER_OF_RECORDS_IN_FILE)-sum(TOTAL_NUMBER_OF_RECORDS_PROCESSED) as Compare_File_Processed part of the query?

Cheers
GK

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-06 : 20:05:05

CASE statement is what you want. Note that you will need to make that column a varchar column if you want to put a message in it, so I have cast the number to a varchar for the calculation.

select right(convert(varchar,load_date,103),10) as Record_Date,
source_system,
sum(TOTAL_NUMBER_OF_RECORDS_IN_FILE) as Records_File,
sum(TOTAL_NUMBER_OF_RECORDS_PROCESSED) as Records_Processed,
sum(TOTAL_NUMBER_OF_RECORDS_LOADED) as Records_Loaded,
sum(TOTAL_NUMBER_OF_RECORDS_NOT_LOADED) as Records_Not_Loaded,
CASE 
    WHEN source_system = 'dd' THEN 'dd calc is ignored' 
    ELSE cast(sum(TOTAL_NUMBER_OF_RECORDS_IN_FILE)-sum(TOTAL_NUMBER_OF_RECORDS_PROCESSED) as varchar(15)) 
END as Compare_File_Processed
from load_report
group by right(convert(varchar,load_date,103),10), source_system
order by Record_date

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-12-06 : 21:12:49

Perfect thank you
GK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources