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</h2>
 
Id  chemical
7   Aluminium Metal Foil
8   Aluminium Metal Powder
9   Iodine
------------------------------------------------
 

2.Stock</h2>
 
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</h2>
 
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 490
1 Sergey Alexandrovich Kryukov 395
2 Maciej Los 285
3 ProgramFOX 265
4 CHill60 200
0 OriginalGriff 490
1 Sergey Alexandrovich Kryukov 395
2 Maciej Los 285
3 ProgramFOX 265
4 CHill60 200


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