SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 DateAdd for Week
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 07/23/2013 :  04:46:16  Show Profile  Reply with Quote
Hi

Im trying to pull records in for the below code but having an issue trying to read the SnapShotDateKey between the start and end of the week to only show these records. I get conversion failed when converting the varchar value 'Jul 22 2' to data type int.Do I need a 112) at the end of each line to format?

Thanks


Select Distinct IncidentID,
Case
When DateKey = convert(int,convert(varchar(8),DateAdd(wk,-9,GetDate()),112))
And [priority] = '1'
And SnapShotDateKey = convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)))
And SnapShotDateKey = convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)))
Then 1 Else 0
End as P1s
From FACT_INCIDENT
order by P1s Desc


SZ1
to learn is to show the universe that you care...!

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/23/2013 :  04:58:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@FromDateKey INT = CONVERT(INT, CONVERT(CHAR(8), DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '19000101'), 112)),
	@ToDateKey INT = CONVERT(INT, CONVERT(CHAR(8), DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '19000107'), 112)),
	@DateKey INT = CONVERT(INT, CONVERT(CHAR(8), DATEADD(WEEK, -9, GETDATE()), 112));

SELECT DISTINCT	IncidentID, 
		CASE
			WHEN DateKey = @DateKey AND [Priority] <> '1' AND SnapShotDateKey BETWEEN @FromDateKey AND @ToDateKey THEN 1
			ELSE 0
		END AS P1s
FROM		dbo.Fact_Incident
ORDER BY	P1s DESC;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/23/2013 :  05:00:53  Show Profile  Reply with Quote
yes you need it
Otherwise it will assume default format of Mon dd yyyy ie Jul 22 2013 etc which will throw error when you try converting to int

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

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 07/23/2013 :  05:07:08  Show Profile  Reply with Quote
I tried adding it to end of statements but does not return and P1 records? have I got the number the wrong way around?
Thanks

Select Distinct IncidentID,
Case
When DateKey = convert(int,convert(varchar(8),DateAdd(wk,-9,GetDate()),112))
And [priority] = '1'
And SnapShotDateKey = convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0),112),0)And SnapShotDateKey = convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6),112),0)Then 1 Else 0
End as P1s
From FACT_INCIDENT
order by P1s Desc

SZ1
to learn is to show the universe that you care...!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/23/2013 :  05:10:48  Show Profile  Reply with Quote
shouldnt condition be this? how can it be = to both first and last values of week at the same time? it should be BETWEEN

Select Distinct IncidentID, 
Case
When DateKey = convert(int,convert(varchar(8),DateAdd(wk,-9,GetDate()),112))
And [priority] = '1'
And SnapShotDateKey BETWEEN convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0),112),0)And convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6),112),0)Then 1 Else 0
End as P1s
From FACT_INCIDENT
order by P1s Desc


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

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 07/23/2013 :  05:11:46  Show Profile  Reply with Quote
I tried your code SwePeso and it looks good, in your statement you say not = to P1 so I changed this to = instead of <>.
The '19000101', GETDATE()), '19000107'), 112)), code in your statement does those dates only act as stamps and not actual reading those dates as the actual dates...so they are acting as a dummy date?
Thanks


SZ1
to learn is to show the universe that you care...!
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 07/23/2013 :  05:54:20  Show Profile  Reply with Quote
Yeah I was wondering about the between clause thats much better!
Im seeing records for -9 weeks, but none for -10 even though I know there is one that came in on the 15/05/2013, any ideas?
I cant see the record no matter what number of weeks I put but its definately there.
Thanks

Select Distinct f.IncidentID, i.IncidentNumber, f.DateKey,
Case
When f.DateKey = convert(int,convert(varchar(8),DateAdd(week,-10,GetDate()),112))
And f.[priority] = '1'
--And f.SnapShotDateKey BETWEEN convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0),112),0)
--And convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6),112),0)
Then 1 Else 0
End as P1s
From FACT_INCIDENT f
Join DIM_INCIDENT i
On i.IncidentID = f.IncidentID
--Where IncidentNumber = '323561'
--where f.Priority =1
order by P1s Desc

SZ1
to learn is to show the universe that you care...!
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 07/23/2013 :  06:56:16  Show Profile  Reply with Quote
How do I make the search between 2 periods so between weeks 7 and 10?

