sql query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql query help

Author

Topic

navbingo20
Starting Member

12 Posts

Posted - 2011-06-15 : 05:30:01

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

quote:

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-06-15 : 08:01:49

using a left join will display all the records from the master table...

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-06-15 : 09:17:48

You may need to use a calander table (Google this)
The following just gets a list of dates from the report table.


-- *** Test Data in Comsumable format ***
-- You should provide this.
CREATE TABLE #ven_machinemaster
(
	machine_id int NOT NULL
	,machine_name varchar(25) NOT NULL
)
INSERT INTO #ven_machinemaster
SELECT 1, 'abc'
UNION ALL SELECT 2, 'def'
UNION ALL SELECT 3, 'ghi'
UNION ALL SELECT 4, 'jkl'

CREATE TABLE #ven_fullreportmaster
(
	entry_date datetime NOT NULL
	,machine_id int NOT NULL
	,description_id int NOT NULL
	,tot_time int NOT NULL
	,shift_id int NOT NULL
)
INSERT INTO #ven_fullreportmaster 
SELECT '20110613', 1, 1, 10, 1
UNION ALL SELECT '20110613', 2, 2, 9, 2
UNION ALL SELECT '20110614', 1, 1, 10, 1
UNION ALL SELECT '20110614', 1, 2, 9, 2
UNION ALL SELECT '20110614', 3, 3, 5, 3
UNION ALL SELECT '20110614', 2, 4, 10, 1
UNION ALL SELECT '20110614', 2, 1, 9, 2
UNION ALL SELECT '20110614', 2, 5, 5, 3
-- *** End Test Data ***

;WITH Calendar
AS
(
	SELECT DISTINCT entry_date
	FROM #ven_fullreportmaster
)
SELECT C.entry_date, M.machine_id, M.machine_name
	,COALESCE(SUM(tot_time), 0) AS tot_time
FROM Calendar C
	CROSS JOIN #ven_machinemaster M
	LEFT JOIN #ven_fullreportmaster R
		ON C.entry_date = R.entry_date
			AND M.machine_id = R.machine_id
--WHERE C.entry_date = '20110614'
GROUP BY C.entry_date, M.machine_id, M.machine_name
ORDER BY entry_date, machine_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources