Question on sql script

Could someone please help, I have started a new position in reporting, and 
I'm major learning on the job.  I need to create a script to extract data 
from a single table.  The fields are Customer, Units, Sales.  The problem I'm 
having is the following.  I know that I need to do some type of pivot query 
to extract the data in the way requested.  See Below

(Requested Results)

Customer  Units08-09 Sales08-09  Units09-10 Sales09-10   DiffUnits Diff Sales


I have created a sample script with a temp table and data and what I have 
come up with so far, but I just can't figure out how to group that data to 
get the needed results. 

http://sql-servers.com/nopaste/index.php?show=706

Could someone Please help!  Once I see it done I can understand how to do 
this type of thing in the future 


Thanks in advance

Dave.




-- 
Dave
0
Utf
9/11/2010 4:08:06 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
1395 Views

Similar Articles

[PageSpeed] 18

I'm not exactly sure what you wanted and the sample result you showed 
doesn't seem to go with the sample data you provided (BTW, thanks for 
providing the DDL and sample data).  But I think you want

With cte As
(Select Customer,
  Sum(Case When Year(orderdate) = 2009 Then Units Else 0 End) As Units0809,
  Sum(Case When Year(orderdate) = 2009 Then Sales Else 0 End) As Sales0809,
  Sum(Case When Year(orderdate) = 2010 Then Units Else 0 End) As Units0910,
  Sum(Case When Year(orderdate) = 2010 Then Sales Else 0 End) As Sales0910
From #temp123
Where orderdate >= '20090101' And orderdate < '20110101'
Group By Customer)
Select Customer, Units0809, Sales0809, Units0910, Sales0910,
  Units0910-Units0809 As DiffUnits, Sales0910 - Sales0809 As DiffSales
From cte;

Tom

"David" <duckkiller53@gmail.com> wrote in message 
news:7663F78A-6DD6-4105-9161-F3E58365E110@microsoft.com...
> Could someone please help, I have started a new position in reporting, and
> I'm major learning on the job.  I need to create a script to extract data
> from a single table.  The fields are Customer, Units, Sales.  The problem 
> I'm
> having is the following.  I know that I need to do some type of pivot 
> query
> to extract the data in the way requested.  See Below
>
> (Requested Results)
>
> Customer  Units08-09 Sales08-09  Units09-10 Sales09-10   DiffUnits Diff 
> Sales
>
>
> I have created a sample script with a temp table and data and what I have
> come up with so far, but I just can't figure out how to group that data to
> get the needed results.
>
> http://sql-servers.com/nopaste/index.php?show=706
>
> Could someone Please help!  Once I see it done I can understand how to do
> this type of thing in the future
>
>
> Thanks in advance
>
> Dave.
>
>
>
>
> -- 
> Dave 

0
Tom
9/11/2010 4:53:19 PM
I have spent some time on the problem which I asked about in my original 
post.  I have come up with a script that I think is close to what I need, but 
it does not use the PIVOT query technology.  Here is what I came up with.

IS THERE A BETTER WAY TO DO THIS WITH  PIVOT QUERY?

CREATE TABLE #temp123
(
Customer varchar(20),
Units integer,
Sales integer,
orderdate datetime
)

CREATE TABLE #temp2
(
 Customer varchar(20),
 Units0809 integer,
 Sales0909 integer,
 Units0910 integer,
 Sales0910 integer
)



INSERT #temp2
	Select Customer, 
           Units0809 = SUM(case when orderdate >= '01/01/2008' and 
                               orderdate <= '12/31/2009' THEN Units end),
           Sales0809 = SUM(case when orderdate >= '01/01/2008' and
                               orderdate <= '12/31/2009' THEN Sales end),    
        
           Units0910 = SUM(case when orderdate >= '01/01/2009' and 
                               orderdate <= '12/31/2010' THEN Units end),
           Sales0910 = SUM(case when orderdate >= '01/01/2009' and 
                               orderdate <= '12/31/2010' THEN Sales end)
            FROM #temp123 GROUP BY Customer


select * from #temp123
select * from #temp2
-- 
Dave


"David" wrote:

> Could someone please help, I have started a new position in reporting, and 
> I'm major learning on the job.  I need to create a script to extract data 
> from a single table.  The fields are Customer, Units, Sales.  The problem I'm 
> having is the following.  I know that I need to do some type of pivot query 
> to extract the data in the way requested.  See Below
> 
> (Requested Results)
> 
> Customer  Units08-09 Sales08-09  Units09-10 Sales09-10   DiffUnits Diff Sales
> 
> 
> I have created a sample script with a temp table and data and what I have 
> come up with so far, but I just can't figure out how to group that data to 
> get the needed results. 
> 
> http://sql-servers.com/nopaste/index.php?show=706
> 
> Could someone Please help!  Once I see it done I can understand how to do 
> this type of thing in the future 
> 
> 
> Thanks in advance
> 
> Dave.
> 
> 
> 
> 
> -- 
> Dave
0
Utf
9/12/2010 10:15:03 PM
Reply:

Similar Artilces:

SQL Delete Layaway at HQ
Looking for an SQL command that will delete an entire Layaway in HQ. Example delete Layaway number 57. Thanks for any help. Neil Never mind. I used DELETE from [Order] WHERE [Order].ID = '57' "nt8378" wrote: > Looking for an SQL command that will delete an entire Layaway in HQ. Example > delete Layaway number 57. Thanks for any help. > > Neil Don't forget to delete any of the Item information from the OrderEntry table for that order also. TomT "nt8378" <nt8378@discussions.microsoft.com> wrote in message news:9B17957F-2696-446C-B08...

change date in a sql statement from a cell
Hello, I am using Excel 2007 I have a pivot table that gets refresh everyday. The data from the pivot table is based on a sql statement, which the data is connected to a AS/400 table. Here is my problem every morning I go in the connection properties and change the SQLstatement (date) in the command text. I don't want my user to do this. What other option can I do? I was thinking change the date in a cell (A1) and somehow the SQL statement picks up the new date or maybe some sort of parameter, but I am clueless in how to do this. Any tips or website to visit I will a...

Windows 7 Mail Question
Does Windows 7 have an included email application? If so, does it support IMAP, POP, SMTP, EXCHANGE and HTTP email configurations? Thank you. -Frank YES "Frankster" <frank@SPAM2TRASH.com> wrote in message news:ze6dnYRTr8IDfqXWnZ2dnUVZ_hOdnZ2d@giganews.com... > Does Windows 7 have an included email application? If so, does it support > IMAP, POP, SMTP, EXCHANGE and HTTP email configurations? > > Thank you. > > -Frank Windows 7 does not include an email program. I use the free Windows Live Mail,(http:\download.live.com) but ther...

SMALL Function question
Hi All, First off, many thanks that assisted me in getting my golf score sheet running properly with the MATCH & INDEX stuff...it works great. I have a problem with the SMALL function...I have a list of 5 lowest scores returned from a range called 'calcutta'. In the adjacent cell I have the coresponding name returned ato match the score with the player. The problem is with ties. If two players hve the same score the SMALL function returns the first one it finds in each cell. I use it like this G1=SMALL(CALCUTTA,1) G2=SMALL(CALCUTTA,2) ETC.... "Monte Comeau" <...

SQL Error 'CoProcess' fail accessing to SQL data
I have two companies in my Great Plains Server, i make a backup of first company and restore that in the second. but when a attempt to access to second company whith any user, and I have this error: una obtener/cambiar primero operacion en la tabla 'coProcess' ha fallado al acceder a los datos SQL Hi Before you restored the company1 to company2, did you add the users to have acces to company2? If not you will need to add the users to company 2 within Great Plains system menu (uncheck the SQL options to apply this). Can you logon as the 'sa' user? Regards James &qu...

VBA speed question.
I want to know that A() and B() which faster... dim arr(99) as long dim arrB(9) as long sub A() arr(arrB()) = arr(arrB()) + 1 end sub sub B() dim X as long X = arrB() arr(X) = arr(X) + 1 end sub ...

Installation Question
I would like to install Vista on a Macintosh I have. It's a 250 g hard drive, but I've already used up 150 g. Is there a way not to put on all that extra junk that comes with Windows? I've already bought it a year and a half ago, the macintosh hard drive was replaced. Thank you On 12 Jul 2010, safety123@aol.com wrote in microsoft.public.windows.vista.general: > I would like to install Vista on a Macintosh I have. > It's a 250 g hard drive, but I've already used up 150 g. > > Is there a way not to put on all that extra junk that comes with ...

Newbie question: finding aliases and forwards
If I have an address such as services@somewhere.com how could I figure out where (which user) that address exists at in Exchange or where it forwards to (if it forwards versus belonging to a user)? Thanks. just go to Active Directory Users and computers and do a find with a filter by the users email Address "Joe Blow" wrote: > If I have an address such as services@somewhere.com > how could I figure out where (which user) that address exists at in Exchange > or where it forwards to (if it forwards versus belonging to a user)? > Thanks. > > > ...

Simple charting question
I don't believe I'm having so much trouble with this. I'm trying to make a graph with Temperature (Celesius) as the X-axis and Baseball Speed (Km/hr) as the Y-axis. When I highlight the columns below and try to make a bar graph - 0 44.1 1 47 3 44.7 4 43.5 5 50.2 13 59.9 I get two series plotted or shown seperately. I would like to get one of the following: ...

2003 Money (Can I -) question
Is it possible to change or preset defaults for the drop down fields used to create new recurring bill under [Bills & Deposits]? For example; [Write Check] is the default in the payment method. I want it to be [Print Check]. [Monthly] is default for the frequency and I want [Only Once]. I would like to be able to set my most used selections as defaults. Thanks - Andy Nope. "Andy" <a1cotton@knology.net> wrote in message news:079b01c392c3$6cfe0290$a301280a@phx.gbl... > Is it possible to change or preset defaults for the drop > down fields used to create new...

Great Plains Question 10-21-03
Can anyone tell me how Great Plains can be set up for 802.11b wireless scanners? Is the session just a regular Telnet session or is there some funky emulation? Also, is there any modules in place within Great Plains that allow portable RF scanners to operate? Given the fact that the portable scanner may only have a 16 x 21 line display. Or is it up to an integrator to set this up. please send this query to microsoft.public.greatplains "Kevin" <anonymous@discussions.microsoft.com> wrote in message news:00d301c39803$d049b0c0$a601280a@phx.gbl... > Can anyone tell...

Question about OleSetClipboard return CO_E_NOTINITIALIZED error!
When I using Clipboard function, for example below codes, SetClipboard() always is failed.I track into it and find OleSetClipboard return CO_E_NOTINITIALIZED. I depend on its tip and add Coinitialize() in. but It strill is wrong. who can help me? COleDataSource* pOds = new COleDataSource(); if (pOds == NULL) return FALSE; pOds->CacheGlobalData(nFormat,hMem); pOds->SetClipboard(); Michael F wrote: > When I using Clipboard function, for example below codes, > SetClipboard() always is failed.I track into it and find > OleSetClipboard return...

SQL Recovery Models
Hello, I am currently running CRM 1.2 and GP 7.5. I am using the backup utility in SQL 2000 to backup the databases for CRM, GP, and Integration. What recovery models are people using for GP and CRM? Some of the databases and configured for Simple while others are setup for Full recovery model. I would like to have full backups run daily and then backup the transaction logs hourly. Are there any advantages to running Veritas Backup Exec (or 3rd party backup software)? Any information would be appreciated. Thanks, Mike I would use Full recovery where possible. You won't...

XMLSPY Question
Altova posted an announcement regarding XMLSPY 2008 stating it can handle much larger files. So how large is large? Does it load the entire XML document into memory, or does it stream, like xmlreader? I read it has spyware so am reluctant to download the trial version. I did download Stylus Studio and it appears to be a DOM based system. I transform very large xml documents for my clients and some of these files are over 500Mb. I even did noe that was almost 3Gb. I wote some tools with xmlreader/writer that split the files, but requires some hard-coding for each one. sure would be nice to...

Access conversion to SQL
Hi all. I have an application that currently connects to an Access database, now the customers are asking for a MySql and MsSql server back end database. MySql handles True and False values through the bit field without any problems, MsSql doesn't. I can't even use the words True or False in an Sql statement. This means I have to write two loads of code for every transaction where there is a true/false value involved, a complete pain. It also gives me the problem of setting controls true/false value by checking a table field: chkAdmin.Checked = GetField("select admin...

installing sql server 2008 express or 2010 express
I get always errors and im not able to continue,,,,,,can anyone help me plz? Thanks On Jan 5, 10:55=A0am, "eusclide" <e...@me.it> wrote: > I get always errors and im not able to continue,,,,,,can anyone help me p= lz? > Thanks 1. This is not the appropriate discussion group for SQL Server issues. 2. If you expect any response at all, you need to indicate the exact errors you are getting. More specifics. "eusclide" <eus@me.it> wrote in news:Ouk449hjKHA.1536@TK2MSFTNGP06.phx.gbl: > I get always errors and im not able to continue,,,...

looping question
I've used a few looping type macros until now with no problems. However, I'm completely stumped when i need to get excel to count things for me>>> I have 5 row of data in each column (for example). I want to get all the data to be in column A each set below the next. so I need to refer to B1:B5 and move it to A6:A10. Then repeat to move C1:C5 to A11:A15 etc..etc... I need to move all 5 rows in each case regardless of content (number letter, blank etc..) I just come up against a brick wall when it comes to all this i=i+1 stuff, refering to ranges and telling excel to move ...

move to new windows server and upgrade sql server 2000 to 2008
Hi I need to move/upgrade one of our sql server (sql server 2000 SP4 on windows server 2003) to a new sql server 2008 on a new windows server 2003 R2 box. What is the best way to upgrade move the databases /logins etc. If I just backup and restore the database logins/passwords will not be transfered. dbdba -- ontario, canada db There is upgrade advisor for sql server 2008 , search on interent "db" <db@discussions.microsoft.com> wrote in message news:99D3A184-9888-4ABA-9A2F-AF2363302950@microsoft.com... > Hi > > I need to move/upgra...

looking for a script to make a Distribution List from a text file
Does anyone have a script for making a new Distribution List from a text file, or CSV file? I created larger dist lists by using adduser. Then, once the group is created, I mail enable it. "BwiseIT" wrote: > Does anyone have a script for making a new Distribution List from a text > file, or CSV file? > > > ...

aggregate sql
im new to the world of sql, so im not sure if this is can even be accomplished in a single query or if two separate queries are required. i am trying to put together a query where the end result is based on a condition of an aggregate...i want the sum of the amount billed (and all other information requested) to appear based on a condition that involves an operation on the amount billed. for example: select name, account, cycle date, sum(amount billed) from billing information group by name, account, cycle date where ((1-((select amount billed from billing information where promo id...

Billing/Statement Question w/Solomon Integration
Here is our company's structure: Our Corporate Office is being ran by Solomon, we have retail locations that will be running RMS for the retail counter sales.....We have many customers that not only call on us for service work, but also will come into the retail store to purchase products and services....When they make the purchase at the retail location, I would like them to be able to put their sale "on account", and once a month we can then bill them for their purchases. At the month end, I will need to be able to generate a statement that shows their activity for the ...

Sql Server 2005. Best configuration and parameters.
Hi, We have a website (.NET 2.0 on IIS 6.0) and a Sql Server 2005 (9.0.4035) running on the same machine, a Windows Server 2003 R2 Service Pack 2 with 8 GB RAM (Xeon E5405 2 Ghz). The website works with stored procedures. And max 20 users at the same time. We are wondering the best following parameters for this particular configuration... So, we have 2 questions: Firstable, what is the best configuration for the 8 processors (0 --> 7) for the processor affinity and the I/O affinity ? Today we have: Id Proc Proc Affinity I/O Afiinity 0 1 ...

QS Hook Function Question
Based on the surfpro.dll example, I have roughed out a prospective function that I hope will allow me to initiate a membership style control over POS transactions. I'm not 100% sure how to call a QSBridge FireEvent -- ClearCustomer as the POS requires a Customer to complete a sale, so I figure no customer, or take away the customer, no sale... right? In VB6 this morning, the Date function, literally returned 6/28/2005. Great! If the Customer Expiration Date is stored in CustomDate1, then a quick subtraction and a few conditionals should do it. The subtraction returned an integer calc of t...

One More Calendar Control Question
Seems as if there are many questions about the ActiveX calendar control and many warnings on not to use them. I have used them with some success, but some issues just make me scratch my head. I am currently using it to have the user select a start date and an end date to run a report. The form opens and the calendars are set to the current date. If the user clicks anywhere on the dates, the start or end date changes accordingly. The problem I have is when the user selects a different month or year, none of the dates are depressed. The user has to select a new date within the sel...

sumproduct question please
Hi everyone I have a list of names in column a - I then have data in various other columns which is copied from other worksheets (Columns B, F and J are names) I want to sum how many time a name in column a is listed in the others so have entered as: =SUMPRODUCT(($B$2:$B$500=A2)*($F$2:$F$500=A2)*($J$2:$J$500=A2)) But this is not calculating correctly - can someone pls advise what step I'm missing?? -- Thanks as always Lise Try =COUNTIF(A:A,A1)+COUNTIF(F:F,A1)+COUNTIF(J:J,A1) -- Jacob "Lise" wrote: > Hi everyone > > I have a list ...