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
Try IsNull as the criteria for date last sold in an SQL query.
I use (Item Table) DateCreated > Date() for a new item list.
Marc
Great thanks Matt & Marc.
What about reports? What should I use to show a blank date on a report?
"Matt Hurst" wrote:
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
//--- Filters ---//
//--- Columns ---//
Begin Column FieldName = "storeitemlastsold.CategoryName" DrillDownFieldName = "Category.Name" DrillDownReportName = "" StoreIDFieldName = "" Title = "Category" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 2385 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "storeitemlastsold.DepartmentName" DrillDownFieldName = "Department.Name" DrillDownReportName = "" StoreIDFieldName = "" Title = "Department" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 2205 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "storeitemlastsold.ItemLookupCode" DrillDownFieldName = "Item.ItemLookupCode" DrillDownReportName = "" StoreIDFieldName = "" Title = "Item Lookup Code" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1035 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "storeitemlastsold.ItemDescription" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Description" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1230 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "storeitemlastsold.LastSold" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "LastSold" VBDataType = vbDate Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1020 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "storeitemlastsold.DateCreated" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "DateCreated" VBDataType = vbDate Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1020 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "storeitemlastsold.LastReceived" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "LastReceived" VBDataType = vbDate Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1020 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "storeitemlastsold.Cost" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Cost" VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1020 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "storeitemlastsold.Quantity" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Quantity" VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1020 GroupMethod = groupmethodSum ColFormat = "" End Column
begin 666 Memorized-Item Never Sold.qrp M+R\M+2T@4F5P;W)T(%-U;6UA
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.