New York, NY

Long Live TSQL

Why can I never remember the IsNull(foo,0) function in SQL Server. It is an ISNULL or NZ type of function that converts a null value to the item specified, which in this case is a zero. I don't know why I forget this code all the time is that I go back to my Access roots and try to do an NZ() or the Null to Zero function specific to the Access object model. (Old habits die hard!) Well I can usually IM my good friend Al for help, which I did today, but now here is an example of using IsNull in a TSQL string:

update tblSummaryByLocation_WebSublocation
set LocalNewsPaper=IsNUll(LocalNewsPaper1,0)+IsNUll(LocalNewsPaper2,0)+IsNUll(LocalNewsPaper3,0)
WHERE Weekending_ID= @Weekending_ID

I am also sick of using the CASE statement, this code requires me to know something about my data, can' t wait for the Yukon PIVOT keyword:

SELECT     dbo.tlkpWeekEnding.WeekEnding_ID, dbo.tlkpWeekEnding.WeekEnding_DT, dbo.tlkpLocation.Location_ID, dbo.tlkpLocation.Location_NM,
                      dbo.tblLocationSub_WebMapping.WebSublocation_ID,
                      dbo.tblLocationSub_WebMapping.WebSublocation_DS,                       SUM(TotalListings) AS GrandTotal, SUM(CASE tlkpServiceProviders.Service_NM WHEN 'Hot Jobs' THEN TotalListings ELSE 0 END) AS HotJobsTotal,
                      SUM(CASE tlkpServiceProviders.Service_NM WHEN 'Monster' THEN TotalListings ELSE 0 END) AS MonsterTotal,
                      SUM(CASE tlkpServiceProviders.Service_NM WHEN 'Career Builder' THEN TotalListings ELSE 0 END) AS CareerBuilderTotal

FROM         dbo.tblData INNER JOIN
                      dbo.trelServiceURL ON dbo.tblData.URL_ID = dbo.trelServiceURL.URL_ID INNER JOIN
                      dbo.tlkpLocation ON dbo.trelServiceURL.Location_ID = dbo.tlkpLocation.Location_ID INNER JOIN
                      dbo.tlkpServiceProviders ON dbo.trelServiceURL.Service_ID = dbo.tlkpServiceProviders.Service_ID INNER JOIN
                      dbo.tlkpWeekEnding ON dbo.tblData.WeekEnding_ID = dbo.tlkpWeekEnding.WeekEnding_ID INNER JOIN
                      dbo.tblLocationSub_WebMapping ON dbo.tlkpLocation.Location_ID = dbo.tblLocationSub_WebMapping.Location_ID INNER JOIN
                      dbo.tblLocationSub_WebMapping_Detail ON
                      dbo.tblLocationSub_WebMapping.WebSublocation_ID = dbo.tblLocationSub_WebMapping_Detail.WebSublocation_ID INNER JOIN
                      dbo.tblLocationSub ON dbo.trelServiceURL.SubLocation_ID = dbo.tblLocationSub.SubLocation_ID AND
                      dbo.tlkpLocation.Location_ID = dbo.tblLocationSub.Location_ID AND
                      dbo.tblLocationSub_WebMapping_Detail.Sublocation_ID = dbo.tblLocationSub.SubLocation_ID

Where tlkpWeekEnding.weekending_id= @Weekending_ID

GROUP BY dbo.tlkpWeekEnding.WeekEnding_ID, dbo.tlkpWeekEnding.WeekEnding_DT, dbo.tlkpLocation.Location_ID, dbo.tlkpLocation.Location_NM,
                      dbo.tblLocationSub_WebMapping.WebSublocation_ID, dbo.tblLocationSub_WebMapping.WebSublocation_DS, dbo.tblLocationSub.SubLocation_ID,
                      dbo.tblLocationSub.SubLocation_DS