• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

begin and end of week in SQL

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
Jason Yousef, MS
Asked:
Jason Yousef, MS
  • 12
  • 8
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:

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
 
Rajkumar GsSoftware EngineerCommented:
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
 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
Hi Raj,
I got an error, can't figure it out !! attached ..

ScreenHunter-02-Aug.-17-10.59.gif
0
 
Rajkumar GsSoftware EngineerCommented:
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
 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
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
 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
Note that in the PIVOT query, only [2009] & [2010] is specified. These two year may vary based on your input date.

Raj
0
 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
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
 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
Thanks Raj
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 12
  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now