I need to accomplish the turning column data into row data via SQL. I can sorta get what I want with creating the report as a matrix report. However, I always seem to need one little thing to happen to shape my data as I need it. Anyway, here goes...
I have a table with 4 columns
UNIQUE | Code | FieldID | CustomField
The Code column is the customer code. FieldID numbers 1-100 and CustomField has string data.
My problem. I need to be able to choose ALL Customers [Code] where FieldID/s ="6", "7" & "8" and the CustomField rowdata corresponding to the FieldID data. BUT I then need to be able to use a daterange parameter on any row with a FieldID of "6" but that is not a datetime format it is in a string format.
Currently my SQL is:
SELECT
[Unique ID], Code, [Field ID] AS IUdate, [Field ID] AS IUNote, [Field ID] AS IUReq, [Custom Field]
FROM
dbo.[Customer Custom Field]
WHERE
(Code = '07-8111')AND ([Field ID] = 6) OR (Code = '07-8111')AND ([Field ID] = 7) OR (Code =
'07-8111')AND ([Field ID] = 8)
However, everytime I try to run a daterange against, I get all kinds of data I don't need because the parameter is running against all the fields and NOT just the stringdate.
My idea is to convert the column data into row data (like a crosstab query) to sharpen it up for parameter ranges.
Is there something i am missing here? Can someone point me in the right direction?
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;
I am a neewbie to SQL 7 and having previously used Access for some time.. I have a table with a date field in a want to create a table with the values filling columns in another table by month ie April,may ,Jun and so on. Ia m using the Month function to get the month number but I dont know how to crosstab this into the relevant columns in the new table...I thinks DTS is the way and do a transformation on the month field but am struglling at the mo...Any help greatfully appreciated...
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 .... .... ....
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.
I am trying to run a report off a crosstab query. The report calls for columns A - F, which are returned from the crosstab query. The only problem is that at times some of the columns are not returned by the query because there is no data associated with them. The report asks for column X, and no such columns exists in the query result; thus, an error is generated. My question is: how do I deal with this problem? I would like to avoid having to build the report from scratch. Is there some way that at runtime I could programatically tell the report to ignore these fields?
I have a table called Ideas and a table called Users_Ideas. In Users_Ideas, there can be up to four rows referencing Ideas with a foriegn key. I need to select all rows from Ideas and for each Idea display the users associated with it.
Total is the total number of alerts for the server between the two dates. Of course, there are many servers, and the number of alert names varies over time which means i can't use "case when...".
Is this possible??? Would be extremely thankful for any help!
I'm trying to port a dymanically renderred form from a .mdb to a .adp. In the Access .mdb. The form is a representation of a crosstab query with unknown column headings (hence, the need for the form to be dynamically rendered at runtime).
I tried to port the crosstab from the .mdb to a stored procedure, but SQL Server doesn't like TRANSFORM, and perhaps the PIVOT as well. How do you do this in sql server?:
TRANSFORM Count(d.CAR_INIT) AS CountOfCAR_INIT SELECT b.WKLD_SEQ, c.TRN_ID, c.TRK_NBR FROM ((TSA_HS_MPCT_CNT a INNER JOIN TSA_HS_COMB2 b ON a.RECC_COMB_ID = b.COMB_ID) INNER JOIN TSA_HS_WKLD c ON b.WKLD_ID = c.WKLD_ID) INNER JOIN TSA_HS_OBJ_TRN d ON c.WKLD_ID = d.WKLD_ID GROUP BY b.WKLD_SEQ, c.TRN_ID, c.TRK_NBR PIVOT d.LST_HMP_DTM + d.OBJ_DEP_TRN
Hi, I have tried with the solutions available in the links provided by Pootle,but still I think I have to go a long way.My main problem is with the second aggregate column of previous year. Let me put it again....
But I can't bring the previous year aggregate column ,i.e 2005 in the above case.
So can you suggest me a way or direction to acheive that?
Any help would be really appreciated. BTW,Pootle gave these links in my previous post... link1 (http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21820764.html#16500817) Link2 (http://www.sqlteam.com/item.asp?ItemID=2955)
Hi I have table called MyTable that has 3 columns (City , Brand, Price) I want to select from this table in Cross Tab format i.e. My table has CityBrandPrice LondonDELL1227 LondonToshiba1100 LondonAcer1007 LondonHP1467 LondonIBM1193 SydneyToshiba2100 SydneyAcer2219 SydneyApple2589 SydneyVAIO2122 SydneyHP1929 SydneyIBM2877 TokyoToshiba7200 TokyoAcer5299 TokyoCOMPAQ9200 TokyoIBM8779 TokyoHP6286 ParisDELL1670 ParisApple1825 ParisVAIO1267 ParisHP1882 ParisCOMPAQ1636 ParisIBM1332 NewYorkDELL2000 NewYorkToshiba1288 NewYorkAcer2333 NewYorkApple2299 NewYorkVAIO2327
__________________________________
i want the select statment result to be like this DELLToshibaAcerHPIBMAppleVAIOCOMPAQ London12271100100714671193N/AN/AN/A SydneyN/A2100221919292877N/AN/AN/A TokyoN/A7200529962868779N/AN/A9200 Paris1670N/AN/A18821332182512671636 NewYork200012882333N/AN/A22992327N/A
City & Brand can be anything so i will not be able to hard code them in my Select.
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 . . . . . . . . . . . .
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
Dear friends,I wonder if exist a way to make crosstab queries in SQL Serverlike those in Access without "external" programming. Does theSQL Server supports the "TRANSFORM" SQL-extension?Thanks in advance, Sotiris.
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?
I re-designed a predecessor's database so that it is more properlynormalized. Now, I must migrate the data from the legacy system intothe new one. The problem is that one of the tables is a CROSSTABTABLE. Yes, the actual table is laid out in a cross-tabular fashion.What is a good approach for moving that data into normalized tables?This is the original table:CREATE TABLE [dbo].[Sensitivities]([Lab ID#] [int] NULL,[Organism name] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[Source] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[BACITRACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CEPHALOTHIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CHLORAMPHENICOL] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CLINDAMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ERYTHROMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[SULFISOXAZOLE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[NEOMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OXACILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[PENICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[TETRACYCLINE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[TOBRAMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[VANCOMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[TRIMETHOPRIM] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CIPROFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[AMIKACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[AMPICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CARBENICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CEFTAZIDIME] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GENTAMICIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[POLYMYXIN B] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[MOXIFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[GATIFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[SENSI NOTE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]
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
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 ............
hello all i'm really confused .. i use VS 2005, VB.NET, Crystal Reports and SQL Server Studio Managment as my DB.. My problem is i dont know where do i have to implement this report i want to produce .. its working hours for all employees showing by month ... ex: Jan-07 Feb-07 March-07 .......HrsSUM joe 54 50 88 sum(hrs) Sam 20 20 50 sum(hrs)
note : it doesnt always starts at jan-07 ... we have a "cut off date" which is the calculations start date till the end of the project (2010) so its a crosstab query ... should i do that using crystal report , writing a function there to generate the months and create a crosstable ?(no idea how to do that)..... or writing a VB code and pass it to the report ? or just create a stored procedure in the DB to do that ? i think the last one is wt should do it ... but i still dont know how
I've got the query below giving me the results I need (results 1) but I need the results to be one row of data by person with the first column (sessionNbr) combined into one column per person (results 2). (I left out some of the columns in the results examples for brevity's sake.) This will be a rarely executed query and only contains a total record count return of less than 5,000 rows. What is the best method of achieving this in your opinion? Thanks to all for any help....
query: select left(s.session_name,charIndex(' ',s.session_name)-1) as sessionNbr, rs2.firstname, rs2.lastname, rs2.organization_name, rs2.address, rs2.city_name, rs2.state_initials, rs2.zip, rs2.event_fk, rs2.person_pk from ncahec.dbo.tblSession s, (select 'XXX-XX-' + right(person_id,4) as ssn, y.firstname, y.lastname, isNull(o.organization_name,'') as organization_name, isNull(y.home_address1,'') + ' ' + isNull(y.home_address2,'') as address, isNull(y.home_zip,'') as zip, c.city_name, t.state_initials, rs1.event_fk, y.person_pk from tblPerson y left join ncahec.dbo.tblAffiliation a on a.affiliation_pk=y.primary_affiliation_fk left join tblOrganization o on o.organization_pk=a.organization_fk left join tblCity c on y.home_city_fk=c.city_pk left join tblState t on c.state_fk=t.state_pk, (select r.event_fk, r.person_fk, registration_pk from tblRegistration r where (r.registration_status_fk=1) and (r.event_fk=11266) ) as rs1 where rs1.person_fk=y.person_pk) as rs2 where (s.session_status_fk=1) and (rs2.event_fk=s.event_fk) and (left(s.session_name,1) in ('0','1','2','3','4','5','6','7','8','9')) order by rs2.person_pk, case when charIndex('~',left(s.session_name,10))>0 then 1 when charIndex('-',left(s.session_name,10))>0 then 2 end, left(s.session_name,charIndex(' ',s.session_name)-1)
I'm sure that I'm being stupid but this is driving me nuts. The case statements in the following query always return the ELSE and never return the THEN for the NULL.
Code:
select day(ums_users.created), count(*) as Total, SUM(CASE ums_user_info.info_key WHEN NULL THEN 1 ELSE 0 END) AS UnReg, SUM(CASE ums_user_info.info_key WHEN NULL THEN 0 ELSE 1 END) AS Reg from ums_users left join ums_user_map on ums_users.user_id = ums_user_map.user_id left join ums_groups on ums_user_map.group_id = ums_groups.group_id left join ums_user_info on ums_user_info.user_id = ums_users.user_id
where (ums_groups.group_id = '43A73FAE-0C2C-4ED8-BE1E-B32C12DB163D' or ums_groups.group_parent_id = '43A73FAE-0C2C-4ED8-BE1E-B32C12DB163D') AND year(ums_users.created) = '2005' and month(ums_users.created) = '01' GROUP BY day(ums_users.created) ORDER BY day(ums_users.created)
I'd be really grateful if somebody could explain what I'm doing wrong.
