Pivot Query
Mar 24, 2008
SELECT
[R].[PaymentMonth],
[S].[RegionCode],
[S].[CmsStateShortName],
[P].[Attribute1] AS [FinalProduct],
[Membership] = SUM([R].[Membership])
FROM
[RptMMRSummary1] [R] INNER JOIN [RefCmsState1] [S]
ON [R].[CmsStateCode] = [S].[CmsStateCode]
INNER JOIN [RefPlanBenefitPackage1] [P]
ON [R].[PlanBenefitPackageID] = [P].[PlanBenefitPackageID]
WHERE
[R].[PaymentMonth] IN ('200712', '200711', '200612')
-- [P].[Attribute1] IN ('HMO', 'PPO', 'PFFS', 'SNP', 'EVCSNP')
GROUP BY
[R].[PaymentMonth],
[S].[RegionCode],
[S].[CmsStateShortName],
[P].[Attribute1]
-------------------------------------------------------------------
How do we use the pivot query for the above script. Layout as below
200801 Month / Year Selection
-------------------------------------------------------
StateShortname | Attribute | Attribute | Attribute | Total
----------------------------------------------------------
AL Values Values Values
OL Values Values Values
ZW Values Values Values
WEST Group By Region Code Total
BK Values Values Values
MN Values Values Values
EAST Group By Region Code Total
200712 Always Previous Month for the above selection Month /Year
-------------------------------------------------------
StateShortname | Attribute | Attribute | Attribute | Total
----------------------------------------------------------
AL Values Values Values
OL Values Values Values
ZW Values Values Values
WEST Group By Region Code Total
BK Values Values Values
MN Values Values Values
EAST Group By Region Code Total
200612 Always Previous year End Month for the above select Month / Year
-------------------------------------------------------
StateShortname | Attribute | Attribute | Attribute | Total
----------------------------------------------------------
AL Values Values Values
OL Values Values Values
ZW Values Values Values
WEST Group By Region Code Total
BK Values Values Values
MN Values Values Values
EAST Group By Region Code Total
Output :
[RegionCode],
[CmsStateShortName],
CurMonth_HMO, CurMonth_PPO, CurMonth_PFFS, CurMonth_SNP, CurMonth_EVCSNP,
PrevMonth_HMO, PrevMonth_PPO, PrevMonth_PFFS, PrevMonth_SNP, PrevMonth_EVCSNP,
PrevYrEndMonth_HMO, PrevYrEndMonth_PPO, PrevYrEndMonth_PFFS, PrevYrEndMonth_SNP, PrevYrEndMonth_EVCSNP
View 4 Replies
ADVERTISEMENT
Feb 19, 2008
consider the following table:
name , TaskDate
john , 01/01/2006
john , 01/03/2005
steve , 01/05/2006
i want to build a select statement that gives result like the following:
name , JanuaryTotal , FebruaryTotal
john , 150 , 110
steve , 170 , 50
so the result will be total tasks in specific month, is it doable in one select or should i turn around using hash tables ???
View 6 Replies
View Related
Mar 8, 2004
Hi, I have the following query that kinda does what i want
SELECT ABTANumber, TourOperator, ReportStatus,
COUNT(*) AS Counter
FROM (SELECT ABTANumber, TourOperator, r.ReportStatus FROM bookingdetails bd LEFT JOIN report r ON bd.Id = r.BookingDetailsId) a
GROUP BY ABTANumber, TourOperator, ReportStatus
This is what it displays
TourOp|ReportStatus|Count
JMC..... Fail.............. .10
JMC..... Pass..............621
JMC..... Warn.............5
SET..... Fail.............. .12
SET..... Pass..............621
SET..... Warn.............3
But what i want to display is this
TourOp|Pass|Fail|Warn
JMC......621 ..10....5
SET..... 621...12....3
I'm really stuck on this and would appreciate any help
thanks
Mark
View 2 Replies
View Related
Mar 30, 2006
hi,
I've table TAB_QUESTION:
ID_QUESTION..........VALUE_ID..........NUM_ANSWER. .....DESC_ANSWER
XB1.................1...................0......... .....YES
XB1.................2...................0......... .....NO
XB1.................3...................1......... .....GOOD
XB1.................4...................0......... .....SUFF
XB1.................5...................1......... .....NO_GOOD
XB1.................6...................0......... .....NR
XB1.................7...................0......... .....NN
YB1.................1...................1......... .....YES
YB1.................2...................2......... .....NO
YB1.................3...................3......... .....GOOD
YB1.................4...................0......... .....SUFF
YB1.................5...................3......... .....NO_GOOD
YB1.................6...................2......... .....NR
YB1.................7...................1......... .....NN
ZC1.................1...................0......... .....YES
ZC1.................2...................0......... .....NO
ZC1.................3...................0......... .....GOOD
ZC1.................4...................0......... .....SUFF
ZC1.................5...................0......... .....NO_GOOD
ZC1.................6...................0......... .....NR
ZC1.................7...................1......... .....NN
TC1.................1...................1......... .....YES
TC1.................2...................1......... .....NO
TC1.................3...................1......... .....GOOD
TC1.................4...................1......... .....SUFF
TC1.................5...................1......... .....NO_GOOD
TC1.................6...................0......... .....NR
TC1.................7...................0......... .....NN
.................................................. ........
.................................................. ........
I've always JUST 7 (seven) DESC_ANSWER (YES,NO,GOOD,SUFF,NO_GOOD,NR,NN)
Now I'd like to have ID_QUESTION like columns and DESC_ANSWER like rows.
like this:
DESC_ANSWER..........XB1........YB1............ZC1 ........TC1
YES.........................0..........1.......... ....0...........1
NO...........................0..........2......... .....0...........1
GOOD.......................1..........3........... ...0...........1
SUFF........................0..........0.......... ....0...........1
NO_GOOD..................1..........3............. .0...........1
NR...........................0..........2......... .....0...........0
NN...........................0..........1......... .....1...........0
How Can I write this query to get this output??
Thanks in advance!
View 2 Replies
View Related
Dec 27, 2007
I€™ve a table with 2 columns with the following data
Col 1 Col2
---------------------------
Vista 2005
Distinguish 2005
ColonialVoice 2006
Vista 2006
Vista 2007
SuperiorCard 2007
Distinguish 2007
I would like to get the output in the following format using PIVOT function
2005 2006 2007
---------------------------------------------------------
Vista ColonialVoice Vista
Distinguish Vista SuperiorCard
Distinguish
Help me to write the query€¦
--
ash
View 3 Replies
View Related
Dec 27, 2007
I€™ve a table with 2 columns with the following data
Col 1 Col2
---------------------------
Vista 2005
Distinguish 2005
ColonialVoice 2006
Vista 2006
Vista 2007
SuperiorCard 2007
Distinguish 2007
I would like to get the output in the following format using PIVOT function
2005 2006 2007
---------------------------------------------------------
Vista ColonialVoice Vista
Distinguish Vista SuperiorCard
Distinguish
Help me to write the query€¦
--
ash
View 3 Replies
View Related
Sep 12, 2007
Hi,
I have a table EmpProject with following:
=========================
unitid projectid employees
----------- --------- -----------------------
1 CCT1 Ravi,Raja,Kanna
2 CCT1 John,Vijay,Nithya
1 CCT2 Senthil,Lee,Suresh
2 CCT2 Ram,Krish,Latha
2 CCT3 Raja,Vijay,Ram
2 CCT4 Sankar
TSql to create the above :
create table empproject (unitid int, projectid varchar(4), employees varchar(1000))
insert empproject select 1,'CCT1', 'Ravi,Raja,Kanna'
insert empproject select 2,'CCT1', 'John,Vijay,Nithya'
insert empproject select 1,'CCT2', 'Senthil,Lee,Suresh'
insert empproject select 2,'CCT2', 'Ram,Krish,Latha'
insert empproject select 2,'CCT3', 'Raja,Vijay,Ram'
insert empproject select 2,'CCT4', 'Sankar'
I would like to have it in the following format
========================================================
unitid CCT1 CCT2 CCT3 CCT4
----------------------------------------------------------------------------------------------------------------
1 Ravi,Raja,Kanna Senthil,Lee,Suresh
2 John,Vijay,Nithya Ram,Krish,Latha Raja,Vijay,Ram Sankar
Is there any way to query?
Thanks in Advance,
Nithyapriya
View 6 Replies
View Related
Mar 30, 2006
Hi!
I have a table Items(id, name, section, id_format, price) and a table Format(id, format,order)
What I want to do is display: Name, Format1, Format2, Format3.
How can I acheive that result??
THanks!!
-JTM
View 2 Replies
View Related
Sep 26, 2007
I have tbWarehouseStock, contain
WHCode Item Stock
------ ------ -----
WH001 Pencil 10
WH001 Pen 10
WH002 Pencil 5
WH003 ruler 100
How to make pivot query like this, I am thinking of dynamic SQL but dont know how to do this
Item WH001 WH002 WH003 WH004 WH005 ....
------ ----- ----- ----- ----- -----
Pencil 10 5 0
Pen 10 0 0
Ruler 0 0 100
thanks
View 3 Replies
View Related
Feb 27, 2008
(SQL Server 2005)
I have the following query (trying to execute in Managment Studio):
SELECT Rate, Lender, Pricing, Max(Pricing) AS Maximum, Min(Pricing) As Minimum, Avg(Pricing) As Median
FROM [10_Tier].[dbo].[Temp10Tier_1000]
PIVOT (Sum(Pricing) For Lender)
I get the error: Incorrect syntax near ')' Microsoft SQL Server Error 102
I've been looking all over and have found many examples but I cannot get any of them to work.
What I want to accomplish is to be able to create a pivot query and use it in the "Query Builder" when designing a report for the reporting services, so that I can have a table that mimicks a "Matrix" - so that I can have multiple value columns.
View 3 Replies
View Related
Nov 7, 2006
hi ,
is it possible to do a pivot , where the number of columns is dynamic...i.e
i dont know how many rows will be selected , and i want to pivot them and insert into
a new (temp/tabletype)table...obv i dont know how many columns i need....
somethin like the example of books online pasted below , consider here that i need data for
all employees (distinct empid) , then pivot it, for that i'll need 'select distinct empid
from emp' in the pivot syntax 'FOR EmployeeID IN' .
pls tell me if such thing is possible or there is a turnaround for my problem...
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID
View 8 Replies
View Related
Dec 4, 2007
I have a table with following structure:
ProductID OrderType Value
1001 BaseSales 2000
1001 Incremental 1000
1001 TotalSales 3000
1002 BaseSales 2002
1002 Incremental 1003
1002 TotalSales 3005
I would like to get the data in following format:
ProductID BaseSales Incremental TotalSales
1001 2000 1000 3000
1002 2002 1003 3005
Thanks for any help
Regards
Josh
View 1 Replies
View Related
Aug 21, 2007
Hello,
Is it possible to create a named query in the DSV that is the result of a pivot (e.g. cross tab?). The number of columns as a result of the pivot are based the number of records in one of the driving tables - in other words, it is not fixed.
View 1 Replies
View Related
Feb 15, 2014
I have below table and within same query i need pivot and unpivot.
create table #temp(name1 varchar(10),name2 varchar(10),name3 varchar(10),month date,emp1 int,emp2 int,emp3 int,emp4 int)
insert into #temp values ('a','b','c','1-1-2013',1,2,3,4)
insert into #temp values ('a','b','c','1-2-2013',11,20,30,40)
insert into #temp values ('a','c','c','1-1-2013',22,30,80,40)
insert into #temp values ('a','c','c','1-2-2013',28,34,39,30)
select * from #temp
Now i need output in below format
name1,name2,name3,Emp,jan-13,feb-13
a,b,c,emp1,1,11
a,b,c,emp2,2,20
a,b,c,emp3,3,30
a,b,c,emp4,4,40
a,c,c,emp1,22,28
a,c,c,emp2,30,34
a,c,c,emp3,80,39
a,c,c,emp4,40,30
View 4 Replies
View Related
Jan 18, 2006
Can someone help me parsing this ms-access PIVOT sql-statement to ams-sql-server sql-statement?Many thanks in advanceTRANSFORM Count(KlantenStops.id) AS AantalVanidSELECT KlantenStops.Uitvoerder, KlantenStops.KlantFROM KlantenStopsGROUP BY KlantenStops.Uitvoerder, KlantenStops.KlantPIVOT DatePart("m",leverdatum,1,0) In("1","2","3","4","5","6","7","8","9","10","11","12");
View 3 Replies
View Related
Sep 27, 2007
Hey all,
i have a question for all the SQL Gurus out there. I have tried to think of a way around, it, but i cannot work it out.
I have a set of data: Samples Below:
Item Warehouse FOR1 FOR2 FOR3 FOR4 FOR5 FOR6 FOR7 FOR8 FOR9 FOR10 FOR11 FOR12 FOR13 FOR14
01-0001 010 329 329 335 343 317 331 328 331 31
I have written a Query to Pivot this data like below:
SELECT WAREHOUSE,ITEM, QTY
FROM
(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10,
for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21,
for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p
UNPIVOT
(QTY FOR tbldmsForecasttoMovex IN (FOR1,FOR2,for3,for4,for5,for6,for7,
for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19,
for20,for21,for22,for23,for24))AS unpvt
Warehouse Item Qty
010 01-0001 329
010 01-0001 329
010 01-0001 335
010 01-0001 343
010 01-0001 317
010 01-0001 331
010 01-0001 328
010 01-0001 331
010 01-0001 315
010 01-0001 344
010 01-0001 334
010 01-0001 321
010 01-0001 327
010 01-0001 328
010 01-0001 332
010 01-0001 342
010 01-0001 316
010 01-0001 330
010 01-0001 330
010 01-0001 331
010 01-0001 315
010 01-0001 343
010 01-0001 333
010 01-0001 322
I would like to add some more code to the query, so for each FOR% column,
i can put a numeric value in it. The value will be the numbers ,1 - 24 . One for each line as this represents Months Forward.
Example:
Warehouse Item Qty Month
010 01-0001 329 1
010 01-0001 329 2
010 01-0001 335 3
010 01-0001 343 4
010 01-0001 317 5
010 01-0001 331 6
010 01-0001 328 7
010 01-0001 331 8
010 01-0001 315 9
010 01-0001 344 10
010 01-0001 334 11
010 01-0001 321 12
010 01-0001 327 13
010 01-0001 328 14
010 01-0001 332 15
010 01-0001 342 16
010 01-0001 316 17
010 01-0001 330 18
010 01-0001 330 19
010 01-0001 331 20
010 01-0001 315 21
010 01-0001 343 22
010 01-0001 333 23
010 01-0001 322 24
Does anyone know how i can do this?
Many Thnank
Scotty
View 5 Replies
View Related
Sep 2, 2015
Is there any restrictions in the number of rows that will be returned when doing a query in PowerBI? I have a query which should return over 1 million rows but the in PowerBI I only seem to get around 57000.
View 3 Replies
View Related
May 18, 2015
i have a table like below,
CREATE TABLE #ATTTABLE
(
Name VARCHAR(20),
AttDate DATE,
PresntTime TIME
[code]....
and then i pivot table by date as column wise using the below query and also displays total time by rowswise
SELECT t1.*, t2.Total
FROM (
SELECT Â name,[2015-08-01],[2015-08-02]
FROM (
SELECT Â name, AttDate,PresentTimeÂ
[code]....
now what i need is to display sum of time at last row as well, means total time of against date
View 16 Replies
View Related
May 19, 2006
Hi all,
In MyDatabase, I have a TABLE dbo.LabData created by the following SQLQuery.sql:
USE MyDatabase
GO
CREATE TABLE dbo.LabResults
(SampleID int PRIMARY KEY NOT NULL,
SampleName varchar(25) NOT NULL,
AnalyteName varchar(25) NOT NULL,
Concentration decimal(6.2) NULL)
GO
--Inserting data into a table
INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)
VALUES (1, 'MW2', 'Acetone', 1.00)
INSERT €¦ ) VALUES (2, 'MW2', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (3, 'MW2', 'Trichloroethene', 20.00)
INSERT €¦ ) VALUES (4, 'MW2', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (5, 'MW2', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (6, 'MW6S', 'Acetone', 1.00)
INSERT €¦ ) VALUES (7, 'MW6S', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (8, 'MW6S', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (9, 'MW6S', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (10, 'MW6S', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (11, 'MW7', 'Acetone', 1.00)
INSERT €¦ ) VALUES (12, 'MW7', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (13, 'MW7', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (14, 'MW7', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (15, 'MW7', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (16, 'TripBlank', 'Acetone', 1.00)
INSERT €¦ ) VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (19, 'TripBlank', 'Chloroform', 0.76)
INSERT €¦ ) VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)
GO
A desired Pivot Table is like:
MW2 MW6S MW7 TripBlank
Acetone 1.00 1.00 1.00 1.00
Dichloroethene 1.00 1.00 1.00 1.00
Trichloroethene 20.00 1.00 1.00 1.00
Chloroform 1.00 1.00 1.00 0.76
Methylene Chloride 1.00 1.00 1.00 0.51
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I write the following SQLQuery.sql code for creating a Pivot Table from the Table dbo.LabData by using the PIVOT operator:
USE MyDatabase
GO
USE TABLE dbo.LabData
GO
SELECT AnalyteName, [1] AS MW2, AS MW6S, [11] AS MW7, [16] AS TripBlank
FROM
(SELECT SampleName, AnalyteName, Concentration
FROM dbo.LabData) p
PIVOT
(
SUM (Concentration)
FOR AnalyteName IN ([1], , [11], [16])
) AS pvt
ORDER BY SampleName
GO
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the above-mentioned code and I got the following error messages:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TABLE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AnalyteName'.
I do not know what is wrong in the code statements of my SQLQuery.sql. Please help and advise me how to make it right and work for me.
Thanks in advance,
Scott Chang
View 6 Replies
View Related
Jul 8, 2015
I have two data tables:
1) Production data with column headers: Key, Facility, Line, Time, Output
2) Costs data with column headers: Key, Site, Cost Center, Time, Cost
The tables have a common key named obviously as Key. The data looks like this:
Key
Facility
Line
Time
Output
Alpha
I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.
View 5 Replies
View Related
Oct 13, 2015
Can I force the following measure to be visible for all rows in a pivot table?
Sales Special Visibility:=IF(
  HASONEVALUE(dimSalesCompanies[SalesCompany])
  ;IF(
    VALUES(dimSalesCompanies[SalesCompany]) = "Sales"
    ;CALCULATE([Sales];ALL(dimSalesCompanies[SalesCompany]))
    ;[Sales]
  )
  ;BLANK()
)
FYI, I also have other measures as well in the pivot table that I don't want to affect.
View 3 Replies
View Related
Jan 20, 2005
I have information on clothes in a table that I want to select out to a result set in a different structure - I suspect that this will include some kind of pivot (or cross-join?) but as I've never done this before I'd appreciate any kind of help possible.
Current structure is:
Colour Size Quantity
-----------------------
Red 10 100
Red 12 200
Red 14 300
Blue 10 400
Blue 12 500
Blue 14 600
Green 10 700
Green 12 800
Green 14 900
Green 16 1000
I want to produce this result set:
Colour Size10 Size12 Size14 Size16
-------------------------------------
Red 100 200 300 0
Blue 400 500 600 0
Green 700 800 900 1000
There could be any number of sizes or colours.
Is this possible? Can anyone give me any pointers?
Thanks in advance
greg
View 8 Replies
View Related
Mar 26, 2015
I currently have data stored in a temporary table and I would like to transpose the data into a better format. I would like for the query to be dynamic since one of the tables currently has over 500 columns.
The attached file provides an example of the table structure along with sample data. Below the first set of data is the desired final format.
View 2 Replies
View Related
Apr 13, 2015
I have created a crosstab query using the Pivot statement that returns the expected results. The results look similar to the sample below:
ItemKey Description Aflatoxin Coliform Bacteria E_Coli Fumonisin Melamine Moisture Mold Salmonella Vomitoxin (DON) Yeast
1000 Item1000 1 0 0 1 0 1 0 1 1 0
1024 Item1024 1 0 0 1 0 1 0 1 1 0
135 Item135 1 0 0 1 0 1 0 1 1 0
107 Item107 0 0 0 0 0 1 0 1 1 0
106 Item106 1 0 0 1 0 1 0 1 1 0
I'm using this statement to create the result set:
SELECT ItemKey, Description, Aflatoxin, [Coliform Bacteria], [E_Coli],[Fumonisin],
Melamine,Moisture, Mold, Salmonella, [Vomitoxin (DON)], Yeast
FROM
(SELECT tblInventory.ItemKey, tblInventory.Description,
jctProductClassificationRequiredTest.ProductTestClassID, tlbTestType.TestDescription
[Code] .....
Instead of doing a Count for the Pivot (the count will always be either 0 or 1 due to the design of the table being used), I would like to return an "X" for those records with a count of 1, and return a blank (otherwise null) for those records with a count of 0. So, the result set would look like:
ItemKey Description Aflatoxin Coliform Bacteria E_Coli Fumonisin Melamine Moisture Mold Salmonella Vomitoxin (DON) Yeast
1000 Item1000 X X X X X
1024 Item1024 X X X X X
135 Item135 X X X X X
107 Item107 X X X
106 Item106 X X X X X
I tried using a Case statement within the PIVOT portion, but I either did it incorrectly or it's not possible to do use a Case within the Pivot. Can I easily accomplish this?
View 6 Replies
View Related
Sep 1, 2015
I have been given a request by a business analyst to update the text 'old' to 'new' within the column names / measure names and associated calculations within a PowerPivot model. There are hundred of columns / measures / calculations, etc. which need to be renamed.
Is there any way of updating these changes to the model other than making these changes manually or is there some way of doing the following type of operation in PowerPivot; -
UPDATE tblColumnNames SET Column_name, etc REPLACE ('old','new', all columns),('old','new', all measures),('old','new', all calculations)
FROM
tblColumnNames
View 2 Replies
View Related
Apr 16, 2008
I have a pivot table query I am running and wanted to find out if there was a way to pull in the dates like getdate() - 12 months, getdate() - 11 months, etc. instead of hard coding the dates.
Here is my query
SELECT Client, [4/1/2007 12:00:00 AM] AS Month1, [5/1/2007 12:00:00 AM] AS Month2, [6/1/2007 12:00:00 AM] AS Month3, [7/1/2007 12:00:00 AM] AS Month4,
[8/1/2007 12:00:00 AM] AS Month5, [9/1/2007 12:00:00 AM] AS Month6, [10/1/2007 12:00:00 AM] AS Month7, [11/1/2007 12:00:00 AM] AS Month8,
[12/1/2007 12:00:00 AM] AS Month9, [1/1/2008 12:00:00 AM] AS Month10, [2/1/2008 12:00:00 AM] AS Month11, [3/1/2008 12:00:00 AM] AS Month12,
[4/1/2008 12:00:00 AM] AS Month13, Engineer
FROM (SELECT Client, DollarsBilled, SlipDates, Engineer
FROM dbo.MonthlyClientBillables) p PIVOT (SUM(DollarsBilled) FOR SlipDates IN ([4/1/2007 12:00:00 AM], [5/1/2007 12:00:00 AM],
[6/1/2007 12:00:00 AM], [7/1/2007 12:00:00 AM], [8/1/2007 12:00:00 AM], [9/1/2007 12:00:00 AM], [10/1/2007 12:00:00 AM], [11/1/2007 12:00:00 AM],
[12/1/2007 12:00:00 AM], [1/1/2008 12:00:00 AM], [2/1/2008 12:00:00 AM], [3/1/2008 12:00:00 AM], [4/1/2008 12:00:00 AM])) AS pvt
View 31 Replies
View Related
Jul 24, 2015
I have a query which I want to convert It PIVOT query
SELECTÂ Â Â Â Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName, SUM(CashSalesDetail.Qty) AS QtyFROMÂ Â Â Â Â Â Â Â CashSalesDetail INNER JOINÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â CashSales ON CashSalesDetail.CSNo = CashSales.CSNo INNER JOINÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Parties ON CashSales.PartyID = Parties.PartyIDWHEREÂ Â Â Â (CashSales.TransDate >= CONVERT(DATETIME, '2014-07-01 00:00:00', 102)) AND (CashSales.TransDate <= CONVERT(DATETIME, '2015-06-30 00:00:00', 102))GROUP BY Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName
following is my requirement after summing up qty of each area
ProductName     area a        area b      area c
abc                         10               0              Â
20
def                          1                Â
4Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2
ghi                          5                Â
3Â Â Â Â Â Â Â Â Â Â Â Â Â Â 10
jkl                            7               Â
15Â Â Â Â Â Â Â Â Â Â Â Â Â 3
Note: numeric values are Quantity of each product in each area
View 15 Replies
View Related
Oct 14, 2015
I have a simple pivot table (screenshot below) that has two variables on it: one for entry year and another for 6 month time intervals. I have very simple DAX functions that count rows to determine the population N (denominator), the number of records in the time intervals (numerator) and the simple percent of those two numbers.
The problem that I am having is that the function for the population N is not overriding the time interval on the pivot table when I use an ALL function to do so. I use ALL in other very simple pivot tables to do the same thing and it works fine.
The formula for all three are below, but the one that is the issue is the population N formula. Why ALL would not work, any other way to override the time period variable on the pivot table.
Population N (denominator):
=CALCULATE(COUNTROWS(analyticJudConsist),ALL(analyticJudConsist[CurrentTimeInCare1]))
Records in time interval (numerator):
=COUNTROWS(analyticJudConsist)
Percent:
=[countrows]/[denominatorCare]
View 13 Replies
View Related
Aug 17, 2015
How can I apply "Min" formula under a "new measure" (calculated field) within a pivot table under Power pivot 2010?Can see that neither does it allow me to apply "min" formula directly "formula box" nor could find any other option.Intent formula: "=Min(1,sum(a:b))" this isn't allowed so all I can do is "=sum(a:b)".
View 3 Replies
View Related
Mar 11, 2015
I have simple pivot table (below screenshot with info redacted) that displays a population number ("N" below), this is the denominator, a cumulative numerator number (below "#") and a simple cumulative percent that just divides the numerator by the denominator. It cumulates from top to bottom. The numerator and percent are cumulative using the below functions. There are two problems with the numerator and percent:
1. When there is not a number for the numerator, there is no value displayed for both the numerator and the percent..There should be a zero displayed for both values.
2. When there has been a prior number for the numerator and percent (for a prior month interval) but there is no number for the numerator in the current month interval, the prior month number and percent are not displayed in the current month interval--see the 3rd yellow line, this should display "3" and "16.7%" from the second yellow line.Here is the formula for the numerator:
=CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory])))
Here is the formula for the percent:
=(CALCULATE(countrows(s1Perm1),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))))/(CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit],s1Perm1[ExitMonthCategory])))
View 24 Replies
View Related
Aug 6, 2015
I have below script
CREATE TABLE dbo.TestPivot(
CollectionDate DATETIME,
Products VARCHAR(40),
ItemCount INT
)
INSERT INTO dbo.TestPivot
SELECT '4/1/2015','Benz' , 20
[Code] ....
-- Original Output
ProductsApr 2015May 2015Jun 2015
Benz10-800NULL
Toyota5NULL-180
****Required output where ever we have negative values we need to display message Invalid out put message for those negative rows
ProductsApr 2015May 2015 Jun 2015
Benz10Invalid NULL
Toyota5NULL Invalid
View 2 Replies
View Related
Oct 31, 2014
I have a query which returns number of columns using pivot ( rows into columns -- dynamic sql pivot columns). Since it is dynamic pivot, how can I bind this returned values into report builder matrix reports.
Look at this example :
First time query returns
StudentId | Col1 | Col2 | Col3
Second time query returns
StudentId | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 ...
How to bind this query into report builder 3.0 reports?
View 13 Replies
View Related
Jun 17, 2015
I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". Â The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". Â I will post the definitions from another effort below.
CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,
[code]....
SQL Server 2008 R2.
View 3 Replies
View Related