How do I bring below report in one query in SQL ? is there a way to bring both date of data in one query?

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

When I run query on (@Date From '2018-06-01') and( @Date To='2018-06-01')
-----------------------------

SQL

IF OBJECT_ID('TEMPDB..#Temp')IS NOT NULL 
DROP TABLE #Temp

Declare @DateFrom DateTime
Set @DateFrom='2018-06-01'
Declare @DateTo DateTime
Set @DateTo='2018-06-01'

Select @DateFrom Date,
 case when cast(ILE.Quantity as Numeric(19,6))>0 and ILE.[Posting Date] between @DateFrom and @DateTo 
 then cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [In Quantity],
case when cast(ILE.Quantity as Numeric(19,6))<0 and ILE.[Posting Date] between @DateFrom and @DateTo 
then -cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Out Quantity],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE1.[Posting Date]<@DateFrom) as [Opening Qty],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE.[Posting Date]<=@DateTo) as [Closing Qty],ILE.[Posting Date][Posting Date],
ILE.[Item No_] Product,
   ROW_NUMBER() OVER (Partition BY [Item No_] Order by ILE.[Posting Date]) Row_Num
into #Temp
 from [Snowman Logistics Limited$Item Ledger Entry]ILE where ILE.[Posting Date]<=@DateTo and  ILE.[Primary Customer No_]in('MMBP000094 ') 
 
  Select
  Date
  ,Sum([Opening Qty])[Opening Qty],
   Sum([In Quantity])[In Quantity],
   Sum([Out Quantity])[Out Quantity],
   Sum([Closing Qty])[Closing Qty],Sum([Opening Qty]+[In Quantity])[For Billing] 
   from #Temp Group by Date

-----------------Result I get

DateFrom	(Opening)	(In Quantity)	(Out Quantity)	(Closing Qty)	 (For Billing)
2018-06-01 	1556	       802.	             0.            	2358.        1556

--------
Now i again run query on with different dates as

(@Date From '2018-06-02') and( @Date To='2018-06-02')

Similarly when I change more dates Yesterdays closing becomes Opening of date which I put

Date	   Opening Qty	In Quantity	Out Quantity	Closing Qty	For Billing
2018-06-02	2358	   1443	            1095	        2706	2358

Is there as way by which I can get data in one table instead of running query every time. Will I be able to get 1 to 30 in one time Sample below
I want result like this

Date		     Opening	  In	    Out  Closing
01-06-18		1556	  802	     0	    2358
02-06-18		2358	  1443     1095	    2706
03-06-18		2706	   0	     0      2706
04-06-18		2706	   0	    416	    2790
05-06-18		2790	  792       291	    2791

What I have tried:

Kindly review my question lease
Thanks in Advance!!

Posted 29-Jul-18 23:52pm

Gaurav Maggoo

Updated 30-Jul-18 1:45am

CHill60

v3

Add a Solution

Comments

CHill60 30-Jul-18 6:17am

Do have some example data for [Snowman Logistics Limited$Item Ledger Entry?
What data type is [Snowman Logistics Limited$Item Ledger Entry.Quantity?

CHill60 30-Jul-18 6:33am

Why are you not using the LAG function to determine the Previous Closing Qty. There is no need for those date variables

Gaurav Maggoo 30-Jul-18 7:44am

I tried LAG /LEAD function actually is working but it is only moving the values up down logically its not working

Gaurav Maggoo 30-Jul-18 7:46am

I have tried below query (check in comments) by adding days. By using it i am getting right data but i need alternative of this query , as for 30 days query will be too long .

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gaurav Maggoo · Answer 1 · 2018-07-30T01:46:00

SQL

<pre>IF OBJECT_ID('TEMPDB..#Temp')IS NOT NULL 
DROP TABLE #Temp
IF OBJECT_ID('TEMPDB..#Temp1')IS NOT NULL 
DROP TABLE #Temp1

Declare @DateFrom DateTime
Set @DateFrom='2018-06-01'
Declare @DateTo DateTime
Set @DateTo='2018-06-01'
Select *  Into #Temp from(
Select @DateFrom DateFrom,@DateTo DateTo, case when cast(ILE.Quantity as Numeric(19,6))>0 and ILE.[Posting Date] between @DateFrom and @DateTo 
 then cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [In Quantity],
case when cast(ILE.Quantity as Numeric(19,6))<0 and ILE.[Posting Date] between @DateFrom and @DateTo 
then -cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Out Quantity],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE1.[Posting Date]<@DateFrom) as [Opening Qty],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE.[Posting Date]<=@DateTo) as [Closing Qty]
--into #Temp
 from [Snowman Logistics Limited$Item Ledger Entry]ILE where ILE.[Posting Date]<=@DateTo and  ILE.[Primary Customer No_]in('MMBP000094 ') 

 Union all
 Select @DateFrom+1 DateFrom,@DateTo+1 DateTo,
 case when cast(ILE.Quantity as Numeric(19,6))>0 and ILE.[Posting Date] between @DateFrom +1 and @DateTo +1
 then cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [In Quantity],
case when cast(ILE.Quantity as Numeric(19,6))<0 and ILE.[Posting Date] between  @DateFrom +1 and  @DateTo +1
then -cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Out Quantity],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE1.[Posting Date]< @DateFrom +1) as [Opening Qty],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE.[Posting Date]<= @DateTo +1) as [Closing Qty]
 from [Snowman Logistics Limited$Item Ledger Entry]ILE where ILE.[Posting Date]<=@DateTo +1 and  ILE.[Primary Customer No_]in('MMBP000094 ')
  Union all
 Select @DateFrom+2 DateFrom,@DateTo+2 DateTo,
 case when cast(ILE.Quantity as Numeric(19,6))>0 and ILE.[Posting Date] between @DateFrom +2 and @DateTo +2
 then cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [In Quantity],
case when cast(ILE.Quantity as Numeric(19,6))<0 and ILE.[Posting Date] between  @DateFrom +2 and  @DateTo +2
then -cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Out Quantity],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE1.[Posting Date]< @DateFrom +2) as [Opening Qty],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE.[Posting Date]<= @DateTo +2) as [Closing Qty]
 from [Snowman Logistics Limited$Item Ledger Entry]ILE where ILE.[Posting Date]<=@DateTo +2 and  ILE.[Primary Customer No_]in('MMBP000094 ')
 Union all
 Select @DateFrom+3 DateFrom,@DateTo+3 DateTo,
 case when cast(ILE.Quantity as Numeric(19,6))>0 and ILE.[Posting Date] between @DateFrom +3 and @DateTo +3
 then cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [In Quantity],
case when cast(ILE.Quantity as Numeric(19,6))<0 and ILE.[Posting Date] between  @DateFrom +3 and  @DateTo +3
then -cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Out Quantity],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE1.[Posting Date]< @DateFrom +3) as [Opening Qty],
(select SUM(ILE1.[Quantity]) from [Snowman Logistics Limited$Item Ledger Entry] as ILE1 where ILE1.[Entry No_]=ILE.[Entry No_] and ILE1.[Item No_]=ILE.[Item No_]
and ILE1.[Document No_]=ILE.[Document No_] and ILE.[Posting Date]<= @DateTo +3) as [Closing Qty]
 from [Snowman Logistics Limited$Item Ledger Entry]ILE where ILE.[Posting Date]<=@DateTo +3 and  ILE.[Primary Customer No_]in('MMBP000094 ')
 ) asp 
 
  Select
  DateFrom , DateTo
  ,Sum([Opening Qty])[Opening Qty],
   Sum([In Quantity])[In Quantity],
   Sum([Out Quantity])[Out Quantity],
   Sum([Closing Qty])[Closing Qty],Sum([Opening Qty]+[In Quantity])[For Billing] 

   from #Temp Group by   DateFrom , DateTo order by  DateFrom , DateTo