How To Write Annual Report Quary
Jun 9, 2007
I want to write a report quary which will dispaly total of amount,hamali,servicetax,other,granttotal for a particular service
for a financial year
i have tried useing cross-tab but it is not working
OUT PUT OF QUARTY SHOULD LIKE
SERVICE NAME DATA 2007-APRIL 2007-MAY
PARCELAMOUNT 29503000
HAMALI 200256
SERVICE TAX 3010
OTHER 2060
GRANT TOT 3200 3326
View 2 Replies
ADVERTISEMENT
Jul 6, 2007
Hi,
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.
Thanks,
Divya
View 16 Replies
View Related
Jan 9, 2008
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())
View 1 Replies
View Related
Mar 14, 2008
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
View 2 Replies
View Related
Feb 4, 2005
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)
thanks advance for answering my question.
View 7 Replies
View Related
Oct 1, 2015
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
annual premium
Insurer name
eg jan child1+ child2+child3 ..
View 5 Replies
View Related
Jul 31, 2013
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.
So the data will look like this.
|Year|HistoricalHC|NumbTermedEmp|
|2010|447 |57 |
|2011|419 |67 |
|2012|420 |51 |
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.
|Year|HistoricalHC|NumbTermedEmp|
|2010|447 |57 |
|2011|419 |67 |
|2012|420 |51 |
I need a script (or possibly a couple of scripts) that will add the numbers every year with the year that the data came from.
(so on Dec 31st this package will run and add |2013|418|42| to the next row, and so on.
View 20 Replies
View Related
Nov 30, 2007
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.
Thanks in advance
Thanks
San
View 2 Replies
View Related
Mar 25, 2014
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.
View 9 Replies
View Related
Dec 27, 2007
I found this thread which allows me to write vertical from the bottom up but I want to flip the text 180 degrees and write it upside down:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=559933&SiteID=1
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?
Thank you in advanced.
View 1 Replies
View Related
Feb 3, 2006
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
View 7 Replies
View Related
Dec 10, 2012
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.
Here is an example of what I'm getting:
Acct Number MR # AdDateDDateCode CDateCTime Result Test
E00000000000 MR00000000 0824110902111012 0830110515 4.5 WBC
E00000000000 MR00000000 0824110902111012 0831110515 4.7 WBC
E00000000000 MR00000000 0824110902111012 0827110525 5.1 WBC
E00000000000 MR00000000 0824110902111012 0826110455 5.3 WBC
E00000000000 MR00000000 0824110902111012 0828110525 5.7 WBC
E00000000000 MR00000000 0824110902111012 0829110500 5.7 WBC
E00000000000 MR00000000 0824110902111012 0901110500 6.6 WBC
E00000000000 MR00000000 0824110902111012 0825110609 6.8 WBC
E00000000000 MR00000000 0824110902111012 0824112050 9.3 WBC
E00000000000 MR00000000 0824110902111032 0830110515 10.1 HB
E00000000000 MR00000000 0824110902111032 0831110515 10.2 HB
E00000000000 MR00000000 0824110902111032 0826110957 10.2 HB
E00000000000 MR00000000 0824110902111032 0827110525 10.4 HB
E00000000000 MR00000000 0824110902111032 0826110455 10.5 HB
E00000000000 MR00000000 0824110902111032 0901110500 10.7 HB
Her is what I need:
Acct Number MR # AdDateDDateCode CDateCTimeResult Test
E00000000000 MR00000000 0824110902111012 0830110515 4.5 WBC
E00000000000 MR00000000 0824110902111012 0824112050 9.3 WBC
E00000000000 MR00000000 0824110902111032 0830110515 10.1 HB
View 7 Replies
View Related
Jun 25, 2015
I have to create a filter on a dataset that accepts a parameter value which in turn,
1. If i check 'NULL' for that parameter, it has to accept all values,
2. If i give value to that parameter, data depends on that value.
My previous developer has written OR(FIND(Parameter: Param1,Param1)<>0, Parameter: Param1 = EMPTY).how to write this formula in my formula builder..
View 2 Replies
View Related
Jul 12, 2006
Hi,
I tried to backup the master key by the following syntax :
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'
but it failed and i got the following message:
Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
NB: I am using the "sa" user to execute this command.
I know that we have a security permission issue , but where and how ?
Regards,
Tarek Ghazali
SQL Server MVP
View 12 Replies
View Related
Jun 16, 2006
SQL server 2005 express reporting problem.
error message:
This feature "remote access to report data sources and/or the report server database" is not supported in this edition of reporting service
I got this error message when I try to connect to database hosted in another PC running SQL server 2000.
Is it true that SQlL server Express can only use Local Database Engine to host the database?
View 5 Replies
View Related
Sep 9, 2015
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.
View 5 Replies
View Related
Jun 30, 2006
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.
Any suggestions would be appreciated. Thanks
View 3 Replies
View Related
May 3, 2007
How do I jump to another report based on a value in my current report. The report that I am jumping from has no parameters, just values.
View 7 Replies
View Related
Jul 27, 2006
I have a table T1
ID Desc
1 aaaa
1 bbbb
1 cccc
2 aaaa
2 cccc
3 cccc
Now, I need to group by ID and make Desc column as follows in a new table
ID Desc
1 aaaa, bbbb, cccc
2 aaaa, cccc
3 cccc
How can I do this?
View 2 Replies
View Related
Jul 28, 2006
I have table T1 with fields T1.ID, T1.CheckBoxCol
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
for each T1.ID
How can I do this?
View 2 Replies
View Related
Jan 1, 2007
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
View 1 Replies
View Related
Apr 29, 2007
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
View 5 Replies
View Related
Aug 29, 2007
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
View 2 Replies
View Related
Sep 14, 2007
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
View 2 Replies
View Related
Jul 7, 2004
I need to compare two column in two different tables but I do not know how to write this query.
Example:
Table1
-------------
EmployeeSSN
EmployeePayRate
Table2
-------------
EmployeeSSN
EmployeePayRate
I need the result set to look like this
Result Set
-----------------------------------------------------
EmployeeSSN T1Rate T2Rate Same?
-----------------------------------------------------
111-11-1111 8.95 8.95 True
222-22-2222 9.95 8.95 False
333-33-3333 8.95 NoMatch False
444-44-4444 NoMatch 8.95 False
and so on....
How do I write this in Transact-SQL also using a function to return the "NoMatch" and True or False statements?
Thanks,
Roger
View 1 Replies
View Related
May 9, 2002
Hey all.. Sorry I have never written a SP in my life.
so this would be a newbie question for sure.
Using sql server 7.
Here is my table field setup with sample values all fields are numeric
except date
weekending size effic dailycap workdays planutil demand
variance
5/172002 8 80 85 5 244
102 142
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??
View 3 Replies
View Related
Dec 1, 2006
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:
dbo.components
compID compName compType
1 srv1 1
2 srv2 1
3 srv3 1
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!
View 1 Replies
View Related
Dec 9, 2005
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...
This site is just making me lazy now...
View 10 Replies
View Related
Apr 15, 2008
Good Afternoon all,
I was wondering how to write the TOP 5 records using Relational Algebra.
Dallr
View 6 Replies
View Related
Nov 29, 2007
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.
Thanks
Renu.
View 1 Replies
View Related
Mar 25, 2008
how to write an UDF and run it
View 2 Replies
View Related
Mar 5, 2007
Hi There,
I have been working in ssis and i have a task to write a log of the all events in daily basis.
I mean writing a log for the entire package for each tasks.
Can some one help me how to do it?
Thanks
View 1 Replies
View Related
Nov 15, 2007
Hello all!
I'm triyng to update a NULL value to another value. I know I can do this, just can't seem to write it out right.
Something like
UPDATE Part_Order
SET Ser = 'NA'
WHERE Ser isnull
I know thats not right, but I know I'm close.
Any hints?
TIA!
Rudy
View 5 Replies
View Related
