function - 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)

function

Author

Topic

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-08-23 : 15:28:56

I have this query which shows the results i want to see but i want to add 2 where conditions
where activity date = last wednesday
and
where acitivty date = results as of last day of last month

SELECT LCLCN AS LOC,
LCNAM AS NAME,
CAST(ROW_VALUE AS DECIMAL) AS LESSON_COUNT,
'R' AS LESSON_TYPE, Activity_Date FROM MAC.MAC.CACTUSDB.TBLPOSXR0 JOIN
MAC.MAC.AICDTAMAC.AICLCNM ON LCLCN = CAST(LOCATION AS DECIMAL)
order by Activity_date

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-23 : 17:11:14

You got the answer here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=148826

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

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-08-24 : 10:19:46

ok i'm sorry i asked the wrong question
i got ur answer to that now my problem is that i want to run both of the statements to gether to produce one resuilt set how can i do that?? an OR statement doesn't wokr right?

USE [ANALYTICS]
GO

/****** Object: StoredProcedure [dbo].[sp_ImportLessonCounts] Script Date: 08/12/2010 16:16:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

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
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)

)

GO

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-08-24 : 10:40:58

instead of an OR i put UNION in between the two queries that worked but i don't understand hte result set that i'm getting

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources