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
1297 Views

Similar Articles

[PageSpeed] 1

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:

Rephrasing Interior.ColorIndex 36 question.
How can I write a formula for a column of colors, (some have numbers, some don't) that will go down the column and look for Interior.ColorIndex 36 (light yellow) and return the number of cells down that particular color is located? Cell A2 Interior.ColorIndex 36? No. Could be blue, but not wanted. Cell A3 Interior.ColorIndex 36? No. Could be white, but not wanted. Cell A4 Interior.ColorIndex 36? No. Cell A5 Interior.ColorIndex 36? No. .. . . . Cell A218 Interior.ColorIndex 36? YES! Answer in formula cell A1 would be 216. First cell is question, last cell is the color, s...

Update stored procedure question
We are using VS 2008 and SQL Server 2005. I have a table Spread that has 1 Integer column called SecurityID. I have a stored procedure spUpdateSpread that increment the SecurityID by 1, and returns that value. If more than 1 process call the stored procedure at the same time, will it return the correct SecurityID ? Thank you CREATE TABLE [dbo].[Spread]( SecurityID [int] NOT NULL, PRIMARY KEY NONCLUSTERED ( [SecurityID] ASC ) ) ON [PRIMARY] CREATE PROCEDURE [dbo].[spUpdateSpread] @SecurityID int output as update Spread set SecurityID = SecurityID + 1 select @Secu...

Journal questions.
In a Corporate or Workgroup environment using Exchange Server can I, as a user not Admin, make the Journal work with the Global Address List as opposed to my Contacts? Also can I configure Journal such that it only record emails etc associated with particular topics as opposed to to/from certain contacts? Outlook performs automatic journaling only for contacts in the user's = default Contacts folder and does not do any filtering.=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http:/...

Payroll Question
I am trying to set up my old office's payroll on a spreadsheet so that the new secretary doesn't have to spend so much time on payroll each week. The problem I am having is when calculating the SSI & Med, Excel automatically rounds the answer to two (2) decimals, but when I total the column it adds using the the extended figure (ex.: $323.00 * .062 = $20.026, in my column it will round the answer to $20.03, but when I total the column to show an employee the withholdings for the year, excel totals the column using the $20.026 amount. I need to have the total calculat...

Combo Box & Report Questions
1) I have a combo box that I want to use to create the content for a report and then have the combo box open the report. My strWhere code is returning a compile error: Variable Not Defined. What needs changing to make this work? 2) Can I open the report directly from the combo box instead of adding another command to open the report after the combo box content is selected? 3) How do I get the combo box to automatically remove the previous selection when I return to the form? 4) Will this work on a bound form? Following is my code (The table name is ASSIGNEES): Privat...

hyperlink question #6
I have a workbook with a series of hyperlinks from one sheet to certainl cells in another sheet. When i protect the destination sheet the hyperlinks no longer work - i ge through to the correct sheet but not to the require cell.. Am i being dumb and missing something simple ?? I created a workbook with two worksheets (using xl2003). I put a hyperlink (via insert|hyperlink) in Sheet2!a1 that points at sheet1!b99. I protected sheet1. And that hyperlink still worked fine. I put a hyperlink (via =hyperlink("#sheet1!c1834") in sheet2!a2. I still had sheet1 protected. And that w...

Sending spreadsheet through email/saving ... question
A user has accidentally 'saved' a spreadsheet in the email ... is there a way to bring it back into Excel out of the email look to save it without the email header? Do you mean: 1) the user created an email with the spreadsheet as an attachment? 2) the user copied the contents of the spreadsheet into the body of an email message? "Annette" <Samm@hotmail.com> wrote in message news:urXe3udMEHA.268@TK2MSFTNGP11.phx.gbl... > A user has accidentally 'saved' a spreadsheet in the email ... is there a > way to bring it back into Excel out of the email look ...

Unhandled Script Exception #7
I have a client who is receiving the following unhandled script exception when logging into a single company: EXCEPTION_CLASS_SCRIPT_BAD_PARAM SCRIPT_CMD_DEREF_TABLE Any idea what could be causing this? It usually happens when the client and server are out of sync. Try removing "products" from the dynamics.set file to match those that are not available in your company database. a. Do you have only one company? b. can you log into this company from a different install of GP? "Hannibal" wrote: > I have a client who is receiving the following unhandled script e...

Address bar autocomplete question
I'm running WXP SP2 with IE6 on a freshly reinstalled OS from HP's "full system recovery to original system" type disks. Previously, at some point when I used Google's advanced search http://www.google.com/advanced_search?hl=en, the address bar auto complete dropdown list had something like "Google Advanced Search" from the title of that web page. I liked it in that it was the first item in the list when I type goo in the address bar. On this fresh reinstallation of the OS, that "Google Advanced Search" item is not being set up in th...

Shape Alignment Question
Greetings, One thing I do frequently is "fill" a rectangle with squares. Basically I overlay the squares on the rectangle one at a time. The squares must touch, however, not overlap. I usually zoom to 400 to help me do this task. Is there a setting or some better technique that will help me do this easier? Something like snap side to side would be great. I have been unable to find any settings, however, this group understands the power of this software and always seems to offer help. Thanks for your help! Ray Determine the number of vertical lines you need (number ...

OLE question
Two of the users here are saving a PDF file from an e-mail to their desktop to attach to the Notepad of the Receivings Transaction Entry screen. When selecting the file to attach, they get the prompt about the application is busy and have the "switch to", "retry", and "cancel" buttons to click. What is causing this? The user workstations are Terminal Server on Windows Server 2003 and Windows XP SP3. We're running GP9 SP4 too. Any insight would be appreciated. Thanks. -J Are they saving the files to their workstation desktop or the Ter...

Is it possible to run a Powershell script as a Windows Service?
Is it possible to run a Powershell script as a Windows Service? Many Thanks, =Adrian= Hi Adrian, Almost :) Easiest way to do it is to create schedule task that will run ever n minutes (and of course will run your powershell script). Martin "Adrian" <Adrian@discussions.microsoft.com> wrote in message news:4F973358-A761-4E37-AA86-3E51822AE428@microsoft.com... > Is it possible to run a Powershell script as a Windows Service? > > Many Thanks, > =Adrian= ...

simplified table question
My table-into-table question comes down to this: Can I take x number of text boxes and put their data into a table with x rows and 1 column? Other than highlight all text of box 1, cut/paste into table row 1, highlight all text of box 2, cut/paste into table row 2, etc., that is. Thanks for your help, Julia Skiles ...

CRM HArdware Question
Hello, We currently have CRM 1.2 with the following hardware Server 1 - CRM 1.2 and SQL server Server 2 - Exchange 2003 Other Servers - DC etc. I was wondering if I can have CRM 1.2, SQL server and Exchange 2003 all on the same box ? i.e. Server 1 : CRM 1.2, SQL , Exchange. This server will be a Dual Xeon with 1GB RAM. Thank you in advance. vikrantca As long as you are willing to live with the performance implications, this should work. The key is that you have your DC's on other machines. There can be some imcompatibilities with AD residing on the CRM server, but the other co...

Query on SQL Server is fast, while Access is crawling
I have a query that executes a Table-Valued Function in SQL server. If I execute it withing Query Analyzer in SQL server, it completes in 40 seconds. If I execute the same query in a pass-through query from Access, it takes over 5 minutes. I ran sp_lock to view the locking information during the execution of the query from both locations. Query Analyzer came back with around 5300 locks immediately and the number remained constant throughout query execution. When executed from Access, it would add about 100 locks every 10 seconds. It would take a very long time to get up t...

Question #2
Hello, I am new to this group. Anyways, I have a question. In Visual C++ 6.0, I make a static text control, then type what it will say. But how do I edit the size/font/color of the text? Thank you lots! -Adam Hi Adam, Please see this. http://www.codeproject.com/staticctrl/clabel.asp Sreeram Adam Cowherd wrote: > Hello, > I am new to this group. Anyways, I have a question. In Visual C++ 6.0, I > make a static text control, then type what it will say. But how do I edit > the size/...

MS Money 2005 backup question
In MS Money 2005 standard version, when you want to backup tp a 3 1/2 floppy it does not offer to overwrite the file which would be particularly handy to do since it unfortunately requires 2 discs unlike any previous versions I have used. I think that is pretty lame. So it seems, unless I missed something somewhere, you have to format or delete the current file before using the same discs again. Is there a fix for this somewhere or, has a newer version got this squared away? A goof in M2005 I guess! No fix available except an upgrade to M2006. BTW floppies are not really reliable fo...

convert the java script to VBA
Here is one java script, i need to convert this java script to VBA., I need the below coding to used in VBA ---------- <Script language="JavaScript"> </SCRIPT> <FORM NAME="form1"> <SELECT NAME="select" ONCHANGE="goto(this.form)" SIZE="1"> <OPTION VALUE="">-------Choose a Selection------- I need this event onchange() to be used in vba. Can any one help me out please. -- Message posted via http://www.officekb.com sureshkasi1985 via OfficeKB.com wrote: > Here is one java script, i need to conver...

Monthly View Question
This is probably a pretty basic question...but I just can't find the solution. When looking at the monthly view is there a way to show more then 2 events? how many you see is based on the window height. See http://slipstick.me/2wk for a way to increase the height of the grid. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTE...

IE 7 question
I have two machines both running XP Pro w/IE7. Older machine has sp2 the other one has sp3. Both are fully patched from Windows update.My IE7 is not working properly on BOTH machines. It is real slow and sometimes when I try to use a link it will just freeze up. I have tried using IE7 with the No Add ons and it does not help. I have used AVG (free). Spybot, SuperAntiSpyware (free) and Malwarebytes (paid version) to check for malware and both machine come up clean. These computers are not networked. I have spybot and Spyware Blaster installed on both machine and have been told...

Question on Multiple language support
Hi there, I am getting started on multiple language support with MFC and VC++ 6.0. I managed to make a simple test MFC application with satellite resource DLLs in various languages incuding Japanese. In works fine under XP and displays the characters well. However, under Win2000 the Japanese characters won't be displayed properly, instead it displays the string: llllllXMLlllllll Quite strange, considering the Japanese fonts are installed on the computer. A colleague of mine tried my test app. on his computer (running on XP) and got the same string displayed, hence my thinking it is n...

bootable CD question
Hello: I have an old bootable CD for WIN XP SP2, I liked to add some data to it, so I copied it to a folder in the hard disk, then I added my data there, then I write back the whole to a new CD (rewritable). the new CD is not bootable, why? and can this be done, how? thanks Maurice "Maurice" <morisaab@hotmail.com> wrote in news:OAaWRWolKHA.2592@TK2MSFTNGP04.phx.gbl: > Hello: > > I have an old bootable CD for WIN XP SP2, I liked to add > some data to it, so I copied it to a folder in the hard > disk, then I added my data there, then I...

similiar upgrade question, on upgrades do contact info auto load, or
do you have to use pst files All data contained in a *.pst file Other than that yr Q is a little hard to follow. "bob" <bob@donotspam> wrote in message news:ex66JE1qEHA.372@TK2MSFTNGP12.phx.gbl... > do you have to use pst files > > ...

Word Auto Number Question
I'm making tickets using a perforated business card sheet, and am wondering if there is a way to automatically number the individual tickets, i.e., not pages. Thanks! Kathryn Douglas There's a newsgroup called microsoft.public.word.numbering that you should check out for this. Also try microsoft.public.word.newusers. "Kathryn Douglas" <anonymous@discussions.microsoft.com> wrote in message news:0ca401c397e4$db5ba5a0$a401280a@phx.gbl... > I'm making tickets using a perforated business card sheet, > and am wondering if there is a way to automatically number...

Formula copying question
Here is the problem that I have, just wondering if someone might kno how to do this. Lets say you have a value in cell B2(i.e. 5) that you want to use t multiply against other vales in another column (i.e. C) how can yo copy a formula so that it retains the reference to cell b2 whil changing to multiply against the cells in column C. B C 5 1 2 3 To get this to work now, I have to copy the values to the left o column C, otherwise the cell reference to B2 changes to B3, etc. So want to have B2*C2 B2*C3 B2*C4 instead of B2*C2 B3*C3 B4*C4. Cheers and thanks for your tim...