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
3:25:20 PM
|
|