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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query

Author  Topic 

Markus_B
Starting Member

6 Posts

Posted - 2014-09-22 : 08:52:06
Hi All,

Well I have again a question on how I can create a Select statement :-(

The table sturcture looks like this

Timestamp Machine Condtion Duration
--------- -------------------------
2014-01-04 05:55:00 A1 3 5
2014-01-04 06:55:00 A1 2 10
2014-01-04 07:55:00 A1 3 5
2014-01-04 09:55:00 A1 5 17
2014-02-04 05:55:00 A1 3 5
2014-02-04 06:55:00 A2 2 10
2014-02-04 07:55:00 A1 3 5
2014-02-04 09:55:00 A2 5 17

The problem now is to create a query where I get a result like this

Day,Machine,Sum of Condition1,Sum of Condtion 2 ....Sum Condition5

And that for a specific time range..

Hope someone can help there

THX

M.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-09-22 : 09:48:40
Maybe something like this. If not, then please provide consumable test data and expected results.

-- *** Test Data ***
CREATE TABLE #t
(
StartTime datetime NOT NULL
,Machine char(2) NOT NULL
,Condition int NOT NULL
,Duration int NOT NULL
);
INSERT INTO #t
VALUES('20140104 05:55:00', 'A1', 3, 5)
,('20140104 06:55:00', 'A1', 2, 10)
,('20140104 07:55:00', 'A1', 3, 5)
,('20140104 09:55:00', 'A1', 5, 17)
,('20140204 05:55:00', 'A1', 3, 5)
,('20140204 06:55:00', 'A2', 2, 10)
,('20140204 07:55:00', 'A1', 3, 5)
,('20140204 09:55:00', 'A2', 5, 17);
-- *** End Test Data ***

SELECT StartTime, Machine
,COALESCE([1], 0) AS Sum1
,COALESCE([2], 0) AS Sum2
,COALESCE([3], 0) AS Sum3
,COALESCE([4], 0) AS Sum4
,COALESCE([5], 0) AS Sum5
FROM
(
SELECT DATEADD(day, DATEDIFF(day, 0, StartTime), 0) AS StartTime
,Machine, Condition, Duration
--WHERE StartTime - DATEDIFF(day, 0, StartTime) BETWEEN '19000101 01:30' AND '19000101 22:30'
FROM #t
) S
PIVOT
(
SUM (Duration)
FOR Condition IN ([1],[2],[3],[4],[5])
) AS P
ORDER BY StartTime, Machine;
Go to Top of Page

Markus_B
Starting Member

6 Posts

Posted - 2014-09-23 : 02:13:09
Looks great so far and it's working...
Is it alos possible to insert in the select part a calculation for each ID?
What I mean is for example ID2. When there is now entry for one day, it should be search for the entry before and takes a defined value for this day?
The other question is .. how can I take for example ID2 from another table? Is this also psossible?

Thanks

Markus
Go to Top of Page

Markus_B
Starting Member

6 Posts

Posted - 2014-09-23 : 04:31:33
Forget my last post......
The only two Problems that I have now is to use a where clause in this Statement where I can select a daterange and how I can take the summary of ID2 from another table within this daterange.
In principle this statemanet is working except this two issues....

Hope you can help me there..

Many thanks
Markus
Go to Top of Page

Markus_B
Starting Member

6 Posts

Posted - 2014-09-23 : 06:13:30
the daterange issue is now also fixed...
Only the Problem to get the data for IDfrom another Table makes me crazy :-(
Go to Top of Page
   

- Advertisement -