query help

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  February 11, 2010 at 2:35 pm

  #219102

  Hi all,

  I am writing a query that will pick data from only one table and based on a date column I am finding count of Accounts in last year and in this year, .......

  i will find the distinct count of a column (account) for those calendar dates..

  Now my problem is that now I want to see also the VARIANCE in 2009 and 2010 counts from the results shown like this:

  YTD 2009

  AREA CNT

  NJ 100

  NY 200

  PA 400

  YTD 2010

  AREA CNT

  NJ 50

  NY 100

  PA 150

  Now...i have calculate VARIANCE like this

  VARIANCE 2009-2010

  AREA CNT

  NJ -50

  NY 100

  PA 250

  here is the complete sql query:

  --create

  CREATE TABLE [dbo].[Dim1](

  [varchar](5) NULL,

  [DATE] [datetime] NULL,

  [ACCOUNT_NO] [char](10) NULL,

  [FILTER_CODE] [varchar](50) NULL,

  [SRC] [varchar](50) NULL

  )

  --insert

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'MA', 'Jan 1 2009 10:34AM', 'SS09000005', '4', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'MA', 'Jan 4 2009 8:50AM', 'SS09000075', '4', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'MA', 'Jan 26 2009 2:08AM', 'SS09000814', '4', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'MA', 'Jan 27 2009 1:34AM', 'SS09000863', '4', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'MA', 'Jan 31 2009 12:46PM', 'SS09000986', '4', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'MA', 'Feb 1 2009 12:06PM', 'SS09001005', '4', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'MA', 'Feb 3 2009 1:21PM', 'SS09001065', '4', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'MA', 'Feb 8 2009 7:33PM', 'SS09001200', '4', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'MA', 'Feb 10 2009 2:06PM', 'SS09001246', '9', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'MA', 'Feb 10 2009 11:04PM', 'SS09001258', '4', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'LA', 'Jan 2 2009 2:56PM', 'SS09000031', '4', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'LA', 'Jan 21 2009 3:01AM', 'SS09000450', '4', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'LA', 'Jan 28 2009 12:54PM', 'SS09000619', '9', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'LA', 'Jan 28 2009 4:43PM', 'SS09000625', '4', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'LA', 'Jan 3 2010 4:59PM', 'SS10000039', '4', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'NJ', 'Jan 26 2009 11:42AM', 'AS09000730', '4', 'Source2')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'NJ', 'Jan 28 2009 3:13PM', 'AS09000804', '4', 'Source2')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'NJ', 'Feb 2 2009 1:41PM', 'AS09000951', '9', 'Source2')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'NJ', 'Feb 2 2009 8:13PM', 'AS09000960', '4', 'Source2')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'NJ', 'Jan 31 2009 12:33PM', 'AS09000981', '9', 'Source2')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'NJ', 'Feb 3 2009 5:14PM', 'AS09000992', '9', 'Source2')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'NJ', 'Jan 31 2009 10:45AM', 'AS09000995', '9', 'Source2')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'NJ', 'Feb 5 2009 8:06AM', 'AS09001032', '4', 'Source2')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'NJ', 'Feb 6 2009 10:15AM', 'AS09001060', '4', 'Source2')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'NJ', 'Feb 6 2009 7:21PM', 'AS09001089', '4', 'Source2')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'NJ', 'Feb 6 2009 9:15PM', 'AS09001096', '4', 'Source2')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'LA', 'Jan 3 2010 4:59PM', 'SS10000039', '4', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 1 2009 9:27AM', 'AS09000005', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 2 2009 10:14AM', 'AS09000022', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 2 2009 3:08PM', 'AS09000037', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 2 2009 3:12PM', 'AS09000038', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 4 2009 5:48PM', 'AS09000074', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 11 2009 10:34AM', 'AS09000250', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 21 2009 7:24PM', 'AS09000564', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 26 2009 9:39AM', 'AS09000675', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 26 2009 10:28PM', 'AS09000704', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 27 2009 1:12PM', 'AS09000717', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 30 2009 3:18PM', 'AS09000813', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 31 2009 6:54PM', 'AS09000838', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 31 2009 6:56PM', 'AS09000839', '9', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 31 2009 7:17PM', 'AS09000842', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 1 2009 10:47AM', 'AS09000853', '9', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 2 2009 10:12AM', 'AS09000870', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 2 2009 2:12PM', 'AS09000877', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 3 2009 3:15PM', 'AS09000914', '9', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 3 2009 7:18PM', 'AS09000922', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 4 2009 11:32AM', 'AS09000936', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 4 2009 3:20PM', 'AS09000947', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 4 2009 4:06PM', 'AS09000952', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 4 2009 6:18PM', 'AS09000954', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 5 2009 12:05AM', 'AS09000964', '9', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 8 2009 3:52PM', 'AS09001038', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 9 2009 8:21AM', 'AS09001045', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 9 2009 11:24AM', 'AS09001054', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 10 2009 8:45AM', 'AS09001071', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 10 2009 9:29AM', 'AS09001074', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 10 2009 2:46PM', 'AS09001081', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 10 2009 10:31PM', 'AS09001095', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 10 2009 10:48PM', 'AS09001096', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Feb 11 2009 10:25AM', 'AS09001104', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 1 2010 12:06PM', 'AS10000003', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 1 2010 5:33PM', 'AS10000009', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'PA', 'Jan 2 2010 9:09PM', 'AS10000026', '4', 'Source3')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'MA', 'Jan 20 2010 2:05AM', 'SS10000613', '4', 'Source1')

  INSERT INTO dim1 ( AREA,DATE,ACCOUNT_NO,FILTER_CODE,SRC) VALUES ( 'MA', 'Jan 20 2010 5:30PM', 'SS10000642', '4', 'Source1')

  --my query to give YTD 2009 and 2010 count

  Select

  AREA,SRC

  ,case

  when DATE between '1/1/2010' AND getdate() THEN 'YTD 2010'

  when DATE between '1/1/2009' AND dateadd(yy,-1,getdate()) THEN 'YTD 2009'

  else 'Not Required'end AS PERIOD

  ,COUNT(distinct ACCOUNT_NO) as AC_Count

  from dbo.Dim1

  group by

  AREA,SRC

  ,case

  when DATE between '1/1/2010' AND getdate() THEN 'YTD 2010'

  when DATE between '1/1/2009' AND dateadd(yy,-1,getdate()) THEN 'YTD 2009'

  else 'Not Required'end

  Any help...

  Thanks

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  February 11, 2010 at 3:52 pm

  #1117818

  Duplicate post. Problem is being worked and answered here.