Problem grouping,,,,,,,with date

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

General SQL Server Forums

New to SQL Server Programming

Problem grouping,,,,,,,with date

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-03-19 : 11:02:45

Here's my sql which works

SELECT tblFileRequests.Dept, tblFileRequests.Division, tblFileRequests.Sect, COUNT(*) AS Expr1
FROM tblFileRequests LEFT OUTER JOIN
tblFileRequestDetails ON tblFileRequests.MovementId = tblFileRequestDetails.MovementId
GROUP BY tblFileRequests.Division, tblFileRequests.Sect, tblFileRequests.Dept
ORDER BY tblFileRequests.Dept, tblFileRequests.Division, tblFileRequests.Sect

I also want to include tblFileRequestDetails.DateOut but this cause my grouping to go haywire. How can I include it ? Ultimately I want to create a stored procedure and crystal report based on it. Thanks

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-19 : 11:06:21

what do you want to do with DateOut ? i.e. do you want to just show a single date (min / max etc...) and therefore not affect your current grouping? or do you want to group by 'something' to do with that date? ...like day / month / quarter etc..?

Em

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-03-19 : 11:10:23

In my web form I want the user to input a startdate and enddate and just return the values for that period. I can pass the parameters to the crystal report ok, but when I include the date field (to use in the crystal report) my sp doesn't sort and count properly.

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-03-19 : 11:10:45

Does your date field contains a time stamp?

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-03-19 : 11:12:04

Yes it does igorblackbelt

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-19 : 11:13:05

you can put a where clause into your query without affecting the group by. will that not do what you want?

Em

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-03-19 : 11:14:41

Yes that works, but I do not want the dates hardcoded in the query. I want the user to be able to input them when they run the report

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-19 : 11:16:05

so use variables?

where dateOut between @startDate and @endDate

Em

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-03-19 : 11:19:49

Try this:

SELECT tblFileRequests.Dept, tblFileRequests.Division, tblFileRequests.Sect, COUNT(*) AS Expr1, convert(char(10),tblFileRequestDetails.DateOut,101)
FROM tblFileRequests LEFT OUTER JOIN
tblFileRequestDetails ON tblFileRequests.MovementId = tblFileRequestDetails.MovementId
GROUP BY tblFileRequests.Dept, tblFileRequests.Division, tblFileRequests.Sect, convert(char(10),tblFileRequestDetails.DateOut,101)
ORDER BY tblFileRequests.Dept, tblFileRequests.Division, tblFileRequests.Sect

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-03-19 : 11:28:30

No, same result.

elancaster - I will then have to somehow pass the variables to the crystal report and then to the sp........

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-03-19 : 11:34:53

How about:

SELECT tblFileRequests.Dept, tblFileRequests.Division, tblFileRequests.Sect, COUNT(r.*) AS Expr1, convert(char(10),tblFileRequestDetails.DateOut,101)
FROM tblFileRequests r LEFT OUTER JOIN
tblFileRequestDetails d ON r.MovementId = d.MovementId
GROUP BY tblFileRequests.Dept, tblFileRequests.Division, tblFileRequests.Sect, convert(char(10),tblFileRequestDetails.DateOut,101)
ORDER BY tblFileRequests.Dept, tblFileRequests.Division, tblFileRequests.Sect

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-03-19 : 11:44:50

says * not recognised......

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-03-19 : 11:56:08

You have 1 request to many request details, correct? I'm wondering if the join is your problem.

SELECT r.Dept, r.Division, r.Sect, COUNT(r.*) AS Expr1, convert(char(10),d.tblFileRequestDetails.DateOut,101)
FROM tblFileRequests r
LEFT OUTER JOIN tblFileRequestDetails d ON r.MovementId = d.MovementId
GROUP BY r.Dept, r.Division, r.Sect, convert(char(10),d.tblFileRequestDetails.DateOut,101)
ORDER BY r.Dept, r.Division, r.Sect

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-03-20 : 05:16:03

I get this error in the sp

Incorrect syntax near '*'.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-20 : 05:29:59

don't cast a date as a char to get rid of the time. and your syntax error for * is because of the count(r.*)

if you want to include date use something like this


SELECT DATEADD(month,DATEDIFF(month,0,DateOut),0) as DateCol, tblFileRequests.Dept, tblFileRequests.Division, tblFileRequests.Sect, COUNT(*) AS Expr1
FROM tblFileRequests LEFT OUTER JOIN
tblFileRequestDetails ON tblFileRequests.MovementId = tblFileRequestDetails.MovementId
GROUP BY DATEADD(month,DATEDIFF(month,0,DateOut),0), tblFileRequests.Division, tblFileRequests.Sect, tblFileRequests.Dept
ORDER BY DATEADD(month,DATEDIFF(month,0,DateOut),0), tblFileRequests.Dept, tblFileRequests.Division, tblFileRequests.Sect

i guess your crystal report can then do the grouping and sum the count to exclude the date again if you want?

Em

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-03-20 : 05:56:09

Sorry - I just copied igorblackbelt's code.... Yours works fine though so thank you very much and for all the help received

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-20 : 05:58:23

good luck

have a nice long weekend then, and stay away from the M5!! (i'd like to get home at a reasonable time)

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-03-20 : 06:03:12

oops no it doesn't I get an entry for each day still........

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-20 : 06:06:54

i thought that's what you wanted? that way your report has the day in the resultset to limit the result by... and then your crystal report can be written to get rid of date again, no?

Em

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-03-20 : 06:08:55

I'll go back to the crystal report - I was having problems with the totals but will try again. Thx

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-20 : 06:09:36

...or you create it as a stored procedure with the @start and @end date variables in the where clause only (as above)

Em

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources