I am new to the forum. I need help in solving the following quary.
Table structure: Region: region_id, name Employee: employee_id, name, region_id Sales: sales_id, employee_id, sale_date, sale_amount -there’s an individual entry in the sales table for each sale
Problem Write a query that returns a list of the Regions and the number of employees in each region. The employee only gets added to the count if they had total sales greater than $50,000 last month. Sort the information by the number of employees per region who fit this criteria.
I'm trying to build a where clause that looks at two columns to determine if QUARTERLY and ANNUAL payments are due. rec_day could be any day. I got the monthly one pretty easily: WHERE rec_start <= GETDATE() AND rec_frequency = 'Monthly' AND rec_day = DAY(GETDATE()) So if the rec_day was 9 and the rec_frequency was 'Quarterly' then I would be looking to see if todays date is 1/9 or 4/9 or 7/9 or 10/9 (not sure how I do this part) I'm pretty much stuck on where to go with the quarterly where clause: WHERE rec_start <= GETDATE() AND rec_frequency = 'Quarterly' AND rec_day = ????
I tried the following, but it did not work: (CAST(DATEPART(Q, GETDATE()) * 3 - 2 AS VARCHAR(2)) + '/' + CAST(rec_day AS VARCHAR(2)) + '/' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) = GETDATE())
I have in SQL Table1 the following values in Field1:
ABC ABc AbC abc aBc abC Select Field1 From Table1 Where Field1 = "AbC"
I get all six records, not just the one record (AbC) that I want to retrieve. How do I tell the select statement to be case sensitive? my asp.net statement is given below DataRow dr1 = ds.Tables["Login"].Select("UserId ='" + txtUserId.Text + "'And Passwords ='" + txtPassword.Text + "'")[0]; i m using datarowso i need a helpi have quary but i don;t know how can use DataRow dr1 = ds.Tables["Login"].Select("UserId ='" + txtUserId.Text + "'And Passwords ='" + txtPassword.Text + "'where CONVERT(binary(5),Userid)=CONVERT(binary(5),'" + txtUserId.Text + "')'" )[0]; this give me error "Where condiction need operator"waiting for reply
hi, i have two tables with parent/child relationship - pipeline and pipelineStatus. the select statement like this:
SELECT * FROM pipeline INNER JOIN pipelineStatus ON pipeline.id = pipelineStatus.parentID
i got multiple records for each pipeline.id because of multiple records of pipelineStatus. Is it possible to get only one record for each pipeline.id with last record of pipelineStatus table? (stored procedure ok)
I have add all linked child reports for particular month based on particular (insurer name )andpolcynumber .I need to add all annual premium of all child reports and put them in another report for that particular month
I am working on an HR project and I have one final component that I am stuck on.
I have an Excel File that is loaded into a folder every month.
I have built a package that captures the data from the excel file and loads it into a staging table (transforming a few bits of data).
I then combine it with another table in a view.
I have another package that loads that view into a Master table and I have added a Slowly Changing Dimension so that it only updates what has been changed. (it’s a table of all employees, positions, hire dates, term dates etc).
Our HR wants to have this data in a report (with charts and tables) and they wanted it to be in a familiar format. So I made a data connection with Excel loading the data into a series of pivot tables.
I have one final component that i cant seem to figure out. At the end of every year I need to capture a count of all Active Employees and all Termed employees for that year. Just a count.
The data is in one table labeled [EEMaster]. To test the count I have the following.
SELECT COUNT([PersNo]) AS HistoricalHC FROM [dbo].[EEMaster] WHERE [ChangeStatus] = 'Current' AND [EmpStatusName] = 'Active'
this returns the HistoricalHC for 2013 as 418.
SELECT COUNT([PersNo]) AS NumbOfTermEE FROM [dbo].[EEMaster] WHERE [ChangeStatus] = 'Current' AND [EmpStatusName] = 'Withdrawn' AND [TermYear] = '2013'
This returns the Number of Termed employees for 2013 as 42.
I have created a table to report from called [dbo.TORateFY] that I have manually entered previous years data into.
Hi Guys, I am generating Transaction Activity report,which should get data by weekly.Report shold look like this.
W1 W2 W3 W4 W5 W6
OKC 79 38 50 76 35 47
NFL 0 0 45 43 33 28
LA
5
12
10
0
0
10
Total 79 38 95 119 68 75
Iam passing 3 parameters @startdate,@enddate,@Market. when i select one Market(OKC/NFL/LA), report generating properly, but when i passing 3 markets values(OKC,NFL,LA), iam getting wrong report,report format is not correct.I used Cross tab for generating this report.Result lam getting like this.
W37 W38 W39 W40 W41 W42 W43 W44 W45
OKC 80
OKC
38
OKC
95
OKC
119
OKC
68
OKC
75
OKC
74
OKC
70
OKC
59
OKC
OKC
LA
And i wrote query like this,
select m.Market_name as Market,'W'+datename(ww,ut.creation_date) as Week,count(ut.transaction_id) as Count from POS.DSC_TRANSACTION_STATUS_VL ts inner join POS.DSC_USER_TRANSACTION ut on ts.transaction_status=ut.transaction_status inner join POS.RETAIL_LOCATION rl on ut.rl_number=rl.rl_number inner join POS.BILLING_MARKETS bm on rl.bm_code=bm.bm_code inner join dbo.Market m on bm.market_id=m.market_id where (ut.creation_date between @startdate and @enddate) and m.market_name IN(@Market) group by m.Market_name,ut.creation_date order by m.Market_name desc
Could you please some one help me to get this correctly.
I want to write a query to generate a report. I have a date column which will be holding all the business dates. No dates of Saturday and Sunday are allowed. What I am looking for is, I want to get the result of every 5th business day of each month. A month could start with any day. I just want only the 5th business day.
I tried modifying it and playing around with the TranslateTranform function but I'm really confused on how it works. I can get the first letter in my text to be upside down but the rest is not showing up. It's like it's being cut off or something. Can anyone point me in the right direction?
Hi, Is there a way to write a stored procedure to get weekly report for 5 weeks?I currently use a stored procedure with 5 select statement to get the result for each week, but I was wondering it there is a way to do that with only one statementthanks
I am trying to write a report that includes different lab values for an account number depending on the test. What I mean is if patient xyz had lab work and procedure number 1012 was ordered I need to include one line for the highest result value and one for the lowest result value. If I have procedure number 1032 I only need a line for the lowest value. I have a list of about 40 lab procedures that some require both highest and lowest, some just the lowest and some the highest. I have played around with CASE, but that hasn't worked for me.
I just created a report builder. I have a main report and i wanted to create a sub report. why i cant or i cant view the path or the folder of my .rdl file to be use as my sub report.
After I use the report builder to create a generic report, how do I actually get that report into the report designer so that I can modify it more effectivly?
The issue that I have now is that the file on the report server is not a .rdl file and if I simply save it as one and then bring it into VS to modify it the code file is a html structure rater than a XML file type.
The T2 tabel has the same ID as T2.ID and T2.CheckBox1, T2.CheckBox2, T2. CheckBox3
Now, I need to check T1 and if T1.CheckBoxCol=1 then set T2.CheckBox1=1 else if T1.CheckBoxCol=2 then set T2.CheckBox2=1 else if T1.CheckBoxCol=3 then set T2.CheckBox3=1
Hello, I have MyTable with ID, IsYesNo fields ID is duplicated so I need perform select on MyTable with the following conditions: 1. Select all the ID distinct where IsYesNo=’Yes’ first 2. Then select all the ID distinct where IsYesNo=’No’ if ID is not in the first selection Combine these two and return the result
I'm sure this is really basic. I've created a simple form that on submittal, I would like the text boxes to be submitted to the database. I have successfully created the Datasource and it connects ok, but what code would i use to submit one text box for email address and a simple submit button. Is there an easy way to do this with VWDE? Thanks
Hi,I have a hotel reservation system.I need to implement Check availability (Room checking) function for the project.But I dont understand how do I start and write SQL for this.Here is table structureTblRooms------------RoomsIDRoomNameNoteunitPriceSeasonalOffersTblReservation----------------ReservationIDArrivalDateDepartureDateArrivalFromFlightNoPurposeOfVisitTblRoomsInventory-------------------RoomsInventoryIDTotalRoomsBookedRooms Should I add more fields or table to implement this or this is enough .please any body can help me
How to write this SQLSelectCommand="SELECT DISTINCT TblOrder.CustomerUID, TblOrder.OrderHiddenID, TblPayment.PaymentAmount, TblPayment.Result, TblOrder.OrderID FROM TblOrder CROSS JOIN TblPayment WHERE (TblOrder.CustomerUID = @IsCustomerID) AND (TblOrder.OrderHiddenID = @IsHiddenID) AND (TblPayment.Result = 'Pending')"How to get latest order id from this.I need to combine it with above sql.I mean i want to select above records but based on max orderid record.such as select latest records from ....above SQL where max orderid
A file with source data will be made available in comma delimitted format to supply a new weekending value, size and demand.
What I will need is first the dts to bring that in. then I am assuming a stored procedure to be run (this is why I am here) to add the data from the comma del. file into the table. If the data EXISTS I would want it to UPDATE the values that are in the dest table but run a calculation first which would be the planutil minus demand then the result to be updated intot the record. if the record from the tab del. file does not exisst in the dest table then insert it.
another words the logic i have in mind read the data from the temp tample (where the file gets imported into) see if the record exists in the live table is it does update it with the calculation of planutil minus demand if not create the new record in the live table. I need it to compare..
Someone help me with some code i thank you kindly in advance
p.s. And good books dedicated to stored procedures??
Many thanks in advance for anyone that can help me write this qry:
To summarise - we have a database that links components to services. Components can have a 1 to many relationship with services. The components are held in a table:
We also have a services table which hold all our various services we own:
dbo.services
svcID svcName 1052 svc1 1053 svc2 1054 svc3
We then have a tbl that shows the links between components and services
dbo.compUses
svcID compID 1052 310 1052 400 1053 122 1256 134
I would like to find out through the qry what components are currently NOT linked to a service. This will allow me to find out what components have no relationships.
This is in SQL2005.
Have I explained this well enough? Any help would be much appreciated!
Hey peeps, fishkake's back, and he's more clueless than ever!
OK, after a few days of wrestling with books and experimenting, I now know all about reading SQL. Well, what that means is I understand Select From Where etc etc.
How do I write data? I have a reference guide, if somebody could give me literally a few high-level commands that are to do with writing data in a similar way to reading it with the SELECT statement, it would be very helpful...
Hi, I have to implement a search functionality. In the various filters for the search, Store Number is one such filter. The user should be able to enter range values for store numbers. Like 1500-1600. So this should filter for all the stores between 1500 and 1600. Similarly, all these also should be valid. 1550,1600 1550 1550 - 1580,6000,8000 etc. I have function which identifies the commas or dashes and seperates out the store number and returns a string like Stores.Storenumber in(1555,1600) Store.StoreNumber between 1555 and 1600 etc... i generate a sql at run time and append this piece and then execute the sql. I have one of the query below.
declare @strQuery varchar(max) declare @strConcat varchar(10) declare @strAppend varchar(max) set @strAppend='' set @StrConcat ='And '
if @IsAdmin is null-- Not a Admin set @StoreId =(select StoreNumber from Stores where Store_Id = @StoreId)
set @strQuery=' Select (Select StoreNumber from Stores where Store_id=d.DestinationId) as StoreNumber, CartonNumber, ActualReceiptDate as [Scan Date], isnull(Sum(QtyShipped),0) as [Total Units], b.BatchNumber from Carton c left outer join CartonDetail Cd on Cd.Carton_Id = c.Carton_ID inner join Batch b on b.General_Id = c.Carton_Id and b.BatchType=''Warehouse'' and b.TranTable=''Carton'' inner join Document d on d.Document_ID = c.document_Id inner join Stores st on st.Store_ID = d.SourceID and st.StoreType =5 inner join Stores on Stores.Store_ID = d.DestinationID inner join Codelist cl on cl.Codelist_Id = c.CartonStatus_ID inner join Codes on Codes.Code_ID = cl.Code_id and Codes.CodeType=''Cartons Status Code'' where not c.cartonNumber is null '
if not (@StoreId) is null begin set @strAppend = @strConcat + '(' + dbo.DecodeStoreNo(@StoreId) + ')' End
if not (@DateFrom) is null and not (@DateTo) is null begin set @strAppend = @strAppend + @strConcat + '(convert(varchar(50),c.ActualReceiptDate,101) between ''' + @DateFrom + ''' and ''' + @DateTo + ''')' End
if not (@CartonNumber) is null Begin set @strAppend = @strAppend + @strConcat + '(c.CartonNumber = ''' + cast(@CartonNumber as varchar) + ''')' End
if not (@Status) is null Begin set @strAppend = @strAppend + @strConcat + '(cl.Codevalue = ''' + @Status + ''')' End
set @strAppend = @strAppend + ' group by d.DestinationId,CartonNumber , ActualReceiptDate , b.batchnumber order by ActualReceiptDate'
set @strQuery = @strQuery +@strAppend execute(@strQuery) This query takes time, if there a little over 1000 records. I wanted to know, if there is any way to optimize this query? or any other way in which the above can be accomplished.
I hope i was able to explain my query fairly. Please let me know otherwise.
