CROSSTAB Query In Defined Order
Taken from another post, the sql listed further below creates/uses the input table
ID ITEM
1 a
1 b
2 a
2 c
2 d
3 b
3 z
and with the SELECT statement returns
ID items
1 a, b
2 a, c, d
3 b, z
The items are sorted in alphab. order (using Min) but I HAVE A 3RD FIELD IN THE INPUT TABLE THAT ORDERS THE ITEMS SO INPUT TABLE NOW IS:
ID ITEM ORDER
1 a 32
1 b 15
2 a 32
2 c 11
2 d 45
3 b 15
3 z 99
SO THE OUTPUT TABLE LOOKS SHOULD BE (items with smaller Order no are added first):
ID items
1 b, a
2 c, a, d
3 b, z
ANY IDEAS on HOW TO CHANGE THE FOLLOWING SQL TO DO THAT?
I've tried ordering the input table on ID, ORDER fields and then using the SELECT statement listed below but it adds the items in alphab. order which I guess it should do since it's using Min. I also tried using the ORDER field in the joins instead of the ITEM field but still didn't work. ANY IDEAS???
CREATE TABLE dbo.table00 (
id INT NOT NULL
, item CHAR(1) NOT NULL
)
INSERT INTO dbo.table00 (id, item)
SELECT 1, 'a'
UNION ALL SELECT 1, 'b'
UNION ALL SELECT 2, 'a'
UNION ALL SELECT 2, 'c'
UNION ALL SELECT 2, 'd'
UNION ALL SELECT 3, 'b'
UNION ALL SELECT 3, 'z'
SELECT a.id, Min(a.item)
+ Coalesce(', ' + Min(b.item), '')
+ Coalesce(', ' + Min(c.item), '')
FROM dbo.table00 AS a
LEFT JOIN dbo.table00 AS b
ON (b.id = a.id
AND a.item < b.item)
LEFT JOIN dbo.table00 AS c
ON (c.id = a.id
AND b.item < c.item)
GROUP BY a.id
ORDER BY a.id
View Complete Forum Thread with Replies
Related Forum Messages:
How To Use ORDER BY Clause In An SELECT DISTINCT Sql Query When AS SINGLECOLUMN Is Defined?
Hi, I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow... Can anyone help? Thanks in advance Gemma
View Replies !
User Defined Functions, Passing Parameters From Another Udf's Results (end Result=Crosstab)
Hi All:I've read a whole slew of posts about creating temp tables using storedproceedures to get the crosstab ability, but I'm wondering if, for thisspecific case, there might be a more efficient way.What makes this question different from the others that I've read isthat I'm using user defined functions, not tables. I actually thinkthat I've got the crosstab thing down, it's just passing the parameterto the 2nd udf that's messing me up.I've got a people table and an address table. Each person can havemultiple addresses. I need to create a dataset that has in each rowthe name of the person, the first address, any second address, and anythird address. I only need to show the first 3, so if there's 100, Ican just ignore the rest.I created a user defined function to return the 1st, 2nd, or 3rdaddress for a given person.udf_ReturnAddress(PersonID,MatchNumber)Another user defined function returns the people that I'm looking for(potential duplicates for a person in this case).udf_ReturnPossibleDupsForAPerson(PersonID)SELECTMain.FoundPersonID, Main.LastName, A1.Street, A2.Street,A3.StreetFROMudf(ReturnPossibleDupsForAPerson(@PersonID) MainTableCROSS JOIN(SELECT Street1 FROMudf_ReturnAddress(Main.FoundPersonID,1) Adr1) A1CROSS JOIN(SELECT Street1 FROMudf_ReturnAddress(Main.FoundPersonID,2) Adr2) A2CROSS JOIN(SELECT Street1 FROMudf_ReturnAddress(Main.FoundPersonID,3) Add3) A3If, for the first parameter for the return address function, I replaceMain.FoundPersonID with the ID of a person, it works just fine. Iobviously don't want a static id as a parameter - I want to use the IDof the person that the first udf found. Leaving the variableMainTable.PersonID there causes an error in the query designer though.I get "Error in list of function arguments: '.' not recognized.So maybe my problem is that I just don't know how to pass the id of theperson that's found by the first UDF as the parameter of the functionto find the found person's 3 addresses.Any guidance would be greatly appreciated!ThanksKen
View Replies !
Scan Order Of User Defined Aggregates
Hi all! I have created (in CLR) a user defined aggregate. The scan order of this aggregate is important, because it return different results for different orders. When i use it with a single group (using order by and where) is working fine. For example select id, dbo.cmp(myclolumn) as myres from (select top 100 percent * from mytable order by id,clmdate) where id=10 group by id This works correctly. Now lets expand it by removing where id=10 clause select id, dbo.cmp(myclolumn) as myres from (select top 100 percent * from mytable order by id,clmdate) group by id I get slightly different results from what the right result must be. Any idea?
View Replies !
Crosstab Query
Can someone show me how to write a query to convert this:Year, Account, Qtr1, Qtr2, Qtr3, Qtr42004, 12345, 100, 200, 300, 4002005, 23456, 200, 300, 400, 500to this:Year, Account, Quarters, Amount2004, 12345, Qtr1, 1002004, 12345, Qtr2, 2002004, 12345, Qtr3, 3002004, 12345, Qtr4, 4002005, 23456, Qtr1, 2002005, 23456, Qtr2, 3002005, 23456, Qtr3, 4002005, 23456, Qtr4, 500So far I've only found examples which convert values to columns but notfor columns to values. TIA... AL
View Replies !
Crosstab Query
I have two tables Bill and Location.Bill(location_id int,prod_period datetime,consumption float,demand float)Location(location_id int,location_name varchar(45))I want to create a stored procedure that takes a parameter of @year. Ibasically want the procedure to return results that show locationswhere consumption and/or demand IS NULL or 0 for each month throughouta given year. I would like my results to look something like this:location_id year Jan Feb Mar Apr May Jun Jul Aug Sept OctNov Dec1 2005 0 0 02 2005 0 0 0 03 2005 0 0If data does exist for consumption or demand, I would like to show it,however I am really interested in the locations that have no dataassociated with them.Any ideas of how I can accomplish this?
View Replies !
Crosstab Query
Is there a way to write Crosstab query in SQL 7.0. I have a Table which has partner, usernames and the city they are from, apart from other fields. I have another table which tracks the user activity on a day by number of visits to the web site. What i want is like this :- Date Total Visits Atlanta Connecticut .... .... .... 03/01/2001 5025 567 324 .... .... .... 03/02/2001 6789 423 146 .... .... .... ... ... ... ... .... .... .... I.E. I wan't the city names to appear as columns. The main catch here is that this data is for a given partner. So the city names could be different for different users from different partners. i.e. city names have to be generated on the fly by looking at the partner table and then by getting all its users and seeing what all are the cities involved. Any help would be appreciated. Thanks Sumit.
View Replies !
Crosstab Query
Hello everyone, I have a tabel which looks like the follows: CODE Type Number 1 account 20 1 empl 3 2 account 15 2 empl 6 3 account 32 3 empl 7 I need to show the results like CODE Account Empl 1 20 3 2 15 6 3 32 7 I've tried different attempts but so far I always get 2 rows for each code,where one column shows a NULL value. How do I have to do this ?
View Replies !
Crosstab SQL Query
I am trıed to do crosstab in sql server 2005 but I failed. I look all document on the net but they are all static means the column that are crosed are avaliable. but ın my query I do not know the count of the columns... Please help me... This the my query... Yağmur Duman Türk Masajı 11 Mehtap . Türk Masajı 10 Mehtap . Japon Masajı 3 Yağmur Duman TENİS KORTU1 SAAT 1 Mehtap . TENİS KORTU 1 SAAT 4 Yağmur Duman Kortu Kirası 1 saat 1 Mehtap . Kortu Kirası 1 saat 2 Administrator . BALI MASAJI 1 Yağmur Duman BALI MASAJI 10 Mehtap . BALI MASAJI 11 Mehtap . 6 EL MASAJI 1 Mehtap . COUPLE MASAJ 1 Yağmur Duman PILATES SINGLE 1 Yağmur Duman CİLT BAKIMI 3 Mehtap . CİLT BAKIMI 4 Mehtap . Çikolata Masajı 2 Yağmur Duman BAL BAKIMI 1 Mehtap . BAL BAKIMI 4 Yağmur Duman 4 EL MASAJI 1 Mehtap . 4 EL MASAJI 2 Yağmur Duman YOSUN BAKIMI 1 Mehtap . MASA TENİSİ 1 Mehtap . HAMAM 1 Mehtap . KESE & KÖPÜK 1 Yağmur Duman TRIO 1 Mehtap . TRIO 1 Özge Öztürk TRIO 1 Özge Öztürk 4 EL MASAJI 16 Ozan Çicek ISVEC MASAJI 1 Özge Öztürk ISVEC MASAJI 3 Ozan Çicek 4 EL MASAJI 5 Ozan Çicek BAL BAKIMI 3 Özge Öztürk BAL BAKIMI 10 Özge Öztürk THAI MASAJI 1 Ozan Çicek Çikolata Masajı 24 Özge Öztürk Çikolata Masajı 12 Ozan Çicek SICAK TAŞ 5 Özge Öztürk SICAK TAŞ 14 Özge Öztürk CİLT BAKIMI 17 Ozan Çicek SHATSU MASAJ 1 Özge Öztürk PILATES SINGLE 16 Özge Öztürk SHATSU MASAJ 4 Ozan Çicek CİLT BAKIMI 3 Ozan Çicek PILATES SINGLE 2 Özge Öztürk COUPLE MASAJ 10 Ozan Çicek COUPLE MASAJ 6 Özge Öztürk 6 EL MASAJI 16 Ozan Çicek 6 EL MASAJI 18 Ozan Çicek BALI MASAJI 44 Özge Öztürk BALI MASAJI 59 Ozan Çicek Kortu Kirası 1 saat 5 Özge Öztürk Kortu Kirası 1 saat 5 Ozan Çicek TENİS KORTU 1 SAAT 12 Özge Öztürk TENİS KORTU 1 SAAT 5 Ozan Çicek Japon Masajı 41 Özge Öztürk Japon Masajı 26 Ozan Çicek Türk Masajı 38 Özge Öztürk Türk Masajı 17 Yeşim Yıldırım Türk Masajı 32 Yeşim Yıldırım Japon Masajı 35 Yeşim Yıldırım TENİS KORTU 1 SAAT 3 Yeşim Yıldırım Kortu Kirası 1 saat 4 Yeşim Yıldırım BALI MASAJI 101 Yeşim Yıldırım 6 EL MASAJI 20 Yeşim Yıldırım COUPLE MASAJ 11 Yeşim Yıldırım SHATSU MASAJ 4 Yeşim Yıldırım PILATES SINGLE 3 Yeşim Yıldırım CİLT BAKIMI 10 Yeşim Yıldırım THAI MASAJI 1 Yeşim Yıldırım Çikolata Masajı 18 Yeşim Yıldırım SICAK TAŞ 14 Yeşim Yıldırım BAL BAKIMI 4 Yeşim Yıldırım 4 EL MASAJI 9 Yeşim Yıldırım HAMAM 1 Yeşim Yıldırım KESE & KÖPÜK 1 Yeşim Yıldırım SAUNA TEK GİRİŞ 3 Yeşim Yıldırım YOSUN BAKIMI 1 Yeşim Yıldırım TRIO 2 Funda Güngörür TRIO 3 Funda Güngörür 4 EL MASAJI 5 Funda Güngörür BAL BAKIMI 8 Funda Güngörür THAI MASAJI 4 Funda Güngörür Çikolata Masajı 10 Funda Güngörür SICAK TAŞ 14 Funda Güngörür SHATSU MASAJ 5 Funda Güngörür CİLT BAKIMI 18 Funda Güngörür PILATES SINGLE 9 Funda Güngörür COUPLE MASAJ 11 Funda Güngörür 6 EL MASAJI 15 Funda Güngörür BALI MASAJI 60 Funda Güngörür Kortu Kirası 1 saat 4 Funda Güngörür TENİS KORTU 1 SAAT 6 Funda Güngörür Japon Masajı 41 Funda Güngörür Türk Masajı 58 Ayşegül Duman Türk Masajı 22 Ayşegül Duman TENİS KORTU 1 SAAT 1 Ayşegül Duman Japon Masajı 17 Ayşegül Duman Kortu Kirası 1 saat 1 Ayşegül Duman BALI MASAJI 53 Ayşegül Duman 6 EL MASAJI 2 Ayşegül Duman COUPLE MASAJ 7 Ayşegül Duman SHATSU MASAJ 7 Ayşegül Duman PILATES SINGLE 9 Ayşegül Duman SICAK TAŞ 7 Ayşegül Duman CİLT BAKIMI 10 Ayşegül Duman Çikolata Masajı 5 Ayşegül Duman THAI MASAJI 1 Ayşegül Duman BAL BAKIMI 6 Ayşegül Duman ISVEC MASAJI 2 Ayşegül Duman TRIO 3 Ayşegül Duman KESE & KÖPÜK 1 Ayşegül Duman 4 EL MASAJI 15 Mert Ilgın HAMAM 2 Mert Ilgın YOSUN BAKIMI 2 Mert Ilgın SAUNA TEK GİRİŞ 1 Mert Ilgın MASA TENİSİ 1 Mert Ilgın 4 EL MASAJI 11 Mert Ilgın KESE & KÖPÜK 1 Mert Ilgın TRIO 2 Mert Ilgın ISVEC MASAJI 1 Mert Ilgın BAL BAKIMI 1 Mert Ilgın Çikolata Masajı 6 Mert Ilgın THAI MASAJI 1 Mert Ilgın SICAK TAŞ 11 Mert Ilgın CİLT BAKIMI 7 Mert Ilgın SHATSU MASAJ 3 Mert Ilgın PILATES SINGLE 6 Mert Ilgın 6 EL MASAJI 9 Mert Ilgın COUPLE MASAJ 11 Mert Ilgın BALI MASAJI 28 Mert Ilgın Kortu Kirası 1 saat 2 Mert Ilgın TENİS KORTU 1 SAAT 5 Mert Ilgın Japon Masajı 6 Mert Ilgın Türk Masajı 11 Aslı Öztürk Türk Masajı 14 Aslı Öztürk Japon Masajı 2 Aslı Öztürk TENİS KORTU 1 SAAT 1 Aslı Öztürk BALI MASAJI 31 Aslı Öztürk 6 EL MASAJI 2 Aslı Öztürk COUPLE MASAJ 1 Aslı Öztürk PILATES SINGLE 10 Aslı Öztürk CİLT BAKIMI 5 Aslı Öztürk SICAK TAŞ 4 Aslı Öztürk Çikolata Masajı 1 Aslı Öztürk BAL BAKIMI 2 Aslı Öztürk ISVEC MASAJI 1 Aslı Öztürk TRIO 3 Aslı Öztürk 4 EL MASAJI 2 Aslı Öztürk KESE & KÖPÜK 2 Ahmet Yılmaz TRIO 2 Ahmet Yılmaz Çikolata Masajı 2 Ahmet Yılmaz CİLT BAKIMI 5 Ahmet Yılmaz SICAK TAŞ 4 Ahmet Yılmaz PILATES SINGLE 20 Ahmet Yılmaz SHATSU MASAJ 1 Ahmet Yılmaz COUPLE MASAJ 2 Ahmet Yılmaz BALI MASAJI 22 Ahmet Yılmaz Japon Masajı 2 Ahmet Yılmaz Kortu Kirası 1 saat 1 Ahmet Yılmaz Türk Masajı 10 Ceren Yıldızdoğan Türk Masajı 4 Ceren Yıldızdoğan Japon Masajı 3 Ceren Yıldızdoğan BALI MASAJI 26 Ceren Yıldızdoğan 6 EL MASAJI 4 Ceren Yıldızdoğan SHATSU MASAJ 5 Ceren Yıldızdoğan PILATES SINGLE 12 Ceren Yıldızdoğan SICAK TAŞ 11 Ceren Yıldızdoğan CİLT BAKIMI 3 Ceren Yıldızdoğan Çikolata Masajı 3 Ceren Yıldızdoğan THAI MASAJI 1 this the query result but I want to do this Türk Masajı TENİS KORTU1 SAAT Kortu Kirası 1 saat ............ Yağmur Duman 11 1 1 ...... can you any idea about my problem
View Replies !
Need Help With Crosstab Query
I cant figure out what is going wrong here. I created this query to be able to show values across periods , and put it inside of a table instead of a matrix, for easier totals and subtotals. but for some reason my subquery is not pullin up the right amounts for "Goal Amount". The fields i have labeled as Period_1 , period_2 and so on work just fine. But the goals are not coming up with the same number as in the Sub query, if i ran it by its self. I dont know if maybe its the join, or what. but for some reason the numbers are way high then they should be. am i doing something wrong. Please let me know if i need to give more information. This is what the output looks like now: 0.0000 0.0000 0.0000 4516182.0000 5569957.8000 2559169.8000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 3915126.9500 3275922.5500 1598011.0000 4526861.2500 6880829.1000 3078265.6500 629010.4000 589697.2500 353818.3500 2218175.9000 1984683.7000 1517699.3000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 this is what it looks like in the subquery: 150539.4000 2008 1 80 12 150539.4000 2008 2 80 12 150539.4000 2008 3 80 12 39313.1500 2008 1 81 12 39313.1500 2008 2 81 12 39313.1500 2008 3 81 12 181074.4500 2008 1 82 12 181074.4500 2008 2 82 12 181074.4500 2008 3 82 12 116746.1000 2008 1 86 12 116746.1000 2008 2 86 12 116746.1000 2008 3 86 12 79900.5500 2008 1 87 12 79900.5500 2008 2 87 12 79900.5500 2008 3 87 12 the two numbers highlighted should have the same amounts. Code Snippet SELECT cusSales.Report_Level_Id AS Report_Level, cusSales.Customer_Sales_Summary_Year AS Year, Goals.Region_Key, Qry_Report_Level_Brand.Report_Level_Description, RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Region_Name, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 1 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_1, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 2 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_2, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 3 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_3, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 4 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_4, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 5 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_5, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 6 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_6, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 7 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_7, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 8 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_8, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 9 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_9, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 10 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_10, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 11 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_11, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 12 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_12, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 13 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_13, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 1 THEN Goal_Amount ELSE 0 END) AS GOAL_1, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 2 THEN Goal_Amount ELSE 0 END) AS GOAL_2, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 3 THEN Goal_Amount ELSE 0 END) AS GOAL_3, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 4 THEN Goal_Amount ELSE 0 END) AS GOAL_4, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 5 THEN Goal_Amount ELSE 0 END) AS GOAL_5, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 6 THEN Goal_Amount ELSE 0 END) AS GOAL_6, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 7 THEN Goal_Amount ELSE 0 END) AS GOAL_7, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 8 THEN Goal_Amount ELSE 0 END) AS GOAL_8, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 9 THEN Goal_Amount ELSE 0 END) AS GOAL_9, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 10 THEN Goal_Amount ELSE 0 END) AS GOAL_10, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 11 THEN Goal_Amount ELSE 0 END) AS GOAL_11, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 12 THEN Goal_Amount ELSE 0 END) AS GOAL_12, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 13 THEN Goal_Amount ELSE 0 END) AS GOAL_13 FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Customer INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Sales_Summary_Fiscal AS cusSales INNER JOIN Qry_Report_Level_Brand ON cusSales.Sub_Brand_Id = Qry_Report_Level_Brand.Sub_Brand_Id ON RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Customer.Customer_Code = cusSales.Customer_Code INNER JOIN RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension ON RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Customer.Territory_Code = RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Code FULL OUTER JOIN (SELECT SUM(cusSales.Customer_Sales_Summary_Amount) AS Goal_Amount, cusSales.Customer_Sales_Summary_Year AS Year, cusSales.Customer_Sales_Summary_Period AS Period, cusSales.Report_Level_Id, RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region.Region_Key FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Sales_Summary_Fiscal AS cusSales INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Customer AS Tbl_Territory_In_Customer_1 ON cusSales.Customer_Code = Tbl_Territory_In_Customer_1.Customer_Code INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region ON Tbl_Territory_In_Customer_1.Territory_Code = RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region.Territory_Code WHERE (cusSales.Customer_Sales_Summary_Year = 2008) AND (cusSales.Consolidated_Sales_Tables_Id = 8) and region_key=12 GROUP BY cusSales.Customer_Sales_Summary_Year, cusSales.Customer_Sales_Summary_Period, cusSales.Report_Level_Id, RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region.Region_Key) AS Goals ON RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Region_Key = Goals.Region_Key AND cusSales.Report_Level_Id = Goals.Report_Level_Id AND cusSales.Customer_Sales_Summary_Year = Goals.Year AND cusSales.Customer_Sales_Summary_Period = Goals.Period WHERE (cusSales.Customer_Sales_Summary_Year IN (2007, 2008)) AND (cusSales.Consolidated_Sales_Tables_Id = 6) AND (NOT (cusSales.Bill_Customer_Code IN ('11825', '11990', '11971'))) AND (cusSales.Report_Level_Id IN (78, 80, 81, 82, 86, 87)) AND (RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'BREEDER') AND (RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'OTHER') AND (RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'Vet - Corporate') AND (RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'export') AND (RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'Vet - Other') AND (RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Region_Key = 12) GROUP BY cusSales.Report_Level_Id, cusSales.Customer_Sales_Summary_Year, Goals.Region_Key, Qry_Report_Level_Brand.Report_Level_Description, RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Region_Name
View Replies !
CrossTab Query
Hi, Currently working on a Attendance System project . Iam storing the data in table against the employee code, date and status (basically a rowwise data). Would require a query which can generate a cross-tab display with Employee Codes (on X axis), Dates (on Y-Axis) and with the Attendance Status. The sample data for the same is as follows: EmpCode Att_Date Att_Status ------- -------- ---------- 001 01/01/2007 P 001 01/02/2007 A .. ... .. 001 01/31/2007 P 002 01/01/2007 P . . . . . . . . . Would require the output as.. EmpCode 01/01/2007 01/02/2007 ...... 01/31/2007 ------- ---------- ---------- ---------- 001 P A P 002 A P P . . . . . . . . . . . . Thanking you in anticipation. Jabez.
View Replies !
Crosstab Query
Crosstab concepts I m using Sql server 2000 version I want to create a simple crosstab… as like the follows My table is as like Date warehouse (WH) Delivery No 7/7/2006 10 10-1 7/7/2006 20 20-1 7/7/2006 20 20-2 7/7/2006 30 30-1 8/7/2006 10 10-2 8/7/2006 10 10-3 11/7/2006 30 30-2 11/7/2006 20 20-3 I want to make a report using crosstab in iReport….. I want report will be as like Date WH10 WH20 WH30 total delivery 7/7/2006 1 2 1 4 8/7/2006 2 0 0 2 11/7/2006 0 1 1 2 my questions are: i create a crosstab procedure and it is running but 01. I want to know the query. 02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table ) I want step by step instructions I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem Please help…… Thanks shohan
View Replies !
Crosstab Query
Crosstab concepts I m using Sql server 2000 version I want to create a simple crosstab… as like the follows My table is as like Date warehouse (WH) Delivery No 7/7/2006 10 10-1 7/7/2006 20 20-1 7/7/2006 20 20-2 7/7/2006 30 30-1 8/7/2006 10 10-2 8/7/2006 10 10-3 11/7/2006 30 30-2 11/7/2006 20 20-3 I want to make a report using crosstab in iReport….. I want report will be as like Date WH10 WH20 WH30 total delivery 7/7/2006 1 2 1 4 8/7/2006 2 0 0 2 11/7/2006 0 1 1 2 my questions are: i create a crosstab procedure and it is running but 01. I want to know the query. 02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table ) I want step by step instructions I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem Please help…… Thanks shohan
View Replies !
Crosstab Query
Crosstab concepts I m using Sql server 2000 version I want to create a simple crosstab… as like the follows My table is as like Date warehouse (WH) Delivery No 7/7/2006 10 10-1 7/7/2006 20 20-1 7/7/2006 20 20-2 7/7/2006 30 30-1 8/7/2006 10 10-2 8/7/2006 10 10-3 11/7/2006 30 30-2 11/7/2006 20 20-3 I want to make a report using crosstab in iReport….. I want report will be as like Date WH10 WH20 WH30 total delivery 7/7/2006 1 2 1 4 8/7/2006 2 0 0 2 11/7/2006 0 1 1 2 my questions are: i create a crosstab procedure and it is running but 01. I want to know the query. 02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table ) I want step by step instructions I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem Please help…… Thanks shohan
View Replies !
Crosstab Query
Crosstab concepts I m using Sql server 2000 version I want to create a simple crosstab… as like the follows My table is as like Date warehouse (WH) Delivery No 7/7/2006 10 10-1 7/7/2006 20 20-1 7/7/2006 20 20-2 7/7/2006 30 30-1 8/7/2006 10 10-2 8/7/2006 10 10-3 11/7/2006 30 30-2 11/7/2006 20 20-3 I want to make a report using crosstab in iReport….. I want report will be as like Date WH10 WH20 WH30 total delivery 7/7/2006 1 2 1 4 8/7/2006 2 0 0 2 11/7/2006 0 1 1 2 my questions are: i create a crosstab procedure and it is running but 01. I want to know the query. 02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table ) I want step by step instructions I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem Please help…… Thanks shohan
View Replies !
Crosstab Query
Crosstab concepts I m using Sql server 2000 version I want to create a simple crosstab… as like the follows My table is as like Date warehouse (WH) Delivery No 7/7/2006 10 10-1 7/7/2006 20 20-1 7/7/2006 20 20-2 7/7/2006 30 30-1 8/7/2006 10 10-2 8/7/2006 10 10-3 11/7/2006 30 30-2 11/7/2006 20 20-3 I want to make a report using crosstab in iReport….. I want report will be as like Date WH10 WH20 WH30 total delivery 7/7/2006 1 2 1 4 8/7/2006 2 0 0 2 11/7/2006 0 1 1 2 my questions are: i create a crosstab procedure and it is running but 01. I want to know the query. 02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table ) I want step by step instructions I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem Please help…… Thanks shohan
View Replies !
Calc In Crosstab Query
Hi all - I have completed a crosstab report - it is working fine... code here... select Lab_Values.Date_Drawn, SUM (case when Lab_Values.LabTypeID = 1 then value else 0 end) as 'TC', SUM (case when Lab_Values.LabTypeID = 2 then value else 0 end) as 'HDL', SUM (case when Lab_Values.LabTypeID = 3 then value else 0 end) as 'LDL', SUM (case when Lab_Values.LabTypeID = 4 then value else 0 end) as 'Trigs', SUM (case when Lab_Values.LabTypeID = 5 then value else 0 end) as 'AST', SUM (case when Lab_Values.LabTypeID = 6 then value else 0 end) as 'ALT' FROM Labs INNER JOIN Lab_Values ON Labs.LabTypeID = Lab_Values.LabTypeID WHERE (Lab_Values.PatientID = 330 AND Lab_Values.Deleted = 0) GROUP BY Lab_Values.Date_Drawn ORDER BY Lab_Values.Date_Drawn DESC Results - 2002-05-31 00:00:00.000233000110 2002-05-23 00:00:00.00000500000 2002-05-22 00:00:00.0000049000 2002-05-16 00:00:00.0002104575502515 2002-05-14 00:00:00.00010000000 2002-05-10 00:00:00.00055500000 2002-05-03 00:00:00.00023300000 2002-05-02 00:00:00.00024600000 2001-07-15 00:00:00.0001004575502515 2000-11-26 00:00:00.000065507500 the first column after the date is the TC, and the third after the date is the HDL... The question is - for each date, i would like to get the values of TC and HDL, do a divide and place the result into another column for display.... What am i missing??? thanks tony
View Replies !
Crosstab Query Problem
month wise production Format that I want item codenameJulyAugSepOctNovDecjan 1002pvc resin 3020115060140 3501SWR pipe566045801002020 I create crosstab procedure as follows create procedure up_CrossTab (@SelectStatement varchar(1000), @PivotColumn varchar(100), @Summary varchar(100), @GroupbyField varchar(100), @OtherColumns varchar(100) = Null) AS /* Inputs are any 1000 character or less valid SELECT sql statement, the name of the column to pivot (transform to rows), the instructions to summarize the data, the field you want to group on, and other fields returned as output. 1 */ set nocount on set ansi_warnings off declare @Values varchar(8000); set @Values = ''; set @OtherColumns= isNull(', ' + @OtherColumns,'') /* An 8000 varchar variable called @values is created to hold the [potentially filtered] values in the pivot column. @Values is initiated to an empty string. Then, a temporary table is created to hold each unique value. After the table is created, its rows are loaded into the variable @values. It's usefullness completed, the temporary table is destroyed. 2 */ create table #temp (Tempfield varchar(100)) insert into #temp exec ('select distinct convert(varchar(100),' + @PivotColumn + ') as Tempfield FROM (' + @SelectStatement + ') A') select @Values = @Values + ', ' + replace(replace(@Summary,'(','(CASE WHEN ' + @PivotColumn + '=''' + Tempfield + ''' THEN '),')[', ' END) as [' + Tempfield ) from #Temp order by Tempfield drop table #Temp /* Finally, a dynamic sql select statement is executed which takes the GroupByField, and OtherColumns, passed into the procedure, and each of the Values from the Pivot Column from the passed in SELECT statement . 3 */ exec ( 'select ' + @GroupbyField + @OtherColumns + @Values + ' from (' + @SelectStatement + ') A GROUP BY ' + @GroupbyField) set nocount off set ansi_warnings on GO And then my sql query is as like EXEC up_CrossTab 'SELECT ProdId, GrnDate,Quantity FROM inteacc..IcGrnD IcGrnD INNER JOIN inteacc..IcProduct IcProduct ON (IcGrnD.ProdId=IcProduct.ProdId) ', 'Year(GrnDate)', 'sum(Quantity)[]', 'ProdId' error occurring ambiguous column name ‘ProdId’ But when I compile this query EXEC up_CrossTab 'SELECT grnNo,GrnDate,Quantity FROM inteacc..IcGrnD IcGrnD INNER JOIN inteacc..IcProduct IcProduct ON (IcGrnD.ProdId=IcProduct.ProdId) ', 'Month(GrnDate)', 'sum(Quantity)[]','GrnNo' Output GrnNo12249 1220NullNull20 2Null20Null10 3NullNull300Null 4NullNull10Null I could not understand the error. What will I do to get the format I want? shohan
View Replies !
Classification Crosstab Query - 2000
Hello --I think this is the term for what I want (something that could be generatedin ACCESS using a pivot table, or, maybe Yukon).We have data for sales by sales people in sales regions. More than oneperson sells in a region.We want to display data as follows:salesperson's names----------- ----------- ----------- ----------- ----------- -----------region 1region 2 row/column values are sales amounts for person inthat regionregion 3We will add a WHERE clause for the period of time covered.I don't want to have to change the query if a new salesperson or new regionis added.Can this be done in SQL Server 2000, Analysis Services, OLAP, anywhere?Can someone direct me to examples of how to do this?Thanks for any direction.Larry Mehl
View Replies !
Matrix / Crosstab Query In SQL Server
How can I produce a Matrix/Crosstab table using SQL Query Analyzer/SQL Server? I have the following tables: people(unique_identifier, employee_number, known_as_and_surname ... ) exitin(unique_identifier, parent_identifier, category, score ... ) Relationship: exitint.parent_identifier = people.unique_identifier which is a one-many relationship (one employee can have many records in exitint) example data: please see attachment (cant make a pretty table appear in the post...) The zip file has an excel spreadsheet with what I have and want to produce as an example. I want to make this in SQL (SQL Server 2000 I think) I have been looking all over the internet for a solution but I just cant get one to work! :eek: Thank you all in advance! <3
View Replies !
How Do I Use The Crosstab Query In Sql Server Mobile Edition
I can use crosstab query(intersection table) in sql server. for example : CREATE TABLE [Test] ( [id] [int] IDENTITY (1, 1) NOT NULL , [name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [Source] [numeric](18, 0) NULL ) ON [PRIMARY] GO INSERT INTO [test] ([name],[subject],[Source]) values (N'mike,N'math,60) INSERT INTO [test] ([name],[subject],[Source]) values (N'tom',N'English',70) INSERT INTO [test] ([name],[subject],[Source]) values (N'mike',N'france',80) INSERT INTO [test] ([name],[subject],[Source]) values (N'jane',N'English',75) INSERT INTO [test] ([name],[subject],[Source]) values (N'mary',N'france',57) INSERT INTO [test] ([name],[subject],[Source]) values (N'tom',N'math',80) INSERT INTO [test] ([name],[subject],[Source]) values (N'mike',N'Englist',100) Go -------- declare @sql varchar(8000) set @sql = 'select name,' select @sql = @sql + 'sum(case subject when ''+subject+'' then source else 0 end) as ''+subject+'',' from (select distinct subject from test) as a select @sql = left(@sql,len(@sql)-1) + ' from test group by name' exec(@sql) But I cannot use it in sql server mobile editon. HOw do I? thank you very much!!!
View Replies !
Help With Pivot Crosstab Query (was &"query Idea ??&")
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 Replies !
Access Crosstab -&> SQL Crosstab
Hi all I'm transferring some Access queries to SQL server and the crosstabs don't want to work, can anyone shed any light on the query below. Thanks. TRANSFORM Min(tCompany.cCompanyName) AS CompanyName SELECT tProjContacts.ProjectID FROM tCompany INNER JOIN (tProjContacts INNER JOIN tCompanyType ON tProjContacts.CoTypeId = tCompanyType.CoTypeId) ON tCompany.CompanyID = tProjContacts.CompanyID WHERE (((tCompanyType.CoType) Like "*topo*" Or (tCompanyType.CoType) Like "*ground*")) GROUP BY tProjContacts.ProjectID PIVOT tCompanyType.CoType;
View Replies !
How Do I Use A Pre-defined Sql Query?
Hi there, hope you can help me. What should I do if I want to use a pre-defined string as sql query (SelectCommand)? So I want to use the sqlquery string in the SelectCommand option...The reason is that this page is request like page.aspx?ID=1234, and I want to use the Request.QueryString option to define the sql query...<HTML><head><sript runat"server">Dim sqlquery As String = New String ("SELECT LastName FROM Employees")</script> <BODY> <FORM runat="server"> <asp:SqlDataSource id="SqlDataSource1" runat="server" DataSourceMode="DataReader" ConnectionString="<%$ ConnectionStrings:MyNorthwind%>" SelectCommand="SELECT LastName FROM Employees"> </asp:SqlDataSource> </FORM> </BODY></HTML>
View Replies !
My Query And User Defined Fuction Problem
I have a table which has six fields ID, dateDue, dateReceived, dueday. month, ContactFYEmy query looks likeselect ID, DateDue, dateReceived, dueday, month, ContactFYEfrom Reportwhere (dbo.Report.DateDue BETWEEN dbo.udfDisplayTime(dueday, month, ContactFYE) AND DateDue )user defined functionCREATE FUNCTION dbo.udfDisplayTime ( @dueday int, @month int, @ContactFYE smalldatetime) RETURNS smalldatetime AS BEGIN DECLARE @ReturnString AS smalldatetime Declare @dueday1 as int if (@month =1)beginset @dueday1 = @dueday -7endelse if (@month =2)begin set @dueday1 = @dueday -14end else if (@month =3)begin set @dueday1 = @dueday -60endselect @ReturnString = DATEADD ( dd, @dueday1, @ContactFYE) Return @ReturnStringENDI got a incorrect result when using this query and user defined function.the result that I got was out of between displaytime and datdue. any idea for this , Thanks.
View Replies !
Query Only Freezing When Put Into A User Defined Function?
I'm experiencing quite an interesting issue with our database. We're currently running SQL Server 2005 Standard (x64) (v9.00.3054.00) and I have a block of code that just insists on not working within a UDF. Everything I've read seems to point that I'm doing this corerctly but apparently I'm missing something. Executing the query below: DECLARE @sp DATETIME DECLARE @ep DATETIME DECLARE @trackType INT SET @sp = '4/21/2008' SET @ep = '4/28/2008' SET @trackType = 1 SELECT c.unqempid, isnull(count(c.contactid),0) AS contacts, isnull(bookings.count,0) AS bookings, isnull(showed.count,0) AS showed, isnull(ow.totalPurchased,0) AS purchases, isnull(ow.totalSold,0) AS volume FROM contacts c WITH (NOLOCK) LEFT JOIN ( SELECT b.unqempid, ISNULL(count(bookingid),0) AS count FROM bookings b WITH (NOLOCK) INNER JOIN contacts c WITH (NOLOCK) ON c.contactid = b.relcontactid WHERE c.contactdt BETWEEN @sp AND @ep GROUP BY b.unqempid ) AS bookings ON bookings.unqempid = c.unqempid LEFT JOIN ( SELECT b.unqempid, ISNULL(count(bookingid),0) AS count FROM bookings b WITH (NOLOCK) INNER JOIN contacts c WITH (NOLOCK) ON c.contactid = b.relcontactid WHERE c.contactdt BETWEEN @sp AND @ep AND didshow > 0 GROUP BY b.unqempid ) AS showed ON showed.unqempid = c.unqempid LEFT JOIN ( SELECT c.unqempid, count(leadid) TotalPurchased, sum(saleprice) AS TotalSold FROM ordermgmt.dbo.orders o WITH (NOLOCK) INNER JOIN ordermgmt.dbo.appointments a WITH (NOLOCK) ON a.weborderid = o.orderid INNER JOIN contacts c WITH (NOLOCK) ON c.personid = a.leadid INNER JOIN bookings b WITH (NOLOCK) on b.relcontactid = c.contactid WHERE c.contactdt BETWEEN @sp AND @ep GROUP BY c.unqempid ) as ow ON ow.unqempid = c.unqempid WHERE c.contactdt BETWEEN @sp AND @ep GROUP BY c.unqempid, bookings.count, showed.count, ow.totalPurchased, ow.totalSold Yields no issues however if I put this into a UDF, it runs and never finishes executing. I'm not quite sure what my issue was so I'm hoping someone could point out where my goof is. Here is the function: CREATE FUNCTION [dbo].[fnEmployeeSchedulingResults] ( @sp DATETIME, @ep DATETIME ) RETURNS TABLE AS RETURN ( SELECT c.unqempid, isnull(count(c.contactid),0) AS contacts, isnull(bookings.count,0) AS bookings, isnull(showed.count,0) AS showed, isnull(ow.totalPurchased,0) AS purchases, isnull(ow.totalSold,0) AS volume FROM contacts c WITH (NOLOCK) LEFT JOIN ( SELECT b.unqempid, ISNULL(count(bookingid),0) AS count FROM bookings b WITH (NOLOCK) INNER JOIN contacts c WITH (NOLOCK) ON c.contactid = b.relcontactid WHERE c.contactdt BETWEEN @sp AND @ep GROUP BY b.unqempid ) AS bookings ON bookings.unqempid = c.unqempid LEFT JOIN ( SELECT b.unqempid, ISNULL(count(bookingid),0) AS count FROM bookings b WITH (NOLOCK) INNER JOIN contacts c WITH (NOLOCK) ON c.contactid = b.relcontactid WHERE c.contactdt BETWEEN @sp AND @ep AND didshow > 0 GROUP BY b.unqempid ) AS showed ON showed.unqempid = c.unqempid LEFT JOIN ( SELECT c.unqempid, count(leadid) TotalPurchased, sum(saleprice) AS TotalSold FROM ordermgmt.dbo.orders o WITH (NOLOCK) INNER JOIN ordermgmt.dbo.appointments a WITH (NOLOCK) ON a.weborderid = o.orderid INNER JOIN contacts c WITH (NOLOCK) ON c.personid = a.leadid INNER JOIN bookings b WITH (NOLOCK) on b.relcontactid = c.contactid WHERE c.contactdt BETWEEN @sp AND @ep GROUP BY c.unqempid ) as ow ON ow.unqempid = c.unqempid WHERE c.contactdt BETWEEN @sp AND @ep GROUP BY c.unqempid, bookings.count, showed.count, ow.totalPurchased, ow.totalSold ) Any help would be greatly appreciated! -Justin
View Replies !
Performance, User Defined Function Or Sub Query
What is better for performance, using a user defined function or a sub query to perform an aggregate calculation in a select statement. for eg would it be best to call a user defined function which performs the below calculation of the sub query shown. I'm speaking purely from a performance point of view. SELECT o.ordersid, o.orders, (SELECT SUM(i.total) FROM items AS i WHERE i.ordersid = o.ordersid) AS [total] FROM order AS o
View Replies !
Help With Crosstab (was &"Query Help Needed!&")
Hey, i have a table which has the foll data: employeecode Amount AmountDescription 1 100 x 2 200 y 3 150 x 4 300 z now i need to fetch this data such that i can display the output as : empcode x y z 1 100 2 200 3 150 4 300 any suggestions????????? platform: SQL Server 2000 thanx!
View Replies !
Dynamic Query Calling User Defined Function
I have the following procedure, that calls a Padding function to pad characters to a field. Here is what the procedure looks like Code: CREATE PROCEDURE [dbo].[Pad_Left] @Table VARCHAR(255), @Column VARCHAR(255), @PadChar CHAR(1), @PadToLen INT AS DECLARE @Query Varchar(5000) SET @Query = 'UPDATE ' + @Table + ' SET ' + @Column + ' = dbo.Function_PadLeft(' + @Column + ', ''' + @PadChar + ''', ' + @PadToLen + ')' EXECUTE(@Query) GO When I run this I get the error Server: Msg 245, Level 16, State 1, Procedure Pad_Left, Line 13 Syntax error converting the varchar value 'UPDATE Lincoln SET baths = dbo.Function_PadLeft(baths, '0', ' to a column of data type int. But when I just run this query, it works Code: CREATE PROCEDURE [dbo].[Pad_Left] @Table VARCHAR(255), @Column VARCHAR(255), @PadChar CHAR(1), @PadToLen INT AS UPDATE Lincoln SET Baths = dbo.Function_PadLeft(Baths, '0', 4) GO Why would one work but not the other? I don't understand, as they are the same thing, just one calls the function dynamically? I must be missing something very obvious Thanks for any help!
View Replies !
Order By Query
Hi, I am using below query: SELECT tbh_Articles.ArticleID, tbh_Articles.AddedDate, tbh_Articles.AddedBy, tbh_Articles.CategoryID, tbh_Articles.Title, tbh_Articles.Abstract, tbh_Articles.Body, tbh_Articles.Country, tbh_Articles.State, tbh_Articles.City, tbh_Articles.ReleaseDate, tbh_Articles.ExpireDate, tbh_Articles.Approved, tbh_Articles.Listed, tbh_Articles.CommentsEnabled, tbh_Articles.OnlyForMembers, tbh_Articles.ViewCount, tbh_Articles.Votes, tbh_Articles.TotalRating, tbh_Articles.ImageURL, tbh_Articles.specialFROM tbh_Lang CROSS JOIN tbh_ArticlesWHERE (tbh_Lang.LangID = @LanguageID) AND (tbh_Articles.ArticleID = tbh_Lang.ArticleMain OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond1 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond2 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond3 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond4 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond5) Problem is that I want to sort in a manner which the results returns as ArticleMain as the first column, ArticleSecond1 as the second and so on... Tables structure: tbh_Articles(id, title, body...) ; tbh_Lang(id,ArticleMain,ArticleSecond1 ,ArticleSecond2.... ) Any suggestions?
View Replies !
Order By Query
I have a Comment Table where a comment can have a reply, if the comment is replied to I want the reply to appear under the comment. Based on the Fields CommentID and Parent ID the parentID is the Comment and the Comment with a ParentID set too that comment is the answer. How do I build this Query?
View Replies !
SQL Query Help-- Order By Clause
HiI want a simple select query on a column-name (smalldatetime) withvalues dislayed in desc order with null values FIRST.i.e.Select orderdate from ordersorder by ( null values first and then orderdate in desc order)could any one please helpThanks
View Replies !
SQL Query Or SP? Order Value Between Two Dates
Hi all, Been having a good root around the forums and the site here and there's some real smart people on here, i'm hoping one or more of them can help me out. I'm expecting this to be a simple question for some of you, however it's way beyond me at this point! Table Structure (abridged, relevant columns): Orders: Code: [Orders]( [OrderID] [int] IDENTITY(1,1) [OrderDateTime] [datetime] [OrderSiteID] [nvarchar](255) [OrderOffline] [bit] [OrderSentToWP] [bit] [OrderReceivedFromWP] [bit] [OrderAuthorised] [bit] [OrderCancelled] [bit] [OrderApproved] [bit] [OrderFraud] [bit] [OrderDispatched] [bit] OrderItems: Code: [OrderItems]( [OrderID] [int] [ProductID] [nvarchar](255) [Quantity] [int] [Price] [real] [Weight] [real] Products: Code: [Products]( [ProductID] [uniqueidentifier] [ProductCode] [nvarchar](255) [ProductTitle] [nvarchar](255) Product price is captured at time of order, so that reports aren't affected by discounts or promotions, and stored with the productid in orderitems. I want to get a report between a set of dates and with certain flags set (see below example) and then get a list of unique products, quantity sold and sales values for that products. Results table would have 4 columns; ProductCode, ProductTitle, QuantitySold, Sales Value. So far I have this: Code: SELECT Products.ProductCode, Products.ProductTitle, SUM(OrderItems.Quantity) AS QuantitySold FROM Orders INNER JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN Products ON OrderItems.ProductID = Products.ProductID WHERE (Orders.OrderDateTime BETWEEN '2007/01/01' AND '2007/12/31') AND (Orders.OrderSentToWP = 1) AND (Orders.OrderReceivedFromWP = 1) AND (Orders.OrderAuthorised = 1) AND (Orders.OrderCancelled = 0) AND (Orders.OrderDispatched = 1) AND (Orders.OrderApproved = 1) AND (Orders.OrderFraud = 0) AND Orders.OrderSiteID= 'someguid' GROUP BY Products.ProductCode, Products.ProductTitle Which gets my summed quantities, and I guess I could use ASP to multiply that by the current price, but that defeats the point of setting the database up properly in the first place! I know how to design data, i just don't know how to get it back out again I could most likely just do the whole thing in ASP and get it to output the correct answer, so if it's impossible/very difficult to do it in pure SQL then I'll go that route. Ideal situation would be a stored proc or saved query that I can pass a start date, an end date and a siteid to and that will get me the answers I want! Thanks in advance to anyone that looks at this for me. Also, any recommended books/sites to learn this kind of query? Richard
View Replies !
Order Of Conditions In A Query
I have a query with many (approximately, 30) conditions, such as: select ....... from table1 join table2 on ( (table1.field1 = table2.field1 OR table1.filed1 IS NULL) AND (table1.field2 = table2.field2 OR table1.filed2 IS NULL) ) My question is: In C++ or C#, when I write a condition like this, say, in an IF or WHILE, I know that I would be better off specifying the IS NULL (well, == null, to be precise) first, and use | instead of ||. In that case, the first condition (equality to null) is checked first, it's fast, and if it's not satisfied, the control flow goes to the next statement. The question is, is there the same rule in T-SQL? I mean, if I put the "... IS NULL" first, and then "OR ... = ...", will the query run faster than if I write it the other way around (that is, "... = ... OR ... IS NULL")? This is very important to me, because most of those fields are VARCHAR, and due to some business rules, I can't change them to numerics etc, which would be compared much faster than text. So, even if I use full text search, I still need to find a way to optimize the query for performance... By the way, I know that I can put those conditions in the WHERE clause, but as far as I know it won't make much of a difference for performance. So, my question is primarily about the order of conditions, in which SQL Server constructs its query plan. [Edited:] In other words, what runs faster: comparing varchar to null or comparing varchars? And does it make a difference if I switch their places in my sql script? We are using SQL Server 2000 SP4, Standard Edition. [Dev edition on the dev machine.] Could someone kindly advise me on this, please? Thank you ever so much.
View Replies !
Parsename Query Order
Greatful for any help.... Have the query below which is taking delimited address information from _Venue column. This works well apart from the order it is returned, for example, the output below has the address tittle displayed in a different column for each row Queen Elizabeth's Hunting Lodge is in Address1 All Saints' Church is in Address2 Audley End House is in Address3 As I need to reference from the query the correct part of the address from the same location each time, is there anyway to get around this? Thanks in advance SELECT coalesce (PARSENAME(REPLACE(_Venue,',', '.'),4), '-') address1 ,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),3), '-') address2 ,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),2), '-') address3 ,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),1), '-') address4 FROM table Address1 Address2Address3Address4 ---------------------------------------------------------------------------------------- Queen Elizabeth's Hunting LodgeRangers RoadChingfordLondon E4 7 QH - All Saints' ChurchShrub End RoadColchester ---Audley End House
View Replies !
Slow Query When You Add An Order By
SELECT Column1,Column2,Column3 .... FROM vwMyView ORDER BY CreatedDT View has about 10000 rows, If I remove order by query runs faster but adding an order by cause query to timeout.. All tables have clustered index based on the primary key of the table.. (a) Should I create an index view with CreatedDT as non clustered index? (b) Or create a non clustered index on CreatedDT column on the underlying table? I can provide DDL but if something obvious I am missing Thanks
View Replies !
How To Get Order Values In Sql Query
Hi every body. Can u tell me how to get the order values of the SQL query Example. My sqlstring ="Select * from tbl_Products" And it returns 6 rows And I want to get order values like this 1,2,3,4,5,6 I am a beginner. Thanks a lots
View Replies !
Need An Special ORDER BY Clause Query
Table:ColumnsUsersList:UserID, UserName, Country I need a query which select all the rows from the above mentioned table with all fieldsButThe order the rows is First all the users from "Pakistan"Second all the users from rest of the countries except "Pakistan" in ascending order So the query first return all the users from Pakistan and the the users from rest of the world in ascending order. Forexample, 1, ABC, USA2, XYZ, Saudi Arabia3, LMN, Pakistan4, TQR, India5, PTR, Afghanistan then the query returns. 3, LMN, Pakistan5, PTR, Afghanistan4, TQR, India2, XYZ, Saudi Arabia1, ABC, USA
View Replies !
How To Make A Random Order Query ?
If we use: select * from ..... , normally, it will return an ordered result ( may be order by ID ), but how can we make an random order select statement. It mean every time we run the query, the result will be different from the other ?
View Replies !
Multiple ORDER BY Query Syntax
I have some data that I'd like to order by a certain attribute.. but if there is a tie, then it should order by a secondary attribute.. and if there's still a tie.. then a 3rd attribute. Currently the query looks like this: SELECT * FROM Players WHERE ORDER BY Points But I want it to look something like this (I know this doesn't work.. but it's just to give an idea): SELECT * FROM Players WHERE ORDER BY (Points desc AND Games asc AND Goals desc) Does anyone know the proper syntax for a multiple ORDER BY query like I described above? Brent
View Replies !
Tricky Group By Order By Query
Hello, I'm trying to find the most optimal way to perform a trickyquery. I'm hoping this is some sort of standard problem that has beensolved before, but I'm not finding anything too useful so far. I havea solution that works (using subqueries), but is pretty slow.Assume I have two tables:[Item]ItemID int (Primary Key)ItemSourceID intItemUniversalKey uniqueidentifierPrice int[Source]ItemSourceIDPriorityI'm looking for a set of ItemIDs that match a query to the Price(something like Price < 30), with a unique ItemUniversalKey, taking thefirst item with each key according to Source.Priority.So, given Item rows like this:1 2 [key_one] 152 2 [key_two] 253 1 [key_one] 15and Source rows like this:1 12 2I want results like this:2 2 [key_two] 253 1 [key_one] 15Row 1 in Item would be eliminated because it shares an ItemUniversalKeywith row 3, and row 3's Source.Priority is lower than row 1.Help!?
View Replies !
Query Performance With Order By Clause?
Hi all,Just wondering if anyone can tell me if an order by clause on a selectquery would have any impact on the time it takes to retrieve results?Essentially I'm selecting Top 1 out of a table via various criteriaand currently getting it back without an order by clause. The order bywould only include the column that has the clustered primary index onit.Can anyone tell me if in theory this will slow the query down?Many thanks in advance!Much warmth,Murrau
View Replies !
Is Order By Affect The Query Speed
hello, I have a query that insert insert into new table , and then i select from this table, if i add ORDER BY in the INSERT INTO script , does it affect the speed of the SELECT i have big table that take about 70 sec
View Replies !
Using An Array To Determine Query Order??
Hi. I have a VB.NET function that returns an array of Integers. Say, FunArray = [2, 3, 5, 8, 6, 23, 1, 10, 20 , 4, 54] One characteristic of the array is that no two numbers repeat - it reflects the IDs of my Users table. And, that it is not ascending or descending. What I would like to know is how do I sort my query in the order of the integers in this array? Ideally, I would like to use ORDER BY for this query. thanks in advance.
View Replies !
|
TRACKER
