August 14, 2014 at 3:17 am
Hey all,
It's been a while due to a job change I don't get to do as much SQL anymore...although this is changing again so you might see me back here more often as I am rusty as hell!
Right so...the below query instead of the week number I need the week beginning date for a report that will be run. And I am having a brain fart, how would I do this?
use swdata
select DATENAME (ww, update_time) as 'Week',DATENAME (mm, update_time) as 'Month', DATENAME (yy, update_time) as 'Year', count (callref) as 'Total'
from vw_updatedb
where year (update_time) in ( '2014')
and month (update_time) in ('02', '03')
and udsource in ('E-Mail')
and repid in ('A', 'M', 'J', 'K', 'T')
group by DATENAME (ww, update_time),DATENAME (yy, update_time),DATENAME (mm, update_time)
order by ABS (DATENAME (ww, update_time)), (DATENAME (mm, update_time))
Thanks in advance it is appreciated!
August 14, 2014 at 3:44 am
please provide create table scripts,sample data and expected result .
August 14, 2014 at 5:05 am
Thanks for your reply.
So the data currently looks like this:
WeekMonthYearTotal
6February2014212
7February2014191
8February2014256
9February2014287
10March2014194
11March2014183
12March2014199
13March2014200
14March201442
What I want it to look like is this (currently I am manually adding in the dates based on the week number):
Week CommencingMonthYearTotal
06/01/2014January201487
13/01/2014January201465
20/01/2014January201476
27/01/2014January201474
And do you mean the script used to create the view? If so I have posted this but if this is not what you needed please let me know:
USE [swdata]
GO
/****** Object: View [dbo].[vw_updatedb] Script Date: 14/08/2014 12:07:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE View [dbo].[vw_updatedb]
as
select [callref]
,[udid]
,[updatetime]
,[updatetimex]
,[timespent]
,[repid]
,[aaid]
,[aid]
,[groupid]
,[udsource]
,[udcode]
,[udtype]
,[udindex]
,[updatetxt]
,[custom_a]
,[custom_b]
,[custom_c]
,[custom_d]
,[custom_e]
,[custom_f]
, dateadd(ss,updatetimex,'19700101') as update_time
from updatedb
GO
August 14, 2014 at 6:56 am
Well I think we are getting there, the wrong dates haha but not sure if maybe my query is wrong? I put in the date as the beginning of this year as I want results from this year:
Week CommencingMonthYearTotal
1899-12-31 00:00:00.000February2014212
1899-12-31 00:00:00.000February2014191
1899-12-31 00:00:00.000February2014256
1899-12-31 00:00:00.000February2014287
1899-12-31 00:00:00.000March2014194
1899-12-31 00:00:00.000March2014183
1899-12-31 00:00:00.000March2014199
1899-12-31 00:00:00.000March2014200
1899-12-31 00:00:00.000March201442
use swdata
select DATEADD(dd, -(DATEPART(dw, 01/01/2014)-1), 01/01/2014) AS [Week Commencing],DATENAME (mm, update_time) as 'Month', DATENAME (yy, update_time) as 'Year', count (callref) as 'Total'
from vw_updatedb
where year (update_time) in ( '2014')
and month (update_time) in ('02', '03')
and udsource in ('E-Mail')
and repid in ('A', 'M', 'J', 'K', 'T')
group by DATENAME (ww, update_time),DATENAME (yy, update_time),DATENAME (mm, update_time)
order by ABS (DATENAME (ww, update_time)), (DATENAME (mm, update_time))
Any additional help is appreciated!
August 14, 2014 at 7:11 am
This any good?
SELECT DATEADD(dd, -( DATEPART(dw, update_time) - 1 ), update_time) AS [Week Commencing] ,
DATENAME(mm, update_time) AS 'Month' ,
DATENAME(yy, update_time) AS 'Year' ,
COUNT(callref) AS 'Total'
FROM vw_updatedb
WHERE YEAR(update_time) IN ( '2014' )
AND MONTH(update_time) IN ( '02', '03' )
AND udsource IN ( 'E-Mail' )
AND repid IN ( 'A', 'M', 'J', 'K', 'T' )
GROUP BY DATENAME(ww, update_time) ,
DATENAME(yy, update_time) ,
DATENAME(mm, update_time)
ORDER BY ABS(DATENAME(ww, update_time)) ,
( DATENAME(mm, update_time) )
August 14, 2014 at 7:26 am
;WITH Dates AS (
SELECT [date]
FROM (VALUES
(GETDATE()-7.7),(GETDATE()-6.6),(GETDATE()-5.5),(GETDATE()-4.4),(GETDATE()-3.3),
(GETDATE()-2.2),(GETDATE()-1.1),(GETDATE()-0),(GETDATE()+1.1),(GETDATE()+2.2),
(GETDATE()+3.3),(GETDATE()+4.4),(GETDATE()+5.5),(GETDATE()+6.6),(GETDATE()+7.7)
) d ([date])
)
SELECT
[date],
[Dayname] = DATENAME(dw, [date]),
LatestSunday = DATEADD(DAY,-1+DATEDIFF(DAY,-1,[date])/7*7,0),
LatestMonday = DATEADD(DAY,DATEDIFF(DAY,0,[date])/7*7,0),
LatestTuesday = DATEADD(DAY,1+DATEDIFF(DAY,1,[date])/7*7,0)
FROM Dates
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 14, 2014 at 8:10 am
Higgim thanks, almost there!
Pulling the data with the right date but now doing each call per time of the day whereas I need calls for that week:
Week CommencingMonthYearTotal
2014-02-02 08:30:42.000February20141
2014-02-02 08:39:27.000February20141
2014-02-02 08:45:01.000February20141
2014-02-02 08:45:31.000February20141
2014-02-02 08:47:36.000February20141
2014-02-02 08:49:51.000February20141
2014-02-02 09:08:53.000February20141
2014-02-02 09:15:03.000February20141
2014-02-02 09:25:09.000February20141
2014-02-02 09:34:40.000February20141
2014-02-02 09:37:14.000February20141
2014-02-02 09:40:25.000February20141
2014-02-02 09:44:41.000February20141
2014-02-02 09:45:25.000February20141
2014-02-02 09:46:38.000February20141
2014-02-02 09:48:14.000February20141
2014-02-02 09:51:32.000February20141
2014-02-02 09:51:41.000February20141
2014-02-02 10:00:04.000February20141
I changed the query slightly as at first it didn't work.
SELECT DATEADD(dw, -( DATEPART(dw, update_time) - 1 ), update_time) AS [Week Commencing] ,
DATENAME(mm, update_time) AS 'Month' ,
DATENAME(yy, update_time) AS 'Year' ,
COUNT(*) AS 'Total'
FROM vw_updatedb
WHERE YEAR(update_time) IN ( '2014' )
AND MONTH(update_time) IN ( '02', '03' )
AND udsource IN ( 'E-Mail' )
AND repid IN ( 'A', 'M', 'J', 'K', 'T' )
GROUP BY DATEADD(dw, -( DATEPART(dw, update_time) - 1 ), update_time) ,
DATENAME(yy, update_time) ,
DATENAME(mm, update_time)
--ORDER BY ABS (DATEADD(dd, -( DATEPART(dw, update_time) - 1 ), update_time)) ,
-- ( DATENAME(mm, update_time) )
Thanks for any more help it is appreciated!
August 14, 2014 at 8:20 am
That should sort it.
DECLARE @somedates AS TABLE ( update_time DATETIME )
INSERT INTO @somedates
( update_time )
VALUES ( '2014-08-14 14:14:35' ),
( '2014-08-14 14:15:35' )
-- Before
SELECT DATEADD(dw, -( DATEPART(dw, update_time) - 1 ), update_time) ,
DATENAME(mm, update_time) AS 'Month' ,
DATENAME(yy, update_time) AS 'Year' ,
COUNT(*) AS 'Total'
FROM @somedates
GROUP BY DATEADD(dw, -( DATEPART(dw, update_time) - 1 ), update_time) ,
DATENAME(mm, update_time) ,
DATENAME(yy, update_time)
-- Altered
SELECT DATEADD(dw, -( DATEPART(dw, CAST(update_time AS DATE)) - 1 ),
CAST(update_time AS DATE)) AS [Week Commencing] ,
DATENAME(mm, update_time) AS 'Month' ,
DATENAME(yy, update_time) AS 'Year' ,
COUNT(*) AS 'Total'
FROM @somedates
GROUP BY DATEADD(dw, -( DATEPART(dw, CAST(update_time AS DATE)) - 1 ),
CAST(update_time AS DATE)) ,
DATENAME(mm, update_time) ,
DATENAME(yy, update_time)
August 14, 2014 at 8:32 am
Higgim, seriously thank you SO much! I played around with bits of each item but couldn't get them all together in my head.
Changed it to this as all I really want is the week commencing in reality:
SELECT DATEADD(dw, -( DATEPART(dw, CAST(update_time AS DATE)) - 1 ),
CAST(update_time AS DATE)) AS [Week Commencing] ,
--DATENAME(mm, update_time) AS 'Month' ,
--DATENAME(yy, update_time) AS 'Year' ,
COUNT(*) AS 'Total'
FROM vw_updatedb
WHERE YEAR(update_time) IN ( '2014' )
--AND MONTH(update_time) IN ( '02', '03' )
AND udsource IN ( 'E-Mail' )
AND repid IN ( 'A', 'M', 'J', 'K', 'T' )
GROUP BY DATEADD(dw, -( DATEPART(dw, CAST(update_time AS DATE)) - 1 ),
CAST(update_time AS DATE))
--DATENAME(mm, update_time) ,
--DATENAME(yy, update_time)
order by DATEADD(dw, -( DATEPART(dw, CAST(update_time AS DATE)) - 1 ),
CAST(update_time AS DATE))
Works like a dream
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply