Stuck On A Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Stuck On A Query

Author

Topic

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-08-16 : 08:37:50

The user is requesting a query that a summary can be obtained every Thursday Morning (for Wed Totals) and the 1st of each month for lesson counts from the last day of the month. I have the beganing part of it i need help with the last part which is getting the 1st of each month...

SELECT LCLCN AS LOC, LCNAM AS NAME, cast (ROW_VALUE as decimal) AS LESSON_COUNT,'R' AS LESSON_TYPE, GETDATE()
FROM MAC.MAC.CACTUSDB.TBLPOSXR0 JOIN
MAC.MAC.AICDTAMAC.AICLCNM ON LCLCN = CAST(LOCATION AS DECIMAL)
WHERE ROW_ID='Room Rent Lesson Count' and (
substring(ACTIVITY_DATE,1,4)*10000 + substring(ACTIVITY_DATE,6,2)*100 + substring(ACTIVITY_DATE,9,2) =
CASE WHEN DATENAME(DW,getdate()) = 'Sunday'
THEN datepart(year, DATEADD(day, -4, getdate())) * 10000 + datepart(month, DATEADD (day, -4, getdate()))*100 + datepart(day, DATEADD (day, -4,
getdate()))
WHEN DATENAME(DW,getdate()) = 'Monday'
THEN datepart(year, DATEADD(day, -5, getdate())) * 10000 + datepart(month, DATEADD (day, -5, getdate()))*100 + datepart(day, DATEADD (day, -5,
getdate()))
WHEN DATENAME(DW,getdate()) = 'Tuesday'
THEN datepart(year, DATEADD(day, -6, getdate())) * 10000 + datepart(month, DATEADD (day, -6, getdate()))*100 + datepart(day, DATEADD (day, -6,
getdate()))
WHEN DATENAME(DW,getdate()) = 'Wednesday'
THEN datepart(year, DATEADD(day, -7, getdate())) * 10000 + datepart(month, DATEADD (day, -7, getdate()))*100 + datepart(day, DATEADD (day, -7,
getdate()))
WHEN DATENAME(DW,getdate()) = 'Thursday'
THEN datepart(year, DATEADD(day, -1, getdate())) * 10000 + datepart(month, DATEADD (day, -1, getdate()))*100 + datepart(day, DATEADD (day, -1,
getdate()))
WHEN DATENAME(DW,getdate()) = 'Friday'
THEN datepart(year, DATEADD(day, -2, getdate())) * 10000 + datepart(month, DATEADD (day, -2, getdate()))*100 + datepart(day, DATEADD (day, -2,
getdate()))
WHEN DATENAME(DW,getdate()) = 'Saturday'
THEN datepart(year, DATEADD(day, -3, getdate())) * 10000 + datepart(month, DATEADD (day, -3, getdate()))*100 + datepart(day, DATEADD (day, -3,
getdate()))
END

or

)

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 08:51:30

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

N 56°04'39.26"
E 12°55'05.63"

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-08-16 : 08:52:16

can you elaborate?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 08:52:52

And this single line will replace all your CASE WHEN above...

SELECT DATEADD(DAY, DATEDIFF(DAY, 2, GETDATE()) / 7 * 7, 2)

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 08:53:27

What datatype is ACTIVITY_DATE column?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 08:56:02

See this formula!

DECLARE	@Sample TABLE
	(
		theDate DATETIME
	)

INSERT	@Sample
SELECT	'20100805' UNION ALL	-- Thursday
SELECT	'20100806' UNION ALL	-- Friday
SELECT	'20100807' UNION ALL	-- Saturday
SELECT	'20100808' UNION ALL	-- Sunday
SELECT	'20100809' UNION ALL	-- Monday
SELECT	'20100810' UNION ALL	-- Tuesday
SELECT	'20100811' UNION ALL	-- Wednesday
SELECT	'20100812' UNION ALL	-- Thursday
SELECT	'20100813' UNION ALL	-- Friday
SELECT	'20100814'		-- Saturday

SELECT	theDate,
	DATENAME(WEEKDAY, theDate) AS theWeekdayName,
	DATEADD(DAY, DATEDIFF(DAY, 2, theDate) / 7 * 7, 2) AS [Including same wednesday],
	DATEADD(DAY, DATEDIFF(DAY, 3, theDate) / 7 * 7, 2) AS [Not including same wednesday]
FROM	@Sample

N 56°04'39.26"
E 12°55'05.63"

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-08-16 : 08:58:05

okay now will this give me the last day of month totals for the lesson counts? cuz this looks like the wed totals for thursday mornign each week

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 08:59:14

[code]SELECT LCLCN AS LOC,
LCNAM AS NAME,
CAST(ROW_VALUE AS DECIMAL) AS LESSON_COUNT,
'R' AS LESSON_TYPE,
GETDATE()
FROM MAC.MAC.CACTUSDB.TBLPOSXR0
INNER JOIN MAC.MAC.AICDTAMAC.AICLCNM ON LCLCN = CAST(LOCATION AS DECIMAL)
WHERE ROW_ID = 'Room Rent Lesson Count'
AND SUBSTRING(ACTIVITY_DATE, 1, 10) = CONVERT(CHAR(10), DATEADD(DAY, DATEDIFF(DAY, 3, GETDATE()) / 7 * 7, 2), 120)
AND SUBSTRING(ACTIVITY_DATE, 1, 10) >= CONVERT(CHAR(10), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0), 120)[/code]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 09:00:02

What datatype is ACTIVITY_DATE column?

N 56°04'39.26"
E 12°55'05.63"

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-08-18 : 15:44:21

I don't know what the datatype is i dont have rights to see that
based on the query that you have written i still see the day of data.
i need the data from the last day of the month

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-08-19 : 09:45:30

any help?

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-08-23 : 12:58:37

hi i still need help. really stuck on this due for production later today

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources