|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to: CrossTab or Pivotput my raw data into a temp table and re-populate the final output table. Details... Don't ask for a DDL or similar beyond what's provided, it's not important, i.e. you don't need it to understand the question or solution. Source table columns under consideration from a single table createon datetime ,source varchar(20) ,createby varchar(10) Createby is only used to determine a class for source, for example... If an in-house user creates a record it can either be '-Tel' or not, when a new record is imported from the web it will never be '-Tel,' but will look the same as an in-house except that the user will be 'WEBIMP' different source. So here are some source sample values C0905A C0905B C0905C... - L C0905A-Tel C0905B-Tel C0905C-Tel... - L ....where user [not] = 'WEBIMP' My current select, but there must be a better way... select convert(varchar(10),c1.createon,101) ,case when c1.source like '%TEL%' then count(c1.source) end TEL -- then for each possibility A thru L.... a separate column and its count... ,case when c1.source not like '%TEL%' and c1.createby like 'WEBIM%' and c1.source like '%0905%' and substring(c1.source,6,1) = 'A' then count(c1.source) end WEBA ,case when c1.source not like '%TEL%' and c1.createby like 'WEBIM%' and c1.source like '%0905%' and substring(c1.source,6,1) = 'B' then count(c1.source) end WEBB from contact1 c1 with(nolock) where c1.createon between '09/01/2005' and getdate() group by c1.createon -- ,c1.source ,c1.createby order by c1.createon and c1.source like '%0905%' and substring(c1.source,6,1) = 'A') [WEB-A] TIA JeffP.... JDP,
Check out: http://support.microsoft.com/default.aspx?scid=kb;en-us;175574 HTH Jerry Show quote "JDP@Work" <JPGMTNoSpam@sbcglobal.net> wrote in message news:u2ZmOgkuFHA.596@TK2MSFTNGP12.phx.gbl... >I need a single row output with respective counts for each column, >currently I > put my raw data into a temp table and re-populate the final output table. > > Details... Don't ask for a DDL or similar beyond what's provided, it's > not > important, i.e. you don't need it to understand the question or solution. > > Source table columns under consideration from a single table > > createon datetime ,source varchar(20) ,createby varchar(10) > > Createby is only used to determine a class for source, for example... > > If an in-house user creates a record it can either be '-Tel' or not, when > a new > record is imported from the web it will never be '-Tel,' but will look the > same > as an in-house except that the user will be 'WEBIMP' different source. > > So here are some source sample values > > C0905A > C0905B > C0905C... - L > > C0905A-Tel > C0905B-Tel > C0905C-Tel... - L > > ...where user [not] = 'WEBIMP' > > My current select, but there must be a better way... > > select convert(varchar(10),c1.createon,101) > ,case when c1.source like '%TEL%' then count(c1.source) end TEL > > -- then for each possibility A thru L.... a separate column and its > count... > ,case when c1.source not like '%TEL%' and c1.createby like 'WEBIM%' > and c1.source like '%0905%' and substring(c1.source,6,1) = 'A' then > count(c1.source) end WEBA > ,case when c1.source not like '%TEL%' and c1.createby like 'WEBIM%' > and c1.source like '%0905%' and substring(c1.source,6,1) = 'B' then > count(c1.source) end WEBB > > from contact1 c1 with(nolock) > where c1.createon between '09/01/2005' and getdate() > group by c1.createon -- ,c1.source ,c1.createby > order by c1.createon > > and c1.source like '%0905%' and substring(c1.source,6,1) = 'A') [WEB-A] > > TIA > > JeffP.... > > Jerry, TFYR
This beautiful, I was having a hard time using Count because in my mind I was counting occurances, not summing $amts Throws an error.... due to "Count" select createOn = convert(varchar(10),c1.createon,101) ,Count(case when c1.source = 'C0805A-TEL' then c1.source else 0 end) TEL ..... So, for a given day say 9/13 and a simplified grouped Sum/Count of the data select createOn = convert(varchar(10),c1.createon,101) ,sum(case when c1.source = 'C0805A-TEL' then 1 else 0 end) TEL ,sum(case when c1.source not like '%TEL%' and c1.createby like 'WEBIM%' and cast(c1.source as varchar(2)) <> 'DI' then 1 else 0 end) WEB ,sum(case when c1.source not like '%TEL%' and c1.createby not like 'WEBIM%' and cast(c1.source as varchar(2)) <> 'DI' then 1 else 0 end) DTA from contact1 c1 with(nolock) where c1.createon between '09/13/2005' and '09/13/2005' group by createOn order by c1.createon I get the expected Counts, yeah! createOn TEL WEB DTA ---------- ----------- ----------- ----------- 09/13/2005 2 30 78 Here are a sample of codes, these are the ones I'm looking for, we have in the database similar codes going back to 1998 The "-TEL" modifier is added when a client calls in even though they had an opportunity to contact via the web or by return mail, but opt to call in. Assuming that we want a column for each code and a count by each day, will I have to write an expression for each column, I already think the answer is yes. There are 25 codes that I'm looking for..... C0805 C0805A C0805A-TEL C0805B C0805B-TEL C0805C C0805C-TEL C0805D C0805D-TEL C0805E C0805E-TEL C0805F C0805F-TEL C0805G C0805G-TEL C0805H C0805H-TEL C0805I C0805I-TEL C0805J C0805J-TEL C0805K C0805K-TEL C0805L C0805L-TEL TIA JeffP.... Show quote "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message news:udqWAkkuFHA.908@tk2msftngp13.phx.gbl... > JDP, > > Check out: http://support.microsoft.com/default.aspx?scid=kb;en-us;175574 > > HTH > > Jerry > "JDP@Work" <JPGMTNoSpam@sbcglobal.net> wrote in message > news:u2ZmOgkuFHA.596@TK2MSFTNGP12.phx.gbl... > >I need a single row output with respective counts for each column, > >currently I > > put my raw data into a temp table and re-populate the final output table. > > > > Details... Don't ask for a DDL or similar beyond what's provided, it's > > not > > important, i.e. you don't need it to understand the question or solution. > > > > Source table columns under consideration from a single table > > > > createon datetime ,source varchar(20) ,createby varchar(10) > > > > Createby is only used to determine a class for source, for example... > > > > If an in-house user creates a record it can either be '-Tel' or not, when > > a new > > record is imported from the web it will never be '-Tel,' but will look the > > same > > as an in-house except that the user will be 'WEBIMP' different source. > > > > So here are some source sample values > > > > C0905A > > C0905B > > C0905C... - L > > > > C0905A-Tel > > C0905B-Tel > > C0905C-Tel... - L > > > > ...where user [not] = 'WEBIMP' > > > > My current select, but there must be a better way... > > > > select convert(varchar(10),c1.createon,101) > > ,case when c1.source like '%TEL%' then count(c1.source) end TEL > > > > -- then for each possibility A thru L.... a separate column and its > > count... > > ,case when c1.source not like '%TEL%' and c1.createby like 'WEBIM%' > > and c1.source like '%0905%' and substring(c1.source,6,1) = 'A' then > > count(c1.source) end WEBA > > ,case when c1.source not like '%TEL%' and c1.createby like 'WEBIM%' > > and c1.source like '%0905%' and substring(c1.source,6,1) = 'B' then > > count(c1.source) end WEBB > > > > from contact1 c1 with(nolock) > > where c1.createon between '09/01/2005' and getdate() > > group by c1.createon -- ,c1.source ,c1.createby > > order by c1.createon > > > > and c1.source like '%0905%' and substring(c1.source,6,1) = 'A') [WEB-A] > > > > TIA > > > > JeffP.... > > > > > > On Thu, 15 Sep 2005 23:13:38 -0700, JDP@Work wrote:
(snip) >Throws an error.... due to "Count" Hi JeffP,>select createOn = convert(varchar(10),c1.createon,101) > ,Count(case when c1.source = 'C0805A-TEL' then c1.source else 0 end) TEL ..... You can fix the error by changing this to > ,Count(case when c1.source = 'C0805A-TEL' then c1.source else NULL end) TEL ..... or even (using the fact that NULL is the default result of CASE) to> ,Count(case when c1.source = 'C0805A-TEL' then c1.source end) TEL ..... Your versions failed because it's trying to convert a varchar column andan integer constant to the same datatype. If c1.source had been any of the numerical datatypes, you'd have gotten no error, but incorrect results (since 0 is a legal value, it is included in the COUNT; that's why you should use NULL to exclude it). Of course, changing to SUM as you did works good too. (snip) >where c1.createon between '09/13/2005' and '09/13/2005' Don't use ambiguous date formats. Use one of the following three:* yyyymmdd (date only - note that there are no dashes, slashes, dots, or any other interpunction) * yyyy-mm-ddThh:mm:ss (date plus time - note the dashes, colons and uppercase T) * yyyy-mm-ddThh:mm:ss.mmm (same as above, but includes milliseconds) Also, this will only return rows that are created on exactly midnight of september 13th (unless you always set the time part of the CreatedOn column to midnight). Here's a safer way: WHERE c1.CreatedOn >= '20050913' AND c1.CreatedOn < '20050914' -- Note: use <, not <= (snip) >Assuming that we want a column for each code and a count by each day, will I You either have to write an expression for each column, or (the>have to write an expression for each column, I already think the answer is yes. recommended way) do the formatting in the front-end. Most front ends have builtin support for crosstab reports (aka pivot tables). Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Hi ,
check out AGS Crosstab Builder for SQL 2000 http://www.ag-software.com/?tabid=21 -- Show quotekind regards Greg O Need to document your databases. Use the first and still the best AGS SQL Scribe http://www.ag-software.com "JDP@Work" <JPGMTNoSpam@sbcglobal.net> wrote in message news:u2ZmOgkuFHA.596@TK2MSFTNGP12.phx.gbl... >I need a single row output with respective counts for each column, >currently I > put my raw data into a temp table and re-populate the final output table. > > Details... Don't ask for a DDL or similar beyond what's provided, it's > not > important, i.e. you don't need it to understand the question or solution. > > Source table columns under consideration from a single table > > createon datetime ,source varchar(20) ,createby varchar(10) > > Createby is only used to determine a class for source, for example... > > If an in-house user creates a record it can either be '-Tel' or not, when > a new > record is imported from the web it will never be '-Tel,' but will look the > same > as an in-house except that the user will be 'WEBIMP' different source. > > So here are some source sample values > > C0905A > C0905B > C0905C... - L > > C0905A-Tel > C0905B-Tel > C0905C-Tel... - L > > ...where user [not] = 'WEBIMP' > > My current select, but there must be a better way... > > select convert(varchar(10),c1.createon,101) > ,case when c1.source like '%TEL%' then count(c1.source) end TEL > > -- then for each possibility A thru L.... a separate column and its > count... > ,case when c1.source not like '%TEL%' and c1.createby like 'WEBIM%' > and c1.source like '%0905%' and substring(c1.source,6,1) = 'A' then > count(c1.source) end WEBA > ,case when c1.source not like '%TEL%' and c1.createby like 'WEBIM%' > and c1.source like '%0905%' and substring(c1.source,6,1) = 'B' then > count(c1.source) end WEBB > > from contact1 c1 with(nolock) > where c1.createon between '09/01/2005' and getdate() > group by c1.createon -- ,c1.source ,c1.createby > order by c1.createon > > and c1.source like '%0905%' and substring(c1.source,6,1) = 'A') [WEB-A] > > TIA > > JeffP.... > >  |
|||||||||||||||||||||||
Other interesting topics