Click here to Skip to main content
Rate this: bad
good
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 at 5-Mar-13 7:34am
   
You need to list only one Chemical or all chemicals at a time?
Nikhil Bhivgade at 6-Mar-13 3:07am
   
all chemical
gvprabu at 6-Mar-13 3:34am
   
I updated the answer.... It will solve ur problem. Try that and get back to me
Nikhil Bhivgade at 6-Mar-13 5:15am
   
thanks...i will get back 2 u..if any problem occurs..
Nikhil Bhivgade at 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 at 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 at 5-Mar-13 7:42am
   
Check my post
Nikhil Bhivgade at 6-Mar-13 6:13am
   
Arithmetic overflow error converting varchar to data type numeric.
gvprabu at 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 at 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
0 OriginalGriff 304
1 Sergey Alexandrovich Kryukov 255
2 Shweta N Mishra 216
3 Maciej Los 210
4 PIEBALDconsult 184
0 OriginalGriff 7,630
1 Sergey Alexandrovich Kryukov 7,022
2 DamithSL 5,586
3 Manas Bhardwaj 4,946
4 Maciej Los 4,525


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 6 Mar 2013
Copyright © CodeProject, 1999-2014
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