Help with SQL code

Question

Post reply

Help with SQL code

Pyman

SSChampion

Points: 13830
More actions
April 16, 2013 at 12:13 pm

#276663

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Based on the sample data, what would the expected results be?

Lowell SSC Guru Points: 323518 More actions · Answer 2

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!

Pyman SSChampion Points: 13830 More actions · Answer 3

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 SSC Guru Points: 442467 More actions · Answer 4

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.

Pyman SSChampion Points: 13830 More actions · Answer 5

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

Mansfield SSCrazy Points: 2396 More actions · Answer 6

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

)

selectdate_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/[/url]

Mansfield SSCrazy Points: 2396 More actions · Answer 7

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/[/url]

Example:

SELECTc.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

Pyman SSChampion Points: 13830 More actions · Answer 8

Thanks

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