SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with SQL code


Help with SQL code

Author
Message
sasken
sasken
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3652 Visits: 1948
Hello,

I have my data in the below format but this is just a sample(but I am looking at data for the last 6 months):

CCDATA ||||CCDATAcompletedTIme
54 ||||2013-04-16 13:56:00
52 ||||2013-04-16 13:55:00
17 ||||2013-04-16 13:55:00
11 ||||2013-04-16 13:55:00
36 ||||2013-04-16 13:55:00
73 ||||2013-04-16 13:55:00
80 ||||2013-04-16 13:55:00
47 ||||2013-04-16 13:55:00
22 ||||2013-04-16 13:55:00
235 ||||2013-04-16 13:55:00
49 ||||2013-04-16 13:55:00
16 ||||2013-04-16 13:55:00
34 ||||2013-04-16 13:55:00
72 ||||2013-04-16 13:55:00
43 ||||2013-04-16 13:55:00
50 ||||2013-04-16 13:55:00
1 ||||2013-04-16 13:55:00
64 ||||2013-04-16 13:55:00
81 ||||2013-04-15 13:54:00
14 ||||2013-04-15 13:54:00
103 ||||2013-04-15 13:54:00
93 ||||2013-04-15 13:54:00
31 ||||2013-04-15 13:54:00
1022 ||||2013-04-15 13:54:00
20 ||||2013-04-15 13:54:00
64 ||||2013-04-15 13:54:00
142 ||||2013-04-14 13:53:00
26 ||||2013-04-14 13:53:00
23 ||||2013-04-14 13:53:00
9 ||||2013-04-14 13:53:00
32 ||||2013-04-14 13:53:00
991 ||||2013-04-14 13:53:00
24 ||||2013-04-14 13:53:00
123 ||||2013-04-14 13:53:00
26 ||||2013-04-14 13:52:00
113 ||||2013-04-13 13:51:00
14 ||||2013-04-13 13:51:00
66 ||||2013-04-13 13:51:00
83 ||||2013-04-13 13:51:00
27 ||||2013-04-13 13:51:00
23 ||||2013-04-13 13:51:00
40 ||||2013-04-13 13:51:00
31 ||||2013-04-12 13:51:00
1 ||||2013-04-12 13:49:00
66 ||||2013-04-12 13:48:00
33 ||||2013-04-12 13:45:00
49 ||||2013-04-12 13:45:00
18 ||||2013-04-12 13:45:00
133 ||||2013-04-12 13:45:00
33 ||||2013-04-11 13:45:00

I need to convert it to the following format:

WEEK_Number ||MOnday_Averages||Tuesday_Averages||Wednesday_Averages||Thursday_Averages||Friday_Averages||Saturday_Averages||Sunday_Averages


Is this possible?

Thanks for your help in advance

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97205 Visits: 38988
Based on the sample data, what would the expected results be?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74041 Visits: 40974
consumable sample data: note there are gaps(no "WednesDay" data for example), so a calendar table or case will be needed to get the values:

With MySampleData(CCDATA,CCDATAcompletedTIme)
AS
(

SELECT 54,'2013-04-16 13:56:00' UNION ALL
SELECT 52,'2013-04-16 13:55:00' UNION ALL
SELECT 17,'2013-04-16 13:55:00' UNION ALL
SELECT 11,'2013-04-16 13:55:00' UNION ALL
SELECT 36,'2013-04-16 13:55:00' UNION ALL
SELECT 73,'2013-04-16 13:55:00' UNION ALL
SELECT 80,'2013-04-16 13:55:00' UNION ALL
SELECT 47,'2013-04-16 13:55:00' UNION ALL
SELECT 22,'2013-04-16 13:55:00' UNION ALL
SELECT 235,'2013-04-16 13:55:00' UNION ALL
SELECT 49,'2013-04-16 13:55:00' UNION ALL
SELECT 16,'2013-04-16 13:55:00' UNION ALL
SELECT 34,'2013-04-16 13:55:00' UNION ALL
SELECT 72,'2013-04-16 13:55:00' UNION ALL
SELECT 43,'2013-04-16 13:55:00' UNION ALL
SELECT 50,'2013-04-16 13:55:00' UNION ALL
SELECT 1,'2013-04-16 13:55:00' UNION ALL
SELECT 64,'2013-04-16 13:55:00' UNION ALL
SELECT 81,'2013-04-15 13:54:00' UNION ALL
SELECT 14,'2013-04-15 13:54:00' UNION ALL
SELECT 103,'2013-04-15 13:54:00' UNION ALL
SELECT 93,'2013-04-15 13:54:00' UNION ALL
SELECT 31,'2013-04-15 13:54:00' UNION ALL
SELECT 1022,'2013-04-15 13:54:00' UNION ALL
SELECT 20,'2013-04-15 13:54:00' UNION ALL
SELECT 64,'2013-04-15 13:54:00' UNION ALL
SELECT 142,'2013-04-14 13:53:00' UNION ALL
SELECT 26,'2013-04-14 13:53:00' UNION ALL
SELECT 23,'2013-04-14 13:53:00' UNION ALL
SELECT 9,'2013-04-14 13:53:00' UNION ALL
SELECT 32,'2013-04-14 13:53:00' UNION ALL
SELECT 991,'2013-04-14 13:53:00' UNION ALL
SELECT 24,'2013-04-14 13:53:00' UNION ALL
SELECT 123,'2013-04-14 13:53:00' UNION ALL
SELECT 26,'2013-04-14 13:52:00' UNION ALL
SELECT 113,'2013-04-13 13:51:00' UNION ALL
SELECT 14,'2013-04-13 13:51:00' UNION ALL
SELECT 66,'2013-04-13 13:51:00' UNION ALL
SELECT 83,'2013-04-13 13:51:00' UNION ALL
SELECT 27,'2013-04-13 13:51:00' UNION ALL
SELECT 23,'2013-04-13 13:51:00' UNION ALL
SELECT 40,'2013-04-13 13:51:00' UNION ALL
SELECT 31,'2013-04-12 13:51:00' UNION ALL
SELECT 1,'2013-04-12 13:49:00' UNION ALL
SELECT 66,'2013-04-12 13:48:00' UNION ALL
SELECT 33,'2013-04-12 13:45:00' UNION ALL
SELECT 49,'2013-04-12 13:45:00' UNION ALL
SELECT 18,'2013-04-12 13:45:00' UNION ALL
SELECT 133,'2013-04-12 13:45:00' UNION ALL
SELECT 33,'2013-04-11 13:45:00'
)
SELECT *,
datepart(week,CCDATAcompletedTIme),
DATENAME(weekday,CCDATAcompletedTIme) AS val FROM MySampleData




Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
sasken
sasken
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3652 Visits: 1948
I am trying to convert into the below format
week number,
average of sunday,
average of monday,
average of tuesday,
average of wednesday,
average of thursday,
average of friday,
average of saturday,

The averages are nothing but average of ccdata for that week on that specific day

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97205 Visits: 38988
Sapen (4/16/2013)
I am trying to convert into the below format
week number,
average of sunday,
average of monday,
average of tuesday,
average of wednesday,
average of thursday,
average of friday,
average of saturday,

The averages are nothing but average of ccdata for that week on that specific day


How do I compare the results returned from a query to what you posted above? There is nothing there to validate that what a query returns is what you expect based on the sample data you provided.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
sasken
sasken
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3652 Visits: 1948
Lynn Pettis (4/16/2013)
Sapen (4/16/2013)
I am trying to convert into the below format
week number,
average of sunday,
average of monday,
average of tuesday,
average of wednesday,
average of thursday,
average of friday,
average of saturday,

The averages are nothing but average of ccdata for that week on that specific day


How do I compare the results returned from a query to what you posted above? There is nothing there to validate that what a query returns is what you expect based on the sample data you provided.

Hi Lynn,

Please find the output format with data attached. Thanks a bunch for the help in advance.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Attachments
Output to compare.JPG (11 views, 21.00 KB)
Mansfield
Mansfield
Say Hey Kid
Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)

Group: General Forum Members
Points: 712 Visits: 394
First of all, I recommend creating a permanent calendar table. A very simplified example would be something like this:


---------------------------------------------------------------------
---------------------- Create a calendar table ----------------------
---------------------------------------------------------------------
create table calendar (
date_key date primary key,
year smallint,
quarter smallint,
month smallint,
day_of_year smallint,
day smallint,
week_of_year smallint,
day_of_week smallint,
day_of_week_label nvarchar(50)
);

insert into calendar
with dk as (
select dateadd(d, rank() over(order by newid()), '19990101') as date_key
from sys.objects cross join sys.columns
)
select date_key,
datepart(yy, date_key),
datepart(qq, date_key),
datepart(m, date_key),
datepart(dy, date_key),
datepart(d, date_key),
datepart(wk, date_key),
datepart(dw, date_key),
datename(dw, date_key)
from dk



Materialising into a table allows easy implementation of different calendaring rules, such as 4-4-5, 4-5-4, ISO, etc.

Further reading: http://www.sqlservercentral.com/articles/T-SQL/70482/
Mansfield
Mansfield
Say Hey Kid
Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)

Group: General Forum Members
Points: 712 Visits: 394
Since the number of days in a week is not likely to change, you can then write a "SELECT AVG(x) GROUP BY y" type of cross-tab query.

Further reading: http://www.sqlservercentral.com/articles/T-SQL/63681/

Example:


SELECT c.week_of_year as WeekNumber,
AVG(CASE WHEN c.day_of_week_label = 'Sunday' then ct.CCDATA ELSE NULL END) as AverageOfSunday,
...
FROM calendar c
JOIN cc_table ct ON dateadd(d, datediff(d, 0, ct.CCDATAcompletedTime), 0) = c.date_key
GROUP BY c.week_of_year


sasken
sasken
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3652 Visits: 1948
Thanks

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search