Set value to Zero not NULL 1

[cdiwindows]

I have a query that I need to have the values always return a Zero instead of NULL.

If there is no category for that day, I still need a zero to appear. There are many days when the "OtherDisc" will not appear in the data, but need the value to be Zero. I have included my query, subsection of the results from the query and the data table.

select RecordType='D',
RestNumber=cast('6' as varchar) + cast(cftp.FKStoreId as varchar),
DateofBusiness=cftp.DateOfBusiness,
OtherDisc=(select sum(cftp1.discamount) from tmpCarlsonDiscounts cftp1 where cftp1.fkstoreid=cftp.fkstoreid and cftp1.dateofbusiness=cftp.dateofbusiness and cftp1.category='OTHER'), 
BrandMktg=(select sum(cftp2.discamount) from tmpCarlsonDiscounts cftp2 where cftp2.fkstoreid=cftp.fkstoreid and cftp2.dateofbusiness=cftp.dateofbusiness and cftp2.category='BRAND'), 
EmpDisc=(select sum(cftp3.discamount) from tmpCarlsonDiscounts cftp3 where cftp3.fkstoreid=cftp.fkstoreid and cftp3.dateofbusiness=cftp.dateofbusiness and cftp3.category='EMPLOYEE'), 
HalfAppsLN =0,
LocalMktg=(select sum(cftp4.discamount) from tmpCarlsonDiscounts cftp4 where cftp4.fkstoreid=cftp.fkstoreid and cftp4.dateofbusiness=cftp.dateofbusiness and cftp4.category='LSM'), 
OperDisc=(select sum(cftp5.discamount) from tmpCarlsonDiscounts cftp5 where cftp5.fkstoreid=cftp.fkstoreid and cftp5.dateofbusiness=cftp.dateofbusiness and cftp5.category='OPERATIONS')
from tmpCarlsonDiscounts cftp
where cftp.FKStoreId between 1700 and 1904
group by cftp.FKStoreId,cftp.Dateofbusiness

[ol]
RecordType RestNumber DateofBusiness OtherDisc BrandMktg EmpDisc HalfAppsLN LocalMktg OperDisc Value
D 61716 2012-02-11 00:00:00.000 NULL 574.29 89.01 0 435.77 61.14 0
D 61729 2012-02-11 00:00:00.000 NULL 914.35 140.87 0 890.71 114.48 0
D 61754 2012-02-11 00:00:00.000 NULL 910.32 48.92 0 884.78 106.97 0
D 61755 2012-02-11 00:00:00.000 NULL 1044.98 139.36 0 446.9 88.53 0
D 61851 2012-02-11 00:00:00.000 NULL 874.48 60.78 0 679.2 89.08 0
D 61852 2012-02-11 00:00:00.000 NULL 925.29 142.77 0 773.07 118.56 0
D 61857 2012-02-11 00:00:00.000 7.99 992.87 90.77 0 828.11 61.71 0
D 61863 2012-02-11 00:00:00.000 NULL 1229.5 83.93 0 1152.78 69.85 0
D 61902 2012-02-11 00:00:00.000 NULL 1423.35 194.05 0 783.53 252.4 0
D 61903 2012-02-11 00:00:00.000 NULL 1760.33 132.77 0 978.25 186.75 0
D 61904 2012-02-11 00:00:00.000 NULL 1650.9 140.24 0 975.82 211.07 0
[/ol]


Data Table:
[ol]
DateOfBusiness FKStoreID DiscID DiscName DiscAmount DiscCount Category DiscType
2012-02-11 00:00:00.000 1716 28 20%\nDiscount 2.72 1 LSM C
2012-02-11 00:00:00.000 1716 89 GMMS\nApp/Dess 8 1 BRAND C
2012-02-11 00:00:00.000 1716 237 GMMS\nChips\Salsa 56.82 18 BRAND C
2012-02-11 00:00:00.000 1729 28 20%\nDiscount 14.01 2 LSM C
2012-02-11 00:00:00.000 1729 74 Kid\nExcel 9.88 2 LSM C
2012-02-11 00:00:00.000 1729 150 GMMS\nB-Day 5.99 1 BRAND C
2012-02-11 00:00:00.000 1729 170 $5 Off $15 15 3 BRAND C
2012-02-11 00:00:00.000 1729 180 Bounce\n$5.00 10 2 BRAND C
2012-02-11 00:00:00.000 1729 237 GMMS\nChips\Salsa 78.76 24 BRAND C
2012-02-11 00:00:00.000 1755 28 20%\nDiscount 7.32 1 LSM C
2012-02-11 00:00:00.000 1755 44 $10 Limit\nApp/Dess 19.99 2 LSM C
2012-02-11 00:00:00.000 1755 170 $5 Off $15 10 2 BRAND C
2012-02-11 00:00:00.000 1755 180 Bounce\n$5.00 0 1 BRAND C
2012-02-11 00:00:00.000 1755 237 GMMS\nChips\Salsa 9.97 3 BRAND C
2012-02-11 00:00:00.000 1851 88 $8\nStripes 32 4 BRAND C
2012-02-11 00:00:00.000 1851 89 GMMS\nApp/Dess 27.99 4 BRAND C
2012-02-11 00:00:00.000 1851 170 $5 Off $15 5 1 BRAND C
[/ol]

 

[SQLBill]

COALESCE might be what you are looking for. Something like:

COALESCE(column, 0)

If the value in column is null, then it will return 0, but if column is not null then it will return the value from the column.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[cdiwindows]

Perfect. Thank you SQLBill. I thought it had to be something simple. I have never seen or used COALESCE before. Guess that is a new function to learn.

 

[gmmastros]

Looking at your query, I see a lot of redundancy, which also means there may be a more efficient way to get your results.

I would encourage you to run this query to see if it produces the same results and also runs faster.

select	RecordType='D',
		RestNumber=cast('6' as varchar) + cast(cftp.FKStoreId as varchar),
		DateofBusiness=cftp.DateOfBusiness,
		OtherDisc = sum(Case When category='OTHER' Then discamount else 0 End), 
		BrandMktg = sum(Case When category='BRAND' Then discamount Else 0 End),
		EmpDisc   = Sum(Case When category='EMPLOYEE' Then discamount Else 0 End),
		HalfAppsLN =0,
		LocalMktg = sum(Case When category='LSM' Then discamount Else 0 End),
		OperDisc  = sum(Case When category='OPERATIONS' Then discamount Else 0 End)
from	tmpCarlsonDiscounts cftp
where	cftp.FKStoreId between 1700 and 1904
group by cftp.FKStoreId,cftp.Dateofbusiness

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

COALESCE is kinda neat. You can put as many expressions in it as you want to check; it will go through each expression and return the first one that isn't null.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875