?
Solved

SQL Syntax to Count Two Combined Database Fields

Posted on 2010-01-05
3
Medium Priority
?
255 Views
Last Modified: 2012-05-08
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
Comment
Question by:Chopp
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
James0628 earned 2000 total points
ID: 26187421
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
 

Author Closing Comment

by:Chopp
ID: 31673177
Thank you, James.
~chopp

0
 
LVL 35

Expert Comment

by:James0628
ID: 26303959
You're welcome.  Glad I could help.

  James
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month15 days, 9 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question