I created a view (vwNewsPerCity) where I am counting the number of new per
city/year/month
SELECT TOP 100 PERCENT COUNT(News_Id) AS iNoNews, YEAR(News_Date) AS iYear, MONTH(News_Date) AS iMonth, News_City
FROM dbo.tblNoticias
GROUP BY YEAR(News_Date), MONTH(News_Date), News_City
Now I would like a query returning me for a particular year and month for each cities the number of news and just the name of the cities when there is no news
I wrote this view:
SELECT City_Id, City_Name, iMonth, iYear, iNoNews
FROM vwNewsPerCity RIGHT OUTER JOIN
tblCity ON vwNewsPerCity. News_City =tblCity.City_Id
WHERE (vwNewsPerCity.iMonth = 2) AND (vwNewsPerCity.iYear = 2002) OR
(vwNewsPerCity.iMonth IS NULL) AND (vwNewsPerCity.iYear IS NULL)
Well, this does not work the way I want because in this case it displays all cities which have news or not in the year 2002 but not the cities which are having news only in 2001
So my question how is the right way to perform this ?
I am trying to use the inner join function and group by to provide a sum of information for a range of dates. Currently i am provided with each date as separate records using the script below.
quote: select salesmn.store, salesmn.salesman as employee_num, salesmn.sales, salesmn.spiffs, employee.first, employee.last from employee inner join salesmn on (employee.login = salesmn.salesman) where salesmn.dte between '1/1/2014' and '1/3/2014' order by salesmn.store, employee.last asc
I want to use this code or something similar to provide a sum of the spiffs and sales for the customer.
quote: Select salesman, sum(sales) as TotalSales, sum(spiffs) as Total_Spiffs from salesmn group by salesman
I have a little problem with my joins and group by query.
I have two tables, A and B.
A has paymentnr, infoid and date.
B has a codetext, codeid.
A and B are related by infoid and codeid.
I want to get paymentnr and codetext for all with the latest date.
I tried with this but x.codetext does not display in the result-table:
SELECT paymentnr, x.codetext FROM ( select paymentnr, max([date]) as latestdate from A inner join (select codetext, codeid from B group by codetext, codeid) x ON x.codeid = A.infoid group by paymentnr ) Y order by paymentnr
It is important to get both paymentnr and codetext, but i dont manage. I know why i cant get codetext out, but the problem i have is how to get around this problem.
If i do the following one and want to run a inner join for the group by Valid ????? go Create table #rpt1 ( recordNUM int NULL , History varchar(15) null, Lastname varchar(20) null, Firstname varchar(12) null, ) Insert into #rpt1 Values (1, '123', 'Limb', 'Sub') Insert into #rpt1 Values (1, '121', 'Limb', 'Sub') Insert into #rpt1 Values ( 4,'124', 'Zimb', 'Kun') Insert into #rpt1 Values (5, '125', 'Geroge', 'Khan')
The Query I tried is: SELECT Branch, ProductID, COUNT(*) as Total FROM Customer_Product cp RIGHT OUTER JOIN Product pd ON cp.ProductID=pd.id GROUP BY Branch, ProductID ORDER BY Branch, ProductID
But Right outer join act on Table level instead of Group. How can i generate desired output by keeping in mind that both Branch and Product are dynamic?
So; if you read my earlier thread here (http://www.dbforums.com/showthread.php?t=1214353), you'll know that I'm trying to build stored procedures to deal with ticketing queries, and that it's all getting a bit complicated. I have, however, made a bit of progress and now have the following working:
SELECT [tblTickets].[id] AS ticketId, [tblEvents].[id] AS eventId, [tblStands].[id] AS standId, [tblAdmissionDates].[id] AS admitDateId, [tblEvents].[event_name], [tblStands].[stand_name], [tblTicketConcessions].[concession_name], [tblMemberships].[membership_name], [tblAdmissionDates].[admission_start_date], [tblAdmissionDates].[admission_end_date], [tblBookingMinQuantities]. AS minBookingQuantity, [tblBookingMaxQuantities].[booking_quantity] AS maxBookingQuantity, MIN([tblQuotas].[quota]) AS Quota, [B]SUM([tblBasket].[ticket_quantity]) AS History, [tblTickets].[price], [tblTickets].[availability]
FROM [tblTickets] LEFT JOIN [tblEvents]ON [tblEvents].[id] = [tblTickets].[event_id] LEFT JOIN [tblStands]ON [tblStands].[id] = [tblTickets].[stand_id] LEFT JOIN [tblBookingDates]ON [tblBookingDates].[id] = [tblTickets]. LEFT JOIN [tblTicketConcessions]ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id] LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id] LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantitiesON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id] LEFT JOIN [tblAdmissionDates]ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id] LEFT JOIN [tblMemberships]ON [tblMemberships].[id] = [tblTickets].[membership_id] LEFT JOIN [tblQuotas]ON ([tblQuotas].[event_id] = [tblTickets].[event_id] OR [tblQuotas].[event_id] IS NULL) AND ([tblQuotas].[stand_id] = [tblTickets].[stand_id] OR [tblQuotas].[stand_id] IS NULL) AND ([tblQuotas].[admission_date_id] = [tblTickets].[admission_date_id] OR [tblQuotas].[admission_date_id] IS NULL) AND ([tblQuotas].[concession_id] = [tblTickets].[ticket_concession_id] OR [tblQuotas].[concession_id] IS NULL) AND ([tblQuotas].[membership_id] = [tblTickets].[membership_id] OR [tblQuotas].[membership_id] IS NULL) AND ([tblQuotas].[ticket_id] = [tblTickets].[id] OR [tblQuotas].[ticket_id] IS NULL) [B]LEFT JOIN [tblBasket] ON [tblBasket].[ticket_id] = [tblTickets].[id] LEFT JOIN [tblOrders] ON [tblOrders].[id] = [tblBasket].[order_id]
WHERE 1=1 AND ([tblTickets].[ticket_open] = 1) AND (([tblEvents].[id] = @eventId OR @eventId = 0)AND ([tblEvents].[event_open] = 1)) AND (([tblStands].[id] = @standId OR @standId = 0)AND ([tblStands].[stand_open] = 1)) AND (([tblAdmissionDates].[id] = @admissionDateId OR @admissionDateId = 0)AND ([tblAdmissionDates].[date_open] = 1)) AND ([tblTicketConcessions].[id] = @concessionId OR @concessionId = 0) AND ((getdate() BETWEEN [tblBookingDates]. AND [tblBookingDates].[booking_end_date]) OR ([tblBookingDates].[booking_start_date] IS NULL AND [tblBookingDates].[booking_end_date] IS NULL)) AND (([tblMemberships].[id] IN (SELECT [membership_id] FROM [tblUsers_Memberships] WHERE [user_id]=@userId)) OR [tblMemberships].[id] IS NULL) [B]AND ([tblOrders].[user_id] = @userId OR @userId=0)
GROUP BY [tblTickets].[id], [tblEvents].[id], [tblStands].[id], [tblAdmissionDates].[id], [tblEvents].[event_name], [tblStands].[stand_name], [tblTicketConcessions].[concession_name], [tblMemberships].[membership_name], [tblAdmissionDates].[admission_start_date], [tblAdmissionDates].[admission_end_date], [tblBookingMinQuantities].[booking_quantity], [tblBookingMaxQuantities].[booking_quantity], [tblTickets].[price], [tblTickets].[availability] GO
Except... there's two problems with it. One is that it only returns tickets that you've already bought, the other is that it doens't work out correctly how many of those tickets you've bought in past orders.
Both the orders in there are for the same user id: "1". All the tickets in there are tied to event "2".
When I run Exec dbo.getAvailableTickets 2,0,0,0,1, it tells me it's found 6 of ticket 5, 4 of ticket 6 and 12 of ticket 14. And I can't for the life of me figure out how it's calculating it. Any ideas?
And how do i get it to return all tickets regardless of whether you've bought them previously or not?
SELECT task1_.Start as y0_, count(this_.FirstName) as y1_ FROM t_contact this_ inner join t_task task1_ on this_.TaskId=task1_.Id GROUP BY task1_.Start
In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?
if OBJECT_ID('tempdb..#left_table') is not null drop table #left_table; if OBJECT_ID('tempdb..#right_table') is not null drop table #right_table; create table #left_table
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.
Provide Individual Page Numbering per Group and Total Pages per Group
Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.
I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.
I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this.
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year Month Date
And the following row groups:
Region Product SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
select top 15 count(*) as cnt, state from table group by state order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
I have one domoain in the forest. The domain level is set to Windows 2000 native mode and forest level is set to mixed mode. My SQL server 2005 server joined to this domain. I added a brand new domain local group and add a normal user account to this domain local group. I login to the SQL server 2005 server and make a query "SELECT * FROM sys.login_token". I cannot see my domain local group in sys.login_token. However, if I add my account to a global group, I can see it there.
Then, I setup another forest. This time, I have domain level set to Windows 2003 mode and forest level is set to Windows 2003 native mode. I do the same testing. This time, I can see my domain local group in sys.login_token.
Why does SQL server 2005 has this limitation? Is it a bug?
HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned. DECLARE @StartDate varchar(12)DECLARE @EndDate varchar(12)DECLARE @Region varchar(20) SET @StartDate = '01/01/2002'SET @EndDate = '12/31/2008'SET @Region = 'Central' SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName FROM dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid WHERE (A.createdon >=@StartDate AND A.createdon <= @EndDate)AND (B.new_RegionName = @Region)AND (A.casetypecode = 2)
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.
this is the best I can come up with:
Code:
SELECT DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts FROM log_directory_contacts GROUP BY post_date
The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.
I have a matrix with two row groups and one column group with about 6 items in it. I have about 2100 rows at the lowewst row group level. This report was built solely for excel export. The first row group has about 20 items and controls the visibility of the other group. When I toggle the visibility of the second row group, how can I make the the header of the first row group copy down for each row of the other row group? The first row group is the Section and the second is Mnemonic.
I was writing a query using both left outer join and inner join. And the query was ....
SELECT S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P LEFT OUTER JOIN Production.Categories AS C ON C.categoryid = P.categoryid) ON S.supplierid = P.supplierid WHERE S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
OLEDB source 1 SELECT ... ,[MANUAL DCD ID] <-- this column set to sort order = 1 ... FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2 SELECT ... ,[Bo Tkt Num] <-- this column set to sort order = 1 ... FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above. join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column) ... 4-400-8000119 4-400-8000120 4-400-8000121 4-400-8000122 <--row not joining 4-400-8000123 4-400-8000124 ...
result of source2 (..tkt num column) ... 4-400-1000118 4-400-1000119 4-400-1000120 4-400-1000121 4-400-1000122 <--row not joining 4-400-1000123 4-400-1000124 4-400-1000125 ...
All other rows are joining as expected. Why is it failing for this one row?
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table. The makeid and orderdate belong to the OrderDetails table. The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?
