Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

begin and end of week in SQL

Posted on 2010-08-16
24
Medium Priority
?
423 Views
Last Modified: 2012-05-10
Hello all,
We have this query to get a yearly comparison report

declare @DateTable TABLE(Date datetime)

;WITH cte_DatesTable
AS
(
  SELECT CAST('20070101' as datetime) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= Getdate()
)
INSERT INTO @DateTable
SELECT [date]
FROM CTE_DatesTable 
WHERE DATEPART(m,[date]) <= DATEPART(m,Getdate())
	AND DATEPART(d,[date]) <= (CASE WHEN DATEPART(m,[date]) = DATEPART(m,Getdate()) THEN DATEPART(d,Getdate()) ELSE 31 END)
OPTION (MAXRECURSION 0)

				
SELECT [PT_Type_Group]
	, ADM_DR
	, [ADM_DR_ID]
	, [splty]
	,[2009] AS Total2009
	,[2010] AS Total2010
FROM
(SELECT [PT_Type_Group]
	, ADM_DR
	, [ADM_DR_ID]
	, [splty]
	, YEAR(ADM_DATE) AS [Year]
	, COUNT(ADM_DR_ID) AS NumPerYear
FROM         Data_Final
WHERE (ADM_DATE) IN (SELECT * FROM @DateTable)
GROUP BY [PT_Type_Group]
	, ADM_DR
	, [ADM_DR_ID]
	, [splty]
	, YEAR(ADM_DATE)) x
PIVOT
(
SUM(NumPerYear)
FOR [Year] IN ([2009],[2010])
) AS pvt

Open in new window



now we need to get a weekly not yearly anymore

so I've edited the code to
declare @DateTable TABLE(Date datetime)

;WITH cte_DatesTable
AS
(
  SELECT CAST('20070101' as datetime) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= GETDATE()
)
INSERT INTO @DateTable
SELECT [date]
FROM CTE_DatesTable 
WHERE DATEPART(m,[date]) <= DATEPART(m,GETDATE())
	AND DATEPART(d,[date]) <= (CASE WHEN DATEPART(m,[date]) = DATEPART(m,GETDATE()) THEN DATEPART(d,GETDATE()) ELSE 31 END)
OPTION (MAXRECURSION 0)
			
SELECT [PT_Type_Group]
	, ADM_DR
	, [ADM_DR_ID]
	, [splty]
	,[2009] AS Total2009
	,[2010] AS Total2010
FROM
(SELECT [PT_Type_Group]
	, ADM_DR
	, [ADM_DR_ID]
	, [splty]
	, YEAR(ADM_DATE) AS [Year]
	, COUNT(ADM_DR_ID) AS NumPerYear
FROM         Data_Final


WHERE (ADM_DATE) IN (SELECT * FROM @DateTable where Day(Date) between 
'Day(dateadd(dd, (datepart(dw, GetDate()) * -1) + 2, GetDate()))'
and 
'Day(dateadd(dd, (datepart(dw, GetDate()) * -1) + 5, GetDate()))')


GROUP BY [PT_Type_Group]
	, ADM_DR
	, [ADM_DR_ID]
	, [splty]
	, YEAR(ADM_DATE)) x
PIVOT
(
SUM(NumPerYear)
FOR [Year] IN ([2009],[2010])
) AS pvt


I keep getting error :  Conversion failed when converting the varchar value 'Day(dateadd(dd, (datepart(dw, GetDate()) * -1) + 5, GetDate()))' to data type int.


any clue what I'm doing wrong?

Any help is appreciated
Thanks

Open in new window


0
Comment
Question by:Jason Yousef, MS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8
  • 4
24 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33449286
you have to remove the quotes:
WHERE (ADM_DATE) IN (SELECT * FROM @DateTable 
where Date >= dateadd(dd, (datepart(dw, GetDate()) * -1) + 2, GetDate()) )
  and Date <  dateadd(dd, (datepart(dw, GetDate()) * -1) + 5, GetDate()) ) 

Open in new window

0
 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 33453696
Hi Angellll,
but that won't get me any results in 2009 , what i'm trying to do is to compare the same dates in 2009 to 2010

that's why i was trying to get the day number.
sorry if I confused you

for example if this week starts at the 16th , then I need 16-17-18-19-20-21-22 from 2009 and 2010 for the same month too.

how that helps

thanks again
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33453706
so:
WHERE (ADM_DATE) IN (SELECT * FROM @DateTable 
where ( Date >= dateadd(dd, (datepart(dw, GetDate()) * -1) + 2, GetDate()) )
    and Date <  dateadd(dd, (datepart(dw, GetDate()) * -1) + 5, GetDate()) ) 
      )
  OR ( Date >= dateadd(year, -1, dateadd(dd, (datepart(dw, GetDate()) * -1) + 2, GetDate()) ))
    and Date <  dateadd(year, -1, dateadd(dd, (datepart(dw, GetDate()) * -1) + 5, GetDate()) ) )
      )

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 33453950
Still gives me only the 16 and the 17 of 2009 and 2010 and I need the whole week !

I even tried that too !! I don't know what's wrong !



where 
day(Date) >= CAST (Day(dateadd(dd, (datepart(dw, GetDate()) * -1) + 2, GetDate())) as int) and
MONTH(Date) = MONTH(getdate())
and
day(Date) < CAST (Day(dateadd(dd, (datepart(dw, GetDate()) * -1) + 6, GetDate())) as int)
and MONTH(Date) = MONTH(getdate())

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33454014
what about:
WHERE (ADM_DATE) IN (SELECT * FROM @DateTable 
where ( Date >= dateadd(dd, (datepart(dw, GetDate()) * -1) - 2, GetDate()) )
    and Date <  dateadd(dd, (datepart(dw, GetDate()) * -1) + 5, GetDate()) ) 
      )
  OR ( Date >= dateadd(year, -1, dateadd(dd, (datepart(dw, GetDate()) * -1) - 2, GetDate()) ))
    and Date <  dateadd(year, -1, dateadd(dd, (datepart(dw, GetDate()) * -1) + 5, GetDate()) ) )
      )

Open in new window

0
 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 33454173
Hi Angelll,
Nope, it gave me wrong results.
this week should start on the 15th till the 21st

ScreenHunter-01-Aug.-17-09.08.gif
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33454375
what does this give for you?
select dateadd(dd, (datepart(dw, GetDate()) * -1) +1, GetDate())
     , dateadd(dd, (datepart(dw, GetDate()) * -1) +7, GetDate())
     , dateadd(year, -1, dateadd(dd, (datepart(dw, GetDate()) * -1) +1, GetDate()))
     , dateadd(year, -1, dateadd(dd, (datepart(dw, GetDate()) * -1) +7, GetDate()))

Open in new window

0
 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 33454639

gives me 4 columns, look at the attached screenshot

2010-08-15 09:52:36.383    2010-08-21 09:52:36.383    2009-08-15 09:52:36.383    2009-08-21 09:52:36.383

by the way, i'm testing against the time table, so you can test it too

here's the query i'm using

declare @DateTable TABLE(Date datetime)

;WITH cte_DatesTable
AS
(
  SELECT CAST('20070101' as datetime) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= GETDATE()
)
INSERT INTO @DateTable
SELECT [date]
FROM CTE_DatesTable 
WHERE DATEPART(m,[date]) <= DATEPART(m,GETDATE())
	AND DATEPART(d,[date]) <= (CASE WHEN DATEPART(m,[date]) = DATEPART(m,GETDATE()) THEN DATEPART(d,GETDATE()) ELSE 31 END)
OPTION (MAXRECURSION 0)
			

select dateadd(dd, (datepart(dw, GetDate()) * -1) +1, GetDate())
     , dateadd(dd, (datepart(dw, GetDate()) * -1) +7, GetDate())
     , dateadd(year, -1, dateadd(dd, (datepart(dw, GetDate()) * -1) +1, GetDate()))
     , dateadd(year, -1, dateadd(dd, (datepart(dw, GetDate()) * -1) +7, GetDate()))

Open in new window

ScreenHunter-01-Aug.-17-09.54.gif
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33455236
Huslayer,

Can you try by modifying last query like attached. Let us know the result.

Raj
SELECT [PT_Type_Group]
	, ADM_DR
	, [ADM_DR_ID]
	, [splty]
	,[2009] AS Total2009
	,[2010] AS Total2010
FROM
(SELECT [PT_Type_Group]
	, ADM_DR
	, [ADM_DR_ID]
	, [splty]
	, DateAdd(day, -1 * datepart(dw, ADM_DATE), ADM_DATE ) AS [Week]
	, COUNT(ADM_DR_ID) AS NumPerWeek
FROM         Data_Final
WHERE (ADM_DATE) IN (SELECT * FROM @DateTable)
GROUP BY [PT_Type_Group]
	, ADM_DR
	, [ADM_DR_ID]
	, [splty]
	, DateAdd(day, -1 * datepart(dw, ADM_DATE), ADM_DATE )) x
PIVOT
(
SUM(NumPerWeek)
FOR Year([Week]) IN ([2009],[2010])
) AS pvt

Open in new window

0
 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 33455357
Hi Raj,
I got an error, can't figure it out !! attached ..

ScreenHunter-02-Aug.-17-10.59.gif
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33456402
Hi Huslayer,

Try this corrected query and let me know

Regards
Raj
SELECT [PT_Type_Group]
	, ADM_DR
	, [ADM_DR_ID]
	, [splty]
	,[2009] AS Total2009
	,[2010] AS Total2010
FROM
(SELECT *, YEAR([Week]) AS [Year] 
FROM
(SELECT [PT_Type_Group]
	, ADM_DR
	, [ADM_DR_ID]
	, [splty]
	, DateAdd(day, -1 * datepart(dw, ADM_DATE), ADM_DATE ) AS [Week]
	, COUNT(ADM_DR_ID) AS NumPerWeek
FROM         Data_Final
WHERE (ADM_DATE) IN (SELECT * FROM @DateTable)
GROUP BY [PT_Type_Group]
	, ADM_DR
	, [ADM_DR_ID]
	, [splty]
	, DateAdd(day, -1 * datepart(dw, ADM_DATE), ADM_DATE )) y) x
PIVOT
(
SUM(NumPerWeek)
FOR [Year] IN ([2009],[2010])
) AS pvt

Open in new window

0
 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 33456873
Raj,
thanks!
 It worked but gave wrong results.
wrong data and didn't group it, weird results !
look at the attached image

ScreenHunter-03-Aug.-17-13.22.gif
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33456971
It was an untested query.

Can you post your create table query (table structure), sample data & expected result ?

If I have it, it will more easier

Raj
0
 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 33457088
Sure,
sample data and create table attached
also expected results.





please note that it's not a live data, all the names has been changed and the data is not correct.

expected-results.gif
sample-data.txt
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33457506
I am little bit confused with your requirement.

As you want weekly report instead of year report, how it could be only two columns 'Total 2009' & 'Total 2010' ? Only these two columns means total of 2009 & 2010, right ? Then how the split up totals based on weeks will displayed in result ?

Raj
0
 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 33457582
it's a weekly comparison between the number of patients for each doctor, comparing his admissions in the same week in 2009 to this week in 2010.

the reason I'm using a time table so I can run the report effective any date in SSRS

i'll change getdate() to @paramater later when we finish the code.


hope you got the idea..

0
 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 33457625
example DR. Raj in 2009 in week 34 had 15 patients and in 2010 in week 34 he had 10 only and he works in the emergency department

so the final results should look like:

report running date  08/17/2010  that's week 34

PT TYPE      ADM DR        ADM DR ID               SPTY    2009      2010
EMergency   RAJ                  1234                       EMR     15           10.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33457896
Ok. Hope now I understood.

You sample data are mostly of the year 2007, 2008 & 2009. And your expected data based on that sample data seems to be not right.

If we run query against your sample data as on today's date, if I am right it will not have much result.

Anyway I am trying by passing current date as parameter, which have some output data.

Raj
0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 2000 total points
ID: 33458034
Here is the query.

Here I have declared a new parameter for report date. Test it with various values that having data.

Please let me know
Raj
DECLARE @CURRENTDATE DATETIME
SET @CURRENTDATE = '2010-03-23' -- Report date 

DECLARE  @Data_Final TABLE(
	[PT_Type_Group] [varchar](31) NULL,
	[ADM_DR_ID] [varchar](50) NULL,
	[ADM_DR] [varchar](50) NULL,
	[splty] [nvarchar](255) NULL,
	[Year]	int,
	[Count] int
)

INSERT INTO @Data_Final
SELECT PT_Type_Group, ADM_DR_ID, ADM_DR, splty, YEAR(ADM_DATE) AS [Year], COUNT(ADM_DR_ID) AS [Count]
FROM [dbo].[Data_Final]
	WHERE datepart(week, [ADM_DATE]) = Datepart(week, @CURRENTDATE) -- Comparing current week data only 
				AND (YEAR([ADM_DATE]) = YEAR(@CURRENTDATE) OR YEAR([ADM_DATE]) = YEAR(@CURRENTDATE)-1) -- for this year & previous year
GROUP BY PT_Type_Group, ADM_DR_ID, ADM_DR, splty, YEAR(ADM_DATE)

SELECT *
FROM @Data_Final
PIVOT
 (
	 SUM([Count])
	 FOR [Year]
	 IN ([2009],[2010])
 )
 AS p

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33458090
Note that in the PIVOT query, only [2009] & [2010] is specified. These two year may vary based on your input date.

Raj
0
 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 33458327
Raj,
thanks Bro...

the expected output is correct but the data is not, I just ran a quick test on one DR and the numbers are not accurate.

look at the attached pic and the SSRS used code :)

am I doing something wrong ?

DECLARE @CURRENTDATE DATETIME
SET @CURRENTDATE = @paramdate -- Report date 

DECLARE  @Data_Final TABLE(
	[PT_Type_Group] [varchar](31) NULL,
	[ADM_DR_ID] [varchar](50) NULL,
	[ADM_DR] [varchar](50) NULL,
	[splty] [nvarchar](255) NULL,
	[Year]	int,
	[Count] int
)

INSERT INTO @Data_Final
SELECT PT_Type_Group, ADM_DR_ID, ADM_DR, splty, YEAR(ADM_DATE) AS [Year], COUNT(ADM_DR_ID) AS [Count]
FROM [dbo].[Data_Final]
	WHERE datepart(week, [ADM_DATE]) = Datepart(week, @CURRENTDATE) -- Comparing current week data only 
				AND (YEAR([ADM_DATE]) = YEAR(@CURRENTDATE) OR YEAR([ADM_DATE]) = YEAR(@CURRENTDATE)-1) -- for this year & previous year
GROUP BY PT_Type_Group, ADM_DR_ID, ADM_DR, splty, YEAR(ADM_DATE)

SELECT *
FROM @Data_Final
PIVOT
 (
	 SUM([Count])
	 FOR [Year]
	 IN ([2009],[2010])
 )
 AS p
 order by ADM_DR

Open in new window

raj-error.jpg
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33458654
Hi Hussain,

Those two dates you used in the where conditon '20090815' & '20090821' belongs to 33rd week & 34th week respectively. You input parameter date '20090817' belongs to 34th week.

SELECT datepart(week, '20090815') -- 33th week of 2009
SELECT datepart(week, '20090821') -- 34th week of 2009
SELECT datepart(week, '20090817') -- 34th week of 2009

That is the issue

This could be right query to check based week number.

SELECT adm_dr
, COUNT(ADM_DR_ID)
 from Data_Final
where ADM_DR_ID = '31061' and datepart(week, ADM_DATE) = datepart(week, '20090817')

Raj
0
 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 33471642
Raj,
I don't know about this query, look at the two attached 2 screen shots.
Thanks :)

ScreenHunter-01-Aug.-18-23.00.jpg
ScreenHunter-03-Aug.-18-23.01.jpg
0
 
LVL 21

Author Closing Comment

by:Jason Yousef, MS
ID: 33559234
Thanks Raj
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question