---
title: "Summera"
type: "forum-thread"
url: "https://www.webforum.nu/amne/databaser-sql/174443-summera"
topic: "Databaser & SQL"
topic_url: "https://www.webforum.nu/amne/databaser-sql"
author: "devotion"
published: "2008-09-30T18:25:35.000Z"
updated: "2008-10-01T09:41:31.000Z"
replies: 7
views: 528
page: 1
pages: 1
language: "sv-SE"
site: "webForum — webforum.nu"
rights: "Upphovsrätten till varje inlägg tillhör dess författare."
attribution: "Citera som: webForum, https://www.webforum.nu/amne/databaser-sql/174443-summera"
---

# Summera

## #1 — devotion, 2008-09-30T18:25Z

Hej!

Jag har följande sql-fråga, som summerar ett antal tidtyper

```
SELECT COALESCE(SUM(tblTime.[Time]),0) AS Hours 
FROM tblcustomer 
	INNER JOIN tblTask ON tblCustomer.[Nr] = tblTask.CustomerNr 
		LEFT JOIN tblTime ON tblTask.[Id] = tblTime.TaskId 
		LEFT JOIN tblTimeType ON tblTimeType.[Id] = tblTime.TimeTypeId 
WHERE tblCustomer.Category = 'Sigtuna' 
	And tblTime.StartDate >='2008-06-01 00:00:00.000'
	And tblTime.StartDate < '2008-06-30 23:59:59.999' 
	And convert(varchar(8),startDate,8) between '07:30:00' and '16:30:00'
	And 
		( 
		[tblTimeType].[Name] like '01%' Or
		[tblTimeType].[Name] like '02%' Or
		[tblTimeType].[Name] like '03%' Or
		[tblTimeType].[Name] like '04%' Or
		[tblTimeType].[Name] like '05%' Or
		[tblTimeType].[Name] like '06%' Or
		[tblTimeType].[Name] like '07%' Or
		[tblTimeType].[Name] like '08%'
		)
```

Summan blir: 926,14 timmar

Om jag tar bort den nedre where-delen enligt:

```
SELECT COALESCE(SUM(tblTime.[Time]),0) AS Hours 
FROM tblcustomer 
	INNER JOIN tblTask ON tblCustomer.[Nr] = tblTask.CustomerNr 
		LEFT JOIN tblTime ON tblTask.[Id] = tblTime.TaskId 
		LEFT JOIN tblTimeType ON tblTimeType.[Id] = tblTime.TimeTypeId 
WHERE tblCustomer.Category = 'Sigtuna' 
	And tblTime.StartDate >='2008-06-01 00:00:00.000'
	And tblTime.StartDate < '2008-06-30 23:59:59.999' 
	And convert(varchar(8),startDate,8) between '07:30:00' and '16:30:00'
```

Så blir summan: 971,44 timmar

Differensen blir 45,3 timmar

Hur gör jag om ovanstående SQL-fråga, så att jag får ut 45,3 timmar?

Mvh
Henrik

Permalänk: https://www.webforum.nu/p/174443

## #2 — Peter S, 2008-09-30T19:07Z

Ställ båda frågorna och ta differensen mellan deras resultat ;)

Om vi vänder litet på det: Vad har de poster som utgör de 45,3 timmarna gemensamt? Kan vi hitta på en helt ny fråga utifrån vad vi vet om dessa?

Permalänk: https://www.webforum.nu/p/2144743

## #3 — devotion, 2008-09-30T19:10Z

Hej!

Detta är gemensamt:

```
[tblTimeType].[Name] like '01%' Or
		[tblTimeType].[Name] like '02%' Or
		[tblTimeType].[Name] like '03%' Or
		[tblTimeType].[Name] like '04%' Or
		[tblTimeType].[Name] like '05%' Or
		[tblTimeType].[Name] like '06%' Or
		[tblTimeType].[Name] like '07%' Or
		[tblTimeType].[Name] like '08%'
```

Dvs alla poster som inte matchas får träff på ovanstående!

Mbh
Henrik

Permalänk: https://www.webforum.nu/p/2144747

## #4 — Peter S, 2008-09-30T19:25Z

Men enbart de villkoren ger inte en summa på 45,3. Frågan är vad posterna mer har gemensamt?

Annars kan du lösa det på t.ex. följande vis:

```
select sum(t.time) as foo
  from tbltime t
 where t.id in
(
SELECT tblTime.[id]
FROM tblcustomer 
	INNER JOIN tblTask ON tblCustomer.[Nr] = tblTask.CustomerNr 
		LEFT JOIN tblTime ON tblTask.[Id] = tblTime.TaskId 
		LEFT JOIN tblTimeType ON tblTimeType.[Id] = tblTime.TimeTypeId 
WHERE tblCustomer.Category = 'Sigtuna' 
	And tblTime.StartDate >='2008-06-01 00:00:00.000'
	And tblTime.StartDate < '2008-06-30 23:59:59.999' 
	And convert(varchar(8),startDate,8) between '07:30:00' and '16:30:00'
EXCEPT
SELECT tblTime.[id]
FROM tblcustomer 
	INNER JOIN tblTask ON tblCustomer.[Nr] = tblTask.CustomerNr 
		LEFT JOIN tblTime ON tblTask.[Id] = tblTime.TaskId 
		LEFT JOIN tblTimeType ON tblTimeType.[Id] = tblTime.TimeTypeId 
WHERE tblCustomer.Category = 'Sigtuna' 
	And tblTime.StartDate >='2008-06-01 00:00:00.000'
	And tblTime.StartDate < '2008-06-30 23:59:59.999' 
	And convert(varchar(8),startDate,8) between '07:30:00' and '16:30:00'
	And 
		( 
		[tblTimeType].[Name] like '01%' Or
		[tblTimeType].[Name] like '02%' Or
		[tblTimeType].[Name] like '03%' Or
		[tblTimeType].[Name] like '04%' Or
		[tblTimeType].[Name] like '05%' Or
		[tblTimeType].[Name] like '06%' Or
		[tblTimeType].[Name] like '07%' Or
		[tblTimeType].[Name] like '08%'
		)
)
```

Permalänk: https://www.webforum.nu/p/2144754

## #5 — devotion, 2008-09-30T19:33Z

Men, det som är gemensamt är ju att posterna INTE innehåller 01, 02, 03 osv...

Eller är jag trög? :)

Permalänk: https://www.webforum.nu/p/2144758

## #6 — Peter S, 2008-09-30T19:44Z

I sådana fall är det följande du vill ha:

```
SELECT COALESCE(SUM(tblTime.[Time]),0) AS Hours 
FROM tblcustomer 
	INNER JOIN tblTask ON tblCustomer.[Nr] = tblTask.CustomerNr 
		LEFT JOIN tblTime ON tblTask.[Id] = tblTime.TaskId 
		LEFT JOIN tblTimeType ON tblTimeType.[Id] = tblTime.TimeTypeId 
WHERE tblCustomer.Category = 'Sigtuna' 
	And tblTime.StartDate >='2008-06-01 00:00:00.000'
	And tblTime.StartDate < '2008-06-30 23:59:59.999' 
	And convert(varchar(8),startDate,8) between '07:30:00' and '16:30:00'
	And [red]Not[/red]
		( 
		[tblTimeType].[Name] like '01%' Or
		[tblTimeType].[Name] like '02%' Or
		[tblTimeType].[Name] like '03%' Or
		[tblTimeType].[Name] like '04%' Or
		[tblTimeType].[Name] like '05%' Or
		[tblTimeType].[Name] like '06%' Or
		[tblTimeType].[Name] like '07%' Or
		[tblTimeType].[Name] like '08%'
		)
```

Permalänk: https://www.webforum.nu/p/2144762

## #7 — devotion, 2008-09-30T20:10Z

Ibland är lösningen enklare än man tror....

Tack! :)   

Mvh
Henrik

Permalänk: https://www.webforum.nu/p/2144767

## #8 — devotion, 2008-10-01T09:41Z

Hej igen!

Skulle man inte kunna få ut båda resultaten i samma fråga?

Mvh
Henrik

Permalänk: https://www.webforum.nu/p/2144816

---

Tråden på webben: https://www.webforum.nu/amne/databaser-sql/174443-summera