Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
archana23
Yak Posting Veteran
89 Posts |
Posted - 2012-09-24 : 10:13:21
|
I am new to writing sql queries. here is my present query select distinct DAY(InvDate) as Date,Location,sum(Inv+St) as Total From InvInfo where (InvDate >= '8/1/2012') and (InvDate <= '8/2/2012')Group by DAY(InvDate) ,LocationHere i am getting reults like thisDate Location Total 1 Dallas 1001 Atlanta 501 Houston 801 Raleigh 751 Chicago 1202 Dallas 802 Atlanta 602 Houston 772 Raleigh 822 Chicago 110But i need to get data like thisDate Dallas Atlanta Houston Raleigh Chicago 1 100 50 80 75 1202 80 60 77 82 110Can you guys please help on this.. i am very new to sql coding. Archana |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-09-24 : 10:32:35
|
is the number of location per date fixed ? is there a max ? or it is unknown ? KH[spoiler]Time is always against us[/spoiler] |
|
|
archana23
Yak Posting Veteran
89 Posts |
Posted - 2012-09-24 : 10:43:58
|
Hi khtanYes number locations are fixed per date. Every date has Inv values for each location. and all locations which i mentioned are present in each date.ThanksArchana |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-09-24 : 11:22:05
|
[code]SELECT *FROM( SELECT DAY(InvDate), Location, Inv+St as Total FROM InvInfo ) dPIVOT( SUM (Total) FOR Location in ( [Dallas', [Atlanta], [Houston], [Raleigh], [Chicago] )) p[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
archana23
Yak Posting Veteran
89 Posts |
Posted - 2012-09-25 : 15:06:34
|
Hi KH,Thanks for you r reply. It worked for me.Appreciated.Archana |
|
|
archana23
Yak Posting Veteran
89 Posts |
Posted - 2012-10-03 : 09:38:18
|
Hello,Now using this querySELECT *FROM( SELECT InvDate, Location, Inv+obs as Total FROM InvInfo where (InvDate >= '8/1/2012')and (InvDate <= '8/31/2012') Group by InvDate ,Location ) dPIVOT( SUM (Total) FOR Location in ( [Dallas', [Atlanta], [Houston], [Raleigh], [Chicago] )) pBy using above query getting this resultsDate Dallas Atlanta Houston Raleigh Chicago 1 100 50 80 75 1202 80 60 77 82 110But i want one more extra column(OBS) in my Actual results as shown belowDate Dallas Atlanta Houston Raleigh Chicago OBS1 100 50 80 75 120 202 80 60 77 82 110 18This extra columns will get by below queryselect sum(OBS) as OBS from InvInfo where (InvDate >= '8/1/2012')and (InvDate <= '8/31/2012') Group by InvDateSo how to combine these two queries to get Actual results as shown above.Please anyone Can help me on this?ThanksArchana |
|
|
|
|
|
|
|