|
Hi, I have 3 tables below. How can I query total payment and prod_principal group by date?
This is what I have, but it results incorrect, why? Help please, anyone? Thanks.
SELECT DAY(shift.date) AS day1,
MONTH(shift.date) AS month1,
YEAR(shift.date) AS year1,
SUM(invoice.payment) AS sum1,
SUM(invoice_details.prod_principal) AS sum2
FROM shift INNER JOIN
invoice ON shift.id = invoice.shift_id INNER JOIN
invoice_details ON invoice.id = invoice_details.invoice_id
GROUP BY DAY(shift.date), MONTH(shift.date), YEAR(shift.date)
ORDER BY year1, month1, day1
shift invoice invoice_details
----- -------- ---------------
id id invoice_id
date shift_id prod_id
payment prod_principal
|
|
|
|
|
Try this:
select DAY(A.date) AS day1,
MONTH(A.date) AS month1,
YEAR(A.date) AS year1,
SUM(A.payment) AS sum1,
SUM(A.prod_principal) AS sum2 From
(select * from shift,invoice,invoice_details
where shift.id=invoice.shift_id and invoice.id=invoice_details.invoice_id
)
A Group by DAY(A.date), MONTH(A.date), YEAR(A.date)
ORDER BY year1, month1, day1
Regards,
Arun Kumar.A
|
|
|
|
|
I tried it, but the result is exactly the same as my original query.
the reason i know it's wrong is because sum1 is not the same as queried from the following:
SELECT DAY(shift.date) AS day1,
MONTH(shift.date) AS month1,
YEAR(shift.date) AS year1,
SUM(invoice.payment) AS sum1
FROM shift, invoice
WHERE shift.id = invoice.shift_id
GROUP BY DAY(shift.date),
MONTH(shift.date),
YEAR(shift.date)
ORDER BY year1, month1, day1
maybe i should specify these:
- there are many invoices in one shift (having the same shift_id)
- there are many invoice_details in one invoice (having the same invoice_id)
Please help, thanks.
|
|
|
|
|
I do not know much about invoice.
Can U provide 4(or minumum) rows of data for each table
and the output U exactly need.
May be some other person, who know the solution but cannot understand Ur need
will help U.
Regards,
Arun Kumar.A
|
|
|
|
|
here are some examples. i hope it's enough. thanks.
shift: id date
-- ----------
1 05/01/2007
2 05/02/2007
invoice: id shift_id payment
-- -------- -------
1 1 100
2 1 250
3 2 375
4 2 210
5 2 333
invoice_details: invoice_id prod_id prod_principal
---------- ------- --------------
1 101 30
1 156 55
2 258 180
3 147 100
3 268 225
4 251 60
4 369 35
4 158 88
5 125 100
5 395 50
5 158 65
5 228 33
5 358 20
the query i want:
day1 month1 year1 sum1 sum2
---- ------ ----- ---- ----
1 5 2007 350 265
2 5 2007 918 776
note: 350 = 100+250 (total payment on 05/01/2007)
918 = 375+210+333 (total payment on 05/02/2007)
265 = 30+55+180 (total prod_principal on 05/01/2007)
776 = 100+...+20 (total prod_principal on 05/02/2007)
|
|
|
|
|
Use this for the moment, untill you find better solution.
SELECT DAY(shift.date) AS day1,
MONTH(shift.date) AS month1,
YEAR(shift.date) AS year1,
C.SumPay AS sum1,
C.SumPri AS Sum2
FROM shift ,
(select shift_id,sum(payment) "SumPay" ,sum(A.sumPrincipal) "SumPri" from invoice B,
(select invoice_id,sum(prod_principal) "sumPrincipal" from invoice_details group by invoice_id) A
where B.id=A.invoice_id group by B.shift_id) C
where shift.id=C.shift_id
Regards,
Arun Kumar.A
|
|
|
|
|
thanks a lot, you've saved me hours of stressing out 
|
|
|
|
|
I want to develop an application that use SQL statment. I am currently learning SQL. I want to know which one is better to use, XPO or SQL. DevExpress does provide a Persistent Object component. I am not an experienced database programmer. I am just learning SQL. Which one will you recommend for me to use between xpo and sql statement.
|
|
|
|
|
XPO is an object/relational mapper. The purpose of these is to abstract away the details of loading data into your object and persisting it, so you can concentrate on OO business logic rather than database plumbing.
But if you're fairly unfamiliar with SQL I would learn that first. Then later on you will gain an appreciation of XPO (or similar object/relational mappers).
Kevin
|
|
|
|
|
I ve written a stored procedure to fetch data from a table. Here i m getting an error "Syntax error converting datetime from character string.". Please guide me.
//////////////////////////////////////////////////////////////////////////
CREATE PROCEDURE ic_get_processeddata
(
@frmDate DateTime,
@toDate DateTime,
@empType varchar(20),
@ein varchar(15)
)
as
declare @sqlStr as varchar(255)
set @sqlStr =''
set @sqlStr='select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate='+@frmDate+' and toDate='+@toDate
if(@empType !='')
begin
set @sqlStr = @sqlStr + ' and empType=' + @empType
end
if(@ein !='')
begin
set @sqlStr = @sqlStr + ' and EIN='+@ein
end
set @sqlStr = @sqlStr + (' order by empName,empType')
print(@sqlStr)
exec @sqlStr
GO
thanx in advance
|
|
|
|
|
Use CAST or CONVERT to convert @frmDate and @toDate to string, before you try to concatenate them.
|
|
|
|
|
Wouldn't this be a better solution:
CREATE PROCEDURE ic_get_processeddata
(
@frmDate DateTime,
@toDate DateTime,
@empType varchar(20),
@ein varchar(15)
)
AS
SELECT EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus
FROM ic_process
WHERE frmDate = @frmDate AND toDate = @toDate
AND (empType = @empType OR @empType = '')
AND (EIN = @ein OR @ein='')
ORDER by empName,empType
This way the @toDate remains a datetime type.
|
|
|
|
|
Hi szukuro
Thanx for your suggestion. But your tric was not working for my solution. Some how i modified my stored procedure. Is this the correct way?? can we optimized it??
CREATE PROCEDURE ic_get_processeddata
(
@frmDate DateTime,
@toDate DateTime,
@empType varchar(20),
@ein varchar(15)
)
as
if(@empType !='') and (@ein !='')
begin
select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate=@frmDate and toDate=@toDate and empType=@empType and ein=@ein order by empType,empName
end
else if (@empType !='') and (@ein ='')
begin
select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate=@frmDate and toDate=@toDate and empType=@empType order by empName,empType
end
else if(@ein !='') and (@empType ='')
begin
select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate=@frmDate and toDate=@toDate and ein=@ein order by empName,empType
end
else
begin
select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate=@frmDate and toDate=@toDate order by empName,empType
end
GO
|
|
|
|
|
Why didn't it work? What was the problem?
|
|
|
|
|
iam working with vb.net using web applications
my webform1.aspx page consists of imagebutton(id=ibmap),button(id=download)
iam displaying a map in imagebutton by using shapefiles data
now i want to give an option to user to download that map
when user clicks on the button map must be downloaded with .gif extension
i wrote the code ,but it is not displaying the map
my code is
Private Sub butdownload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butdownload.Click
dim filepath As String = Server.MapPath("test.gif")
Dim filename As String = Path.GetFileName(filepath)
Response.Clear()
Response.ContentType = "application/octet-stream"
Response.AddHeader("Content-Disposition", "attachment; filename=""" & filename & """")
Response.Flush()
filename = ibMap.ImageUrl
Response.WriteFile(filename)
Response.End()
end sub
plz currect my code
|
|
|
|
|
You need to change the content type:
Response.ContentType = "Image/GIF"
remove this line if you want the image to open in the same page:
Response.AddHeader("Content-Disposition", "attachment; filename=""" & filename & """")
Sample code that worked on my PC:
Dim filename As String
Response.Clear()
Response.ContentType = "Image/GIF"
filename = "C:\imagename.GIF"
Response.WriteFile(filename)
Response.End()
|
|
|
|
|
i have a Query, below, i was to Display results based on the first results
select* from property where substring(func_key,1,5)='GEOSS'
(select * from property where
substring(func_key,6,8) <=2)
1)First Query will Display all records where in the Functional key column there is 'GEOOS' accordingly and it working, and i want to search again from this results.
2)I want to Search all records where Fun_key is <=2, based on the First one.
when i run this Query here is the Error i get.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
(40569 row(s) affected)
Server: Msg 245, Level 16, State 1, Line 2
Syntax error converting the varchar value 'J00' to a column of data type int.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Help
Vuyiswa
|
|
|
|
|
Can U try this?
select* from property where substring(func_key,1,5)='GEOSS'
AND substring(func_key,6,8) <=2)
or
Select * from
(select* from property where substring(func_key,1,5)='GEOSS')A
where substring(func_key,6,8) <=2
Regards,
Arun Kumar.A
|
|
|
|
|
/***Find All the GEOSS records that start from 1 ends with 2****/
select* from property where substring(func_key,1,5)='GEOSS'
AND substring(func_key,6,8) <=2
/*from the Above results i want to Select Distinct the Functional Key*/
select distinct fun_key from "(above results)"
Vuyiswa
|
|
|
|
|
Try this:
select distinct(func_key) from property where substring(func_key,1,5)='GEOSS'
AND substring(func_key,6,8) <=2
Regards,
Arun Kumar.A
|
|
|
|
|
Thank man it work.
Vuyiswa
|
|
|
|
|
Hello
I have a task coming up shortly which involves loading several BILLION rows of data from a text file into SQL 2000.
Wondering what the fastest way to do this is eg SQL BULK INSERT or code it up in C# executable etc.
Does anyone know any tips or tricks?
|
|
|
|
|
From one text file? Is it at least CSV or fixed-width?
The SQL Server bulk copy utility is bcp, but I wouldn't use it for such an extensive import, I'd use a program (written in C#, but that's just me).
I would have the program commit and checkpoint its position in the file once in a while (10000 rows?) so it can restart near where it left off after a failure. With bcp you'd probably have to start all over.
In my database library routines I also have the ability to make a log entry every so many inserts or seconds so I can watch the log and be sure it's actually still progressing, and get a feel for how long it's taking. With bcp you're flying blind.
I do occasionally use bcp for small imports, and it usually involves importing to a sort of "raw" table from whence I can then copy the data to the proper places, especially if data from each row has to be put into several tables.
|
|
|
|
|
Thanks for the info. Data is in several files.
I'll write some C# stuff to analyse it and make sure there are no errors before load. Tried a test on that yesterday with hundred million rows and only took a couple of minutes. Slow point is SQL import.
Commit at intervals is a good idea.
Testing 1 million rows import at the moment (C# and Stored Procedure) and getting about 7 minutes. Thats only five days processing for a billion records...
Might try SqlBulkCopy class next and see whether thats any faster.
Thanks
-- modified at 1:40 Friday 4th May, 2007
SqlBulkCopy - 14 seconds!
|
|
|
|
|
Wel wel,
I always new that stored procedures are faster then runtime build queries, but i bumped into something weird and now i have my doubts.
- Retrieving data with a normal query returns a table length 4000 bytes returned from server.
- Retrieving exactly the same data using a stored procedure returns a table length of 6800 bytes returned from server.
So if bandwidth is important for example on 128000 bits/sec connection, then i suppose i go for the Queries..
is there an expert who can explain this item?
Thx
Kurt
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
