Count down days and display sum for each day

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 2005 Forums

Transact-SQL (2005)

Count down days and display sum for each day

Author

Topic

bearswent
Starting Member

20 Posts

Posted - 2010-02-08 : 13:55:33

Hello
I have code below. Presently it shows total sum of hours between two dates. Trying to figure how to make it show summary for each day between two dates and leave total summary as it is?
Any help would be greatly appreciated!!!

select tblTeams.Name as Tech_Name, tblJobs.Startdate as Date, tblCustomers.Companyname as Customer,
tblJobs.Manhours, lkpJobSubStatus.Substatusname as Status, tblSchedules.subject as Summary

from tblTeams, tblJobs, tblSchedules, tblCustomers, lkpJobSubStatus

where tblJobs.ScheduleID = tblSchedules.ScheduleID
and tblSchedules.TeamID = tblTeams.TeamID
and tblSchedules.CustomerID = tblCustomers.CustomerID
and tblJobs.SubStatusID = lkpJobSubStatus.SubStatusID
and tbljobs.substatusid = '46'
and tblSchedules.StartDate >= '1/1/2010'
and tblSchedules.StartDate <= '3/1/2010'
compute SUM(tblJobs.Manhours)

Sachin.Nand

2937 Posts

Posted - 2010-02-09 : 01:47:15

Can u show some expected output?

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 05:09:02

[code]select DATEADD(dd,DATEDIFF(dd,0,tblJobs.Startdate),0) as Date, SUM(tblJobs.Manhours) AS TotalHours

from tblTeams, tblJobs, tblSchedules, tblCustomers, lkpJobSubStatus

where tblJobs.ScheduleID = tblSchedules.ScheduleID
and tblSchedules.TeamID = tblTeams.TeamID
and tblSchedules.CustomerID = tblCustomers.CustomerID
and tblJobs.SubStatusID = lkpJobSubStatus.SubStatusID
and tbljobs.substatusid = '46'
and tblSchedules.StartDate >= '1/1/2010'
and tblSchedules.StartDate <= '3/1/2010'
GROUP BY DATEADD(dd,DATEDIFF(dd,0,tblJobs.Startdate),0)
WITH CUBE
[/code]

bearswent
Starting Member

20 Posts

Posted - 2010-02-09 : 11:46:00

Thank you for your feedback

Out put should like this for say 7 days 11/1/2010 to 11/7/2010:

Name Date Customer manhours Status Summary
Bob 11/2/2010 ART 4 Comp bla bla
Mike 11/2/2010 ART 4 Comp bla bla
Sum Total 8
Date 11/2/2010

Bob 11/3/2010 ART 4 Comp bla bla
Mike 11/3/2010 ART 4 Comp bla bla
Sum Total 8
Date 11/3/2010

visakh16 code has the following output:
Date TotalHours
1/1/2010 4
Null Which is total hours

Looks like it combines date and I have to have every tech shown
Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 12:02:23

use the query below

select tblTeams.Name as Tech_Name, DATEADD(dd,DATEDIFF(dd,0,tblJobs.Startdate),0) as Date, 
tblCustomers.Companyname as Customer, SUM(tblJobs.Manhours) AS TotalHours, lkpJobSubStatus.Substatusname as Status, tblSchedules.subject as Summary

from tblTeams, tblJobs, tblSchedules, tblCustomers, lkpJobSubStatus 

where tblJobs.ScheduleID = tblSchedules.ScheduleID 
and tblSchedules.TeamID = tblTeams.TeamID 
and tblSchedules.CustomerID = tblCustomers.CustomerID
and tblJobs.SubStatusID = lkpJobSubStatus.SubStatusID
and tbljobs.substatusid = '46'
and tblSchedules.StartDate >= '1/1/2010'
and tblSchedules.StartDate <= '3/1/2010'
GROUP BY tblTeams.Name, DATEADD(dd,DATEDIFF(dd,0,tblJobs.Startdate),0), 
tblCustomers.Companyname as Customer, lkpJobSubStatus.Substatusname as Status, tblSchedules.subject as Summary

then use front end application to generate data in format you want.
whats the front end you're using?

bearswent
Starting Member

20 Posts

Posted - 2010-02-09 : 13:57:51

Hello visakh16

Thank you for feedback

For some reason I do not get any SUM totals. it just lists

Name Date Customer Manhours Status Summary

This is meant to be a part of store procedure which will be picked by Crystal Reports

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 14:07:07

quote:
Originally posted by bearswent

Hello visakh16

Thank you for feedback

For some reason I do not get any SUM totals. it just lists

Name Date Customer Manhours Status Summary

This is meant to be a part of store procedure which will be picked by Crystal Reports

and you're sure you've data satisfying below conditions?


...
where tblJobs.ScheduleID = tblSchedules.ScheduleID 
and tblSchedules.TeamID = tblTeams.TeamID 
and tblSchedules.CustomerID = tblCustomers.CustomerID
and tblJobs.SubStatusID = lkpJobSubStatus.SubStatusID
and tbljobs.substatusid = '46'
and tblSchedules.StartDate >= '1/1/2010'
and tblSchedules.StartDate <= '3/1/2010'

bearswent
Starting Member

20 Posts

Posted - 2010-02-09 : 14:25:25

visakh16
Yes, I have 9 records if I run original code with 5 different dates:

select tblTeams.Name as Tech_Name, tblJobs.Startdate as Date, tblCustomers.Companyname as Customer,
tblJobs.Manhours, lkpJobSubStatus.Substatusname as Status, tblSchedules.subject as Summary

from tblTeams, tblJobs, tblSchedules, tblCustomers, lkpJobSubStatus

where tblJobs.ScheduleID = tblSchedules.ScheduleID
and tblSchedules.TeamID = tblTeams.TeamID
and tblSchedules.CustomerID = tblCustomers.CustomerID
and tblJobs.SubStatusID = lkpJobSubStatus.SubStatusID
and tbljobs.substatusid = '46'
and tblSchedules.StartDate >= '1/1/2010'
and tblSchedules.StartDate <= '3/1/2010'
compute SUM(tblJobs.Manhours)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 14:27:23

are you telling about output in reports or are you telling about results you get in suery analyser? If former, can you try in query analyser also to see if you've not getting any values?

------------------------------------------------------------------------------------------------------
SQL Server MVP

bearswent
Starting Member

20 Posts

Posted - 2010-02-09 : 14:29:45

I am getting these result from SQl management Studio just by running new query before it becomes part of store procedure

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 14:31:26

quote:
Originally posted by bearswent

I am getting these result from SQl management Studio just by running new query before it becomes part of store procedure

you just get headers alone with no data at all?

------------------------------------------------------------------------------------------------------
SQL Server MVP

bearswent
Starting Member

20 Posts

Posted - 2010-02-09 : 14:33:03

I get all 9 records with your code but no summaries?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 14:49:51

quote:
Originally posted by bearswent

I get all 9 records with your code but no summaries?

Aha..you mean sum() bits? thats what I told you to generate in reports

------------------------------------------------------------------------------------------------------
SQL Server MVP

bearswent
Starting Member

20 Posts

Posted - 2010-02-09 : 15:48:50

I do not know how to accomplish break up by days in Crystal.

Sum by day, another sum is for whole period (sum of all sums). Is not there a way to create a sum for a single day in the range of dates?

I can do total sum in Crystal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 01:51:20

quote:
Originally posted by bearswent

I do not know how to accomplish break up by days in Crystal.

Sum by day, another sum is for whole period (sum of all sums). Is not there a way to create a sum for a single day in the range of dates?

I can do total sum in Crystal

what about this?


select tblTeams.Name as Tech_Name, DATEADD(dd,DATEDIFF(dd,0,tblJobs.Startdate),0) as Date, 
tblCustomers.Companyname as Customer, SUM(tblJobs.Manhours) AS TotalHours, lkpJobSubStatus.Substatusname as Status, tblSchedules.subject as Summary

from tblTeams, tblJobs, tblSchedules, tblCustomers, lkpJobSubStatus 

where tblJobs.ScheduleID = tblSchedules.ScheduleID 
and tblSchedules.TeamID = tblTeams.TeamID 
and tblSchedules.CustomerID = tblCustomers.CustomerID
and tblJobs.SubStatusID = lkpJobSubStatus.SubStatusID
and tbljobs.substatusid = '46'
and tblSchedules.StartDate >= '1/1/2010'
and tblSchedules.StartDate <= '3/1/2010'
GROUP BY tblTeams.Name, DATEADD(dd,DATEDIFF(dd,0,tblJobs.Startdate),0), 
tblCustomers.Companyname as Customer, lkpJobSubStatus.Substatusname as Status, tblSchedules.subject as Summary 
WITH ROLLUP

then filter out unwanted aggregate rows in crystal report

------------------------------------------------------------------------------------------------------
SQL Server MVP

bearswent
Starting Member

20 Posts

Posted - 2010-02-25 : 19:02:25

Hello The outcome I get looks like this:

Line Techname Date Customer TotalHours Status Summary
1. Bob 1/1/2010 NK 0.5 bla bla bla 1
2. Bob 1/1/2010 NK 1 bla bla bla 2
3. Bob 1/1/2010 NK 1.5 bla Null
4. Bob 1/1/2010 NK 1.5 NULL Null
5. Bob 1/1/2010 Null 1.5 NULL Null
6. Null 1/1/2010 Null 1.5 NULL Null

I would like to keep line 1, 2 , 5,6.

Not sure how to do it Crystal. Can be done with sql?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 08:51:33

whats the significance of those 4 rows?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bearswent
Starting Member

20 Posts

Posted - 2010-02-26 : 15:44:07

Hello
I am trying to get list of jobs for any given day, with total time spent for all employees on that day. In the end of report get total hours for all days. Rollup generally works but it creates garbage data lines 3,4,5 with Null values. I do not know how to suppress them in Crystal reports

bearswent
Starting Member

20 Posts

Posted - 2010-02-26 : 16:36:59

Hello
I have modified the query and trying to get it say Grant total when date is NULL. The error I get is Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

select
case when DATEADD(dd,DATEDIFF(dd,0,tblJobs.Startdate),0) is null then 'Grand Total' else DATEADD(dd,DATEDIFF(dd,0,tblJobs.Startdate),0) end,
tblTeams.Name as Tech_Name,
tblCustomers.Companyname as Customer,
SUM(tblJobs.Manhours) AS TotalHours, lkpJobSubStatus.Substatusname as Status,
tblSchedules.subject as Summary

from tblTeams, tblJobs, tblSchedules, tblCustomers, lkpJobSubStatus

where tblJobs.ScheduleID = tblSchedules.ScheduleID
and tblSchedules.TeamID = tblTeams.TeamID
and tblSchedules.CustomerID = tblCustomers.CustomerID
and tblJobs.SubStatusID = lkpJobSubStatus.SubStatusID
and tbljobs.substatusid = '46'
and tblSchedules.StartDate >= '1/1/2010'
and tblSchedules.StartDate <= '3/1/2010'
GROUP BY DATEADD(dd,DATEDIFF(dd,0,tblJobs.Startdate),0), tblTeams.Name,
tblCustomers.Companyname, lkpJobSubStatus.Substatusname, tblSchedules.subject with ROLLUP
order by DATEADD(dd,DATEDIFF(dd,0,tblJobs.Startdate),0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 23:36:10

try passing date values as
and tblSchedules.StartDate >= '20100101'
and tblSchedules.StartDate <= '20100103'

also is tblJobs.Startdate datetime field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 01:11:32

If tblSchedules.StartDate has time too

and tblSchedules.StartDate >= '20100101'
and tblSchedules.StartDate < '20100104'

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources