Year To Date Table - 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 2005 Forums

Transact-SQL (2005)

Year To Date Table

Author

Topic

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-11-06 : 13:46:43

Hi guys I have a question, dont know if this is the best approach though. I have a database that has 11 tables in it, each table represents a department. Now once a month the departments but there stats for the month into thier designated table. The data from this table will show in a report in report services, what the boss wants is for the report to show a YTD for each category (COLUMN). In other words

New Hires - Month 20 YtD 30
Updates - Month 40 YTD 100

What I wsa wondering is if a YTD child table link to the Deparment tables would be best or calcualte it inot report services for each column?? And how would i do that??

thank you

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-06 : 14:42:58

Please post some sample data and desired results.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-06 : 16:24:08

Once a month each department put their stats into their Table: Please clarify. I am guessing they are they are manually typing in a precalculated number into each category for that Month.

Yes, post your table and sample data.


Select category, [month], stats, (select sum(stats) from <table> where category = z.category and month <= z.month) as ytd
from <table> z
where [month] = '11'

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-11-06 : 17:54:21

here is one of the tables, what I would like to show in Report services is the amount of [StateKeyNewApplicants] 12 and then the YTD next to it. and the end of the month each dept will enter in thier [StateKeyNewApplicants] for the month, their [FinalSuitabilitySenttoState] for the month..so on and so forth. as the year goes on I just want to show a total for each category that would be that categories YTD (Example: YTD FOR [FinalSuitabilitySenttoState])

CREATE TABLE [dbo].[State](
	[StateKeyNewApplicants] [nvarchar](50) NULL,
	[FinalSuitabilitySenttoState] [nvarchar](50) NULL,
	[CGCCCertificationOfFindingSuitReturned] [nvarchar](50) NULL,
	[RenewalsSenttoState] [nvarchar](50) NULL,
	[Denials] [nvarchar](50) NULL,
	[Revocation] [nvarchar](50) NULL,
	[Suspension] [nvarchar](50) NULL,
	[Notifications] [nvarchar](50) NULL,
	[UpgradesToKeyPosition] [nvarchar](50) NULL,
	[StateRptID] [int] IDENTITY(1,1) NOT NULL,
	[Start_Date] [datetime] NULL,
	[End_Date] [datetime] NULL,
 CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED 
(

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-06 : 18:35:26

can you provide data? I'm still confused about the Entering [StateKeyNewApplicants] for the month, their [FinalSuitabilitySenttoState] for the month..

Are these column you've mentioned NUMERICS? What do they actually put into these columns?

Please provide SAMPLE DATA. thanks!

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-11-06 : 19:23:37

StateKeyNewApplicants- 22
FinalSuitabilitySenttoState- 33
Denials - 10
Revocations - 20
Start Date 10/01/2008
End Date 10/31/2008

Yes Numerics, datatypes should be INT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 23:52:02

how will handle those counts which spans over a month? i.e start date and end date falling on different months? will you add them to start date month's count or enddate month's?

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-11-07 : 03:41:20

Example when the new year starts and we are in January lets say the numbers are
StateKeyNewApplicants- 22
FinalSuitabilitySenttoState- 33
Denials - 10
Revocations - 20

the YTD for those numbers will be the same 22,33, 10, 20. Now the next month February will be januray's numbers plus febuary. I just need to keep a running total of each column thats all. And I think I need to do that in report services. Each deparment has to put down the date so that the boss knows what dates the stats for that department is for, it would be so that he would know that dept D stats are from 10/01/2008 to 10/31/2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 04:56:20

Still didnt answer my point. I was asking what date you will consider start or end date?

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-11-07 : 11:22:13

The beginning of the month and the end of the month, they are suppose to calculate theirs totals (performance) for every month. So if Gaming Dept get 45 Fingerprints for the of October and 50 for the month of November then the YTD needs to be 95. Does that answer your question, I just need to keep a running total of each column

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 11:27:08

nope.not fully. suppose you've a record with startdate value in table State in October and enddate in November, will you calculate it among 45 or 50?
in other words, what date value will you consider while counting number of records for month?

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-11-07 : 11:36:46

I would calculate it among 50

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-07 : 11:38:00

[code]
select start_date, end_date,
statekeynewapplicants, (select sum(cast(statekeynewapplicants as int)) from state where start_date <= z.start_date) as tyd1,
FinalSuitabilitySenttoState, (select sum(cast(FinalSuitabilitySenttoState as int)) from state where start_date <= z.start_date) as tyd2,
CGCCCertificationOfFindingSuitReturned, (select sum(cast(CGCCCertificationOfFindingSuitReturned as int)) from state where start_date <= z.start_date) as tyd3,
RenewalsSenttoState, (select sum(cast(RenewalsSenttoState as int)) from state where start_date <= z.start_date) as tyd4,
Denials, (select sum(cast(Denials as int)) from state where start_date <= z.start_date) as tyd5,
Revocation, (select sum(cast(Revocation as int)) from state where start_date <= z.start_date) as tyd6,
Suspension, (select sum(cast(Suspension as int)) from state where start_date <= z.start_date) as tyd7,
Notifications, (select sum(cast(Notifications as int)) from state where start_date <= z.start_date) as tyd8,
UpgradesToKeyPosition, (select sum(cast(UpgradesToKeyPosition as int)) from state where start_date <= z.start_date) as tyd9
from state z[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 11:53:51

quote:
Originally posted by muzzettemm

I would calculate it among 50

SELECT DATEPART(mm,Date) AS Month,
MonthStateKeyNewApplicantsCount,
MonthFinalSuitabilitySenttoStateCount,
MonthDenialsCount,
MonthRevocationsCount,
YTDStateKeyNewApplicantsCount,
YTDFinalSuitabilitySenttoStateCount,
YTDDenialsCount,
YTDRevocationsCount
FROM
(
SELECT DATEADD(mm,DATEDIFF(mm,0,StartDate),0) AS Date,
       COUNT(StateKeyNewApplicants) AS MonthStateKeyNewApplicantsCount,
       COUNT(FinalSuitabilitySenttoState) AS MonthFinalSuitabilitySenttoStateCount,
       COUNT(Denials) AS MonthDenialsCount,
       COUNT(Revocations) AS MonthRevocationsCount
FROM YourTable
GROUP BY DATEADD(mm,DATEDIFF(mm,0,StartDate),0)
)mn
CROSS APPLY
(
SELECT COUNT(StateKeyNewApplicants) AS YTDStateKeyNewApplicantsCount,
       COUNT(FinalSuitabilitySenttoState) AS YTDFinalSuitabilitySenttoStateCount,
       COUNT(Denials) AS YTDDenialsCount,
       COUNT(Revocations) AS YTDRevocationsCount
FROM YourTable
WHERE StartDate<DATEADD(mm,1,Date)
)rn

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-01 : 14:01:45

never mind got it now thank you Visakh16 :)

SELECT DATEPART(mm,Date) AS Month,
MonthStateKeyNewApplicantsCount,
MonthFinalSuitabilitySenttoStateCount,
MonthDenialsCount,
MonthRevocationsCount,
YTDStateKeyNewApplicantsCount,
YTDFinalSuitabilitySenttoStateCount,
YTDDenialsCount,
YTDRevocationCount
FROM 
(
SELECT DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0) AS Date,
       COUNT(StateKeyNewApplicants) AS MonthStateKeyNewApplicantsCount,
       COUNT(FinalSuitabilitySenttoState) AS MonthFinalSuitabilitySenttoStateCount,
       COUNT(Denials) AS MonthDenialsCount,
       COUNT(Revocation) AS MonthRevocationsCount
FROM StateLicensing
GROUP BY DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0)
)mn
CROSS APPLY
(
SELECT COUNT(StateKeyNewApplicants) AS YTDStateKeyNewApplicantsCount,
       COUNT(FinalSuitabilitySenttoState) AS YTDFinalSuitabilitySenttoStateCount,
       COUNT(Denials) AS YTDDenialsCount,
       COUNT(Revocation) AS YTDRevocationCount
FROM StateLicensing
WHERE [Start_Date] <DATEADD(mm,1,Date)
)rn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 23:51:34

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources