help me in forming an sql query

Question

i have two tables .. one is a master table and other one is an daily report table.

Master table :

 machine_id  Machinename 
    1           abc
    2           def
    3           ghi
    4           jkl

ven_fullreportmaster :

entry_date   machine_id  description_id  tot_time   shift_id  
 20110613      1             1             10         1
 20110613      2             2              9         2
 20110614      1             1             10         1
 20110614      1             2              9         2
 20110614      3             3              5         3
 20110614      2             4             10         1
 20110614      2             1              9         2
 20110614      2             5              5         3

now, i want to retrieve the data from the daily report table that it should contain all the machine names with tot_time and entry_date..

i have used this query to retrieve the data,

 select  entry_date,           
  machinename,                
   (IsNull(cast(TotalMins / 60 as varchar(24)),'0') + ':' +  IsNull(cast(TotalMins % 60 as varchar(24)),'0'))   as TotalHrs--, shift_type                
    from    (           
    select  vm.machinename , vfrm.entry_date,                
    sum(case when vfrm.description_id in ('1','2','3','4','5') then DateDiff(mi, 0, total_time) else '0'  end) as TotalMins
   --vsm.shift_type            
    from    ven_fullreportmaster vfrm      
                    inner join  ven_machinemaster vm   on  vm.machine_id = vfrm.machine_id
                    inner join ven_shifttypemaster vsm on vsm.shift_id = vfrm.shift_id          
                      where  vfrm.entry_date = '20110614' 
                        -- and  vfrm.shift_id in (1,2,3)                                
    group by   machinename, vfrm.entry_date --, vsm.shift_type         
         ) as SubQueryALias  group by  entry_date, machinename,TotalMins --,shift_type

when i run the above query, i am getting details for machine-id 1 , 2,3 alone..

output:

   entry_date            machineid  TotalHrs
2011-06-14 00:00:00.000     1              19:0
2011-06-14 00:00:00.000     2              24:0
2011-06-14 00:00:00.000     3              5:0

i need to get machine_id =4 value as 0 in TotalMins for each shift.. how to resolve it..plz help me ..

expected output:

 entry_date          machineid  TotalHrs
2011-06-14 00:00:00.000     1              19:0
2011-06-14 00:00:00.000     2              24:0
2011-06-14 00:00:00.000     3              5:0
**2011-06-14 00:00:00.000       4              0:0**

thanks and regards

T.Navin

enter image description here

output:

enter image description here

William Lawn Stewart · Accepted Answer · 2011-06-15 08:55:20Z

2

Try using left joins instead of inner joins, that should get machine #3 to appear even though it has no entries in the second table.

answered Jun 15, 2011 at 8:55

William Lawn Stewart

1,2051 gold badge12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

navbingo Over a year ago

@william: i have tried left /right outer join, it didn't work for me

William Lawn Stewart Over a year ago

Thats odd. I've put together something vaguely similar in my SQL server, and it seems to work fine with a left join.

navbingo Over a year ago

@william : i have tried left, right, full but still its not working.. i have atttached the image for you , the total in the master table is 15, but i am getting only 13 rows...

William Lawn Stewart Over a year ago

@navbingo: Maybe its being filtered out because of the where entrydate = 'stuff'?. Thats the only other possibility I can think of, if its not that then I don't really have a clue.

navbingo Over a year ago

@william: for example, machine id =4 wont be available in ven_fullreportmaster table on the specified date, but in the result set it should show machine id =4 and total time as 0

|

Lieven Keersmaekers · Accepted Answer · 2011-06-15 14:27:20Z

2

You are searching for records with a specific date but there is no entry for 20110614 in your reporting table.

One solution is to add records to your select using a UNION with an initial SUM of'0'.

These fake records will not throw off your existing SUM.
They will show up where there's missing data with '0'.

SQL Statement

SELECT  entry_date
        , machinename
        , (ISNULL(CAST(TotalMins / 60 AS VARCHAR(24)),'0') + ':' +  ISNULL(CAST(TotalMins % 60 AS VARCHAR(24)),'0')) AS TotalHrs--, shift_type                
 FROM   (           
            SELECT  vm.machinename 
                    , vfrm.entry_date
                    ,  SUM(case when vfrm.description_id in ('1','2','3','4','5') THEN DATEDIFF(mi, 0, total_time) else '0' END) AS TotalMins --vsm.shift_type            
            FROM    ven_fullreportmaster vfrm      
                    INNER JOIN ven_machinemaster vm on  vm.machine_id = vfrm.machine_id
                    INNER JOIN ven_shifttypemaster vsm on vsm.shift_id = vfrm.shift_id          
                    WHERE   vfrm.entry_date BETWEEN '20110614' AND  '20110615' 
            GROUP BY
                    machinename
                    , vfrm.entry_date --, vsm.shift_type
            UNION ALL
            SELECT  DISTINCT vm.machinename
                    , vfrm.entry_date
                    , '0'
            FROM    ven_machinemaster vm
                    CROSS APPLY ven_fullreportmaster vfrm
            WHERE   vfrm.entry_date BETWEEN '20110614' AND  '20110615' 
         ) AS SubQueryALias  
GROUP BY
        entry_date
        , machinename
        , TotalMins --,shift_type

edited Jun 15, 2011 at 14:27

answered Jun 15, 2011 at 11:17

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

2 Comments

navbingo Over a year ago

the solution works fine if i select a single date..thnx alot..if i want to search between a range of dates it is not working

Lieven Keersmaekers Over a year ago

@navbingo - I have altered the statement. Could you try it out?

ypercubeᵀᴹ · Accepted Answer · 2011-06-15 20:40:38Z

1

Instead of:

from  ven_fullreportmaster vfrm      
        inner join  ven_machinemaster vm   on  vm.machine_id = vfrm.machine_id
        inner join ven_shifttypemaster vsm on vsm.shift_id = vfrm.shift_id  
            where  vfrm.entry_date = '20110614'

can you try this LEFT JOIN ? Note the moving of the condition from WHERE to the ON clause:

FROM  ven_machinemaster vm    
    LEFT JOIN  ven_fullreportmaster vfrm    
        ON   vm.machine_id = vfrm.machine_id
        AND  vfrm.entry_date = '20110614' 
    INNER JOIN ven_shifttypemaster vsm
        ON   vsm.shift_id = vfrm.shift_id

or:

FROM  ven_machinemaster vm    
    LEFT JOIN  ven_fullreportmaster vfrm    
        ON   vm.machine_id = vfrm.machine_id
        AND  vfrm.entry_date = '20110614' 
    LEFT JOIN ven_shifttypemaster vsm
        ON   vsm.shift_id = vfrm.shift_id

edited Jun 15, 2011 at 20:40

answered Jun 15, 2011 at 12:06

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

2 Comments

ypercubeᵀᴹ Over a year ago

@navbingo: And if you turn both JOINs into LEFT JOIN ones?

navbingo Over a year ago

option 2 works well for a single date thnx alot.. if i want to use for a range of dates its not working...

Collectives™ on Stack Overflow

help me in forming an sql query

Master table :

ven_fullreportmaster :

output:

expected output:

output:

3 Answers 3

7 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Master table :

ven_fullreportmaster :

output:

expected output:

output:

3 Answers 3

7 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related