Complex SQL - 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 2000 Forums

SQL Server Development (2000)

Complex SQL

Author

Topic

dalewright24
Starting Member

6 Posts

Posted - 2007-09-18 : 06:50:11

Hi People...
Any chance of some help...
I have a view and SP. The view is used to calculate yearly totals for users:

CREATE VIEW dbo.qryYearlyCasuals
AS
SELECT DISTINCT
userID, SUM(totalAmountSum) AS SumOftotalAmountSum, SUM(TrainingAmount) AS SumOfTrainingAmount, SUM(TotalSub + TotalAmount)
AS SubsAndExp, SUM(NonEssUserNonNI) AS SumOfNonEssUserNonNI, SUM(NonEssUserNI) AS SumOfNonEssUserNI, SUM(LumpSum)
AS SumOfLumpSum, SUM(NIAmountTotal) AS SumOfNIAmountTotal, SUM(CONVERT(int, Miles1)) AS Miles1, SUM(CONVERT(int, Miles2)) AS Miles2,
SUM(CONVERT(int, Miles3)) AS Miles3, SUM(CONVERT(int, Miles4)) AS Miles4, SUM(CONVERT(int, Miles5)) AS Miles5, SUM(CONVERT(int, Miles6))
AS Miles6, SUM(CONVERT(int, Miles7)) AS Miles7, SUM(CONVERT(int, Miles8)) AS Miles8, SUM(CONVERT(int, Miles9)) AS Miles9, SUM(CONVERT(int,
Miles10)) AS Miles10
FROM dbo.tblApp
WHERE (CONVERT(datetime, AuthorisedHRDate, 103) BETWEEN CONVERT(datetime, '31/03/2007', 103)AND CONVERT(datetime, '31/03/2008', 103)) AND
(AuthorisedHRStatus = 1)
GROUP BY userID

As you can see i am filtering by a date range. However this is a currently a static value, i wish to change it a dynamic value (passed in by the user)

The sp is used to join the view together, so that i can produce a list of all users with values.

As follows:

CREATE PROCEDURE [YearlyCasualsReport]
AS
SELECT * FROM tblUser Left JOIN qryYearlyCasuals
ON tblUser.UserID = qryYearlyCasuals.UserID
WHERE tblUser.EssUser = 'No'
ORDER BY Clock
GO

This all works fine, however i am unable to pass a dyanmic value to the date. ie instead of 31/03/2007, i want to be able to allow the user to do this. Can some some give me some pointers or advice on how i can get around this?

Thanks for your time.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 06:56:45

[code]CREATE PROCEDURE dbo.uspYearlyCasualsReport
(
@FromDate DATETIME,
@ToDate DATETIME
)
AS

SET NOOCUNT ON

SELECT @FromDate = DATEADD(DAY, DATEDIFF(DAY, 0, @FromDate), 0),
@ToDate = DATEADD(DAY, DATEDIFF(DAY, 0, @FromDate), 1)

SELECT u.*,
d.*
FROM tblUser AS u
LEFT JOIN (
SELECT UserID,
SUM(TotalAmountSum) AS SumOftotalAmountSum,
SUM(TrainingAmount) AS SumOfTrainingAmount,
SUM(TotalSub + TotalAmount) AS SubsAndExp,
SUM(NonEssUserNonNI) AS SumOfNonEssUserNonNI,
SUM(NonEssUserNI) AS SumOfNonEssUserNI,
SUM(LumpSum) AS SumOfLumpSum,
SUM(NIAmountTotal) AS SumOfNIAmountTotal,
SUM(CONVERT(int, Miles1)) AS Miles1,
SUM(CONVERT(int, Miles2)) AS Miles2,
SUM(CONVERT(int, Miles3)) AS Miles3,
SUM(CONVERT(int, Miles4)) AS Miles4,
SUM(CONVERT(int, Miles5)) AS Miles5,
SUM(CONVERT(int, Miles6)) AS Miles6,
SUM(CONVERT(int, Miles7)) AS Miles7,
SUM(CONVERT(int, Miles8)) AS Miles8,
SUM(CONVERT(int, Miles9)) AS Miles9,
SUM(CONVERT(int, Miles10)) AS Miles10
FROM dbo.tblApp
WHERE AuthorisedHRDate >= @FromDate
AND AuthorisedHRDate < @ToDate
AND AuthorisedHRStatus = 1
GROUP BY UserID
) AS d ON d.UserID = u.UserID
WHERE u.EssUser = 'No'
ORDER BY Clock[/code]

E 12°55'05.25"
N 56°04'39.16"

dalewright24
Starting Member

6 Posts

Posted - 2007-09-18 : 08:50:37

Fantstic reply.. thank you very much... I was going wrong on the nested select. I tried this previous, but was getting an error, but once again, excellent and thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources