• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

SQL Syntax to Count Two Combined Database Fields

Hello Experts,

I have a script (which I have attached for your reference) that returns the total count for this year and last year, per month, per value, for a specific database field.  

Now I need to create a new script that is basically the same, except what needs to be COUNTED is a combination of  TWO database fields due to a many to one relationship: There will be a single Case Number which may have multiple OffenseCID values.  I want to count the following two fields - combined - in order to count all Offenses per Case.  The fields should be cast from integers to strings: Cases.CaseID plus CROffenses.OffenseCID
Can you provide the syntax to achieve this?  In the attached code sample, I want to replace the: Count(iti.ITI_TypeText)  with Count(Cases.CaseID plus CROffenses.OffenseCID)

Thank you for your assistance.
~Chopp

-- Original Version 1/4/10

 

USE [LPDReportingAccessUDT]

GO

/****** Object:  View [udt].[Vw_Part3OffenseUDT - original]    Script Date: 01/04/2010 17:13:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

Create View [udt].[Vw_Part3OffenseUDT - original]

As

 

 

 

Select      IncidentType

 

      ,     Sum(JanLastYear) As SumOfJanLastYear

      ,     Sum(JanThisYear) As SumOfJanThisYear

      

      ,     Sum(FebLastYear) As SumOfFebLastYear

      ,     Sum(FebThisYear) As SumOfFebThisYear

      

      ,     Sum(MarLastYear) As SumOfMarLastYear

      ,     Sum(MarThisYear) As SumOfMarThisYear

      

      ,     Sum(AprLastYear) As SumOfAprLastYear 

      ,     Sum(AprThisYear) As SumOfAprThisYear

      

      ,     Sum(MayLastYear) As SumOfMayLastYear

      ,     Sum(MayThisYear) As SumOfMayThisYear

      

      ,     Sum(JunLastYear) As SumOfJunLastYear

      ,     Sum(JunThisYear) As SumOfJunThisYear

      

      ,     Sum(JulLastYear) As SumOfJulLastYear

      ,     Sum(JulThisYear) As SumOfJulThisYear

      

      ,     Sum(AugLastYear) As SumOfAugLastYear

      ,     Sum(AugThisYear) As SumOfAugThisYear

      

      ,     Sum(SepLastYear) As SumOfSepLastYear

      ,     Sum(SepThisYear) As SumOfSepThisYear

      

      ,     Sum(OctLastYear) As SumOfOctLastYear

      ,     Sum(OctThisYear) As SumOfOctThisYear

      

      ,     Sum(NovLastYear) As SumOfNovLastYear

      ,     Sum(NovThisYear) As SumOfNovThisYear

      

      ,     Sum(DecLastYear) As SumOfDecLastYear

      ,     Sum(DecThisYear) As SumOfDecThisYear

      

From (

            Select      Case 

                              When iti.ITI_TypeText Like '%ALARM%ACTIVATION%' Then 'Alarm Activations'

                              When iti.ITI_TypeText Like 'SUSPICIOUS%' Then 'Suspicious Circumstances'

                              When iti.ITI_TypeText = 'CIVIL STANDBY' Then 'Civil Standby'

                              When iti.ITI_TypeText = 'DISTURBANCE, FAMILY' Then 'Family Distubance'

                              When iti.ITI_TypeText = 'OUTSIDE ASSIST' Then 'Outside Assist'

                              When iti.ITI_TypeText = 'WELFARE CHECK' Then 'Welfare Check'

                        Else iti.ITI_TypeText 

                        End As IncidentType

                        

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 1 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 1) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As JanLastYear

                        

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 1 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 0) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As JanThisYear

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

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 2 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 1) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As FebLastYear

                        

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 2 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 0) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As FebThisYear

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

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 3 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 1) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As MarLastYear

                        

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 3 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 0) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As MarThisYear

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

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 4 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 1) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As AprLastYear

                        

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 4 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 0) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As AprThisYear

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

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 5 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 1) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As MayLastYear

                        

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 5 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 0) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As MayThisYear

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

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 6 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 1) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As JunLastYear

                        

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 6 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 0) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As JunThisYear

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

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 7 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 1) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As JulLastYear

                        

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 7 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 0) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As JulThisYear

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

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 8 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 1) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As AugLastYear

                        

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 8 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 0) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As AugThisYear

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

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 9 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 1) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As SepLastYear

                        

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 9 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 0) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As SepThisYear

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

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 10 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 1) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As OctLastYear

                        

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 10 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 0) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As OctThisYear

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

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 11 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 1) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As NovLastYear

                        

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 11 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 0) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As NovThisYear

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

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 12 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 1) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As DecLastYear

                        

                  ,     Case

                              When DatePart(Month,ii.I_tTimeTransmit) = 12 And DatePart(Year,ii.I_tTimeTransmit) = ( DatePart(Year,GetDate()) - 0) Then Count(iti.ITI_TypeText)

                        Else 0 

                        End As DecThisYear

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

                        

 

                              

 

            From UDT4.dbo.IIncident ii (Nolock)

             Join UDT4.dbo.ITypeInfo iti (Nolock)

                  ON ii.I_kTypeInfo = iti.ITI_TypeInfo_PK

 

            Where 

            ii.I_tTimeTransmit >= (Select + '1/1/' + (Cast( (DatePart(YEAR, GetDate()) - 1) As VarChar(4)))) -- >= Jan 1st of last year

            And   (           iti.ITI_TypeText Like '%ALARM%ACTIVATION%'

                        Or    iti.ITI_TypeText Like 'SUSPICIOUS%'

                        Or    iti.ITI_TypeText = 'CIVIL STANDBY'

                        Or    iti.ITI_TypeText = 'DISTURBANCE, FAMILY'

                        Or    iti.ITI_TypeText = 'OUTSIDE ASSIST'

                        Or    iti.ITI_TypeText = 'WELFARE CHECK'

                  )

                    

            Group By iti.ITI_TypeText, ii.I_tTimeTransmit

            

      ) As FlattenedLookAtIIncident_CallByMonth

      

Group By FlattenedLookAtIIncident_CallByMonth.IncidentType

Open in new window

0
Chopp
Asked:
Chopp
  • 2
1 Solution
 
James0628Commented:
I think something like the following should work:

Count(CAST (Cases.CaseID as varchar (10)) + CAST (CROffenses.OffenseCID as varchar (10)))

 The size of the varchar for each would depend on the maximum value that would be in that field.

 If SQL decides to convert the strings back to numbers and add them, instead of concatenating the strings, add a string literal.  For example:

Count(CAST (Cases.CaseID as varchar (10)) + '-' + CAST (CROffenses.OffenseCID as varchar (10)))


 Another possible approach would be to keep the values numeric.  If you know that OffenseCID will be from 1 - 9999, for example, then you might be able to use something like:

Count((Cases.CaseID * 10000) + CROffenses.OffenseCID)

 That would give you numbers like 10000 - 19999 for CaseID 1, 20000 - 29999 for CaseID 2, and so on.  As long as CaseID and OffenseCID don't get too large, I think that should work, and may be more efficient (ie. faster) than converting two numeric fields to strings and then concatenating them, and then counting those values.

 James
0
 
ChoppAuthor Commented:
Thank you, James.
~chopp

0
 
James0628Commented:
You're welcome.  Glad I could help.

  James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now