merge two sql statements
-
I have two sql statements for the only reason that I have to look at the same field for 2 different types of image type extensions( .tif and .jpg ) and calculate the total of each image type seperately.
the stmts are in a stored procedure and are executed using sp_executesql
they are as follows :
'SELECT @sz = sum ( CAST (file_size_bytes as bigint))/1024, @CT = count( Image_Filename) from ' +
@filename + ' WHERE Image_Filename LIKE ''%tif'' AND Daily_Date_Transferred BETWEEN '' ' +
@beg_date + ''' and ''' + @end_date + ''''
...some processing with @sz and @CT between these 2 lines.......
'SELECT @sz = sum ( CAST (file_size_bytes as bigint))/1024, @CT = count( Image_Filename) from ' +
@filename + ' WHERE Image_Filename LIKE ''%jpg'' AND Daily_Date_Transferred BETWEEN '' ' +
@beg_date + ''' and ''' + @end_date + ''''
Is there any way I can combine these 2 statements ? I have to have the two image counts in two seperate variables.
-
Do you want 2 records with your 2 values from 1 SELECT
or 4 variables, @SzTIF, @ctTIF, @SzJPG, @ctJPG assigned from 1 SELECT?
Once you understand the BITs, all the pieces come together
-
I would like to have 4 variables from 1 select.
-
Hope this helps (Hope it pastes ok too).
Declare @filename varchar(100), @beg_date varchar(30), @end_date varchar(30)
SELECT @filename = 'TheFilenameTB', @beg_date = '1/1/04', @end_date = '1/5/04'
Print 'SELECT @SzTIF = SzTIF, @ctTIF = ctTIF, @SzJPG = SzJPG, @ctJPG = ctJPG
FROM (
SELECT -- Get TIF Data
sum ( CAST(file_size_bytes as bigint) ) / 1024 as SzTIF,
count(Image_Filename) as ctTIF,
0 as SzJPG,
0 as ctJPG,
from ' + @filename + '
WHERE Image_Filename LIKE ''%tif''
AND Daily_Date_Transferred BETWEEN '' ' + @beg_date + ''' and ''' + @end_date + '''
UNION ALL SELECT -- get JPG data
0 as SzTIF,
0 as ctTIF,
sum ( CAST(file_size_bytes as bigint) ) / 1024 as SzJPG,
count(Image_Filename) as ctJPG,
from ' + @filename + '
WHERE Image_Filename LIKE ''%jpg''
AND Daily_Date_Transferred BETWEEN '' ' + @beg_date + ''' and ''' + @end_date + '''
) DerivedComb'
Once you understand the BITs, all the pieces come together -
ThomasH ,Thanku for your suggestion. I should have pasted the entire code to show you that these statements are executed using sp_executesql in a sproc. My question is , how will I use the above code within the block below ...can I just combine them using the UNION ALL, but i still have to assign the 4 variables.
SET @emySQL = N'SELECT @sz = sum ( CAST (file_size_bytes as bigint))/1024, @CT = count( Image_Filename) from ' +
@filename + ' WHERE Image_Filename LIKE ''%jpg'' AND Daily_Date_Transferred BETWEEN '' ' +
@beg_date + ''' and ''' + @end_date + ''''
Set @myParms = N'@Sz bigint output, @CT int output'
EXEC sp_executesql @emySQL, @myParms, @sz= @Sum_File_Size OUTPUT , @CT = @Total_Jpegs OUTPUT
IF @Sum_File_Size IS NULL
BEGIN
SET @Sum_File_Size = ISNULL( @Sum_File_Size, 0)
END
SET @Sum_File_Size = @Sum_file_size + @var1
SELECT @mySQL = 'INSERT INTO TblLandrec_Statistics(County, Series, Tif_Total, Jpeg_Total, Filesize_Total, Date_Beg, Date_End) VALUES ( ''' +
@county + ''' , ' + @series + ','+ CAST ( @Total_Tifs as varchar)+ ' ,' +
CAST ( @Total_Jpegs as varchar) +' , ' + CAST ( @Sum_File_Size as varchar) + ',''' + @beg_date + ''',''' + @end_date + ''' )'
EXEC ( @mySQL)
-
Sorry I have to ask, How urgent is this for you? Can you wait 'til tomorrow for response from me?
Is the last post the entirety of what is involved? I haven't had time to realy study, but we might be able to get rid much of the dynamic SQL aspects of what you've posted
.Are you "game" to try?
Once you understand the BITs, all the pieces come together -
yes, i can wait till tomorrow and i am willing to try any changes to make the code better and work efficiently.
there is some more to this stored procedure and if you need, i can post the whole sproc here.
i appreciate your interest and help.
-
How do you like this?
SELECT @sz = sum ( CAST (coalesce(file_size_bytes, 0) as bigint))/1024,
@Total_Tifs = sum(case when Image_Filename LIKE '%tif' then 1 else 0 end),
@Total_Jpegs = sum(case when Image_Filename LIKE '%jpg' then 1 else 0 end)
from YourTable
where Daily_Date_Transferred BETWEEN @beg_date and @end_date
Bye
Gabor -
thriveni,
I'm thinking something like (Psuedo code)....
1. Create #Temp table, structured for dynamic insert of step 2
2. Dynamic SQL :Insert into #Temp Select ... From ... Union ... similar to the one I posted earlier
Dynamic needed because source table(s) name is a variable
3. INSERT INTO TblLandrec_Statistics ... SELECT ... from #Temp
4. Cleanup
thriveni, you got it from here?
Once you understand the BITs, all the pieces come together -
First of all , Thanku Gabor, your suggestion worked out very well and took a lot of messiness from SQL stmt.
ThomasH, thanku for your suggestion too, I will try it out right now, though it might be a invlolve more code and a temp table. I have never used a UNION ALL before , so it will help me learn some more SQL.
I really appreciate the time and effort that contributors to this group take to help out others.
-
Just FYI, usually at the start folks like using just UNION, instead of UNION ALL. In your case either will give the same result since the rows returned from each UNIONed SELECT will be distinct.
UNION returns distinct rows
UNION ALL returns all rows.
Having SQL do the extra work of performing a distinct operation makes UNION a slower operation than UNION ALL.
If you know your individual UNIONs will provide distinct rows anyway, or you don't care if result has duplicate rows, always use UNION ALL. It's faster, sometimes much faster!.
UNIONS are a very powerfull part of SQLs query syntax and being familiar with them can give you options to many problems.
Once you understand the BITs, all the pieces come together
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply