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
 sum a count

Author  Topic 

stwp86
Starting Member

42 Posts
Posted - 2012-07-26 : 16:47:48
Hey Everyone . . .

I am fully aware that my SQL skills are lacking to say the least, so I defer to all of the experts out there.

I am currently stuck on the following query:

select count(distinct w.user_name) as "Distinct User Count by Day", convert(date, w.start_time) as "Date"
	from workflow_data as w
	WHERE
	w.definition = 'Patient Search - Patient Search Window'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Patient Lists - Patient List to Open Chart'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Home View - Schedule Viewer to Open Chart'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Sign Document'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Refuse Document'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Modify Document'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Accept Proposal-Refill Orders'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Reject Proposal-Refill Orders'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Accept Cosign Orders'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Reject Cosign Orders'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Endorse Results FYI'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Refuse Results FYI'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Endorse Results'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Refuse Results'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Respond to Message'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Delete Message'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - View Sent Items'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Create New Message-Reminder'  and datepart(dw, w.Start_Time) not in (1,7)
	group by CONVERT(date, w.start_time)


This is returning the correct count for each day I currently have in my table, looks something like this:

Distinct User Count by Day | Date
3 | 2012-06-25
4 | 2012-06-26
4 | 2012-06-27
3 | 2012-06-28
4 | 2012-06-29
4 | 2012-07-02
4 | 2012-07-03



Now, the problem that I am having is as follows. I would like to get a sum by week for the above info. I am doing this elsewhere and it is working great, but as always it is slightly different (just enough that I can't do a copy), but here is the snippet i am using (I need to return monday of the week and the sum for that week)

CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, w.Start_Time) - 5, w.Start_Time)),101)

Ultimately, here is what I need my data to look like:

Distinct User Count by Day | Date
18 | 2012-06-25
8 | 2012-07-02

Any help is greatly appreciated, I am running up on my deadline and this thing is putting me behind

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-26 : 16:51:17
[code]
select sum([Distinct User Count by Day]),MIN([Date])
from
(
select count(distinct w.user_name) as "Distinct User Count by Day", convert(date, w.start_time) as "Date"
from workflow_data as w
WHERE
w.definition = 'Patient Search - Patient Search Window' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Patient Lists - Patient List to Open Chart' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Home View - Schedule Viewer to Open Chart' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Sign Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Modify Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Accept Proposal-Refill Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Reject Proposal-Refill Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Accept Cosign Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Reject Cosign Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Endorse Results FYI' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Results FYI' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Endorse Results' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Results' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Respond to Message' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Delete Message' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - View Sent Items' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Create New Message-Reminder' and datepart(dw, w.Start_Time) not in (1,7)
group by CONVERT(date, w.start_time)
)t
GROUP BY DATEADD(wk,DATEDIFF(wk,0,[Date]),0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

stwp86
Starting Member

42 Posts
Posted - 2012-07-26 : 16:56:33
This is why we ask the experts . . .

Thanks so much for the quick response!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-26 : 16:58:28
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

stwp86
Starting Member

42 Posts
Posted - 2012-07-26 : 17:33:08
I apologize in advance, but it would appear that I made the mistake of thinking I could combine this query with another one easily . . .

So you helped me with retrieving the rolled up number of distinct users for the week. I had previously completed the following query wich calculates and total elapsed time for the week:


SELECT 
   Client_Mnemonic,  CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, w.Start_Time) - 5, w.Start_Time)),101), 
   (count(w.User_Name)*avg(w.duration)) as "Total Elapsed Time (sec)"
FROM workflow_data as w
WHERE
w.definition = 'Patient Search - Patient Search Window'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Patient Lists - Patient List to Open Chart'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Home View - Schedule Viewer to Open Chart'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Sign Document'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Document'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Modify Document'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Accept Proposal-Refill Orders'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Reject Proposal-Refill Orders'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Accept Cosign Orders'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Reject Cosign Orders'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Endorse Results FYI'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Results FYI'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Endorse Results'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Results'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Respond to Message'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Delete Message'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - View Sent Items'  and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Create New Message-Reminder'  and datepart(dw, w.Start_Time) not in (1,7)
GROUP BY client_mnemonic,  CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101)
ORDER BY CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, w.Start_Time) - 5, w.Start_Time)),101)


The output of this one looks like this:

Client | Date | Total Elapsed Time (sec) per week
ABC |6/25/2012| 7406.949
ABC |7/02/2012| 22275.346
ABC |7/09/2012| 5613.74
ABC |7/16/2012| 6566.69

These numbers all match what they should.

So what I was hoping to do is combine the query you helped me with previously with this one and end by getting the following info:


Client | Date | Average Elapsed Time per week
ABC |6/25/2012| 6.9
ABC |7/02/2012| 20.6
ABC |7/09/2012| 4.5
ABC |7/16/2012| 5.2


So to get the Average in this last column we use the following formula in excel:
(sum(Total Elapsed Time (sec) per week)/sum(Distinct User Count for week (query you helped me with)))/60


Any idea how I could combine the queries to retrieve that number?

Thanks again!
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2012-07-26 : 17:46:59
Sorry not answering your question, but I think you can simplifyyour WHERE clause a bit:
	w.definition IN
	(
		'Patient Search - Patient Search Window'  
		,'Patient Lists - Patient List to Open Chart'  
		,'Home View - Schedule Viewer to Open Chart'  
		,'Message Center - Sign Document'  
		,'Message Center - Refuse Document'  
		,'Message Center - Modify Document'  
		,'Message Center - Accept Proposal-Refill Orders'  
		,'Message Center - Reject Proposal-Refill Orders'  
		,'Message Center - Accept Cosign Orders'  
		,'Message Center - Reject Cosign Orders'  
		,'Message Center - Endorse Results FYI'  
		,'Message Center - Refuse Results FYI'  
		,'Message Center - Endorse Results'  
		,'Message Center - Refuse Results'  
		,'Message Center - Respond to Message'  
		,'Message Center - Delete Message'  
		,'Message Center - View Sent Items'  
		,'Message Center - Create New Message-Reminder'
	)
	and datepart(dw, w.Start_Time) not in (1,7)
Go to Top of Page

stwp86
Starting Member

42 Posts
Posted - 2012-07-26 : 19:43:05
Yes, that does make sense and simplifies it . . . will make the change, but unfortunately still working on combining the two querys to get that output. Thanks for the additional insight, much appreciated!
Go to Top of Page

stwp86
Starting Member

42 Posts
Posted - 2012-07-26 : 22:18:58
So, I am still plugging and chugging on this one . . . with little success

I have combined the two queries and now am able to get the correct "AvgTimerPerDay" numbers, however it is returing a row for every row in the table, and is displaying the info on everyday, not the Monday only view I am looking for. Here is the current query:

Select ww.client_mnemonic, o1.[Total Elapsed Time]/o2.DistUserCnt/60 as "AvgTimePerDay" , convert(date, ww.start_time) as "Date"
From workflow_data as ww,
	(SELECT 
	   /*Client_Mnemonic,*/ (count(w.User_Name)*avg(w.duration)) as "Total Elapsed Time"
	FROM workflow_data as w
	WHERE
	w.definition = 'Patient Search - Patient Search Window'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Patient Lists - Patient List to Open Chart'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Home View - Schedule Viewer to Open Chart'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Sign Document'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Refuse Document'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Modify Document'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Accept Proposal-Refill Orders'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Reject Proposal-Refill Orders'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Accept Cosign Orders'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Reject Cosign Orders'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Endorse Results FYI'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Refuse Results FYI'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Endorse Results'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Refuse Results'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Respond to Message'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Delete Message'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - View Sent Items'  and datepart(dw, w.Start_Time) not in (1,7) or
	w.definition = 'Message Center - Create New Message-Reminder'  and datepart(dw, w.Start_Time) not in (1,7)
	GROUP BY client_mnemonic,  CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101)) as o1,
	(select sum([Distinct User Count by Day]) as "DistUserCnt" --,MIN([Date])
	from
	(
	select count(distinct w.user_name) as "Distinct User Count by Day", convert(date, w.start_time) as "Date"
		from workflow_data as w
		WHERE
		w.definition = 'Patient Search - Patient Search Window'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Patient Lists - Patient List to Open Chart'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Home View - Schedule Viewer to Open Chart'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Message Center - Sign Document'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Message Center - Refuse Document'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Message Center - Modify Document'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Message Center - Accept Proposal-Refill Orders'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Message Center - Reject Proposal-Refill Orders'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Message Center - Accept Cosign Orders'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Message Center - Reject Cosign Orders'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Message Center - Endorse Results FYI'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Message Center - Refuse Results FYI'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Message Center - Endorse Results'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Message Center - Refuse Results'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Message Center - Respond to Message'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Message Center - Delete Message'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Message Center - View Sent Items'  and datepart(dw, w.Start_Time) not in (1,7) or
		w.definition = 'Message Center - Create New Message-Reminder'  and datepart(dw, w.Start_Time) not in (1,7)
		group by CONVERT(date, w.start_time)
	)t
	GROUP BY DATEADD(wk,DATEDIFF(wk,0,[Date]),0)) as o2
	Order by date


The above query is returning the following data:

Client | AvgTimePerDay | Date
ABC |5.19 | 6/25/2012
ABC |5.19 | 6/25/2012
... |5.19 | 6/25/2012
ABC |20.5 | 6/25/2012
ABC |20.5 | 6/25/2012
ABC |20.5 | 6/25/2012
... |6.9 | 6/25/2012
... |4.5 | 6/25/2012
... | Progress through every day in the table one by one

However, what I need it to return is this:
Client | AvgTimePerDay | Date
ABC |6.9 | 6/25/2012
ABC |20.5 | 7/02/2012
ABC |4.5 | 7/09/2012
ABC |5.19 | 7/16/2012


Any thoughts on this new one?

I appreciate the help on this, trying to grind through it, but it is beyond my knowledge at this point.

Thanks!

T
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-27 : 00:56:50
[code]
SELECT n.client_mnemonic,(([Total Elapsed Time (sec)]/WeeklyUserCount)/60.0) AS AvgTimePerDay ,m.WeekMinDate
FROM
(
--My earlier suggestion
select sum([Distinct User Count by Day]) AS WeeklyUserCount,MIN([Date]) AS weekMinDate
from
(
select count(distinct w.user_name) as "Distinct User Count by Day", convert(date, w.start_time) as "Date"
from workflow_data as w
WHERE
w.definition = 'Patient Search - Patient Search Window' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Patient Lists - Patient List to Open Chart' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Home View - Schedule Viewer to Open Chart' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Sign Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Modify Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Accept Proposal-Refill Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Reject Proposal-Refill Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Accept Cosign Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Reject Cosign Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Endorse Results FYI' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Results FYI' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Endorse Results' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Results' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Respond to Message' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Delete Message' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - View Sent Items' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Create New Message-Reminder' and datepart(dw, w.Start_Time) not in (1,7)
group by CONVERT(date, w.start_time)
)t
GROUP BY DATEADD(wk,DATEDIFF(wk,0,[Date]),0)
)m
INNER JOIN
(
--your intial query

SELECT
Client_Mnemonic, CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, w.Start_Time) - 5, w.Start_Time)),101) as WeekDate,
(count(w.User_Name)*avg(w.duration)) as "Total Elapsed Time (sec)"
FROM workflow_data as w
WHERE
w.definition = 'Patient Search - Patient Search Window' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Patient Lists - Patient List to Open Chart' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Home View - Schedule Viewer to Open Chart' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Sign Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Modify Document' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Accept Proposal-Refill Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Reject Proposal-Refill Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Accept Cosign Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Reject Cosign Orders' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Endorse Results FYI' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Results FYI' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Endorse Results' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Refuse Results' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Respond to Message' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Delete Message' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - View Sent Items' and datepart(dw, w.Start_Time) not in (1,7) or
w.definition = 'Message Center - Create New Message-Reminder' and datepart(dw, w.Start_Time) not in (1,7)
GROUP BY client_mnemonic, CONVERT(varchar(50), (DATEADD(d, @@DATEFIRST - DATEPART(dw, Start_Time) - 5, Start_Time)),101)
)n
ON n.WeekDate = m.WeekMinDate
ORDER BY n.client_mnemonic,m.WeekMinDate
[/code]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

stwp86
Starting Member

42 Posts
Posted - 2012-07-27 : 07:22:26
Man . . . I really appreciate all of the work everyone put into this to help me.

Thanks visakh16 and thanks Lamprey

Definately couldn't have completed it without you.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-27 : 10:19:02
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2012-07-28 : 14:01:45
if you still read this. You should look into normalising that table....

I'm guessing you have many, many lines with values for [definition] in workflow_data which equal 'Message Center - Accept Proposal-Refill Orders'. This is completely denormalised and will be using a ton of space.

What about having a definitions table and a simple foreign key from workflow...

Also this part of the query 

WHERE
    ...... and datepart(dw, w.Start_Time) not in (1,7)


Is very inefficient. There is no possible index on [Start_Time] that could be hit. A better way would be to have a calender table and join to it to filter out whether the day is Sunday or Saturday... Then you would be able to use indices

Also is that value location specific? Anyone know if DW changes based on locale?

http://en.wikipedia.org/wiki/Database_normalization

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site