Click here to Skip to main content
12,066,626 members (56,329 online)
Rate this:
 
Please Sign up or sign in to vote.
i have 3 table
1.Chemical
--------------------------------------------------
Id	chemical
7	Aluminium Metal Foil
8	Aluminium Metal Powder
9	Iodine
------------------------------------------------

2.Stock
------------------------------------------------
Id	Date	    PONo	ChemicalId	Grade	   QuantityA	QuantityB	Unit
1	1/10/2013 QL/PO/1303001	8	             GR	       1	1000	         gm
2	1/10/2013 QL/PO/1303001	9	               GR	1	1000	          gm
----------------------------------------------------------------------------------------------

3.Consumed
----------------------------------------------------------
Id	date	ChemicalId	Grade	Quantity	units
1	3/2/2013 0:00	8	GR	200	gm
2	3/5/2013 0:00	8	GR	200	gm
3	3/1/2013 0:00	9	GR	300	gm
4	3/4/2013 0:00	9	GR	300	gm
 
----------------------------------------------------------------

1st table is chemical master 2nd one is chemicalstock when i purchase the chemical add to stock and 3rd table is consumption

i want a query which out like stock register

Date	   chemical	Units	Stock	Consumed
1/10/2013 	8	gm	1000	-
3/2/2013 	8	gm	-	200
3/5/2013 	8	gm	-	300
------------------------------------------------	


plz help.
Posted 5-Mar-13 2:19am
v2
Comments
gvprabu 5-Mar-13 7:34am
   
You need to list only one Chemical or all chemicals at a time?
Nikhil Bhivgade 6-Mar-13 3:07am
   
all chemical
gvprabu 6-Mar-13 3:34am
   
I updated the answer.... It will solve ur problem. Try that and get back to me
Nikhil Bhivgade 6-Mar-13 5:15am
   
thanks...i will get back 2 u..if any problem occurs..
Nikhil Bhivgade 6-Mar-13 6:21am
   
i m giving u table structure
-------------------------------------------
CREATE TABLE [dbo].[Consumption](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ConsumeDate] [smalldatetime] NULL,
[ChemicalId] [int] NULL,
[Grade] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Quantity] [decimal](18, 0) NULL,
[Unit] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
------------------------------------------------------------------
CREATE TABLE [dbo].[Recevied](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RecivedDate] [smalldatetime] NULL,
[PONO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ChallanNo] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ChemicalId] [int] NULL,
[Grade] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[QuantityA] [decimal](18, 0) NULL,
[QuantityB] [decimal](18, 0) NULL,
[Units] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ManufacturerName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Items] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Hi

Try this...
SELECT T.Date, T.chemical, T.Unit, T.Stock, T.Consumed
FROM (SELECT Date, ChemicalId 'chemical', Unit, CAST(QuantityB AS VARCHAR(15)) 'Stock', '-' 'Consumed', 1 'DispOrder'
      FROM Stock
      UNION ALL 
      SELECT date, ChemicalId 'chemical', units, '-' 'Stock', CAST(Quantity AS VARCHAR(15)) 'Consumed', 2 'DispOrder'
      FROM Consumed) T 
ORDER BY T.chemical, T.DispOrder, T.DateC
Regards,
GVPrabu
  Permalink  
v4
Comments
Nikhil Bhivgade 5-Mar-13 7:37am
   
thier can be n number of chemical like 8,9,10------ n then i need to group by them
gvprabu 5-Mar-13 7:42am
   
Check my post
Nikhil Bhivgade 6-Mar-13 6:13am
   
Arithmetic overflow error converting varchar to data type numeric.
gvprabu 6-Mar-13 6:16am
   
This is because of UNION ALL Query... I update Check now
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Try this one
SELECT t.Date,
       t.ChemicalId                AS Chemical,
       t.Unit,
       ISNULL(t.StockQty, '-')     AS Stock,
       ISNULL(t.ConsumedQty, '-')  AS Consumed
FROM   (
           SELECT DISTINCT s.Date,
                  ChemicalId,
                  Unit,
                  CAST(Quantity AS VARCHAR)  AS StockQty
           FROM   Stock s
                  INNER JOIN (
                           SELECT DISTINCT c.Date,
                                  ChemicalId,
                                  Unit,
                                  CAST(Quantity AS VARCHAR) AS ConsumedQty
                           FROM   Consumed c
                       )    AS t2
                       ON  t1.ChemicalId = t2.ChemicalId
                       AND t.Date = t2.Date
       )                           AS t
  Permalink  
Comments
Nikhil Bhivgade 6-Mar-13 6:51am
   
i have given table structure plz have a look.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

i had used this query

SELECT     Date,chemical, Unit, Stock, Consumed
FROM         (SELECT     RecivedDate AS 'Date', ChemicalId AS 'chemical', Units as 'Unit',CAST(QuantityB AS VARCHAR)  AS    'Stock', '-' AS 'Consumed'
                       FROM          Recevied
                       UNION ALL
                       SELECT     ConsumeDate AS 'Date', ChemicalId AS 'chemical', Unit, '-' AS 'Stock', CAST(Quantity AS VARCHAR) AS 'Consumed'
                       FROM         Consumption ) AS T

thanks 2 all for ur answer , i wont be able to get it.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160208.1 | Last Updated 6 Mar 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100