How to obtain week by week plus YTD totals

Question

Post reply

How to obtain week by week plus YTD totals

simflex-897410

Hall of Fame

Points: 3881
More actions
May 10, 2010 at 7:45 pm

#224401

Greetings experts,
K Cline was kind and generous, and patient enough to assist me with a problem that displays weekly, as well as YTD totals.
For instance, the code below, will give me current week's total and cumulative YTD totals.
But I really need one more help with the part 2 of this problem.
This part displays all weekly totals, and the cumulative YTD totals.
Below is a sample of how data should look like:
Week1 Week2 Week3 Week4 Week5 Week6 Week7 Week8 Week9 Week10 Week11 Week12 Week13 YTD
Date1 Date2 Date3 Date4 Date5 Date6 Date7 Date8 Date9 Date10 Date11 Date12 Date13
----------------------------------------------------------------------------------------------------------------
12 37 12 16 23 66 34 67 24 62 33 44 51 481
etc
etc
Date1, date2...Date13 represent date ranges like the one on the code below:
Declare @StartDate DateTime;
Declare @EndDate DateTime;
Declare @ReportYear Int;
set @StartDate = '4/8/2010';
set @EndDate = '4/15/2010';
-- Determine the year you are working with.
Set @ReportYear = YEAR(@StartDate);
-- Build the CTEs to hold your unpivoted data.
With WeeklyReport (IncidentType, [Reporting Week])
As
(
Select IncidentType, [Reporting Week]
From
(
Select ReportQID,CONVERT(NVarChar(150), NumFoodSvcInsPerformed) As [# of food service inspections performed],
CONVERT(NVarChar(150), NumRoutineFoodSvcInsPerformed) As [# of routine food service inspections performed],
CONVERT(NVarChar(150), NumFollowupFoodSvcInsPerformed) As [# of follow-up food service inspections performed],
CONVERT(NVarChar(150), NumCompliantFoodSvcInsPerformed) As [# of complaint food service inspections performed],
CONVERT(NVarChar(150), NumTouristAccomInspections) As [# of tourist accommodation inspections],
CONVERT(NVarChar(150), NumSolidWasteInsPerformed) As [# of solid waste inspections performed],
CONVERT(NVarChar(150), NumFacilitiesClosed) As [# of facilities closed]
From HHSReportsData
Where (startDate = @StartDate and EndDate = @EndDate)
) As HHSData
UnPivot
(
[Reporting Week] For IncidentType In ([# of food service inspections performed],
[# of routine food service inspections performed],
[# of follow-up food service inspections performed],
[# of complaint food service inspections performed],
[# of tourist accommodation inspections],
[# of solid waste inspections performed],
[# of facilities closed])
) As HHSDataUnpivot
),
YtdReport (IncidentType, YTD)
As
(
Select IncidentType, [Reporting Week]
From
(
Select CONVERT(NVarChar(150), SUM(NumFoodSvcInsPerformed)) As [# of food service inspections performed],
CONVERT(NVarChar(150), SUM(NumRoutineFoodSvcInsPerformed)) As [# of routine food service inspections performed],
CONVERT(NVarChar(150), SUM(NumFollowupFoodSvcInsPerformed)) As [# of follow-up food service inspections performed],
CONVERT(NVarChar(150), SUM(NumCompliantFoodSvcInsPerformed)) As [# of complaint food service inspections performed],
CONVERT(NVarChar(150), SUM(NumTouristAccomInspections)) As [# of tourist accommodation inspections],
CONVERT(NVarChar(150), SUM(NumSolidWasteInsPerformed)) As [# of solid waste inspections performed],
CONVERT(NVarChar(150), SUM(NumFacilitiesClosed)) As [# of facilities closed]
From HHSReportsData F
Where (YEAR(StartDate) = @ReportYear) And (StartDate <= @StartDate)
) As HHSData
UnPivot
(
[Reporting Week] For IncidentType In ([# of food service inspections performed],
[# of routine food service inspections performed],
[# of follow-up food service inspections performed],
[# of complaint food service inspections performed],
[# of tourist accommodation inspections],
[# of solid waste inspections performed],
[# of facilities closed])
) As HHSDataUnpivot
)
-- Join the weekly and monthly results based off of incident type to produce
-- your result set.
Select W.IncidentType, W.[Reporting Week], Ytd.YTD
From WeeklyReport As W
Inner Join YtdReport As Ytd On W.IncidentType = Ytd.IncidentType
Inner Join dbo.HumanServices As It On It.IncidentType = W.IncidentType
Order By It.SortOrder;

Viewing 15 posts - 1 through 15 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

First... http://www.sqlservercentral.com/articles/Best+Practices/61537/

Second... http://www.sqlservercentral.com/articles/Crosstab/65048/

I have the patience if you have the data and the willingness to help yourself by learning something new. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 2

Hi Jeff,

Hopefully, I have followed the best practices as described on the link you provided.

Here is some sample data.

First one creates 2 tables, HHSReportsdata which holds data for each week. Each week is detertimed by date range of StartDate and EndDate combo.

the Second table is called HumanServices, used expressly for sorting.

You may find that it may not be needed here even though it appears in the SQL statement as a join to HHSReportsData table.

The last two statements are sample data to both tables.

If (OBJECT_ID('dbo.HHSReportsData', 'Table') Is Not Null)

Begin;

Drop Table dbo.HHSReportsData;

End;

Go

If (OBJECT_ID('dbo.HumanServices', 'Table') Is Not Null)

Begin;

Drop Table dbo.HumanServices;

End;

Go

CREATE TABLE [dbo].[HHSReportsData](

[ReportQID] [int] IDENTITY(1,1) NOT NULL,

[NumFoodSvcInsPerformed] [int] NULL,

[NumRoutineFoodSvcInsPerformed] [int] NULL,

[NumFollowupFoodSvcInsPerformed] [int] NULL,

[NumCompliantFoodSvcInsPerformed] [int] NULL,

[NumTouristAccomInspections] [int] NULL,

[NumSolidWasteInsPerformed] [int] NULL,

[NumFacilitiesClosed] [int] NULL,

CONSTRAINT [PK_HHSReportsData] PRIMARY KEY CLUSTERED

(

ReportQID Asc

) On [Primary]

) On [Primary];

Go

Create Table HumanServices

(

IncidentTypeId TinyInt Not Null Identity(1, 1),

IncidentType NVarChar(30) Not Null,

SortOrder Int Not Null

Constraint [Idx_HumanServices_SortOrder] Unique,

Constraint [Pk_HumanServices_IncidentTypeId] Primary Key Clustered

(

IncidentTypeId Asc

) On [Primary]

) On [Primary];

Go

**********************Human Services Sample data***************

Insert Into dbo.HumanServices (IncidentType, SortOrder)

Values('# of routine food service inspections performed', 16);

Insert Into dbo.HumanServices (IncidentType, SortOrder)

Values('# of follow-up food service inspections performed', 17);

Insert Into dbo.HumanServices (IncidentType, SortOrder)

Values('# of complaint food service inspections performed', 18);

Insert Into dbo.HumanServices (IncidentType, SortOrder)

Values('# of tourist accommodation inspections', 19);

Insert Into dbo.HumanServices (IncidentType, SortOrder)

Values('# of solid waste inspections performed', 20);

Insert Into dbo.HumanServices (IncidentType, SortOrder)

Values('# of facilities closed', 21);

******************************************************

**********************HHSReportsData Sample data***************

INSERT INTO [FIB].[dbo].[HHSReportsData]

([StartDate]

,[EndDate]

,[NumFoodSvcInsPerformed]

,[NumRoutineFoodSvcInsPerformed]

,[NumFollowupFoodSvcInsPerformed]

,[NumCompliantFoodSvcInsPerformed]

,[NumTouristAccomInspections]

,[NumSolidWasteInsPerformed]

,[NumFacilitiesClosed])

VALUES

('6/19/2009',

'6/26/2009',

12,

15,

9,

44,

36,

71,

61),

[FIB].[dbo].[HHSReportsData]

([StartDate]

,[EndDate]

,[NumFoodSvcInsPerformed]

,[NumRoutineFoodSvcInsPerformed]

,[NumFollowupFoodSvcInsPerformed]

,[NumCompliantFoodSvcInsPerformed]

,[NumTouristAccomInspections]

,[NumSolidWasteInsPerformed]

,[NumFacilitiesClosed])

VALUES

('6/27/2009',

'7/3/2009',

3,

15,

2,

24,

6,

7,

6),

INSERT INTO [FIB].[dbo].[HHSReportsData]

([StartDate]

,[EndDate]

,[NumFoodSvcInsPerformed]

,[NumRoutineFoodSvcInsPerformed]

,[NumFollowupFoodSvcInsPerformed]

,[NumCompliantFoodSvcInsPerformed]

,[NumTouristAccomInspections]

,[NumSolidWasteInsPerformed]

,[NumFacilitiesClosed])

VALUES

('6/19/2009',

'6/26/2009',

12,

15,

9,

44,

36,

71,

61),

[FIB].[dbo].[HHSReportsData]

([StartDate]

,[EndDate]

,[NumFoodSvcInsPerformed]

,[NumRoutineFoodSvcInsPerformed]

,[NumFollowupFoodSvcInsPerformed]

,[NumCompliantFoodSvcInsPerformed]

,[NumTouristAccomInspections]

,[NumSolidWasteInsPerformed]

,[NumFacilitiesClosed])

VALUES

('6/27/2009',

'7/3/2009',

3,

5,

2,

4,

6,

7,

6),

INSERT INTO [FIB].[dbo].[HHSReportsData]

([StartDate]

,[EndDate]

,[NumFoodSvcInsPerformed]

,[NumRoutineFoodSvcInsPerformed]

,[NumFollowupFoodSvcInsPerformed]

,[NumCompliantFoodSvcInsPerformed]

,[NumTouristAccomInspections]

,[NumSolidWasteInsPerformed]

,[NumFacilitiesClosed])

VALUES

('7/4/2009',

'7/11/2009',

12,

15,

9,

44,

36,

71,

61),

[FIB].[dbo].[HHSReportsData]

([StartDate]

,[EndDate]

,[NumFoodSvcInsPerformed]

,[NumRoutineFoodSvcInsPerformed]

,[NumFollowupFoodSvcInsPerformed]

,[NumCompliantFoodSvcInsPerformed]

,[NumTouristAccomInspections]

,[NumSolidWasteInsPerformed]

,[NumFacilitiesClosed])

VALUES

('7/12/2009',

'7/17/2009',

3,

15,

12,

24,

46,

4,

6)

I am sure you have more questions.

Please let me know what they are and I will be glad to provide them.

Thanks a lot for your assistance.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Two more things I'd like to know:

1) what is your expected result based on your sample data? and

2) What have you tried so far?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 4

hi Lutz,

If you scroll up, you will see where I have Week1, Week2, etc.

That's my expected result.

In other words, if I get the code working, it will will show YTD up to this month but future week's totals will be added to the matrix.

Answer to question2, sorry but I have not tried anything yet.

I am struggling to get started.

Perhaps, if I can get at least some pseudo-code based on the sample data I posted earlier, I can give it a shot.

Thanks again.

LutzM SSC Guru Points: 107049 More actions · Answer 5

Please try to run the code you provided to generate the sample data. It won't run...

I don't mind adding a few comment marks or removing commas. But at least the columns to be inserted into should be declared...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 6

Here is the sample data generated by the code:

Incident Type Reporting Week YTD

-------------------------------------------------------------------------------------

# of food service inspections performed 4 7

# of food service inspections performed 3 7

# of routine food service inspections performed 1 7

# of routine food service inspections performed 6 7

# of follow-up food service inspections performed 1 7

# of follow-up food service inspections performed 6 7

# of complaint food service inspections performed 3 8

# of complaint food service inspections performed 5 8

# of tourist accommodation inspections 5 10

# of solid waste inspections performed 47 51

# of solid waste inspections performed 4 51

# of facilities closed 4 5

# of facilities closed 1 5

Here is the code, just in case I mistakenly removed or added something I shouldn't have in the original code I posted.

Sorry about that.

Declare @StartDate DateTime;

Declare @EndDate DateTime;

Declare @ReportYear Int;

--set @StartDate = '4/8/2010';

--set @EndDate = '4/15/2010';

-- Determine the year you are working with.

Set @ReportYear = YEAR(@StartDate);

-- Build the CTEs to hold your unpivoted data.

With WeeklyReport (IncidentType, [Reporting Week])

As

(

Select IncidentType, [Reporting Week]

From

(

Select ReportQID,CONVERT(NVarChar(150), NumFoodSvcInsPerformed) As [# of food service inspections performed],

CONVERT(NVarChar(150), NumRoutineFoodSvcInsPerformed) As [# of routine food service inspections performed],

CONVERT(NVarChar(150), NumFollowupFoodSvcInsPerformed) As [# of follow-up food service inspections performed],

CONVERT(NVarChar(150), NumCompliantFoodSvcInsPerformed) As [# of complaint food service inspections performed],

CONVERT(NVarChar(150), NumTouristAccomInspections) As [# of tourist accommodation inspections],

CONVERT(NVarChar(150), NumSolidWasteInsPerformed) As [# of solid waste inspections performed],

CONVERT(NVarChar(150), NumFacilitiesClosed) As [# of facilities closed]

From HHSReportsData

--Where (startDate = @StartDate and EndDate = @EndDate)

) As HHSData

UnPivot

(

[Reporting Week] For IncidentType In ([# of food service inspections performed],

[# of routine food service inspections performed],

[# of follow-up food service inspections performed],

[# of complaint food service inspections performed],

[# of tourist accommodation inspections],

[# of solid waste inspections performed],

[# of facilities closed])

) As HHSDataUnpivot

),

YtdReport (IncidentType, YTD)

As

(

Select IncidentType, [Reporting Week]

From

(

Select CONVERT(NVarChar(150), SUM(NumFoodSvcInsPerformed)) As [# of food service inspections performed],

CONVERT(NVarChar(150), SUM(NumRoutineFoodSvcInsPerformed)) As [# of routine food service inspections performed],

CONVERT(NVarChar(150), SUM(NumFollowupFoodSvcInsPerformed)) As [# of follow-up food service inspections performed],

CONVERT(NVarChar(150), SUM(NumCompliantFoodSvcInsPerformed)) As [# of complaint food service inspections performed],

CONVERT(NVarChar(150), SUM(NumTouristAccomInspections)) As [# of tourist accommodation inspections],

CONVERT(NVarChar(150), SUM(NumSolidWasteInsPerformed)) As [# of solid waste inspections performed],

CONVERT(NVarChar(150), SUM(NumFacilitiesClosed)) As [# of facilities closed]

From HHSReportsData F

--Where (YEAR(StartDate) = @ReportYear) And (StartDate <= @StartDate)

) As HHSData

UnPivot

(

[Reporting Week] For IncidentType In ([# of food service inspections performed],

[# of routine food service inspections performed],

[# of follow-up food service inspections performed],

[# of complaint food service inspections performed],

[# of tourist accommodation inspections],

[# of solid waste inspections performed],

[# of facilities closed])

) As HHSDataUnpivot

)

-- Join the weekly and monthly results based off of incident type to produce

-- your result set.

Select W.IncidentType, W.[Reporting Week], Ytd.YTD

From WeeklyReport As W

Inner Join YtdReport As Ytd On W.IncidentType = Ytd.IncidentType

Inner Join dbo.HumanServices As It On It.IncidentType = W.IncidentType

Order By It.SortOrder;

I commented out the WHERE Clause since I wanted all data.

Also, from the data I just posted, you can see it has 2 weeks' data.

That's where my dilemma is.

Again, thanks a lot

BTW: I think you will need to create the 2 tables for it to run successfully.

Otherwise, please let me know how i can declare those with #

LutzM SSC Guru Points: 107049 More actions · Answer 7

My I ask you again to run the code you provided to genarate the test data?

Strong hint (among others):

You have the following statement in your code

INSERT INTO [FIB].[dbo].[HHSReportsData]

([StartDate]

,[EndDate]

but the table def does not include those columns (among other discrepancies...)

Take the sample code you provided, create a "sandbox" database and try to run your code...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 8

Once again, my apologies Lutz.

I have fixed them up and have been able to generate data.

Please see entire scripts from Createtable, to insert, to query.

If (OBJECT_ID('dbo.HHSDataTest', 'Table') Is Not Null)

Begin;

Drop Table dbo.HHSDataTest;

End;

Go

If (OBJECT_ID('dbo.HumanServicesTest', 'Table') Is Not Null)

Begin;

Drop Table dbo.HumanServicesTest;

End;

Go

CREATE TABLE [dbo].[HHSDataTest](

[ReportQID] [int] IDENTITY(1,1) NOT NULL,

[NumFoodSvcInsPerformed] [int] NULL,

[NumRoutineFoodSvcInsPerformed] [int] NULL,

[NumFollowupFoodSvcInsPerformed] [int] NULL,

[NumCompliantFoodSvcInsPerformed] [int] NULL,

[NumTouristAccomInspections] [int] NULL,

[NumSolidWasteInsPerformed] [int] NULL,

[NumFacilitiesClosed] [int] NULL,

[StartDate DateTime],

[EndDate DateTime])

CONSTRAINT [PK_HHSDataTest] PRIMARY KEY CLUSTERED

(

ReportQID Asc

) On [Primary]

) On [Primary];

Go

Create Table HumanServicesTest

(

IncidentTypeId TinyInt Not Null Identity(1, 1),

IncidentType NVarChar(150) Not Null,

SortOrder Int Not Null

Constraint [Idx_HumanServicesTest_SortOrder] Unique,

Constraint [Pk_HumanServicesTest_IncidentTypeId] Primary Key Clustered

(

IncidentTypeId Asc

) On [Primary]

) On [Primary];

Go

--**********************Human Services Sample data***************

Insert Into dbo.HumanServicesTest (IncidentType, SortOrder)

Values('# of routine food service inspections performed', 16);

Insert Into dbo.HumanServicesTest (IncidentType, SortOrder)

Values('# of follow-up food service inspections performed', 17);

Insert Into dbo.HumanServicesTest (IncidentType, SortOrder)

Values('# of complaint food service inspections performed', 18);

Insert Into dbo.HumanServicesTest (IncidentType, SortOrder)

Values('# of tourist accommodation inspections', 19);

Insert Into dbo.HumanServicesTest (IncidentType, SortOrder)

Values('# of solid waste inspections performed', 20);

Insert Into dbo.HumanServicesTest (IncidentType, SortOrder)

Values('# of facilities closed', 21);

--******************************************************

--**********************HHSReportsData Sample data***************

INSERT INTO [FIB].[dbo].[HHSDataTest]

([StartDate]

,[EndDate]

,[NumFoodSvcInsPerformed]

,[NumRoutineFoodSvcInsPerformed]

,[NumFollowupFoodSvcInsPerformed]

,[NumCompliantFoodSvcInsPerformed]

,[NumTouristAccomInspections]

,[NumSolidWasteInsPerformed]

,[NumFacilitiesClosed])

VALUES

('6/19/2009',

'6/26/2009',

12,

15,

9,

44,

36,

71,

61);

INSERT INTO [FIB].[dbo].[HHSDataTest]

([StartDate]

,[EndDate]

,[NumFoodSvcInsPerformed]

,[NumRoutineFoodSvcInsPerformed]

,[NumFollowupFoodSvcInsPerformed]

,[NumCompliantFoodSvcInsPerformed]

,[NumTouristAccomInspections]

,[NumSolidWasteInsPerformed]

,[NumFacilitiesClosed])

VALUES

('6/27/2009',

'7/3/2009',

3,

15,

2,

24,

6,

7,

6);

INSERT INTO [FIB].[dbo].[HHSDataTest]

([StartDate]

,[EndDate]

,[NumFoodSvcInsPerformed]

,[NumRoutineFoodSvcInsPerformed]

,[NumFollowupFoodSvcInsPerformed]

,[NumCompliantFoodSvcInsPerformed]

,[NumTouristAccomInspections]

,[NumSolidWasteInsPerformed]

,[NumFacilitiesClosed])

VALUES

('6/19/2009',

'6/26/2009',

12,

15,

9,

44,

36,

71,

61);

INSERT INTO [FIB].[dbo].[HHSDataTest]

([StartDate]

,[EndDate]

,[NumFoodSvcInsPerformed]

,[NumRoutineFoodSvcInsPerformed]

,[NumFollowupFoodSvcInsPerformed]

,[NumCompliantFoodSvcInsPerformed]

,[NumTouristAccomInspections]

,[NumSolidWasteInsPerformed]

,[NumFacilitiesClosed])

VALUES

('6/27/2009',

'7/3/2009',

3,

5,

2,

4,

6,

7,

6);

INSERT INTO [FIB].[dbo].[HHSDataTest]

([StartDate]

,[EndDate]

,[NumFoodSvcInsPerformed]

,[NumRoutineFoodSvcInsPerformed]

,[NumFollowupFoodSvcInsPerformed]

,[NumCompliantFoodSvcInsPerformed]

,[NumTouristAccomInspections]

,[NumSolidWasteInsPerformed]

,[NumFacilitiesClosed])

VALUES

('7/4/2009',

'7/11/2009',

12,

15,

9,

44,

36,

71,

61);

INSERT INTO [FIB].[dbo].[HHSDataTest]

([StartDate]

,[EndDate]

,[NumFoodSvcInsPerformed]

,[NumRoutineFoodSvcInsPerformed]

,[NumFollowupFoodSvcInsPerformed]

,[NumCompliantFoodSvcInsPerformed]

,[NumTouristAccomInspections]

,[NumSolidWasteInsPerformed]

,[NumFacilitiesClosed])

VALUES

('7/12/2009',

'7/17/2009',

3,

15,

12,

24,

46,

4,

6)

--*************************************

Declare @StartDate DateTime;

Declare @EndDate DateTime;

Declare @ReportYear Int;

--set @StartDate = '4/8/2010';

--set @EndDate = '4/15/2010';

-- Determine the year you are working with.

Set @ReportYear = YEAR(@StartDate);

-- Build the CTEs to hold your unpivoted data.

With WeeklyReport (IncidentType, [Reporting Week])

As

(

Select IncidentType, [Reporting Week]

From

(

Select ReportQID,CONVERT(NVarChar(150), NumFoodSvcInsPerformed) As [# of food service inspections performed],

CONVERT(NVarChar(150), NumRoutineFoodSvcInsPerformed) As [# of routine food service inspections performed],

CONVERT(NVarChar(150), NumFollowupFoodSvcInsPerformed) As [# of follow-up food service inspections performed],

CONVERT(NVarChar(150), NumCompliantFoodSvcInsPerformed) As [# of complaint food service inspections performed],

CONVERT(NVarChar(150), NumTouristAccomInspections) As [# of tourist accommodation inspections],

CONVERT(NVarChar(150), NumSolidWasteInsPerformed) As [# of solid waste inspections performed],

CONVERT(NVarChar(150), NumFacilitiesClosed) As [# of facilities closed]

From HHSDataTest

--Where (startDate = @StartDate and EndDate = @EndDate)

) As HHSData

UnPivot

(

[Reporting Week] For IncidentType In ([# of food service inspections performed],

[# of routine food service inspections performed],

[# of follow-up food service inspections performed],

[# of complaint food service inspections performed],

[# of tourist accommodation inspections],

[# of solid waste inspections performed],

[# of facilities closed])

) As HHSDataUnpivot

),

YtdReport (IncidentType, YTD)

As

(

Select IncidentType, [Reporting Week]

From

(

Select CONVERT(NVarChar(150), SUM(NumFoodSvcInsPerformed)) As [# of food service inspections performed],

CONVERT(NVarChar(150), SUM(NumRoutineFoodSvcInsPerformed)) As [# of routine food service inspections performed],

CONVERT(NVarChar(150), SUM(NumFollowupFoodSvcInsPerformed)) As [# of follow-up food service inspections performed],

CONVERT(NVarChar(150), SUM(NumCompliantFoodSvcInsPerformed)) As [# of complaint food service inspections performed],

CONVERT(NVarChar(150), SUM(NumTouristAccomInspections)) As [# of tourist accommodation inspections],

CONVERT(NVarChar(150), SUM(NumSolidWasteInsPerformed)) As [# of solid waste inspections performed],

CONVERT(NVarChar(150), SUM(NumFacilitiesClosed)) As [# of facilities closed]

From HHSDataTest F

--Where (YEAR(StartDate) = @ReportYear) And (StartDate <= @StartDate)

) As HHSData

UnPivot

(

[Reporting Week] For IncidentType In ([# of food service inspections performed],

[# of routine food service inspections performed],

[# of follow-up food service inspections performed],

[# of complaint food service inspections performed],

[# of tourist accommodation inspections],

[# of solid waste inspections performed],

[# of facilities closed])

) As HHSDataUnpivot

)

-- Join the weekly and monthly results based off of incident type to produce

-- your result set.

Select W.IncidentType, W.[Reporting Week], Ytd.YTD

From WeeklyReport As W

Inner Join YtdReport As Ytd On W.IncidentType = Ytd.IncidentType

Inner Join dbo.HumanServicesTest As It On It.IncidentType = W.IncidentType

Order By It.SortOrder;

I will be back in about 45minutes as I am meeting with my boss in a few minutes.

Many thanks for your assistance

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

simflex-897410 (5/11/2010)
Once again, my apologies Lutz.
I have fixed them up and have been able to generate data.
Please see entire scripts from Createtable, to insert, to query.
If (OBJECT_ID('dbo.HHSDataTest', 'Table') Is Not Null)
Begin;
Drop Table dbo.HHSDataTest;
End;
Go
If (OBJECT_ID('dbo.HumanServicesTest', 'Table') Is Not Null)
Begin;
Drop Table dbo.HumanServicesTest;
End;
Go
CREATE TABLE [dbo].[HHSDataTest](
[ReportQID] [int] IDENTITY(1,1) NOT NULL,
[NumFoodSvcInsPerformed] [int] NULL,
[NumRoutineFoodSvcInsPerformed] [int] NULL,
[NumFollowupFoodSvcInsPerformed] [int] NULL,
[NumCompliantFoodSvcInsPerformed] [int] NULL,
[NumTouristAccomInspections] [int] NULL,
[NumSolidWasteInsPerformed] [int] NULL,
[NumFacilitiesClosed] [int] NULL,
[StartDate DateTime],
[EndDate DateTime])
CONSTRAINT [PK_HHSDataTest] PRIMARY KEY CLUSTERED
(
ReportQID Asc
) On [Primary]
) On [Primary];
Go
Create Table HumanServicesTest
(
IncidentTypeId TinyInt Not Null Identity(1, 1),
IncidentType NVarChar(150) Not Null,
SortOrder Int Not Null
Constraint [Idx_HumanServicesTest_SortOrder] Unique,
Constraint [Pk_HumanServicesTest_IncidentTypeId] Primary Key Clustered
(
IncidentTypeId Asc
) On [Primary]
) On [Primary];
Go
--**********************Human Services Sample data***************
Insert Into dbo.HumanServicesTest (IncidentType, SortOrder)
Values('# of routine food service inspections performed', 16);
Insert Into dbo.HumanServicesTest (IncidentType, SortOrder)
Values('# of follow-up food service inspections performed', 17);
Insert Into dbo.HumanServicesTest (IncidentType, SortOrder)
Values('# of complaint food service inspections performed', 18);
Insert Into dbo.HumanServicesTest (IncidentType, SortOrder)
Values('# of tourist accommodation inspections', 19);
Insert Into dbo.HumanServicesTest (IncidentType, SortOrder)
Values('# of solid waste inspections performed', 20);
Insert Into dbo.HumanServicesTest (IncidentType, SortOrder)
Values('# of facilities closed', 21);
--******************************************************
--**********************HHSReportsData Sample data***************
INSERT INTO [FIB].[dbo].[HHSDataTest]
([StartDate]
,[EndDate]
,[NumFoodSvcInsPerformed]
,[NumRoutineFoodSvcInsPerformed]
,[NumFollowupFoodSvcInsPerformed]
,[NumCompliantFoodSvcInsPerformed]
,[NumTouristAccomInspections]
,[NumSolidWasteInsPerformed]
,[NumFacilitiesClosed])
VALUES
('6/19/2009',
'6/26/2009',
12,
15,
9,
44,
36,
71,
61);
INSERT INTO [FIB].[dbo].[HHSDataTest]
([StartDate]
,[EndDate]
,[NumFoodSvcInsPerformed]
,[NumRoutineFoodSvcInsPerformed]
,[NumFollowupFoodSvcInsPerformed]
,[NumCompliantFoodSvcInsPerformed]
,[NumTouristAccomInspections]
,[NumSolidWasteInsPerformed]
,[NumFacilitiesClosed])
VALUES
('6/27/2009',
'7/3/2009',
3,
15,
2,
24,
6,
7,
6);
INSERT INTO [FIB].[dbo].[HHSDataTest]
([StartDate]
,[EndDate]
,[NumFoodSvcInsPerformed]
,[NumRoutineFoodSvcInsPerformed]
,[NumFollowupFoodSvcInsPerformed]
,[NumCompliantFoodSvcInsPerformed]
,[NumTouristAccomInspections]
,[NumSolidWasteInsPerformed]
,[NumFacilitiesClosed])
VALUES
('6/19/2009',
'6/26/2009',
12,
15,
9,
44,
36,
71,
61);
INSERT INTO [FIB].[dbo].[HHSDataTest]
([StartDate]
,[EndDate]
,[NumFoodSvcInsPerformed]
,[NumRoutineFoodSvcInsPerformed]
,[NumFollowupFoodSvcInsPerformed]
,[NumCompliantFoodSvcInsPerformed]
,[NumTouristAccomInspections]
,[NumSolidWasteInsPerformed]
,[NumFacilitiesClosed])
VALUES
('6/27/2009',
'7/3/2009',
3,
5,
2,
4,
6,
7,
6);
INSERT INTO [FIB].[dbo].[HHSDataTest]
([StartDate]
,[EndDate]
,[NumFoodSvcInsPerformed]
,[NumRoutineFoodSvcInsPerformed]
,[NumFollowupFoodSvcInsPerformed]
,[NumCompliantFoodSvcInsPerformed]
,[NumTouristAccomInspections]
,[NumSolidWasteInsPerformed]
,[NumFacilitiesClosed])
VALUES
('7/4/2009',
'7/11/2009',
12,
15,
9,
44,
36,
71,
61);
INSERT INTO [FIB].[dbo].[HHSDataTest]
([StartDate]
,[EndDate]
,[NumFoodSvcInsPerformed]
,[NumRoutineFoodSvcInsPerformed]
,[NumFollowupFoodSvcInsPerformed]
,[NumCompliantFoodSvcInsPerformed]
,[NumTouristAccomInspections]
,[NumSolidWasteInsPerformed]
,[NumFacilitiesClosed])
VALUES
('7/12/2009',
'7/17/2009',
3,
15,
12,
24,
46,
4,
6)
--*************************************
Declare @StartDate DateTime;
Declare @EndDate DateTime;
Declare @ReportYear Int;
--set @StartDate = '4/8/2010';
--set @EndDate = '4/15/2010';
-- Determine the year you are working with.
Set @ReportYear = YEAR(@StartDate);
-- Build the CTEs to hold your unpivoted data.
With WeeklyReport (IncidentType, [Reporting Week])
As
(
Select IncidentType, [Reporting Week]
From
(
Select ReportQID,CONVERT(NVarChar(150), NumFoodSvcInsPerformed) As [# of food service inspections performed],
CONVERT(NVarChar(150), NumRoutineFoodSvcInsPerformed) As [# of routine food service inspections performed],
CONVERT(NVarChar(150), NumFollowupFoodSvcInsPerformed) As [# of follow-up food service inspections performed],
CONVERT(NVarChar(150), NumCompliantFoodSvcInsPerformed) As [# of complaint food service inspections performed],
CONVERT(NVarChar(150), NumTouristAccomInspections) As [# of tourist accommodation inspections],
CONVERT(NVarChar(150), NumSolidWasteInsPerformed) As [# of solid waste inspections performed],
CONVERT(NVarChar(150), NumFacilitiesClosed) As [# of facilities closed]
From HHSDataTest
--Where (startDate = @StartDate and EndDate = @EndDate)
) As HHSData
UnPivot
(
[Reporting Week] For IncidentType In ([# of food service inspections performed],
[# of routine food service inspections performed],
[# of follow-up food service inspections performed],
[# of complaint food service inspections performed],
[# of tourist accommodation inspections],
[# of solid waste inspections performed],
[# of facilities closed])
) As HHSDataUnpivot
),
YtdReport (IncidentType, YTD)
As
(
Select IncidentType, [Reporting Week]
From
(
Select CONVERT(NVarChar(150), SUM(NumFoodSvcInsPerformed)) As [# of food service inspections performed],
CONVERT(NVarChar(150), SUM(NumRoutineFoodSvcInsPerformed)) As [# of routine food service inspections performed],
CONVERT(NVarChar(150), SUM(NumFollowupFoodSvcInsPerformed)) As [# of follow-up food service inspections performed],
CONVERT(NVarChar(150), SUM(NumCompliantFoodSvcInsPerformed)) As [# of complaint food service inspections performed],
CONVERT(NVarChar(150), SUM(NumTouristAccomInspections)) As [# of tourist accommodation inspections],
CONVERT(NVarChar(150), SUM(NumSolidWasteInsPerformed)) As [# of solid waste inspections performed],
CONVERT(NVarChar(150), SUM(NumFacilitiesClosed)) As [# of facilities closed]
From HHSDataTest F
--Where (YEAR(StartDate) = @ReportYear) And (StartDate <= @StartDate)
) As HHSData
UnPivot
(
[Reporting Week] For IncidentType In ([# of food service inspections performed],
[# of routine food service inspections performed],
[# of follow-up food service inspections performed],
[# of complaint food service inspections performed],
[# of tourist accommodation inspections],
[# of solid waste inspections performed],
[# of facilities closed])
) As HHSDataUnpivot
)
-- Join the weekly and monthly results based off of incident type to produce
-- your result set.
Select W.IncidentType, W.[Reporting Week], Ytd.YTD
From WeeklyReport As W
Inner Join YtdReport As Ytd On W.IncidentType = Ytd.IncidentType
Inner Join dbo.HumanServicesTest As It On It.IncidentType = W.IncidentType
Order By It.SortOrder;
I will be back in about 45minutes as I am meeting with my boss in a few minutes.
Many thanks for your assistance

You very obviously didn't actually try to run your own code in a separate environment like we have to....

Msg 173, Level 15, State 1, Line 10

The definition for column 'StartDate DateTime' must include a data type.

Msg 173, Level 15, State 1, Line 11

The definition for column 'EndDate DateTime' must include a data type.

Msg 156, Level 15, State 1, Line 13

Incorrect syntax near the keyword 'CONSTRAINT'.

Msg 208, Level 16, State 1, Line 2

Invalid object name 'FIB.dbo.HHSDataTest'.

Try again... I'd do it all for you but there are other people that need help, as well... and they took the time to make sure their test data works. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 10

Jeff,

Man, you are right!

Trust me; I swear with my hand on a stack of bibles, I ran the create table, the insert and the query and they all worked. Now, I found the same issues you found.

I have corrected them.

Please try again. sorry about that.

If (OBJECT_ID('dbo.HHSDataTest', 'Table') Is Not Null)

Begin;

Drop Table dbo.HHSDataTest;

End;

Go

If (OBJECT_ID('dbo.HumanServicesTest', 'Table') Is Not Null)

Begin;

Drop Table dbo.HumanServicesTest;

End;

Go

CREATE TABLE [dbo].[HHSDataTest](

[ReportQID] [int] IDENTITY(1,1) NOT NULL,

[NumFoodSvcInsPerformed] [int] NULL,

[NumRoutineFoodSvcInsPerformed] [int] NULL,

[NumFollowupFoodSvcInsPerformed] [int] NULL,

[NumCompliantFoodSvcInsPerformed] [int] NULL,

[NumTouristAccomInspections] [int] NULL,

[NumSolidWasteInsPerformed] [int] NULL,

[NumFacilitiesClosed] [int] NULL,

[StartDate] [datetime] NULL,

[EndDate] [datetime] NULL,