i building pretty simple inventory items database allow me check out items between dates. need return single row tell me available, reserved, , total inventory of item. cannot seem correct.
begin tran declare @startdate date declare @enddate date declare @partid int set @startdate = '4/15/2015' set @enddate = '4/25/2015' set @partid = 248 select count(ii.[partid] ii.[partstatus] = 1 ) [available], count(ii.[partid] ii.[partstatus] = 2 ) [reserverd], count(ii.[partid] ii.[partstatus] <> 4 ) [totalinventory], shiplistinventory.dbo.inventoryitems ii left join shiplistinventory.dbo.inventoryitemcalendars iic on iic.[itemid] = ii.[id] iic.[startdate] not between @startdate , @enddate , iic.[inbounddate] not between @startdate , @enddate , ii.[partid] = 248 commit tran edit: returning rows inventoryitemcalendarsi want return items inventoryitems
create table [dbo].[inventoryitems]( [id] [int] identity(1,1) not null, [partsatuts] [int] not null, [partid] [int] not null, [barcode] [nvarchar](max) null, [picturepath] [nvarchar](255) null, [notes] [nvarchar](1000) null, constraint [pk_dbo.inventoryitems] primary key clustered ( [id] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary] textimage_on [primary] go alter table [dbo].[inventoryitems] check add constraint [fk_dbo.inventoryitems_dbo.parts_partid] foreign key([partid]) references [dbo].[parts] ([id]) on delete cascade go alter table [dbo].[inventoryitems] check constraint [fk_dbo.inventoryitems_dbo.parts_partid] go create table [dbo].[inventoryitemcalendars]( [id] [int] identity(1,1) not null, [startdate] [datetime] not null, [inbounddate] [datetime] not null, [projectnumber] [int] not null, [itemid] [int] not null, [project_id] [int] null, constraint [pk_dbo.inventoryitemcalendars] primary key clustered ( [id] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary] go alter table [dbo].[inventoryitemcalendars] check add constraint [fk_dbo.inventoryitemcalendars_dbo.inventoryitems_itemid] foreign key([itemid]) references [dbo].[inventoryitems] ([id]) on delete cascade go alter table [dbo].[inventoryitemcalendars] check constraint [fk_dbo.inventoryitemcalendars_dbo.inventoryitems_itemid] go alter table [dbo].[inventoryitemcalendars] check add constraint [fk_dbo.inventoryitemcalendars_dbo.projects_project_id] foreign key([project_id]) references [dbo].[projects] ([id]) go alter table [dbo].[inventoryitemcalendars] check constraint [fk_dbo.inventoryitemcalendars_dbo.projects_project_id] go insert shiplistinventory.[dbo].[inventoryitems]([partsatuts],[partid]) values(1,1); insert shiplistinventory.[dbo].[inventoryitems]([partsatuts],[partid]) values(2,1); insert shiplistinventory.[dbo].[inventoryitems]([partsatuts],[partid]) values(1,1); insert shiplistinventory.[dbo].[inventoryitems]([partsatuts],[partid]) values(4,1); insert shiplistinventory.[dbo].[inventoryitemcalendars](startdate,inbounddate,project_id, itemid)values('1/1/2015', '4/15/2015',1,1) insert shiplistinventory.[dbo].[inventoryitemcalendars](startdate,inbounddate,project_id, itemid)values('1/1/2015', '4/15/2015',1,1) insert shiplistinventory.[dbo].[inventoryitemcalendars](startdate,inbounddate,project_id, itemid)values('1/1/2015', '4/14/2015',1,2) insert shiplistinventory.[dbo].[inventoryitemcalendars](startdate,inbounddate,project_id, itemid)values('1/1/2015', '4/15/2015',1,1) this isnt returning data correct table returning data form inventoryitemcalendars isnt want. want use check dates. should return items inventoryitems table
here syntactically correct version:
select sum(case when ii.[partstatus] = 1 1 else 0 end) [available], sum(case when ii.[partstatus] = 2 1 else 0 end) [reserverd], sum(case when ii.[partstatus] <> 4 1 else 0 end) [totalinventory] shiplistinventory.dbo.inventoryitems ii left join shiplistinventory.dbo.inventoryitemcalendars iic on iic.[itemid] = ii.[id] iic.[startdate] not between @startdate , @enddate , iic.[inbounddate] not between @startdate , @enddate , ii.[partid] = 248; there no transaction when select, committing not necessary.
Comments
Post a Comment