October 21, 2009 at 8:23 am
I have two tables, tblStmtChar and tblData. tblStmtChar holds all of the possible answers a person could answer for a particular statement in a survey.
USE [Mercury]
GO
/****** Object: Table [dbo].[tblStmtChar] Script Date: 10/21/2009 08:26:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblStmtChar](
[CSNum] [smallint] NOT NULL,
[CharNum] [int] NULL,
[StmtNum] [int] NOT NULL,
[SurveyNum] [int] NOT NULL,
CONSTRAINT [PK_tblStmtChar] PRIMARY KEY CLUSTERED
(
[CSNum] ASC,
[StmtNum] ASC,
[SurveyNum] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Here is some data for one statement in a survey from the above table:
"CSNum","CharNum","StmtNum","SurveyNum"
1,12498,7226,1722
2,12499,7226,1722
3,12500,7226,1722
4,12501,7226,1722
5,12502,7226,1722
6,12503,7226,1722
7,12504,7226,1722
8,26,7226,1722
9,12534,7226,1722
tblData holds the actual responses for the survey. The [Score] field from tblData has the CharNum value from the above table when they answer that question.
What I want to do is run a query that will get me the counts for all nine possibles for a given timeframe showing 0 if there were no answers. What I am running across is when one or more answers do not have values I am only getting counts for the ones that do. Here is the query and the returned records that I am getting. I need CSNum 5 and 7 to show 0 for CountofScore.
SELECT tblStmtChar.CSNum, tblCSSDataN.Score, ISNULL(Count(CASE WHEN tblStmtChar.CharNum IS NOT NULL Then 1 ELSE 0 END), 0) AS CountOfScore
FROM tblCSSDataN RIGHT OUTER JOIN tblStmtChar ON (tblCSSDataN.Score = tblStmtChar.CharNum) AND (tblCSSDataN.StmtNum = tblStmtChar.StmtNum) AND (tblCSSDataN.SurveyNum = tblStmtChar.SurveyNum)
WHERE (((tblCSSDataN.SurveyNum)=1722) AND ((tblCSSDataN.StmtNum)=7226) AND ((tblCSSDataN.SurveyDate) Between '10/1/2008' And '12/31/2008'))
GROUP BY tblStmtChar.CSNum, tblCSSDataN.Score
ORDER BY tblStmtChar.CSNum
"CSNum","CharNum","CountOfCharNum"
1,12498,10
2,12499,6
3,12500,2
4,12501,6
6,12503,1
8,26,4
9,12534,13
Any ideas?
October 21, 2009 at 9:03 am
Fill the blanks and you'll get your answer:
--Drop temporary table
IF OBJECT_ID('tempdb..#tblStmtChar') IS NOT NULL DROP TABLE #tblStmtChar
--Create temp test table
CREATE TABLE #tblStmtChar(
CSNum smallint NOT NULL,
CharNum int NULL,
StmtNum int NOT NULL,
SurveyNum int NOT NULL,
CONSTRAINT PK_tblStmtChar PRIMARY KEY CLUSTERED
(
CSNum ASC,
StmtNum ASC,
SurveyNum ASC
)
)
--Fill temp table with sample data
INSERT INTO #tblStmtChar
SELECT
1,12498,7226,1722 UNION ALL SELECT
2,12499,7226,1722 UNION ALL SELECT
3,12500,7226,1722 UNION ALL SELECT
4,12501,7226,1722 UNION ALL SELECT
5,12502,7226,1722 UNION ALL SELECT
6,12503,7226,1722 UNION ALL SELECT
7,12504,7226,1722 UNION ALL SELECT
8,26,7226,1722 UNION ALL SELECT
9,12534,7226,1722
--Drop temp table
IF OBJECT_ID('tempdb..#tblCSSDataN') IS NOT NULL DROP TABLE #tblCSSDataN
--Create temp table
CREATE TABLE #tblCSSDataN (
CSNum smallint,
CharNum int,
Score int -- Is this the "Score" column?
--Where's the SurveyDate column?
--Where's the StmtNum column?
)
--Fill with sample data
INSERT INTO #tblCSSDataN
SELECT
1,12498,10 UNION ALL SELECT
2,12499,6 UNION ALL SELECT
3,12500,2 UNION ALL SELECT
4,12501,6 UNION ALL SELECT
6,12503,1 UNION ALL SELECT
8,26,4 UNION ALL SELECT
9,12534,13
If you want people to understand what you're after, provide DDL fro your table, possibly as temp table or table variable.
Don't forget to provide sample data as well.
Last but not least, provide what you have coded so far, related to the temp tables you posted.
If you give us what I suggested, you will have your answer in a eye blink!
-- Gianluca Sartori
October 21, 2009 at 9:39 am
The two tables that I am working with are not temporary tables being created and dropped
in SQL code. tblStmtChar holds all characteristics for all of our surveys and has over 3000
records in it. What i put in the first blue code box is the recordset for the SurveyNum and
StmtNum that I am trying to get the counts for out of tblCSSDataN. What I put above that is
the DDL for that table but I should have gotten rid of the Create Table part of the code.
Here is the table structure of tblCSSDataN:
[BatchID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SType] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ResponseCount] [int] NULL,
[SPSSSerial] [int] NULL,
[CorpNum] [int] NULL,
[dbid] [int] NULL,
[SurveyDate] [datetime] NULL,
[SurveyNum] [int] NULL,
[StmtNum] [int] NULL,
[DStmtNum] [int] NULL,
[FacNum] [int] NULL,
[DOUNum] [int] NULL,
[Factor] [money] NULL,
[Score] [int] NULL,
[ReportDate] [datetime] NULL,
[DOUCode] [nvarchar](50)
Now here is the recordset of the data that I am trying to get the counts for from this table based
on SurveyNum=1722, StmtNum=7226 and SurveyDate Between '10/1/2008' and '12/31/2008'.
"BatchID","SType","ResponseCount","SPSSSerial","CorpNum","dbid","SurveyDate","SurveyNum","StmtNum","DStmtNum","FacNum","DOUNum","Factor","Score","ReportDate","DOUCode"
"EHED_01052009","Written ",3411262,,132,,10/31/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12534,,"EDS"
"EHED_01052009","Written ",3411263,,132,,10/31/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12499,,"EDS"
"EHED_01052009","Written ",3411264,,132,,10/31/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12498,,"EDS"
"EHED_01052009","Written ",3411265,,132,,10/31/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12501,,"EDS"
"EHED_01052009","Written ",3411266,,132,,10/31/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12534,,"EDS"
"EHED_01052009","Written ",3411267,,132,,10/31/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12534,,"EDS"
"EHED_01052009","Written ",3411268,,132,,10/31/2008 0:00:00,1722,7226,5057,516,17583,$0.00,26,,"EDS"
"EHED_01052009","Written ",3411269,,132,,10/31/2008 0:00:00,1722,7226,5057,516,17583,$0.00,26,,"EDS"
"EHED_01052009","Written ",3411270,,132,,10/31/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12498,,"EDS"
"EHED_01052009","Written ",3411271,,132,,10/31/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12534,,"EDS"
"EHED_01052009","Written ",3411272,,132,,10/31/2008 0:00:00,1722,7226,5057,516,17583,$0.00,26,,"EDS"
"EHED_01052009","Written ",3411273,,132,,10/31/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12534,,"EDS"
"EHED_01052009","Written ",3411274,,132,,10/31/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12534,,"EDS"
"EHED_01052009","Written ",3411275,,132,,10/31/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12503,,"EDS"
"EHED_01052009","Written ",3411276,,132,,10/31/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12499,,"EDS"
"EHED_01052009","Written ",3411277,,132,,11/17/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12501,,"EDS"
"EHED_01052009","Written ",3411278,,132,,11/17/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12498,,"EDS"
"EHED_01052009","Written ",3411279,,132,,11/17/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12501,,"EDS"
"EHED_01052009","Written ",3411280,,132,,11/17/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12498,,"EDS"
"EHED_01052009","Written ",3411281,,132,,11/17/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12498,,"EDS"
"EHED_01052009","Written ",3411282,,132,,11/17/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12498,,"EDS"
"EHED_01052009","Written ",3411283,,132,,11/17/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12534,,"EDS"
"EHED_01052009","Written ",3411284,,132,,11/18/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12500,,"EDS"
"EHED_01052009","Written ",3411285,,132,,11/18/2008 0:00:00,1722,7226,5057,516,17583,$0.00,26,,"EDS"
"EHED_01052009","Written ",3411286,,132,,11/18/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12534,,"EDS"
"EHED_01052009","Written ",3411287,,132,,11/18/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12534,,"EDS"
"EHED_01052009","Written ",3411288,,132,,11/18/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12501,,"EDS"
"EHED_01052009","Written ",3411289,,132,,11/24/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12498,,"EDS"
"EHED_01052009","Written ",3411290,,132,,11/24/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12498,,"EDS"
"EHED_01052009","Written ",3411291,,132,,11/24/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12534,,"EDS"
"EHED_01052009","Written ",3411292,,132,,11/30/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12500,,"EDS"
"EHED_01052009","Written ",3411293,,132,,11/30/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12534,,"EDS"
"EHED_01052009","Written ",3411294,,132,,12/8/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12499,,"EDS"
"EHED_01052009","Written ",3411295,,132,,12/8/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12499,,"EDS"
"EHED_01052009","Written ",3411296,,132,,12/8/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12499,,"EDS"
"EHED_01052009","Written ",3411297,,132,,12/8/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12534,,"EDS"
"EHED_01052009","Written ",3411298,,132,,12/8/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12534,,"EDS"
"EHED_01052009","Written ",3411299,,132,,12/8/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12501,,"EDS"
"EHED_01052009","Written ",3411300,,132,,12/15/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12499,,"EDS"
"EHED_01052009","Written ",3411301,,132,,12/17/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12498,,"EDS"
"EHED_01052009","Written ",3411302,,132,,12/18/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12501,,"EDS"
"EHED_01052009","Written ",3411303,,132,,12/22/2008 0:00:00,1722,7226,5057,516,17583,$0.00,12498,,"EDS"
I am trying to build an aggregated table to run a report against but I need to show all possible
counts to the answers even if the answers are 0 as in 5 and 7 from first post. Sorry if I didn't
explain better in my first post.
Thank you.
October 21, 2009 at 9:43 am
Sorry, I forgot to add that tblCSSDataN is also a static table that has over 3,000,000 records in it now and more records are added to it nightly from many different surveys.
October 21, 2009 at 10:27 am
OK, that's fine...
You just have to make a small additional effort and change the sample data you posted into something like:
INSERT INTO #Table
SELECT values UNION ALL SELECT values ... etc
And there's still a table definition missing.
It's not that I don't want to help, believe me, it's just that I don't understand what you're after.
Take a look at this article and maybe you will understand what I mean:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For today I won't reply, since I'm going home, but tomorrow I'll be glad to help you.
Probably somebody else will catch this up meanwhile.
-- Gianluca Sartori
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply