problem at the time of fetching data in sql server

Question

Now when i run this query

Select distinct slot_code,COUNT(MonoboxNo) capacit from wms_loose_transaction

Where Statustemp='PKL035311' and left(monoboxno,7)='1619199' and statustemp1='H' group by slot_code

I got SAFL21 20

but i need order wise like in above table i have two orders with same artno
not i need to fetch data order wise.

desired data SAFL21 10 for both orders.

Pls give me a query.(above image in attachment.you can download for reference)

Ravi Shekhar · Answer

i think this is the single option to do this. to add orderno in second table. okay sir. Thanks for help.

Deepak Verma · Answer

Ravi You can only get this output if you're able to determine the rows in second table corresponding to different OrderNo.

Iftikar Hussain · Answer

I like to know how you will recognize in your second table that some record belongs to order no 1 and some record belongs to order no 2. I suggest you better insert orderno details in second table while initially added data into these table Regards Iftikar

Ravi Shekhar · Answer

when i run Select lt.Aielse_Codecount(lt.Monoboxno) Capacity from wms_loose_transaction lt inner join wms_picking wp on wp.PickListNo=lt.Statustemp Where lt.Status='LOD' And Statustemp='PKL035311' and wp.OrderNo='010731252012' and left(monoboxno7)='1619199' and statustemp1='H' group by lt.aielse_codewp.OrderNo I got same result.

Ravi Shekhar · Answer

iftikhar sir i dont know how. i am stuck here. but with this record SAFL21 20 . I got dirty data in further operations.

Ravi Shekhar · Answer

CREATE TABLE [dbo].[wms_picking]( [PickListNo] [varchar](9) NOT NULL [OrderNo] [varchar](12) NOT NULL [ArtNo] [varchar](7) NOT NULL [Destination] [varchar](6) NOT NULL [Aielse_Code] [varchar](50) NOT NULL [Size] [varchar](10) NOT NULL [Source] [varchar](6) NULL [MatType] [char](2) NULL [Concept] [varchar](10) NULL [Status] [varchar](5) NULL [StatusLoad] [varchar](4) NULL [Category] [varchar](6) NULL [slotcode] [varchar](500) NULL [ActualQty] [int] NULL [ToPicked] [int] NULL [PickedQty] [int] NULL [PickedCaseQty] [int] NULL [LoadQty] [int] NULL [LoadCaseQty] [int] NULL [UnloadQty] [int] NULL [UnloadCaseQty] [int] NULL [Picker] [varchar](50) NULL [PickListCreatedOn] [datetime] NULL [PickListCreatedBy] [varchar](50) NULL [PickingOn] [datetime] NULL [PickingBy] [varchar](50) NULL [LoadedOn] [datetime] NULL [LoadedBy] [varchar](50) NULL [UnloadedBy] [varchar](50) NULL [UnloadedOn] [datetime] NULL [CancelDate] [datetime] NULL [Cancelby] [varchar](50) NULL [Remark] [varchar](50) NULL CONSTRAINT [PK_wms_picking] PRIMARY KEY CLUSTERED ( [PickListNo] ASC [OrderNo] ASC [ArtNo] ASC [Destination] ASC [Aielse_Code] ASC [Size] 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 SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[wms_picking] WITH CHECK ADD CONSTRAINT [FK_wms_picking_OrderDetails] FOREIGN KEY([OrderNo] [ArtNo] [Destination]) REFERENCES [dbo].[OrderDetails] ([OrderNo] [ArtNo] [Destination]) GO ALTER TABLE [dbo].[wms_picking] CHECK CONSTRAINT [FK_wms_picking_OrderDetails] GO ALTER TABLE [dbo].[wms_picking] ADD CONSTRAINT [DF_wms_picking_Picker] DEFAULT ((0)) FOR [Picker] GO CREATE TABLE [dbo].[wms_loose_transaction]( [OuterBoxNo] [varchar](25) NOT NULL [MonoboxNo] [varchar](23) NOT NULL [GateInNo] [varchar](10) NULL [concept] [varchar](50) NULL [category] [varchar](50) NULL [Status] [varchar](3) NULL [StatusTemp] [varchar](50) NULL [StatusTemp1] [varchar](50) NULL [aielse_code] [varchar](50) NULL [slot_code] [varchar](50) NULL [InvoiceNo] [varchar](15) NULL [lt_attribute2] [varchar](50) NULL [lt_attribute3] [varchar](50) NULL [lt_attribute4] [varchar](50) NULL [transaction_on] [datetime] NULL [transaction_by] [varchar](50) NULL [OKStatus] [varchar](50) NULL [OLDOUTERBOX] [varchar](50) NULL [REPACKSERIAL] [varchar](50) NULL [PRINTSERIALNO] [varchar](50) NULL [REPACKON] [datetime] NULL [REPACKBY] [varchar](50) NULL [putaway_on] [datetime] NULL [putaway_by] [varchar](50) NULL [picking_on] [datetime] NULL [picking_by] [varchar](50) NULL [loaded_on] [datetime] NULL [loaded_by] [varchar](50) NULL [steging_on] [datetime] NULL [steging_by] [varchar](50) NULL [grn_on] [datetime] NULL [grn_by] [varchar](50) NULL [letdown_on] [datetime] NULL [letdown_by] [varchar](50) NULL [hold_on] [datetime] NULL [hold_by] [varchar](50) NULL [StatusTemp2] [char](1) NULL [TempUserID] [varchar](50) NULL [cyclecountdone] [varchar](50) NULL [cyclecountdoneon] [datetime] NULL [cyclecountdoneby] [varchar](50) NULL [GRNNO] [varchar](10) NULL [VARIENCEFLAG] [varchar](15) NULL [UPLOADSTATUS] [varchar](10) NULL [OrderNo] [varchar](15) NULL [Capacity] [int] NULL [ArtCapacity] [int] NULL CONSTRAINT [PK_wms_loose_transaction] PRIMARY KEY CLUSTERED ( [OuterBoxNo] ASC [MonoboxNo] 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 SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[wms_loose_transaction] WITH CHECK ADD CONSTRAINT [FK_wms_loose_transaction_wms_aielse_master] FOREIGN KEY([aielse_code]) REFERENCES [dbo].[wms_aielse_master] ([aielse_code]) GO ALTER TABLE [dbo].[wms_loose_transaction] CHECK CONSTRAINT [FK_wms_loose_transaction_wms_aielse_master] GO ALTER TABLE [dbo].[wms_loose_transaction] WITH CHECK ADD CONSTRAINT [FK_wms_loose_transaction_wms_gatein_receiving_data1] FOREIGN KEY([GateInNo]) REFERENCES [dbo].[wms_gatein_receiving_data] ([GateInNo]) GO ALTER TABLE [dbo].[wms_loose_transaction] CHECK CONSTRAINT [FK_wms_loose_transaction_wms_gatein_receiving_data1] GO ALTER TABLE [dbo].[wms_loose_transaction] WITH CHECK ADD CONSTRAINT [FK_wms_loose_transaction_wms_slot_master] FOREIGN KEY([slot_code]) REFERENCES [dbo].[wms_slot_master] ([slot_code]) GO ALTER TABLE [dbo].[wms_loose_transaction] CHECK CONSTRAINT [FK_wms_loose_transaction_wms_slot_master] GO

Deepak Verma · Answer

Hi Ravi Add 'OrderNo' in your Group By Clause too.

Sunny Sharma · Answer

Hi Ravi Mention the Primary Keys of both the tables or better if you can share the schema.

Iftikar Hussain · Answer

without relationship how can you bring record from second table by orderno wise? Regards Iftikar

Ravi Shekhar · Answer

there is no relation of order no. orderno only exist in wms_picking. someone said to me we can do this by using temptable. but i dont know how.

Iftikar Hussain · Answer

Hi Please tell me what is the relationship of order number between two table. Regards Iftikar

Ravi Shekhar · Answer

above image in attachment. you can download for reference