Solved

begin and end of week in SQL

Posted on 2010-08-16
24
416 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:huslayer
  • 12
  • 8
  • 4
24 Comments
 
LVL 142

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:huslayer
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 142

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
 
LVL 21

Author Comment

by:huslayer
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 142

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:huslayer
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 142

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:huslayer
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:huslayer
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:huslayer
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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:huslayer
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:huslayer
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:huslayer
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 500 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:huslayer
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:huslayer
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:huslayer
ID: 33559234
Thanks Raj
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now