Select f.IncidentID, i.IncidentNumber, f.DateKey,
Case
--When f.DateKey Between convert(int,convert(varchar(8),DateAdd(week,-7,GetDate()),0),112),0)
--And f.DateKey convert(int,convert(varchar(8),DateAdd(week,-10,GetDate()),112))
When f.CreatedDateTime BETWEEN DATEADD(wk,-7,GETDATE())
And DATEADD(wk,-10,GETDATE())
And f.[priority] In ('1','2','3')
--And f.DateKey BETWEEN convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0),112),0)
--And convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6),112),0)
Then 1 Else 0
End as P1s
From FACT_INCIDENT f
Join DIM_INCIDENT i
On f.IncidentID = i.IncidentID
order by P1s Desc

SZ1
to learn is to show the universe that you care...!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/23/2013 :  06:58:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If you don't get any rows with this query, it means there are no rows in the table.
DECLARE	@FromDateKey INT = CONVERT(INT, CONVERT(CHAR(8), DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '19000101'), 112)),
	@ToDateKey INT = CONVERT(INT, CONVERT(CHAR(8), DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '19000107'), 112)),
	@DateKey INT = CONVERT(INT, CONVERT(CHAR(8), DATEADD(WEEK, -9, GETDATE()), 112));

SELECT DISTINCT	IncidentID, 
		CASE
			WHEN DateKey = @DateKey AND [Priority] = '1' AND SnapShotDateKey BETWEEN @FromDateKey AND @ToDateKey THEN 1
			ELSE 0
		END AS P1s
FROM		dbo.Fact_Incident
ORDER BY	P1s DESC;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 07/23/2013 :  07:04:37  Show Profile  Reply with Quote
Yeh Im getting rows but I need to be able to search between 1 whole week and not -9 weeks from today for that one day, I need the whole week: so below Im trying to return only between weeks 7 and 8?

Select Distinct f.IncidentID, i.IncidentNumber, f.DateKey, f.Priority,
Case
--When f.DateKey Between convert(int,convert(varchar(8),DateAdd(week,-7,GetDate()),0),112),0)
--And f.DateKey convert(int,convert(varchar(8),DateAdd(week,-10,GetDate()),112))
When f.CreatedDateTime BETWEEN DATEADD(week,-7,GETDATE())
And DATEADD(week,-8,GETDATE())
And f.[priority] = 1
--And f.DateKey BETWEEN convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0),112),0)
--And convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6),112),0)
Then 1 Else 0
End as P1s
From FACT_INCIDENT f
Join DIM_INCIDENT i
On f.IncidentID = i.IncidentID
order by P1s Desc,Priority

SZ1
to learn is to show the universe that you care...!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/23/2013 :  07:10:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You mean 7 and 8 weeks back?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/23/2013 :  07:13:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think it's time you learn the code you have been given before asking more questions.
Everything you need to know is there.
DECLARE	@FromDateKey INT = CONVERT(CHAR(8), DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()) - 9, '19000101'), 112),
	@ToDateKey INT = CONVERT(CHAR(8), DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()) - 8, '19000107'), 112),
	@DateKey INT = CONVERT(CHAR(8), DATEADD(WEEK, -9, GETDATE()), 112);

SELECT DISTINCT	IncidentID, 
		CASE
			WHEN DateKey = @DateKey AND [Priority] = '1' AND SnapShotDateKey BETWEEN @FromDateKey AND @ToDateKey THEN 1
			ELSE 0
		END AS P1s
FROM		dbo.Fact_Incident
ORDER BY	P1s DESC;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 07/23/2013 :  07:21:45  Show Profile  Reply with Quote
Yeh I get the code and it works well I get records back with your first code but now I want to be able to search between a week so yes looking back between weeks or for one whole week so week 8 and 9, how many rows.
Thanks

SZ1
to learn is to show the universe that you care...!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/23/2013 :  07:33:41  Show Profile  Reply with Quote
quote:
Originally posted by sz1

Yeh I get the code and it works well I get records back with your first code but now I want to be able to search between a week so yes looking back between weeks or for one whole week so week 8 and 9, how many rows.
Thanks

SZ1
to learn is to show the universe that you care...!


you just need to extend last suggestion to include parameters for weeks and then pass appropriate values


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

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 07/23/2013 :  07:53:51  Show Profile  Reply with Quote
Thanks for your help guys, much appreciated!

SZ1
to learn is to show the universe that you care...!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000