What is the sql command for a blank date? What should I use to show a blank date on a report? We new items are added the have a blank last sold date. I would like to run a report for items that have not sold. Any ideas? thanks
If this is a store query, then the answer is easy:
select * from item where lastsold is null
If you are trying to get this info from HQ, you will need to use a view, since the detail is in itemdynamic by each store, and doesn't update the main item table.
create view storeitemlastsold as select item.id, description,itemlookupcode,max(d.lastsold) lastsold from item left join itemdynamic d on item.id=itemid group by item.id, description,itemlookupcode
then you can query from the view with this:
select * from storeitemlastsold where lastsold is null
See the use of Is Null in the SelCriteria of this example HQ report: (beware of line wrap if trying to copy and paste)
//--- Report Summary --- //
Begin ReportSummary ReportType = reporttypeItems ReportTitle = "Item Never Sold" PageOrientation = pageorientationPortrait WordWrap = False ShowDateTimePicker = False OutLineMode = True Groups = 1 GroupDescription = "" DisplayLogo = True LogoFileName = "MyLogo.bmp" ProcedureCall = "" PreQuery1 = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'storeitemlastsold') DROP VIEW storeitemlastsold" PreQuery2 CREATE VIEW storeitemlastsold AS SELECT Department.Name as DepartmentName, Category.Name as CategoryName, Item.ItemLookupCode AS ItemLookupCode, Item.Description AS ItemDescription, Item.DateCreated AS DateCreated, Item.Quantity AS Quantity, Item.Cost AS Cost, Max(ItemDynamic.LastReceived) AS LastReceived, Max(ItemDynamic.LastSold) AS LastSold
FROM Item WITH(NOLOCK) LEFT JOIN ItemDynamic WITH(NOLOCK) ON ItemDynamic.ItemID Item.ID LEFT JOIN Department WITH(NOLOCK) ON Item.DepartmentID Department.ID LEFT JOIN Category ON Item.CategoryID = Category.ID
GROUP BY Department.Name, Category.Name, ItemLookupCode, Description, Item.DateCreated, Item.Quantity, Item.Cost
TablesQueried = "FROM storeitemlastsold" SelCriteria = "storeitemlastsold.LastSold Is Null" GroupBy = "" SortOrder = "" End ReportSummary
//--- Title Rows ---//
Begin TitleRow Text = "" Font = "Arial" FontBold = True FontSize = 16 Color = "Blue" End TitleRow
Begin TitleRow Text = "" Font = "Arial" FontBold = True FontSize = 12 Color = "Black" End TitleRow
Begin TitleRow Text = "Generated On " Font = "Arial" FontBold = True FontSize = 10 Color = "Black" End TitleRow
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here.
All logos and trade names are the property of their respective owners.