Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    1

    Unanswered: Problem in making SQL Query

    This is a textile mil project, Loom is machine which is use to make cloth.
    The company have 10 LOOMs which run 24 hours a day(Shift A, Shift B and Shift C). Making of cloth
    Is called production of machine, now a query is required that when it run show result like that manner
    Date LOOM1 LOOM2 LOOM3 ...... LOOM10

    1-NOV-2010 222 434 434 .. 4343
    2-NOV-2010 422 334 834 .. 4543
    3-NOV-2010 252 534 634 .. 6343
    .
    .
    .
    30-NOV-2010 242 234 439 .. 3343

    Table and sample report is attached with this message.
    Kindly tell me query.
    I write this query
    SELECT
    loomid, SUM(Production) TotalProduction FROM UnitsDocument
    GROUP
    BY date, loomid
    HAVING
    date BETWEEN convert(datetime,date,103) AND convert(datetime,date103);

    but this query return this result.

    loomid TotalProduction SateTimeStamp
    2 10 2010-11-01 00:00:00.000
    2 1 2010-11-02 00:00:00.000
    2 0 2010-11-03 00:00:00.000
    2 10 2010-11-04 00:00:00.000
    2 5 2010-11-05 00:00:00.000
    3 0 2010-11-06 00:00:00.000
    3 0 2010-11-07 00:00:00.000
    3 20 2010-11-08 00:00:00.000
    4 5 2010-11-09 00:00:00.000
    4 10 2010-11-10 00:00:00.000
    4 20 2010-11-11 00:00:00.000
    5 12 2010-11-12 00:00:00.000


    It is showing date wise n loomID wise, but I want to display individual sum of each loom date wise
    Attached Thumbnails Attached Thumbnails LoomDetail.GIF   table.jpg  

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Have you tried to just group by loomid
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36
    Try (note- NOT tested):

    SELECT date,loomid, SUM(Production) TotalProduction FROM UnitsDocument
    GROUP BY date, loomid
    ORDER BY date, loomid


    if your wanting to restrict by date:
    SELECT date,loomid, SUM(Production) TotalProduction FROM UnitsDocument
    WHERE date between '2010-01-01' and '2010-06-30' --For example!
    GROUP BY date, loomid
    ORDER BY date, loomid

    Cheers,
    Marc

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME
    SET @StartDate = convert(datetime, '01/11/2010', 103)
    SET @EndDate = convert(datetime, '30/11/2010', 103)
    
    PRINT @StartDate
    PRINT @EndDate
    
    SELECT date, 
    	SUM(CASE WHEN LoomId = 1 THEN Production ELSE 0 END) as Loom#1,
    	SUM(CASE WHEN LoomId = 2 THEN Production ELSE 0 END) as Loom#2,
    	SUM(CASE WHEN LoomId = 3 THEN Production ELSE 0 END) as Loom#3,
    -- ...	
    	SUM(CASE WHEN LoomId = 10 THEN Production ELSE 0 END) as Loom#10
    FROM UnitsDocument
    WHERE date BETWEEN @StartDate AND @EndDate
    GROUP BY date;
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •