Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with SQL code Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 12:13 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:03 AM
Points: 1,353, Visits: 1,733
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
Post #1442919
Posted Tuesday, April 16, 2013 12:15 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:56 PM
Points: 23,400, Visits: 32,256
Based on the sample data, what would the expected results be?



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)
Post #1442920
Posted Tuesday, April 16, 2013 12:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:30 PM
Points: 12,918, Visits: 32,089
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1442923
Posted Tuesday, April 16, 2013 12:24 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:03 AM
Points: 1,353, Visits: 1,733
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
Post #1442930
Posted Tuesday, April 16, 2013 12:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:56 PM
Points: 23,400, Visits: 32,256
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.



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)
Post #1442940
Posted Tuesday, April 16, 2013 2:24 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:03 AM
Points: 1,353, Visits: 1,733
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

  Post Attachments 
Output to compare.JPG (7 views, 21.45 KB)
Post #1443009
Posted Tuesday, April 16, 2013 3:51 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:20 PM
Points: 364, Visits: 384
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/
Post #1443037
Posted Tuesday, April 16, 2013 4:01 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:20 PM
Points: 364, Visits: 384
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

Post #1443043
Posted Wednesday, April 17, 2013 1:49 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:03 AM
Points: 1,353, Visits: 1,733
Thanks

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Post #1443479
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse